COURSE OUTLINE

1.    Introduction to PL/SQL

·      About PL/SQL  

o  PL/SQL Run-Time Architecture  

o  Benefits of PL/SQL  

o  PL/SQL Block Structure 

·      Block Types  

o  Program Constructs 

o  Examining an Anonymous Block

o  Executing an Anonymous Block 

·      Enabling Output of a PL/SQL Block 

o  Viewing the Output of a PL/SQL Block 

2.    Declaring PL/SQL Variables

·      Use of Variables 

o  Requirements for Variable Names 

o  Handling Variables in PL/SQL 

o  Declaring and Initializing PL/SQL Variables 

o  Delimiters in String Literals 

·      Types of Variables 

o  Guidelines for Declaring and Initializing PL/SQL Variables 

o  Guidelines for Declaring PL/SQL Variables 

o  Naming Conventions of PL/SQL Structures Used in This Course 

o  Scalar Data Types 

o  Base Scalar Data Types  

o  Declaring Scalar Variables 

o  %TYPE Attribute  

o  Declaring Variables with the %TYPE Attribute  

o  Declaring Boolean Variables  

o  LOB Data Type Variables  

o  Composite Data Types: Records and Collections  

·      Bind Variables  

o  Referencing Bind Variables  

o  Using AUTOPRINT with Bind Variables  

3.    Writing Executable Statements

·      Lexical Units in a PL/SQL Block 

o  PL/SQL Block Syntax and Guidelines  

o  Commenting Code 

o  SQL Functions in PL/SQL 

o  Using Sequences in PL/SQL Expressions 

o  Data Type Conversion 

·      Nested Blocks  

o  Variable Scope and Visibility  

o  Using a Qualifier with Nested Blocks  

o  Challenge: Determining Variable Scope  

·      Operators in PL/SQL 

o  Programming Guidelines 

o  Indenting Code  

4.    Using SQL Statements within a PL/SQL Block

·      SQL Statements in PL/SQL 

o  SELECT Statements in PL/SQL

o  Retrieving Data in PL/SQL 

o  Naming Ambiguities  

o  Naming Conventions  

·      Using PL/SQL to Manipulate Data 

o  Inserting Data

o  Updating Data

o  Deleting Data

o  Merging Rows  

·      SQL Cursor  

o  SQL Cursor Attributes for Implicit Cursors 

5.    Writing Control Structures

·      Controlling Flow of Execution 

·      IF Statement 

o  Simple IF Statement 

o  IF THEN ELSE Statement 

o  IF ELSIF ELSE Clause  

o  NULL Value in IF Statement  

·      CASE Expressions  

o  Searched CASE Expressions  

o  CASE Statement 

o  Handling Nulls  

o  Logic Tables 

o  Boolean Expressions or Logical Expression? 

·      Iterative Control: LOOP Statements 

o  Basic Loops 

o  WHILE Loops 

o  FOR Loops 

o  FOR Loop Rules 

o  Suggested Use of Loops 

o  Nested Loops and Labels 

o  PL/SQL CONTINUE Statement 

6.    Working with Composite Data Types

·      Composite Data Types  7-4

o  PL/SQL Records or Collections? 

·      PL/SQL Records 

o  Creating a PL/SQL Record 

o  PL/SQL Record Structure 

o  %ROWTYPE Attribute  

o  Advantages of Using the %ROWTYPE Attribute 

o  Inserting a Record by Using %ROWTYPE  

o  Updating a Row in a Table by Using a Record 

·      Associative Arrays (INDEX BY Tables) 

o  Associative Array Structure  

o  Steps to Create an Associative Array 

o  Creating and Accessing Associative Arrays 

o  Using INDEX BY Table Methods 

o  INDEX BY Table of Records Option 

o  Nested Tables 

o  VARRAY 

7.    Using Explicit Cursors

·      Cursors 

o  Explicit Cursor Operations 

o  Controlling Explicit Cursors  

·      Agenda  

·      Declaring the Cursor 

o  Opening the Cursor  

o  Fetching Data from the Cursor 

o  Closing the Cursor 

o  Cursors and Records 

o  Cursor FOR Loops 

o  Explicit Cursor Attributes  

o  %ISOPEN Attribute 

o  Cursor FOR Loops Using Subqueries 

·      Cursors with Parameters 

·      FOR UPDATE Clause  

o  WHERE CURRENT OF Clause 

8.    Handling Exceptions

·      What Is an Exception?  

o  Understanding Exceptions with PL/SQL 

o  Handling Exceptions  

o  Exception Types  

·      Syntax to Trap Exceptions  

o  Guidelines for Trapping Exceptions  

o  Trapping Predefined Oracle Server Errors  

o  Trapping Nonpredefined Oracle Server Errors  

o  Functions for Trapping Exceptions  

o  Trapping User-Defined Exceptions  

o  Propagating Exceptions in a Subblock  

o  The RAISE Statement 

o  RAISE_APPLICATION_ERROR Procedure  

9.    Introducing Stored Procedures and Functions

·      Procedures and Functions 

o  Differences Between Anonymous Blocks and Subprograms  

·      Procedure: Syntax  

o  Creating a Procedure  

o  Invoking a Procedure  

·      Function: Syntax  

o  Creating a Function  

o  Invoking a Function  

o  Passing a Parameter to the Function 

o  Invoking the Function with a Parameter