ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Goals and Objectives
• Schedule Overview
• Participant Introductions
• Required Prerequisites
• Participant Responsibilities

SQL Tools

• Learning Objectives
• SQL Developer Environment
• Establishing Connections in SQL Developer
• Inspecting Table Information
• Executing Queries with SQL Developer
• Logging in via SQL*Plus
• Direct Connection Methods
• Using the SQL*Plus Interface
• Terminating a Session
• SQL*Plus Command Reference
• SQL*Plus Configuration Environment
• Understanding the SQL*Plus Prompt
• Retrieving Table Metadata
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Table Structure
• The FILM Table Structure
• Course Table Reference Handout
• SQL Statement Syntax Rules
• Advanced SQL*Plus Commands

Understanding PL/SQL

• Definition of PL/SQL
• Benefits of Using PL/SQL
• PL/SQL Block Architecture
• Outputting Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Examples and Coding Style Guide

Variables

• Variable Basics
• Data Types in PL/SQL
• Assigning Values to Variables
• Working with Constants
• Local vs. Global Variables
• Using %Type Variables
• Substitution Variables
• Handling Comments with &
• The Verify Option
• Using && Variables
• DEFINE and UNDEFINE Commands

SELECT Statement

• Using the SELECT Statement
• Populating Variables from Data
• %Rowtype Variables
• The CHR Function
• Self-Study Section
• PL/SQL Record Types
• Example Record Declarations

Conditional Statements

• Using the IF Statement
• Integration with SELECT Statements
• Self-Study Section
• The CASE Statement

Error Handling

• Exception Management
• Handling Internal Errors
• Retrieving Error Codes and Messages
• Handling No Data Found Exceptions
• Defining User Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Self-Study Section
• Nested PL/SQL Blocks
• Practical Workshop

Iteration and Loops

• The Basic Loop Statement
• While Loops
• For Loops
• Using Goto Statements and Labels

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Examples
• Cursor Declaration
• Variable Declaration for Cursors
• Opening Cursors and Fetching the First Row
• Fetching Subsequent Rows
• Exiting via %Notfound
• Closing the Cursor
• For Loop Implementation I
• For Loop Implementation II
• Update Examples
• Using FOR UPDATE
• Using FOR UPDATE OF
• Using WHERE CURRENT OF
• Committing Changes with Cursors
• Validation Example I
• Validation Example II
• Passing Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions, and Packages

• The CREATE Statement
• Managing Parameters
• Writing the Procedure Body
• Error Reporting
• Describing a Procedure
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Utilizing Output Parameters
• Handling Output Parameters in Calls
• Creating Functions
• Function Examples
• Error Reporting in Functions
• Describing a Function
• Invoking Functions
• Calling Functions in SQL*Plus
• Principles of Modular Programming
• Example Procedure
• Function Invocation Strategies
• Using Functions within IF Statements
• Building Packages
• Package Examples
• Advantages of Using Packages
• Public vs. Private Subprograms
• Error Reporting in Packages
• Describing a Package
• Calling Packages in SQL*Plus
• Calling Packages from Subprograms
• Dropping Subprograms
• Locating Subprograms
• Creating Debug Packages
• Invoking Debug Packages
• Positional and Named Notation
• Parameter Default Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Database Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Restrictions
• Selective Triggers using IF
• Error Reporting
• Committing within Triggers
• Trigger Limitations
• Mutating Trigger Issues
• Locating Triggers
• Dropping Triggers
• Generating Auto-Increment Numbers
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• SQL Execution within PL/SQL
• Binding Variables
• Dynamic SQL Concepts
• Native Dynamic SQL
• Handling DDL and DML
• The DBMS_SQL Package
• Dynamic SQL for SELECT Queries
• Dynamic SQL in SELECT Procedures

File Operations

• Working with Text Files
• The UTL_FILE Package
• Write and Append Examples
• File Reading Examples
• Trigger-Based File Examples
• The DBMS_ALERT Package
• The DBMS_JOB Package

Collections

• %Type Variables in Collections
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Values to Collections
• Handling Nonexistent Elements
• Nested Tables
• Initialising Nested Tables
• Using Constructors
• Adding Items to Nested Tables
• Varrays
• Varray Initialisation
• Inserting Elements into Varrays
• Multilevel Collections
• Bulk Binding
• Bulk Binding Examples
• Transactional Considerations
• The BULK COLLECT Clause
• Using RETURNING INTO

Ref Cursors

• Cursor Variables Explained
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Cursors
• Working with Cursor Variables
• Cursor Variable Examples