The Oracle PL/SQL course is designed to introduce Oracle DBAs, software development personnel and database support staff to the primary features and constructs of the PL/SQL Programming Language.
Versions supported 11gR2, 12cR2, 18c, 19c.
To provide the skills needed to write stand-alone programs using the main features of Oracle’s PL/SQL Programming Language.
Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.
The 2 Day Oracle PL/SQL course can be combined with the 3 Day Stored Program Units course, this is equivalent to attending the 5 Day Oracle Program with PL/SQL course.
The delegate will learn and acquire skills as follows:
Writing and running PL/SQL programs from SQL*Plus or SQL Developer
Declaring and using variables and constants
Assigning and manipulating values
Using SQL statements within PL/SQL
Implementing conditional execution and loop constructs
Declaring and using cursors with and without parameters
Updating data with cursors
Trapping and handling errors and exceptions in code
Implementing nested and labelled loops and blocks
Handling PL/SQL records and tables
Using PL/SQL table built-in functions
A working knowledge of SQL*Plus or SQL Developer and Oracle SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite course. Some previous programming experience will also prove advantageous.
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: PL/SQL FUNDAMENTALS
What is PL/SQL?
Variables and Constants
Initialising Variables and Assigning Values
Using SQL Statements in Code
Generating Output to SQL or SQL Developer
Session 2: PROGRAM LOGIC
IF THEN ELSIF ELSE Statements
The Basic Loop Construct
WHILE and FOR Loops
Nested and Labelled Loops
The GOTO Statement
The CONTINUE Statement
Session 3: USING CURSORS
What is a Cursor?
Implicit and Explicit Cursors
Declaring, Opening and Closing Cursors
Using Cursors FOR UPDATE
The Cursor FOR Loop
Session 4: EXCEPTIONS AND NESTED BLOCKS
The EXCEPTION Section
Types of Exception
Handling Named System-Raised Exceptions
Handling Un-named System-Raised Exceptions
User-Declared Exceptions and Application Errors
WHEN OTHERS THEN NULL
Nested and Labelled Blocks
Propagation of Exceptions
Scope of Variables and Cursors
Scope of Goto Statements
Session 5: PL/SQL RECORDS AND INDEX-BY TABLES
Declaring Record Types
Handling PL/SQL Records
Declaring PL/SQL Index-By Tables or Associative Arrays
PL/SQL Table Built-in Functions
Manipulating PL/SQL Tables or Associative Arrays