Oracle^® PL/SQL Programming: Hands-On

Introduction and Overview

PL/SQL fundamentals

• Declaring variables
• Anchoring variables to database definitions
• Flow control constructs

Oracle 10g and 11g PL/SQL features

• PL/Scope in Oracle 11g
• CASE statement process flow
• Referencing PL/SQL records in DML
• Improving performance with native compilation
• Handling regular expressions with Oracle 10g functions
• Compound triggers in Oracle 11g
• Multiset operators for collections

Data Manipulation Techniques

Maintaining data with DML statements

• Employing the RETURNING INTO clause
• Solving the fetch-across-commit problem

Managing data retrieval with cursors

• Implications of explicit and implicit cursors
• Cursor attributes
• Simplifying cursor processing with cursor FOR LOOPs
• Embedding cursor expressions in SELECT statements

Cursor variables

• Strong vs. weak cursor variables
• Passing cursor variables to other programs
• Defining REF CURSORS in packages

Developing Well-Structured and Error-Free Code

Error handling using EXCEPTIONs

• Predefined and user EXCEPTIONs
• Propagation and scope
• "Retrying" problem transactions with EXCEPTION processing

Debugging PL/SQL blocks

• Simplifying testing and debugging with conditional compilation
• Interpreting compiler messages
• Applying structured testing techniques

Achieving Maximum Reusability

Writing stored procedures and functions

• Calling and invoking server-side logic
• Passing input and output parameters
• Implementing an autonomous transaction
• Definer rights vs. invoker rights

Coding user-written functions

• Calling PL/SQL functions from SQL
• Building table-valued functions

Developing safe triggers

• Employing :OLD and :NEW bind variables in row-level triggers
• Implementing complex business rules
• Avoiding unreliable trigger constructs
• Exploiting schema and database triggers

Exploiting Complex Datatypes

Collection types

• PL/SQL tables, nested tables, VARRAYs
• Stepping through dense and nonconsecutive collections

Bulk binding for high performance

• Moving data into and out of PL/SQL blocks
• BULK COLLECT INTO
• FORALL
• BULK cursor attributes
• BULK EXCEPTION handling

Invoking Native Dynamic SQL

Finessing the compiler

• The EXECUTE IMMEDIATE statement
• The RETURNING INTO clause

Types of dynamic SQL

• Building SQL statements during runtime
• Autogenerating standard code

Package Tips and Techniques

• Package structure: SPEC and BODY
• Eliminating dependency problems
• Overloading for polymorphic effects
• Evaluating application frameworks
• Bodiless packages for all application definitions
• Declaring and using persistent global variables