Oracle9i: Advanced PL/SQL

What you will learn:

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