Topic summary

• Introduction to PL/SQL
  • What is PL/SQL?
  • Why Use PL/SQL?
  • PL/SQL Program Structure
  • Anonymous Blocks
  • Compile Errors
  • Output to SQL*Plus
  • Procedures
  • Procedure Compile Errors
  • Functions
  • Packages
  • Data Dictionary
  • Triggers
  • Tools for Development
  • Working in SQL*Plus
  • Introduction to PL/SQL Workshops
• Language Fundamentals
  • Statements
  • PL/SQL Symbols
  • Common PL/SQL Datatypes
  • Declaring Variables
  • %TYPE
  • PL/SQL Records
  • Records: %ROWTYPE
  • Programmer Defined Records
  • Language Fundamentals Workshop A
  • Variable Scope
  • Nested Blocks
  • Functions
  • IF Statement
  • CASE
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example
  • Array Methods
  • Language Fundamentals Workshop B
• PL/SQL and SQL: Basics
  • SELECT INTO Statement
  • Implicit Cursor Loops
  • Basics Workshop A (Cursor Loop)
  • DML in PL/SQL
  • Cursor Attributes
  • Embedding DDL
  • Basics Workshop B (NDS)
• PL/SQL and SQL: Cursors
  • What is a Cursor?
  • Cursor Processing
  • Cursor Attributes
  • Cursors Workshop A
  • Dynamic Cursors
  • Dynamic Cursor Example
  • Variable Scope & Cursors
  • Problem Example
  • Cursor Parameters
  • Cursor Record
  • Cursor FOR LOOP
  • FOR LOOP Example
  • Cursors Optional Workshop B
  • Referencing the Current Row
  • FOR UPDATE Example
  • Cursors Optional Workshop C
• PL/SQL and SQL: Bulk Processing
  • Bulk Processing
  • Bulk Collect
  • Bulk Collect: SELECT INTO
  • Bulk Collect: Multiple Arrays
  • Bulk Collect: Array of Records
  • Bulk Collect: FETCH with LIMIT
  • Bulk Processing Workshop A
  • Bulk DELETE
  • Bulk INSERT
  • Returning into Arrays
  • Summary
  • Bulk Processing Workshop B
• Procedures
  • What is a Procedure?
  • Abbreviated Syntax
  • Simple Example
  • Review: Compile Errors
  • Procedure Signatures
  • Calling Procedures
  • Procedure Synonyms
  • Referencing Parameters by Name
  • Returning Sets: REF CURSOR
  • Dropping Procedures
  • Dependencies
  • ALTER COMPILE
  • Privileges
  • Execute Privilege
  • The Data Dictionary
  • Procedures Workshop
• Functions
  • What is a Function?
  • Abbreviated Syntax
  • Simple Example
  • Calling Functions
  • The Data Dictionary
  • Functions Workshop A
  • Table Functions
  • Building a Table Function
  • Using a Table Function
  • Pipelined Functions
  • Pipelined Example
  • Test Data Generator
  • Functions Workshop B
•  Error Handling
  • Types of Errors
  • Runtime Errors
  • Exception Concepts
  • Predefined Named Exceptions
  • Syntax
  • Handling Exceptions
  • Recovering from Errors
  • Logging Exceptions
  • RAISE_APPLICATION_ERROR
  • RAE Example
  • Error Handling Workshop
  • Raising Exceptions
  • Raising User Exceptions
  • EXCEPTION_INIT
  • Summary
• Packages
  • Concepts
  • Package Benefits
  • Package Benefits Diagram
  • Package Contents
  • Package Specification Syntax
  • Package Specification Example
  • Package Body Syntax
  • Package Body Example
  • Privileges
  • Calling Packaged Objects
  • Initialization Code
  • Session Variables
  • Package Body Variables
  • Packages Workshop A
  • Package Overloading
  • Overloading Example
  • Packages Workshop B
  • Compiling Packages
  • DROP Package
  • Packages Workshop C
  • Supplied Packages
  • Summary
• Triggers
  • Trigger Concepts
  • Trigger Execution
  • Create Trigger Syntax
  • Row Trigger
  • :OLD Reference Variable
  • :NEW Reference Variable
  • Trigger Attributes
  • Audit Trigger
  • Derived Value Trigger
  • Restrictions
  • Security Privileges
  • Triggers Workshop A
  • Statement Level Trigger
  • Triggers Workshop B
  • Autonomous Transactions
  • Autonomous Trigger
  • Triggers Workshop C
  • INSTEAD OF Triggers
  • Sybase / MS SQL Server vs. Oracle Triggers
  • Triggers Workshop D
• File I/O Using UTL_FILE
  • UTL_FILE Concepts
  • Setup
  • Unix File Permissions
  • Opening, closing, reading and Writing Files
  • Writing Files - PUT
  • Writing Files - PUT_LINE
  • Writing Files - NEW_LINE
  • Writing Files - PUTF
  • Writing Files Example
  • UTL_FILE Workshop
• Using DBMS_JOB
  • What is DBMS_JOB?
  • Setup
  • DBMS_JOB.SUBMIT
  • SUBMIT Example
  • Running a Job
  • Cleaning Up Broken Jobs
  • Removing a Job
  • Changing Job Parameters
  • Viewing Job Information
  • Using DBMS_JOB Workshop
• Profile and Tune
  • Introduction to Tuning PL/SQL
  • DBMS_PROFILER
  • Profiler Setup
  • Using Profiler
  • Testing
  • Reporting
  • Profile & Tune PLSQL Workshop
• Optional Appendices: Dynamic SQL
  • What is Dynamic SQL?
  • NDS vs. DBMS_SQL
  • NDS Statement Summary
  • NDS Single-Row Query Syntax
  • NDS Single-Row INSERT Example
  • NDS Multi-Row Query Syntax
  • NDS Multi-Row Query Example
  • NDS PL/SQL Example
  • DBMS_SQL Procedures Summary
  • DBMS_SQL Example
  • Privileges
  • Dynamic SQL Workshop