Oracle9i PL/SQL Programming

Course Length: 5 days, $250

This class will focus on developing PL/SQL programming skills. Students will learn the fundamentals of the PL/SQL programming language. Stored procedures, functions, packages and triggers implement complex business rules in Oracle. Students will learn programming, management and security issues of working with PL/SQL program units. The Oracle9 release has greatly enhanced the feature/functionality of PL/SQL. Numerous exercises and labs will introduce students to the new PL/SQL features in Oracle9.

Audience: Database administrators, application developers, and system administrators.

Prerequisites: A solid understanding of SQL and 3GL programming is required.

Introduction to PL/SQL Programming

What is PL/SQL?
Features of PL/SQL
The Block Structure
Language Rules
Using PL/SQL
Executing PL/SQL from SQL*Plus
Variables and Constants
Identifiers
Customizing SQL*Plus for PL/SQL Scripts
SELECT INTO Statement
Using Bind Variables
Interactive PL/SQL Programs
Oracle Datatypes are supported in PL/SQL
Numeric Datatypes
Datatype Conversion
Implicit and Explicit Conversion
%TYPE and %ROWTYPE
Subtypes
Subtype Declarations
Labels
SQL and PL/SQL Reserved Words
Direction of PL/SQL
External Routines and Java Stored Procedures

Control Flow

Operators
Built-In Functions
Handling Exceptions
Working with Exceptions
SQLCODE and SQLERRM Functions
Predefined PL/SQL Exceptions
User-Defined Exceptions
Working with Exceptions
The Procedure Raise_Application_Error
IF Statements
Generic LOOP
Working with NULL
LOOP Labels
FOR LOOP
WHILE LOOP
GOTO Statement

Working with Cursors

Working with Cursors
Implicit Cursors and Attributes
Explicit Cursors
Explicit Cursor Attributes
Using Subqueries with Cursors
Using Cursor FOR LOOPs
Using %ROWTYPE with Cursors
Using Aliases and Subqueries
Using Parameters with Explicit Cursors
Modifying Data using Explicit Cursors

Procedures and Functions

Working with Subprograms
Named Subprograms
Software Evolution
Stored Procedures
Parameters
NOCOPY Compiler Directive
Parameter Default Values
Positional and Named Notation
Executing a Stored Procedure
Compiling and Debugging Stored Procedures
Compiling a Stored Procedure
Stored Function Syntax
Executing a Function from SQL*Plus
The RETURN Statement

Packages

Advantages of Packages
What can a Package Contain?
Components of a Package
Package Specification
Package Body
Defining Package Components
Package Scope
Public Scope
Private Scope
Accessing Package Components
Privileges
Managing Side Effects
Pragma RESTRICT_REFERENCES
Determining the Purity of a Routine
Adding TRUST to
RESTRICT_REFERENCES
Defining Subprograms within a Subprogram
Forward Declarations
Overloading
Restrictions on Overloading
Restrictions on Packaged Variables

Oracle Supplied Packages

Table Storage Statistics
DBMS_STATS
Copying Statistics to Different Databases
DBMS_ALERT Procedures
DBMS_AQ and DBMS_AQADM
DBMS_DDL
DBMS_DEBUG
DBMS_PIPE
DBMS_OUTPUT
DBMS_LOGMNR
DBMS_TRACE
DBMS_SQL
DBMS_STANDARD
DBMS_SESSION
DBMS_LOB
DBMS_JOB
The Run() Procedure
DBMS_LOCK
UTL Packages

Triggers

Triggers
Purpose of Triggers
Trigger Guidelines
Table Triggers
Firing Order of Table Triggers
Table Trigger Syntax
ALTER and DROP Trigger Commands
Boolean Functions
Reference Variables for ROW Triggers
ROW Trigger Examples
Information about Triggers
WHEN Clause
Restrictions
Database Triggers
Event Functions
Resource Manager Events
Client Events
Schema Triggers
INSTEAD-OF Triggers

Advanced Features of Procedures and Functions

Autonomous Transactions
Executing Autonomous Transactions
Advantages and Restrictions
Defining an Autonomous Block
Packages and Triggers
Autonomous Triggers
Autonomous PL/SQL Blocks
Transaction Visibility
Definer's Rights
Invoker's Rights
Using the AUTHID Clause
Resolving External References
PL/SQL Wrappers
External Procedures
External Procedure Architecture
Alias Library Object
Call Specifications
Mapping Procedure to an External Procedure
Mapping Function to an External Procedure

Introduction to Object Technology

Software Development
Issues with 3GL Development
Issues with Relational Database Development
Object-Relational Development
Goals of Object-Relational Development
Functional versus Object Hierarchies
Software Evolution
Type Table
Type and Class Definitions
What is an Object?
Creating a User Defined Type
Rules on Working with User Defined Types
Defining an Abstract Data Type
Definitions
Inheritance
Single versus Multiple Inheritance
Polymorphism
With and Without Polymorphism
Different Object Methodologies
What is UML?
Visual Models
Attributes
Different Types of Methods

Object-Relational Concepts

Object-Relational Databases
Object-Relational Features
Oracle Object-Relational Features
Oracle Datatypes
Abstract Datatypes
Data Dictionary Views
The CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User Defined Datatypes
Modifying Data in Object Tables
Working with Object Tables
Object Identifiers
Defining a REF
Updating REF Values
INSERTING REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
Object Methods
Different Types of Methods
Create Type Body Command
Executing Object Methods
Differences between Packages and Objects
DDL Type Commands
MAP Method
ORDER Method
MAP versus ORDER Method
STATIC Methods
Objects and Nulls
LOBS

Collections

Records
Creating a PL/SQL Record
PL/SQL2 Tables
Table Attributes
PL/SQL8 Tables
Coding Examples
Create a Type Specification
CAST Operator
Operator "THE"
Queries on Nested Tables

Dynamic SQL

Executing Dynamic SQL Statements in
PL/SQL
Advantages of Dynamic SQL
Advantages of DBMS_SQL
DBMS_SQL Package
DBMS_SQL Example
Advantages of Native Dynamic SQL in
PL/SQL
Syntax: EXECUTE IMMEDIATE Statement
Bind Variables
Defining Modes for Bind Variables
Executing DML, DCL, and DDL Statements
Executing Dynamic Multi-row Queries
Using Schema objects with Dynamic SQL
Cursor Attributes
Assigning Nulls to Bind Variables
Issues When Executing Subprograms

Debugging

Problem Analysis
Handling Exceptions
Continuing After an Exception
Using an Inner Block to Continue after an
Exception
Inner Block Approach
Function Approach
Locator Variables
Temporary Tables
Storing Debugging Information
The DBMS_TRACE Package
DBMS_TRACE Constants
Managing the Volume of Tracing Information
The DBMS_DEBUG Package
Procedures in DBMS_DEBUG
Setting up a Debug Session

Performance Tuning

Working with Object Types and Collections
Bulk Binds
Bulk Collect Into Clause
Use the RETURNING Clause
NOCOPY Hint
Memory and PL/SQL
Pinning PL/SQL Programs
KEEP Procedure
What Do I to PIN?
Relative Sizes of Code to Pin
Serially Reusable Packages
Optimizing Integer Operations
Avoid NOT NULL Constraints
Rephrase Conditional Control Statements
Additional Considerations
DBMS_PROFILER
Profiler Tables
Table PLSQL_PROFILER_DATA