Print Outline in PDF format

Course Description:

This course will introduce the Oracle10g PL/SQL programming language. Students will control data sharing and locking, learn Error Reporting Functions and Debugging Procedures, 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 10g, PL/SQL or the SQL language.

Prerequisites:

Oracle 10g 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