Oracle PL/SQL Programming

Oracle PL/SQL Programming

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

Virtual Courses

ALL of our courses can be delivered virtually. And our Bath public schedule of courses are now available as live virtual sessions, using the popular Zoom Virtual Classroom and remote labs. Delegates can test their access at: www.zoom.us/test

Public Courses

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...