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

Some programming skills are required to benefit from this class. (We will not teach you the basics of programming such as logic flow and conditional logic.  We will, however, teach you how to do it efficiently in PL/SQL.)

Experience with SQL and SQL*Plus is required.