Oracle PLSQL
  • Course Content 

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