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.
Delegates should have a good knowledge of Oracle SQL. Attending our Oracle SQL course is highly recommended.
3 days. Hands on.
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
Database derived variables User defined records
User defined variables
Conditional structures (IF and CASE)
Iteration structures (FOR LOOP and WHILE LOOP)
Cursor basics (OPEN FETCH CLOSE)
Cursor FOR LOOP
Cursor data modifications
Cursor variables (REF CURSOR)
Stored Procedures & Functions
Local procedures and functions
Package creation, definition and body
Single event triggers
Multiple events 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
OPEN FOR (CURSOR VARIABLES)
Using BIND variables with NDS