1. Introduction to PL/SQL

• What is PL/SQL?  1-3
• About PL/SQL  1-4
• PL/SQL Environment  1-5
• Benefits of PL/SQL  1-6
• PL/SQL Block Structure  1-9
• Block Types  1-11
• Programs Constructs  1-13
• PL/SQL Programming Environments  1-15
• iSQL*Plus Architecture  1-18
• Create an Anonymous Block  1-19
• Execute an Anonymous Block  1-20
• Test the Output of a PL/SQL Block  1-21

2. Declaring PL/SQL Variables

• Use of Variables  2-3
• Identifiers  2-4
• Handling Variables in PL/SQL  2-5
• Declaring and Initializing PL/SQL Variables  2-6
• Delimiters in String Literals  2-8
• Types of Variables  2-9
• Guidelines for Declaring and Initializing PL/SQL Variables  2-11
• Guidelines for Declaring PL/SQL Variables  2-12
• Scalar Data Types  2-13
• Base Scalar Data Types  2-14

3. Writing Executable Statements

• Lexical Units in a PL/SQL Block  3-3
• PL/SQL Block Syntax and Guidelines  3-5
• Commenting Code  3-6
• SQL Functions in PL/SQL  3-7
• SQL Functions in PL/SQL: Examples  3-8
• Data Type Conversion  3-9
• Nested Blocks  3-12
• Variable Scope and Visibility  3-14
• Qualify an Identifier  3-16
• Determining Variable Scope  3-17
• Operators in PL/SQL  3-18
• Programming Guidelines  3-20
• Indenting Code  3-21

4. Interacting with the Oracle Server

• SQL Statements in PL/SQL  4-3
• SELECT Statements in PL/SQL  4-5
• Retrieving Data in PL/SQL  4-9
• Naming Conventions  4-11
• Manipulating Data Using PL/SQL  4-13
• Inserting Data  4-14
• Updating Data  4-15
• Deleting Data  4-16
• Merging Rows  4-17
• SQL Cursor  4-19
• SQL Cursor Attributes for Implicit Cursors  4-21

5. Writing Control Structures

• Controlling Flow of Execution  5-3
• IF Statements  5-4
• Simple IF Statement  5-6
• IF THEN ELSE Statement  5-7
• IF ELSIF ELSE Clause  5-8
• NULL Values in IF Statements  5-9
• CASE Expressions  5-10
• CASE Expressions: Example  5-11
• Searched CASE Expressions  5-12
• CASE Statement  5-13
• Handling Nulls  5-14
• Logic Tables  5-15
• Boolean Conditions  5-16
• Iterative Control: LOOP Statements  5-17
• Basic Loops  5-18
• WHILE Loops  5-20
• FOR Loops  5-22
• Guidelines for Loops  5-26
• Nested Loops and Labels  5-27

6. Working with Composite Data Types

• Composite Data Types  6-3
• PL/SQL Records  6-5
• Creating a PL/SQL Record  6-6
• PL/SQL Record Structure  6-8
• %ROWTYPE Attribute  6-9
• Advantages of Using %ROWTYPE  6-11
• %ROWTYPE Attribute  6-12
• Inserting a Record by Using %ROWTYPE  6-13
• Updating a Row in a Table by Using a Record  6-14
• INDEX BY Tables or Associative Arrays  6-15
• Creating an INDEX BY Table  6-16
• INDEX BY Table Structure  6-18
• Creating an INDEX BY Table  6-19
• Using INDEX BY Table Methods  6-20
• INDEX BY Table of Records  6-21
• INDEX BY Table of Records: Example  6-23
• Nested Tables  6-24
• VARRAY  6-26

7. Using Explicit Cursors

• Cursors  7-3
• Explicit Cursor Operations  7-4
• Controlling Explicit Cursors  7-5
• Declaring the Cursor  7-7
• Opening the Cursor  7-9
• Fetching Data from the Cursor  7-10
• Closing the Cursor  7-13
• Cursors and Records  7-14
• Cursor FOR Loops  7-15
• Explicit Cursor Attributes  7-17
• %ISOPEN Attribute  7-18
• %ROWCOUNT and %NOTFOUND: Example  7-19
• Cursor FOR Loops Using Subqueries  7-20
• Cursors with Parameters  7-21
• FOR UPDATE Clause  7-23
• WHERE CURRENT OF Clause  7-25
• Cursors with Subqueries  7-26

8. Handling Exceptions

• Objectives  8-2
• Example of an Exception  8-3
• Handling Exceptions with PL/SQL  8-5
• Handling Exceptions  8-6
• Exception Types  8-7
• Trapping Exceptions  8-8
• Guidelines for Trapping Exceptions  8-10
• Trapping Predefined Oracle Server Errors  8-11
• Trapping Non-Predefined Oracle Server Errors  8-14
• Non-Predefined Error  8-15
• Functions for Trapping Exceptions  8-16
• Trapping User-Defined Exceptions  8-18
• Calling Environments  8-20
• Propagating Exceptions in a Subblock  8-21
• RAISE_APPLICATION_ERROR Procedure  8-22

9. Creating Stored Procedures and Functions

• Procedures and Functions  9-3
• Differences Between Anonymous Blocks and Subprograms  9-4
• Procedure: Syntax  9-5
• Procedure: Example  9-6
• Invoking the Procedure  9-8
• Function: Syntax  9-9
• Function: Example  9-10
• Invoking the Function  9-11
• Passing Parameter to the Function  9-12
• Invoking the Function with a Parameter  9-13I