Oracle PL/SQL Programming Part 1

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.
PREREQUISITES: Delegates should be computer literate with a good knowledge of Oracle SQL and SQL*Plus and the standard editor available on their target hardware platform. Attendance of our course Oracle SQL Part 1 is highly recommended.
DURATION: 3 days. Hands on.
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 the SQL*Plus environment in order to write Stored Procedures and Stored Functions. This course is suitable for Oracle 9i/10g/11g users.
On completion, delegates will be able to:
- Write PL/SQL Control Structures.
- Edit, store & recall PL/SQL blocks.
COURSE CONTENT:
PL/SQL Overview
SQL*Plus
Client/server architecture
Internet/intranet architecture
ISO ( Incorporating ANSI and BSI) standards
Quiz

Programming
Local variables
Database derived variables User defined records
User defined variables
Variable assignment
If ... Else
Oracle PL/SQL case statements
Oracle PL/SQL case expressions
Oracle searched case statements
General statement considerations
For loop
While
(repeat until) loop
Goto and labels
Quiz and practical session on programming

Exception Handling
User defined
Oracle predefined
Oracle defined
Sqlcode and sqlerrm
Nested blocks
Quiz practical session on exception handling

Cursors
Introduction
Cursor
Open
Fetch
Close
Cursor for loop
Data modifications
Implicit cursors
Cursor variables
Quiz and practical session on cursors

Stored Procedures and Stored Functions
Advantages
Stored procedures
Input parameters
Output parameters
I/O parameters
Stored functions
Local procedures and functions
Compilation
User defined errors
External programs
Quiz and practical session on stored procedures

Database DML Triggers
Trigger roles
Trigger restrictions
Trigger execution
Trigger creation
Old and new data & update trigger
A delete trigger
An insert trigger
Multiple event triggers
An instead of trigger
Quiz and practical session on database DML triggers

PI07/01