Declaring Variables
PL/SQL Block Structure
Executing Statements and PL/SQL Blocks
Block Types
Use of Variables
Handling Variables in PL/SQL
Types of Variables
Using SQL*Plus Variables Within PL/SQL Blocks
Types of Variables
Declaring PL/SQL Variables
Guidelines for Declaring PL/SQL Variables
Naming Rules
Variable Initialization and Keywords
Scalar Data Types
Base Scalar Data Types
Scalar Variable Declarations
The %TYPE Attribute
Declaring Variables with the %TYPE Attribute
Declaring Boolean Variables
Composite Data Types
LOB Data Type Variables
Bind Variables
Referencing Bind Variables
DBMS_OUTPUT.PUT_LINE
Writing Executable Statements
PL/SQL Block Syntax and Guidelines
Identifiers
Commenting Code
Functions in PL/SQL
SQL Functions in PL/SQL
Data Type Conversion
Identifier Scope
Nested Blocks
Nested Blocks and Variable Scope
Qualifying an Identifier
Operators in PL/SQL
Programming Guidelines
Indenting Code
Interacting with the Oracle Server
SQL Statements in PL/SQL
SELECT Statements in PL/SQL
Retrieving Data in PL/SQL
Naming Conventions
Manipulating Data Using PL/SQL
Inserting Data
Updating Data
Deleting Data
Merging Rows
SQL Cursor
SQL Cursor Attributes
Transaction Control Statements
Writing Control Structures
Controlling PL/SQL Flow of Execution
IF Statements
Simple IF Statements
Compound 'Conditional' Statements
IF-THEN-ELSE Statements
IF-THEN-ELSIF Statements
CASE Expressions
Handling Nulls
Logic Tables
Boolean Conditions
Iterative Control: LOOP Statements
Basic Loops
WHILE Loops
FOR Loops
Guidelines While Using Loops
Nested Loops and Labels
Composite Data Types
PL/SQL Records
Creating a PL/SQL Record
PL/SQL Record Structure
The %ROWTYPE Attribute
Advantages of Using %ROWTYPE
INDEX BY Tables
Creating an INDEX BY Table
INDEX BY Table Structure
Using INDEX BY Table Methods
INDEX BY Table Example
INDEX BY Table of Records
Example of INDEX BY Table of Records
Explicit Cursors
About Cursors
Explicit Cursor Functions
Controlling Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching Data from the Cursor
Closing the Cursor
Explicit Cursor Attributes
The %ISOPEN Attribute
Controlling Multiple Fetches
The CURSOR%NOTFOUND and %CURSOR%ROWCOUNT Attributes Example
Cursors and Records
Cursor FOR Loops
Cursor FOR Loops Using Subqueries
Additional Cursor Concepts
Cursors with Parameters
The FOR UPDATE Clause
The WHERE CURRENT OF Clause
Cursors with Sub-queries
Error Handling
Handling Exceptions with PL/SQL
Exception Types
Trapping Exceptions
Trapping Exceptions Guidelines
Trapping Predefined Oracle Server Errors
Predefined Exceptions
Trapping Non-predefined Oracle Server Errors
Non-predefined Error
Functions for Trapping Exceptions
Trapping User-Defined Exceptions
User-Defined Exceptions
Propagating Exceptions
The RAISE_APPLICATION_ERROR Procedure
Creating Procedures
Overview of Subprograms
Benefits of Subprograms
Anonymous PL/SQL Block Structure
Block Structure for PL/SQL Subprograms
Creating Procedures
Developing Procedures
Formal Versus Actual Parameters
Creating Procedures with Parameters
IN Parameters
OUT Parameters
IN OUT Parameters
Setting DEFAULT Values for Parameters
Methods for Passing Parameters
Declaring Local Subprograms
Invoking a Procedure from an Anonymous PL/SQL Block
Invoking a Procedure from Another Procedure
Handled Exceptions
Removing Procedures
Creating Functions
Overview of Functions
Syntax for Creating Functions
Creating a Function
Creating a Stored Function using SQL*PLUS
Executing Functions
Usage of User-Defined Functions is SQL Expressions
Invoking Functions from SQL Expressions
Calling Locations for User-Defined Functions
Calling Restrictions from SQL Expressions
Removing Functions
Function or Procedure
Functions verses Procedures
Advantages of stored Functions
Creating Database Triggers
Database Triggers
Statement vs Row Triggers
Trigger Execution Model
Statement Trigger Syntax
Example: Statement Trigger
Row Trigger Syntax
Example Row Trigger
Using OLD and NEW Qualifiers
Restricting a Row Trigger
Trigger Executable Section
Using Conditional Predicates
INSTEAD OF Triggers
Creating an INSTEAD OF Trigger
Differentiating Between Database Triggers and Stored Procedures
Managing Triggers
DROP TRIGGER Syntax
Trigger Test Cases
Additional Trigger Concepts
Creating Database Triggers
Creating Triggers on DDL Statements
Creating Triggers on Database Events
LOGON and LOGOFF Triggers
Problems with Mutating Tables
Implementing Triggers
Controlling Data Access with Triggers
Enforcing Data Integrity
Maintaining Referential Integrity using Triggers
Replicating a Table with a Trigger
Computing Derived Data
Keeping a Running Total using Triggers
Benefits of Database Triggers
Managing Triggers
Viewing Trigger Information
USER_TRIGGERS View
Creating Packages
Packages Overview
Advantages of Packages
Package Components
Referencing Package Components
Developing Packages
The Package Specification
Declaring Public Constructs
Creating the Package Body
Public and Private Constructs
Invoking Package Constructs
Declaring a Bodiless Package
Overloading Subprograms
Using Forward Declarations
Restrictions on Package Functions Used in SQL
User Defined Packages
Invoking a Package Function from a SQL Statement
Persistent State of Package Variables
Controlling the Persistent State of a Package Cursor
PL/SQL Tables and Records in Packages
Removing Packages
Guidelines for Developing Packages
More Package Concepts
Overloading
Using Forward Declarations
Creating a One-Time-Only Procedure
Restrictions on Package Functions Used in SQL
User Defined Package: taxes_pack
Invoking a User-Defined Package Function from a SQL Statement
Persistent State of Package Variables
Controlling the Persistent State of a Package Cursor
Executing PACK_CUR
PL/SQL Tables and Records in Packages
Oracle Supplied Packages
Using Supplied Packages
Using Native Dynamic SQL
Execution Flow
Using the DBMS_SQL Package
Using DBMS_SQL
Using the EXECUTE IMMEDIATE Statement
Dynamic SQL Using EXECUTE IMMEDIATE
Using the DBMS_DDL Package
Using the DBMS_OUTPUT Package
Interacting with Operating System Files
What Is the UTL_FILE Package?
File Processing Using the UTL_FILE Package
UTL_FILE Procedures and Functions
Exceptions Specific to the UTL_FILE Package
The FOPEN and IS_OPEN Functions
Using UTL_FILE
The UTL_HTTP Package
Using the UTL_HTTP Package
Using the UTL_TCP Package
Oracle-Supplied Packages
Managing Dependencies
Understanding Dependencies
Dependencies
Local Dependencies
Scenario: Local Dependencies
Displaying Direct Dependencies via USER_DEPENDENCIES
Displaying Direct and Indirect Dependencies
Displaying Dependencies
Scenario: Local Dependencies
Scenario: Local Naming Dependencies
Understanding Remote Dependencies
Concepts of Remote Dependencies
REMOTE_DEPENDENCIES_MODE Parameter
Remote Dependencies and Time Stamp Mode
Signature Mode
Recompiling a PL/SQL Program Unit
Unsuccessful Recompilation
Successful Recompilation
Recompilation of Procedures
Packages and Dependencies
REFERENCE Cursors
Cursor Variables
Why Use Cursor Variables?
Defining REF CURSOR Types
Define a REF CURSOR type
Using the OPEN-FOR, FETCH, and CLOSE Statements
Example: Fetching