ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Schedule Overview
• Participant Introductions
• Prerequisites
• Participant Responsibilities

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Connecting via SQL Developer
• Viewing Table Metadata
• Querying Data in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Operating SQL*Plus
• Terminating Sessions
• Essential SQL*Plus Commands
• The SQL*Plus Environment
• Prompts in SQL*Plus
• Retrieving Table Information
• Accessing Help Resources
• Executing SQL Files
• iSQL*Plus and Entity Models
• The ORDERS Table Structure
• The FILM Table Structure
• Course Tables Handout
• SQL Statement Syntax
• SQL*Plus Command Reference

What is PL/SQL?

• Defining PL/SQL
• Benefits of Using PL/SQL
• Understanding Block Structure
• Displaying Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Examples and Style Guides

Variables

• Introduction to Variables
• Data Types
• Assigning Variable Values
• Constants
• Local vs. Global Variables
• %Type Variables
• Substitution Variables
• Comments with &
• The Verify Option
• && Variables
• Defining and Undefining Variables

SELECT Statement

• The SELECT Statement in PL/SQL
• Populating Variables from Queries
• %Rowtype Variables
• The CHR Function
• Independent Study
• PL/SQL Records
• Declaration Examples

Conditional Statement

• IF Statements
• Conditional SELECT Statements
• Independent Study
• Case Statements

Trapping Errors

• Exception Handling
• Handling Internal Errors
• Error Codes and Messages
• Handling No Data Found
• User-Defined Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Strategies
• Independent Study
• Nested Blocks
• Workshop Activity

Iteration - Looping

• Basic Loop Statements
• While Loops
• For Loops
• Goto Statements and Labels

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Examples
• Cursor Declarations
• Variable Declarations
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exiting When %Notfound
• Closing the Cursor
• For Loop Implementation I
• For Loop Implementation II
• Update Examples
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Workshop Activity
• Workshop Solutions

Procedures, Functions and Packages

• The CREATE Statement
• Parameters
• Structure of Procedure Bodies
• Error Display Mechanisms
• Describing Procedures
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Utilizing Output Parameters
• Execution with Output Parameters
• Creating Functions
• Function Examples
• Error Reporting in Functions
• Describing Functions
• Invoking Functions
• Calling Functions in SQL*Plus
• Principles of Modular Programming
• Example Procedures
• Function Invocation Techniques
• Using Functions in IF Statements
• Creating Packages
• Package Examples
• Advantages of Packages
• Public vs. Private Sub-programs
• Error Visibility in Packages
• Describing Packages
• Invoking Packages in SQL*Plus
• Calling Packages from Other Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating Debug Packages
• Invoking Debug Packages
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Workshop Activity

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• The WHEN Restriction
• Selective Triggers with IF
• Error Handling in Triggers
• Commit Operations in Triggers
• Trigger Limitations
• Mutating Trigger Issues
• Identifying Triggers
• Dropping Triggers
• Generating Auto-numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDERS Table Schema
• FILM Table Schema
• EMPLOYEE Table Schema

Dynamic SQL

• Executing SQL within PL/SQL
• Variable Binding
• Concepts of Dynamic SQL
• Native Dynamic SQL
• Executing DDL and DML
• The DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

Using Files

• Working with Text Files
• The UTL_FILE Package
• Writing and Appending to Files
• Reading from Files
• Trigger-Based File Examples
• DBMS_ALERT Packages
• The DBMS_JOB Package

COLLECTIONS

• Variables with %Type
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Collection Values
• Handling Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructors
• Appending to Nested Tables
• VArrays
• Initializing VArrays
• Adding Elements to VArrays
• Multilevel Collections
• Bulk Binding
• Bulk Binding Examples
• Transactional Considerations
• The BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Cursor Variables Overview
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Cursors
• Utilizing Cursor Variables
• Cursor Variable Use Cases