Introduction to Oracle 12c PL/SQL Programming Training in Kewanee, Illinois

1. Course Introduction
Course Objectives
Course Overview
Using the Workbook
Suggested References
Sample Database

2. Triggers
Beyond Declarative Integrity
Triggers
Types of Triggers
Trigger Sequencing
Row-Level Triggers
Trigger Predicates
Trigger Conditions
Using SEQUENCEs
Cascading Triggers
and Mutating Tables
Generating an Error
Maintaining Triggers

3. PL/SQL Variables and Datatypes
Anonymous Blocks
Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and
Conversions
Selecting into a Variable
Returning into a Variable

4. PL/SQL Syntax and Logic
Conditional Statements–IF/THEN
Conditional Statements–CASE
Comments and Labels
Loops
WHILE and FOR Loops
SQL in PL/SQL
Local Procedures and Functions

5. Stored Procedures and Functions
Stored Subprograms
Creating a Stored Procedure
Procedure Calls and Parameters
Parameter Modes
Named Parameter Notation

6. Exception Handling
SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
Declaring and Raising Named
Exceptions
User-Defined Exceptions

7. Records, Collections, and User-Defined Types
Record Variables
Using the %ROWTYPE Attribute
User-Defined Object Types
VARRAY and Nested TABLE
Collections
Using Nested TABLEs
Using VARRAYs
Collections in Database Tables
Associative Array Collections
Collection Methods
Iterating Through Collections

8.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

9. Bulk Operations
Bulk Binding
BULK COLLECT Clause
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors

10. 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

11. Creating Packages
Structure of a Package
The Package Interface and
Implementation
Package Variables and Package
State
Overloading Package Functions and Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables

12. Working with LOBs
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage and
SECUREFILEs
Externa
l LOBs
Temporary LOBs
The DBMS_LOB Package

13. Maintaining PL/SQL Code
Privileges for Stored Programs
Data Dictionary
PL/SQL Stored Program
Compilation
Conditional Compilation
Compile-Time Warnings
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

Appendix B: PL/SQL Versions, Datatypes and Language Limits