Oracle PL/SQL — A 2 day course

Synopsis

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

Together with the PL/SQL Stored Program Units course, this course is suitable for delegates intending to sit the Oracle examination #1Z0-144: Program with PL/SQL.

Examinations are not included as part of the course and anyone intending to sit examinations should check directly on the Oracle website for up to date requirements.

Objectives

On completion of this course, delegates will have gained knowledge of:

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 labeled loops and blocks
Raising exceptions
Handling PL/SQL records and tables
Using PL/SQL table built-in functions

Suitable For

Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.

Prerequisites

A working knowledge of Oracle's SQL*Plus or SQL Developer products and practical experience in writing SQL statements. This can be gained by attendance of the Oracle SQL course.

Some previous programming experience will also prove advantageous

Publicly scheduled dates, locations, and prices

Select a date to enquire about availability of places. For dates after 3 December 2021, please call 0333 210 0140 or use our contact form to enquire about places and availability.

London — £795 (+VAT)

PL/SQL Fundamentals

What is PL/SQL?
Basic Elements
Variables and Constants
Data Types
Initialising Variables and Assigning Values
Using SQL Statements in Code
Generating Output to SQL or SQL Developer

Program Logic

IF THEN ELSIF ELSE Statements
CASE Statements
The Basic Loop Construct
WHILE and FOR Loops
Nested and Labeled Loops
The GOTO Statement
The CONTINUE Statement

Using Cursors

What is a Cursor?
Implicit and Explicit Cursors
Cursor Operations
Declaring, Opening and Closing Cursors
Fetching Rows
Status Checking
Using Cursors FOR UPDATE
The Cursor FOR Loop
Parameterised Cursors

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

PL/SQL Records and Index-By Tables

Declaring Record Types
Handling PL/SQL Records
Nested Records
Declaring PL/SQL Index-By Tables or Associative Arrays
PL/SQL Table Built-in Functions
Manipulating PL/SQL Tables or Associative Arrays