Audience
This course is for programmers and support staff who wish to gain a comprehensive understanding of how the Oracle Procedural Language / Structured Query Language (PL/SQL) is used.
This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.
Prerequisites
Delegates should have a good knowledge of Oracle SQL. Attending our Oracle SQL course is highly recommended.
Duration
3 days. Hands on.
Course Objectives
This course is highly interactive and consists of theory coupled with plenty of hands on practical sessions. It provides an in-depth look at PL/SQL in order to write Stored Procedures, Stored Functions and Stored Packages.
On completion, delegates will be able to:
- Write PL/SQL Control Structures
- Edit, store & recall PL/SQL blocks
- Create Procedures, Functions and Packages
Course Content
Programming Overview
Local variables
Database derived variables User defined records
User defined variables
Variable assignment
Conditional structures (IF and CASE)
Iteration structures (FOR LOOP and WHILE LOOP)
Exception Handling
User defined
Oracle predefined
Oracle defined
Nested blocks
Cursors
Cursor basics (OPEN FETCH CLOSE)
Cursor FOR LOOP
Cursor data modifications
Implicit cursors
Cursor variables (REF CURSOR)
Stored Procedures & Functions
Advantages
Structure
Input parameters
Output parameters
I/O parameters
Local procedures and functions
Compilation
Packages
Package structure
Package creation, definition and body
Triggers
Trigger roles
Trigger restrictions
Trigger execution
Trigger creation
Single event triggers
Multiple events triggers
View triggers
DDL Triggers
Database level triggers
Built-In Oracle Packages
Review of Oracle package philosophy
Use of an Oracle package within PLSQL
Common application development packages
Native Dynamic SQL (NDS)
DBMS_SQL versus NDS
NDS Commands
EXECUTE IMMEDIATE
OPEN FOR (CURSOR VARIABLES)
Using BIND variables with NDS