Oracle PL/SQL Programming Workshop

Course Overview

• Introduction to PL/SQL    
  • What is PL/SQL?    
  • Why Use PL/SQL?    
  • PL/SQL Program Structure    
  • Anonymous Blocks    
  • Compile Errors
  • Sending Output to SQL*Plus    
  • Introduction to Procedures    
  • Procedure Compile Errors
  • Procedure Compile Warnings
  • Introduction to Functions
  • Introduction to Packages    
  • Querying the Data Dictionary    
  • Introduction to Triggers
  • Tools for PL/SQL Development    
  • Working in SQL*Plus    
• Language Fundamentals    
  • PL/SQL Statements    
  • PL/SQL Symbols
  • Quoting Mechanism
  • Common PL/SQL Datatypes
  • Declaring Variables
  • Assignment
  • Referencing Sequences (11g)
  • %TYPE Attribute
  • PL/SQL Records    
  • %ROWTYPE Attribute
  • Programmer Defined Records    
  • Variable Scope
  • Nested Blocks
  • Functions
  • Regular Expressions (11g Enhancements)
  • IF Statement
  • CASE Statement and Expression
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • CONTINUE Statement (11g)
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example    
  • Array Methods    
• PL/SQL and SQL, Part I Basics
  • SELECT INTO Statement    
  • Implicit Cursor Loops    
  • DML in PL/SQL    
  • Cursor Attributes    
  • Embedding DDL    
• PL/SQL and SQL, Part II Cursors
  • What is a Cursor?    
  • Explicit Cursor Processing    
  • Cursor Attributes    
  • Dynamic Cursors    
  • Dynamic Cursor Example    
  • Variable Scope & Cursors    
  • Cursor Parameters    
  • Cursor Records
  • Cursor FOR LOOP    
  • Referencing the Current Row    
  • FOR UPDATE Example     
• PL/SQL and SQL, Part III Bulk Processing    
  • Bulk Processing    
  • Bulk Collect    
  • Bulk DML – FORALL
  • Using Tables of Records (11g)
  • Bulk DELETE
  • Bulk INSERT
  • Bulk UPDATE    
  • Returning into Arrays
• Stored Procedures    
  • What is a Stored Procedure?    
  • CREATE PROCEDURE Syntax    
  • Simple Example    
  • Review: Compile Errors    
  • Procedure Signatures    
  • Calling Procedures    
  • Procedure Synonyms    
  • Referencing Parameters by Name    
  • Returning Sets: REF CURSOR    
  • Dropping Stored Procedures    
  • Procedure Dependencies    
  • ALTER COMPILE Statement
  • PL/SQL Procedure Privileges
  • Granting Execute Privilege    
  • The Data Dictionary    
• PL/SQL Functions    
  • What is a Function?    
  • CREATE FUNCTION Syntax    
  • Simple Example    
  • Calling Functions
  • Mixing Argument Types in Function Calls (11g)
  • The Data Dictionary    
  • Table Functions
  • Building a Table Function    
  • Using a Table Function    
  • Pipelined Functions    
  • Pipelined Example    
  • Test Data Generator Example
• Error Handling
  • Types of Errors    
  • Runtime Errors    
  • Exception Concepts    
  • Predefined Named Exceptions    
  • EXCEPTION Block Syntax
  • Handling Exceptions    
  • Recovering from Errors    
  • Logging Exceptions
  • RAISE_APPLICATION_ERROR Statement
  • Raising Exceptions    
  • Raising User Exceptions    
  • EXCEPTION_INIT Pragma
  • Using FORMAT_ERROR_BACKTRACE
• PL/SQL Packages
  • Concepts    
  • Package Benefits    
  • Package Benefits Diagram    
  • Package Contents    
  • Package Spécification    
  • Package Body    
  • Package Privileges
  • Calling Packaged Objects    
  • Initialization Code    
  • Session Variables    
  • Package Body Variables    
  • Package Overloading    
  • Compiling Packages    
  • DROP PACKAGE Statement
  • Sample of Oracle Supplied Packages
• DML Triggers
  • Simple (11g) DML Trigger Concepts
  • Trigger Execution    
  • Trigger Ordering (11g)
  • CREATE TRIGGER Syntax    
  • Row Triggers
  • :OLD and :NEW Reference Variables
  • Trigger Attributes    
  • Audit Trigger Example
  • Derived Value Trigger Example
  • Trigger Restrictions    
  • Compound Triggers (11g)
  • Table Triggers
  • Autonomous Transactions    
  • Autonomous Trigger Example
  • INSTEAD OF Triggers    
• Trace and Tune    
  • Introduction to Tuning PL/SQL    
  • DBMS_PROFILER    
  • Profiler Setup    
  • Using Profiler    
  • START_PROFILER and STOP_PROFILER
  • Reporting with PROFSUM
  • Effective Use of Bind Variables
  • Benchmarking with GET_TIME and GET_CPU_TIME
  • Subprogram Inlining (11g)
  • PL/SQL Result Cache (11g)
• File I/O Using UTL_FILE    
  • UTL_FILE Concepts    
  • Setup for UTL_FILE
  • Unix File Permissions
  • Opening Files
  • Closing Files    
  • Reading Files    
  • Writing Files - PUT    
  • Writing Files - PUT_LINE    
  • Writing Files - NEW_LINE    
  • Writing Files - PUTF    
• Sending Email with UTL_MAIL  
  • Introduction to UTL_MAIL
  • Setup for Emailing from the Database
  • UTL_MAIL Example  

Prerequisites

Knowledge of Structured Query Language (SQL) is required.
Any experience with procedural or structured programming is also recommended.