Oracle9i: Program With Pl/Sql

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