Oracle PL/SQL Programming

PL/SQL fundamentals

• Declaring and anchoring variables to database definitions
• Flow control constructs

Oracle^® 11g and 12c PL/SQL features

• PL/Scope in Oracle 11g
• Returning implicit cursor results from stored procedures in Oracle 12c
• Declaring local functions within SELECT statements in Oracle 12c

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
• 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

Error handling using 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
• Building and applying a test bed
• Leveraging the debugging facilities in SQL Developer

Writing stored procedures and functions

• Calling and invoking server-side logic
• Passing input and output parameters
• Implementing an autonomous transaction

Coding user-written functions

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

Developing safe triggers

• Employing :OLD and :NEW variables
• Avoiding unreliable trigger constructs
• Exploiting schema and database triggers

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 and FORALL
• BULK cursor attributes
• BULK EXCEPTION handling

Finessing the compiler

• The EXECUTE IMMEDIATE statement
• The RETURNING INTO clause

Types of dynamic SQL

• Building SQL statements during runtime
• Autogenerating standard code

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