Introduction and Overview
- PL/SQL fundamentals
- Declaring and anchoring variables to database definitions
- Flow control constructs
- Oracle 12c PL/SQL features
- Returning implicit cursor results from stored procedures in Oracle 12c
- Declaring local functions within SELECT statements in Oracle 12c
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
- 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
- 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
Achieving Maximum Reusability
- 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
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 and 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
- Declaring and using persistent global variables
