Oracle9i PL/SQL Language Course

The Oracle PL/SQL language provides for programming logic features not contained within SQL.  The grammar, syntax and benefits of using the PL/SQL language within an Oracle database environment are considered in this class.

This course is designed for all Oracle professionals including developers, database administrators, designers and analysts.

Upon completion of this course, the participant will be able to:
> Declaring variables and complex datatypes
> Developing logic within PL/SQL program blocks
> Fetching data from the database into program variables
> Returning program output to users
> Handling program exceptions
> Using explicit and implicit database cursors
> Using the advanced features of nested blocks and subprograms

Language Features
Why use PL/SQL?
Where PL/SQL Program Units may be deployed
The Java Alternative to PL/SQL
Structure of a PL/SQL Program Block
PL/SQL from Interactive Tools
Generating Output
Declare Clause
Datatypes
Declare Example
Special Datatypes
% Type Variable
%ROW TYPE Record
Type Table

Begin Clause
Data Manipulation
CHAR and VARCHAR
Numeric
Date
Boolean
Comparison Operators
Functions
Date Calculations
SELECTINTO
LOGIC Control and Branching
GOTO
LOOP
IF-THEN-ELSE
CASE
Exception
Handling Exception
Special Features
System Defined Exceptions
Using SQLCODE & SQLLERRM
Implicit Cursors
User-Defined EX_INED
PRAGMA EXCEPTION_INIT
Explicit Cursors
Understanding Cursors
Explaining the Cursor Example
Declare Cursor E
OPEN Cursor Example
CLOSE Cursor Example
Including Cursor Parameters
Performing Updates from cursors
Using FORLOOP Cursors

Advanced
Using Nested Blocks
Using Declared Subprograms
Display_Message() Procedure Example
GET_SPOUSE_NAME() Function Example

Oracle9i SQL (RT 262)
Oracle9i Advanced SQL (RT 258)