course | Oracle® PL - SQL Training

Module 1: Introduction and Overview
 

• 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

 

Module 2: 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

Module 3: 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

 

Module 4: 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

Module 6: 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

Module 7: 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