Oracle PL/SQL

Oracle PL/SQL Course Overview
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.

Skills Gained
The delegates will practise:

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
Raising exceptions
Handling PL/SQL records and tables
Using PL/SQL table built-in functions
Who will the Course Benefit?
Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.

Course Objectives
To provide the skills needed to write stand-alone programs using the main features of Oracle's PL/SQL Programming Language.

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

Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.

Exams are not included as part of the course.

Requirements
A working knowledge of Oracle's SQL*Plus or iSQL*Plus products and practical experience of writing SQL statements. This can be gained by attendance on the pre-requisite course. Some previous programming experience will also prove advantageous.

A working knowledge of SQL*Plus or SQL Developer and Oracle SQL is required. Some previous programming experience will also prove advantageous.

Course Contents - DAY 1
Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: 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
Session 2: PROGRAM LOGIC
IF THEN ELSIF ELSE Statements
CASE 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
Cursor Operations
Declaring, Opening and Closing Cursors
Fetching Rows
Status Checking
Using Cursors FOR UPDATE
The Cursor FOR Loop
Parameterised Cursors

Oracle PL/SQL Training Course
Course Contents - DAY 2
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
Nested Records
Declaring PL/SQL Index-By Tables or Associative Arrays
PL/SQL Table Built-in Functions
Manipulating PL/SQL Tables or Associative Arrays

Pre-Requisite Courses:
Oracle SQL

Follow-On Courses:
Oracle PL/SQL Stored Program Units
Oracle Reports
Oracle Forms - Part I
Oracle Database 10g DBA - Part I
Oracle Database 11g DBA - Part I

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.