Introduction To Oracle 9i/10g PL/SQL Programming Course

Topics covered on the 3 day Introduction To Oracle 9i/10g PL/SQL Programming course

Triggers

• Beyond Declarative Integrity
• Triggers
• Types of Triggers
• Row-Level Triggers
• Cascading Triggers and Mutating Tables
• Generating an Error
• Triggers on Views
• System Triggers
• Maintaining Triggers

PL/SQL Variables and Datatypes

• Declaring Variables
• Datatypes
• Subtypes
• Character Data
• Dates and Timestamps
• Date Intervals
• Anchored Types
• Assignment and Conversions
• Selecting into a Variable

PL/SQL Syntax and Logic

• PL/SQL Blocks and Programs
• Conditional Statements – IF/THEN
• Conditional Statements – CASE
• Comments and Labels
• Loops
• WHILE and FOR Loops

Stored Procedures and Functions

• Stored Subprograms
• Procedures and Functions
• Creating a Stored Procedure
• Calling a Stored Procedure
• Passing Parameters and Default Arguments
• Parameter Modes
• Creating a Stored Function
• Calling a Stored Function
• Stored Functions and SQL
• Local Procedures and Functions

Exception Handling

• SQLCODE and SQLERRM
• Exception Handlers
• Nesting Blocks
• Scope and Name Resolution
• User-Defined Exceptions
• Compile-Time Warnings

Records and Collections

• Record Variables
• Using the %ROWTYPE Attribute
• VARRAY and Nested TABLE Collections
• Using Nested TABLEs
• Using VARRAYs
• Associative Array Collections
• Collection Methods
• Iterating Through Collections
• FORALL Statement
• BULK COLLECT Clause

Cursors

• Multi-Row Queries
• Declaring and Opening Cursors
• Fetching Rows
• Closing Cursors
• The Cursor FOR Loop
• FOR UPDATE Cursors
• Cursor Parameters
• The Implicit (SQL) Cursor

Using Packages

• Packages
• Oracle-Supplied Packages
• The DBMS_OUTPUT Package
• The DBMS_UTILITY Package
• The UTL_FILE Package
• The DBMS_METADATA Package
• XML Packages
• Networking Packages
• Other Supplied Packages

Creating Packages

• Structure of a Package
• The Package Interface and Implementation
• Package Variables
• Overloading Package Functions and Procedures
• Named Parameter Notation
• REF CURSOR Variables

Working with LOBs

• Large Object Types
• Oracle Directories
• LOB Locators
• Internal LOBs
• External LOBs
• Temporary LOBs
• The DBMS_LOB Package

Maintaining PL/SQL Code

• Privileges for Stored Programs
• PL/SQL Stored Program Compilation
• The PL/SQL Execution Environment
• Dependencies and Validation
• Maintaining Stored Programs

Appendix A - Dynamic SQL

• Generating SQL at Runtime
• Native Dynamic SQL vs. DBMS_SQL Package
• The EXECUTE IMMEDIATE Statement
• Using Bind Variables
• Multi-row Dynamic Queries
• Bulk Operations with Dynamic SQL
• Using DBMS_SQL
• DBMS_SQL Subprograms