Course Description:

This course will introduce the Oracle9i PL/SQL programming language. Students will control data sharing and locking, develop an understanding of multi-user and concurrent transactions and develop database triggers, stored procedures and functions.

You can learn …

• The PL/SQL Environment and Program Structure

• Update, Insert and Delete Statements

• Variable Scope Rules

• Error Report Functions

• Debugging Procedures

• Logical Comparisons

• To define and Execute Procedures, and To define Functions, store and Test a Function

• To create Package Specifications

• To store PL/SQL Program Units

Who Needs to Attend:

Application Developers and Database Administrators who need a fundamental understanding of Oracle 9i, PL/SQL or the SQL language.

Prerequisites:

Oracle 9i Foundation: SQL Basics & SQL*PLUS or equivalent experience.

Course Labs:

1. Using PL/SQL to create an Anonymous Block

2. PL/SQL Basics

3. Selecting and Updating Database Data

4. Using Explicit Cursors

5. Handling Exceptions

6. Creating a Procedure

7. Creating a Function

8. Creating a Package

9. Creating a Database Trigger

Course Content:

PL/SQL BASICS

• SQL and PL/SQL

• PL/SQL anonymous block structure

• PL/SQL Datatypes

• Variable declarations

• Object naming rules

• Executable Statements

• Repetition control

• LOOP statements – Basic LOOP, WHILE, FOR

• CASE Expressions and Statements

• Basic Coding Standards

• SQL*PLUS development environment

WORKING WITH DATABASE DATA

• Selecting single and multiple rows

• Referencing other variables or a database column’s datatypes

• Modifying database data (DML)

• Implicit cursor attributes

• Transaction control commands

• Autonomous Transactions

• Selecting data into PL/SQL variables

• Declaring explicit cursors

• Referencing other structures datatypes

• Using the cursor FOR LOOP

• Displaying messages with DBMS_OUTPUT

• FOR UPDATE / WHERE CURRENT OF

EXCEPTION HANDLING

• Writing an exception handler section

• Handling predefined exceptions

• Handling user-defined exceptions

• Controlling exception processing

• Using RAISE_APPLICATION_ERROR

• Preventing unhandled exceptions

• Using PRAGMA EXCEPTION_INIT

• Using SQLCODE and SQLERRM

STORED PL/SQL OBJECTS

• Introduction to procedures and functions stored in the database

• Creating stored PL/SQL objects

• Passing values with parameters

• Formal vs. actual parameters

• Parameter types – IN / OUT / IN OUT

• Creating procedures, functions, packages, package bodies

• Introduction to database triggers

• Viewing and recompiling stored code