PL/SQL

Prerequisites:

A working knowledge of SQL*Plus. This can be gained by attendance on SQL*Plus (OSP). Some previous programming experience would prove advantageous.

Course Description:

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 and enhance their productivity in manipulating data.

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

This course includes the following modules:

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 Labelled 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