SYSTEMS & SOFTWARE
Training Limited
Oracle™ 10g PL/SQL
This course is suitable for anyone new to Oracle™ 10G PL/SQL
Course Content 1 Day

Objectives
                                                                                            

All attendees will learn how to:
Create triggers
Use PL/SQL variables and understand datatypes
Write PL/SQL with proper syntax
Build stored procedures and functions
Handle exceptions
Perform calculations
Manage cursors
Use existing packages
Create new packages
Work with LOBs
Maintain PL/SQL code

Outline


Triggers

Beyond Declarative Integrity
Triggers
Types of Triggers
Row-Level Triggers
Cascading Triggers and Mutating Tables
Generating an Error
Triggers on Views
System Triggers
Maintaining Triggers


PL/SQL Variables and Datatypes

Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable


PL/SQL Syntax and Logic

PL/SQL Blocks and Programs
Conditional Statements – IF/THEN
Conditional Statements – CASE
Comments and Labels
Loops
WHILE and FOR Loops

Stored Procedures and Functions

Stored Subprograms
Procedures and Functions
Creating a Stored Procedure
Calling a Stored Procedure
Passing Parameters and Default Arguments
Parameter Modes
Creating a Stored Function
Calling a Stored Function
Stored Functions and SQL
Local Procedures and Functions

Exception Handling

SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
User-Defined Exceptions
Compile-Time Warnings


Records and Collections

Record Variables
Using the %ROWTYPE Attribute
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Associative Array Collections
Collection Methods
Iterating Through Collections
FORALL Statement
BULK COLLECT Clause


Cursors

Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor


Using Packages

Packages
Oracle™-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages


Creating Packages

Structure of a Package
The Package Interface and Implementation
Package Variables
Overloading Package Functions and Procedures
Named Parameter Notation
REF CURSOR Variables


Working with LOBs

Large Object Types
Oracle™ Directories
LOB Locators
Internal LOBs
External LOBs
Temporary LOBs
The DBMS_LOB Package


Maintaining PL/SQL Code

Privileges for Stored Programs
PL/SQL Stored Program Compilation
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs


Conclusion