ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Goals and Objectives
• Agenda and Timeline
• Participant Introductions
• Prerequisites Review
• Participant Responsibilities

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Establishing Connections in SQL Developer
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Navigating SQL*Plus Interface
• Terminating a Session
• Essential SQL*Plus Commands
• SQL*Plus Environment Configuration
• Understanding the SQL*Plus Prompt
• Retrieving Table Information
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• Introduction to ORDERS Tables
• Introduction to FILM Tables
• Review of Course Tables
• SQL Statement Structure
• SQL*Plus Command Reference

What is PL/SQL?

• Definition of PL/SQL
• Benefits of Using PL/SQL
• Understanding Block Structure
• Outputting Messages
• Reviewing Sample Code
• Configuring SERVEROUTPUT
• Update Example and Coding Standards

Variables

• Variable Fundamentals
• Data Types Overview
• Assigning Values to Variables
• Working with Constants
• Scope: Local vs. Global Variables
• Using %Type Variables
• Substitution Variables
• Handling Comments with Special Characters
• Verification Options
• Advanced Substitution Variables (&&)
• DEFINE and UNDEFINE Commands

SELECT Statement

• Basic SELECT Syntax
• Populating Variables from Queries
• Using %Rowtype Variables
• The CHR Function
• Independent Practice
• Defining PL/SQL Records
• Sample Record Declarations

Conditional Logic

• Using IF Statements
• Integrating SELECT Statements
• Independent Practice
• Implementing CASE Statements

Exception Handling

• Understanding Exceptions
• Handling Internal Errors
• Retrieving Error Codes and Messages
• Catching No Data Found Exceptions
• Raising User-Defined Exceptions
• Raising Application Errors
• Catching Unhandled Exceptions
• Utilizing PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Independent Practice
• Working with Nested Blocks
• Practical Workshop

Iteration - Looping

• Basic LOOP Structures
• While Loops
• For Loops
• Goto Statements and Labels

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Demonstration
• Declaring a Cursor
• Declaring Associated Variables
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Looping Until End of Data
• Closing a Cursor
• For Loop Application I
• For Loop Application II
• Update Operations with Cursors
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing Changes with Cursors
• Validation Scenario I
• Validation Scenario II
• Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions and Packages

• CREATE Statement Syntax
• Managing Parameters
• Constructing Procedure Bodies
• Debugging and Error Display
• Inspecting Procedure Definitions
• Invoking Procedures
• Executing Procedures via SQL*Plus
• Working with Output Parameters
• Calling with Output Parameters
• Developing Functions
• Function Demonstration
• Debugging Functions
• Inspecting Function Definitions
• Invoking Functions
• Executing Functions via SQL*Plus
• Modular Programming Concepts
• Sample Procedure Implementation
• Function Invocation Review
• Using Functions in Conditional Logic
• Developing Packages
• Package Demonstration
• Advantages of Using Packages
• Public vs. Private Subprograms
• Debugging Packages
• Inspecting Package Definitions
• Executing Packages via SQL*Plus
• Calling Packages from Within Subprograms
• Dropping Subprograms
• Locating Subprograms
• Creating Debugging Packages
• Invoking Debugging Packages
• Positional vs. Named Notation
• Setting Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation Basics
• Statement-Level Triggers
• Row-Level Triggers
• Using WHEN Clauses
• Conditional Triggers with IF
• Debugging Triggers
• Committing Transactions in Triggers
• Trigger Limitations
• Mutating Table Triggers
• Locating Triggers
• Dropping Triggers
• Generating Auto-Numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDERS Table Structure
• FILM Table Structure
• EMPLOYEE Table Structure

Dynamic SQL

• Embedding SQL in PL/SQL
• Binding Variables
• Dynamic SQL Concepts
• Native Dynamic SQL
• Executing DDL and DML
• Using the DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT in Procedures

File Operations

• Reading and Writing Text Files
• Using the UTL_FILE Package
• Writing and Appending Examples
• Reading File Examples
• Trigger-Based File Examples
• Using the DBMS_ALERT Package
• Using the DBMS_JOB Package

Collections

• Reviewing %Type Variables
• Record Variables
• Collection Data Types
• Associative Arrays (Index-By Tables)
• Assigning Collection Values
• Handling Non-Existent Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructor Functions
• Extending Nested Tables
• VARRAYs
• Initializing VARRAYs
• Extending VARRAYs
• Multilevel Collections
• Bulk Binding Operations
• Bulk Binding Demonstration
• Considerations for Transactions
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Ref Cursor Concepts
• Defining REF CURSOR Types
• Declaring Ref Cursor Variables
• Strong vs. Weak Typing
• Working with Ref Cursors
• Ref Cursor Demonstrations