Description
This course provides participants with the skills necessary to make full use of the procedural extensions to SQL that are supplied with Oracle7. It is a practical hands-on course with more than half of the class time spent on exercises.

Audience
Application designers, developers and programmers who will be moving to an Oracle environment.

Prerequisites
Familiarity with Oracle’s implementation of SQL to the level provided by Arkoa’s Using Oracle7 SQL*Plus course is required.

Hardware and Software
Oracle7 properly installed on any platform, with sufficient logins for the number of students and instructor.

Format

• Presentation
  
• Hands-on
  

Objectives
After completing this course, participants should be able to:

• Describe the rationale behind PL/SQL
• Perform PL/SQL programming
• Define composite datatypes
• Use exception handling
• Create stored procedures and functions
• Implement packages
• Attach triggers to tables

Introduction

• The purpose of PL/SQL
• Block structures and flow control
• IF and LOOP statements
• General comments on PL/SQL
• Advantages of using PL/SQL

Variable and Constants

• Declaring variables
• Declaring constants
• Using variables
• Variable scope

Cursors and Multi-Row SELECT Statements

• Declaring and using cursors
• Implicit cursors

Composite Datatypes

• Tables
• Records

Exception Handling

• Predefined exceptions
• User defined exceptions
• SQLCODE and SQLERRM

Stored Procedures and Functions

• Stored procedures and functions
• Creating SQL accessible functions
• Testing procedures

Packages

• The Customer Maintenance Package
• Using packages
• Dependencies
• Wrapped PL/SQL
• Supplied packages

Triggers

• An UPDATE trigger
• Triggering statement
• Trigger restriction
• Trigger action
• Creating, enabling, and disabling triggers
• Compiling triggers