Oracle9i: Program with PL/SQL

What you will learn:

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create procedures, functions, packages, and database triggers.

Students use iSQL*Plus to develop these program units. Students also learn to manage PL/SQL program units and database triggers, to manage dependencies, to manipulate large objects, and to use some of the Oracle-supplied packages.

This course counts towards the Hands-on course requirement for the Oracle 9i Database Administrator and Oracle Database 10g Administrator Certification.

Audience:
Database Administrators
System Analysts

Prerequisites:

Required Prerequisites:

Working with iSQL*Plus
Introduction to Oracle9i: SQL

Course Objectives:
Create, execute, and maintain procedures, functions, packages, and triggers
Manage dependencies
Manipulate large objects
Conditionally control code flow (loops, control structures, and explicit cursors)
Describe Oracle-supplied packages
Handle runtime errors
Manage program constructs
Describe the features and syntax of PL/SQL

Course Topics:
Introduction
Describing PL/SQL
Describing the Use of PL/SQL for the Developer and the Database Administrator
Explaining the Benefits of PL/SQL
PL/SQL program constructs
PL/SQL anonymous block structure
Subprogram block structure
Course objectives and overview

Declaring Variables
Recognizing the Basic PL/SQL Block and Its Sections
Describing the Significance of Variables in PL/SQL
Distinguishing Between PL/SQL and Non-PL/SQL Variables
Declaring Variables and Constants
Executing a PL/SQL Block

Writing Executable Statements
Recognizing the Significance of the Executable Section
Writing Statements Within the Executable Section
Describing the Rules of Nested Blocks
Executing and Testing a PL/SQL Block
Using Coding Conventions

Interacting with the Oracle Server
Writing a Successful SELECT Statement in PL/SQL
Declaring the Data type and Size of a PL/SQL Variable Dynamically
Writing Data Manipulation Language (DML) Statements in PL/SQL
Controlling Transactions in PL/SQL
Determining the Outcome of SQL DML Statements

Writing Control Structures
Identifying the Uses and Types of Control Structures
Constructing an IF Statement
Constructing and Identifying Different Loop Statements
Controlling Block Flow Using Nested Loops and Labels
Using Logic Tables

Working with Composite Data types
Creating User-Defined PL/SQL Records
Creating a PL/SQL Table
Creating a PL/SQL Table of Records
Differentiating Among Records, Tables, and Tables of Records

Writing Explicit Cursors
Using a PL/SQL Record Variable
Distinguishing Between the Implicit and Explicit Cursor
Writing a Cursor FOR Loop

Advanced Explicit Cursor Concepts
Writing a Cursor that Uses Parameters
Determining When a FOR UPDATE Clause in a Cursor Is Required
Using a PL/SQL Table Variable
Using a PL/SQL Table of Records

Handling Exceptions
Defining PL/SQL Exceptions
Recognizing Unhandled Exceptions
Listing and Using Different Types of PL/SQL Exception Handlers
Trapping Unanticipated Errors
Describing the Effect of Exception Propagation in Nested Blocks
Customizing PL/SQL Exception Messages

Creating Procedures
Describe the uses of procedures
Create procedures
Create procedures with arguments
Invoke a procedure
Remove a procedure

Creating Functions
Describe the uses of functions
Create a function
Invoke a function
Remove a function
Differentiate between a procedure and a function

Managing Subprograms
Describe system privilege requirements
Describe object privilege requirements
Query the relevant data dictionary views
Debug subprograms

Creating Packages
Describe packages and list their possible components
Create packages that include public and private subprograms, as well as global and local variables
Invoke objects in a package
Remove packages

More Package Concepts
Write packages that use the overloading feature of PL/SQL
Avoid errors with mutually referential subprograms
Initialize variables with a one-time-only procedure
Specify the purity level of packaged functions
Describe the persistent state of packaged variables, cursors, tables, and records
Query the relevant data dictionary views

Using Oracle-Supplied Packages
Overview of Oracle-supplied packages
View examples of some supplied packages
Write dynamic SQL

Creating Database Triggers
Describe different types of triggers
Describe database triggers and their use
Create database triggers
Describe database trigger firing rules
Drop database triggers

More Trigger Concepts
Create triggers that fire when certain database actions occur
List some of the limitations of database triggers
Determine when to use database triggers or Oracle Server features
Create triggers by using alternative events (notINSERT/UPDATE/DELETE)
Create triggers by using alternative levels (not STATEMENT/ROW)
Query the relevant data dictionary views

Managing Dependencies
Overview of object dependencies
Manage PL/SQL objects for recompilation

Manipulating Large Objects
Compare and contrast LONG/RAW/LONG RAW with large objects (LOBs)
Understand LOBs
Manage binary large file objects (BFILEs)
Use PL/SQL with an LOB
Create a table with LOB columns
Manipulate LOBs
Use DBMS_LOB Oracle-supplied packages
Create a temporary LOB

Related Courses:

Oracle9i: Program with PL/SQL Self-Study CD Course