Introduction to PL/SQL Programming

Course Length: 2 days

This class will develop database programming skills using PL/SQL. Students will learn PL/SQL programming skills, proper software engineering techniques for database programming, PL/SQL problem solving and performance tuning skills.

Audience: This class is intended for Oracle analysts, developers, designers, administrators, and managers new to Oracle database programming.

Prerequisites: Students should have a basic understanding of SQL, six months experience working with an Oracle database, and at least six months 3GL programming experience.

Introduction to PL/SQL Programming

What is 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
Displaying Dept Values
Subtypes
Subtype Declarations
Labels
Variable Scope and Visibility Rules
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
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
Using Subqueries with Cursors
Using Cursor FOR LOOPs
Using %ROWTYPE with Cursors
Using Aliases and Subqueries
Using Bind Variables
Using Parameters with Explicit Cursors
Using Cursor Parameters
Modifying Data using Explicit Cursors
WHERE CURRENT OF
Managing Transactions in PL/SQL Programs

Procedures, Functions, and Packages

Working with Subprograms
Named Subprograms
Software Evolution
Stored Procedures
Parameters
Positional and Named Notation
Executing a Stored Procedure
Compiling and Debugging Stored Procedures
Stored Function Syntax
Executing a Function from SQL*Plus
The RETURN Statement
Packages
Managing Side Effects
Pragma RESTRICT_REFERENCES
Defining Subprograms Within Another Subprogram
Forward Declarations
Package Specification and Body

Collections

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