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 (10g)
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 (10g)
Common PL/SQL Datatypes (10g Enhancements)
Declaring Variables
%TYPE Attribute
PL/SQL Records
%ROWTYPE Attribute
Programmer Defined Records
Variable Scope
Nested Blocks
Functions
Regular Expressions (10g)
IF Statement
CASE Statement and Expression
Simple CASE
Searched CASE
Simple Loops
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 (10g Enhancements)
Bulk DELETE
Bulk INSERT
Bulk UPDATE
Returning into Arrays (10g Enhancements)
|
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
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 (10g)
|
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
DML Trigger Concepts
Trigger Execution
CREATE TRIGGER Syntax
Row Triggers
:OLD and :NEW Reference Variables
Trigger Attributes
Audit Trigger Example
Derived Value Trigger Example
Trigger Restrictions
Table Triggers
Autonomous Transactions
Autonomous Trigger Example
INSTEAD OF Triggers
|
Trace and Tune
Introduction to Tuning PL/SQL
DBMS_PROFILER (10g Enhancements)
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 (10g)
|
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 (10g)
Introduction to UTL_MAIL
Setup for Emailing from the Database
UTL_MAIL Example
|