Part Ⅰ．Programming in PL/SQL

1．Introduction to PL/SQL

What Is PL/SQL?

The Origins of PL/SQL

The Early Years of PL/SQL

Improved Application Portability

Improved Execution Authority and Transaction Integrity

Humble Beginnings,Steady Improvement

So This Is PL/SQL

Integration with SQL

Control and Conditional Logic

When Things Go Wrong

About PL/SQL Versions

Oracle Database 12c New PL/SQL Features

Resources for PL/SQL Developers

The O'Reilly PL/SQL Series

PL/SQL on the Internet

Some Words of Advice

Don't Be in Such a Hurry!

Don't Be Afraid to Ask for Help

Take a Creative,Even Radical Approach

2．Creating and Running PL/SQL Code

Navigating the Database

Creating and Editing Source Code

SQL＊Plus

Starting Up SQL＊Plus

Running a SQL Statement

Running a PL/SQL Program

Running a Script

What Is the"Current Directory"?

Other SQL＊Plus Tasks

Error Handling in SQL＊Plus

Why You Will Love and Hate SQL＊Plus

Performing Essential PL/SQL Tasks

Creating a Stored Program

Executing a Stored Program

Showing Stored Programs

Managing Grants and Synonyms for Stored Programs

Dropping a Stored Program

Hiding the Source Code of a Stored Program

Editing Environments for PL/SQL

Calling PL/SQL from Other Languages

C:Using Oracle's Precompiler(Pro＊C)

Java:Using JDBC

Perl:Using Perl DBI and DBD::Oracle

PHP:Using Oracle Extensions

PL/SQL Server Pages

And Where Else?

3．Language Fundamentals

PL/SQL Block Structure

Anonymous Blocks

Named Blocks

Nested Blocks

Scope

Qualify All References to Variables and Columns in SQL Statements

Visibility

The PL/SQL Character Set

Identifiers

Reserved Words

Whitespace and Keywords

Literals

NULLs

Embedding Single Quotes Inside a Literal String

Numeric Literals

Boolean Literals

The Semicolon Delimiter

Comments

Single-Line Comment Syntax

Multiline Comment Syntax

The PRAGMA Keyword

Labels

Part Ⅱ．PL/SQL Program Structure

4．Conditional and Sequential Control

IF Statements

The IF-THEN Combination

The IF-THEN-ELSE Combination

The IF-THEN-ELSIF Combination

Avoiding IF Syntax Gotchas

Nested IF Statements

Short-Circuit Evaluation

CASE Statements and Expressions

Simple CASE Statements

Searched CASE Statements

Nested CASE Statements

CASE Expressions

The GOTO Statement

The NULL Statement

Improving Program Readability

Using NULL After a Label

5．Iterative Processing with Loops

Loop Basics

Examples of Different Loops

Structure of PL/SQL Loops

The Simple Loop

Terminating a Simple Loop:EXIT and EXIT WHEN

Emulating a REPEAT UNTIL Loop

The Intentionally Infinite Loop

The WHILE Loop

The Numeric FOR Loop

Rules for Numeric FOR Loops

Examples of Numeric FOR Loops

Handling Nontrivial Increments

The Cursor FOR Loop

Example of Cursor FOR Loops

Loop Labels

The CONTINUE Statement

Tips for Iterative Processing

Use Understandable Names for Loop Indexes

The Proper Way to Say Goodbye

Obtaining Information About FOR Loop Execution

SQL Statement as Loop

6．Exception Handlers

Exception-Handling Concepts and Terminology

Defining Exceptions

Declaring Named Exceptions

Associating Exception Names with Error Codes

About Named System Exceptions

Scope of an Exception

Raising Exceptions

The RAISE Statement

Using RAISE_APPLICATION_ERROR

Handling Exceptions

Built-in Error Functions

Combining Multiple Exceptions in a Single Handler

Unhandled Exceptions

Propagation of Unhandled Exceptions

Continuing Past Exceptions

Writing WHEN OTHERS Handling Code

Building an Effective Error Management Architecture

Decide on Your Error Management Strategy

Standardize Handling of Different Types of Exceptions

Organize Use of Application-Specific Error Codes

Use Standardized Error Management Programs

Work with Your Own Exception"Objects"

Create Standard Templates for Common Error Handling

Making the Most of PL/SQL Error Management

Part Ⅲ．PL/SQL Program Data

7．Working with Program Data

Naming Your Program Data

Overview of PL/SQL Datatypes

Character Data

Numbers

Dates,Timestamps,and Intervals

Booleans

Binary Data

ROWIDs

REF CURSORs

Internet Datatypes

"Any"Datatypes

User-Defined Datatypes

Declaring Program Data

Declaring a Variable

Declaring Constants

The NOT NULL Clause

Anchored Declarations

Anchoring to Cursors and Tables

Benefits of Anchored Declarations

Anchoring to NOT NULL Datatypes

Programmer-Defined Subtypes

Conversion Between Datatypes

Implicit Data Conversion

Explicit Datatype Conversion

8．Strings

String Datatypes

The VARCHAR2 Datatype

The CHAR Datatype

String Subtypes

Working with Strings

Specifying String Constants

Using Nonprintable Characters

Concatenating Strings

Dealing with Case

Traditional Searching,Extracting,and Replacing

Padding

Trimming

Regular Expression Searching,Extracting,and Replacing

Working with Empty Strings

Mixing CHAR and VARCHAR2 Values

String Function Quick Reference

9．Numbers

Numeric Datatypes

The NUMBER Type

The PLS_INTEGER Type

The BINARY_INTEGER Type

The SIMPLE_INTEGER Type

The BINARY_FLOAT and BINARY_DOUBLE Types

The SIMPLE_FLOAT and SIMPLE_DOUBLE Types

Numeric Subtypes

Number Conversions

The TO_NUMBER Function

The TO_CHAR Function

The CAST Function

Implicit Conversions

Numeric Operators

Numeric Functions

Rounding and Truncation Functions

Trigonometric Functions

Numeric Function Quick Reference

10．Dates and Timestamps

Datetime Datatypes

Declaring Datetime Variables

Choosing a Datetime Datatype

Getting the Current Date and Time

Interval Datatypes

Declaring INTERVAL Variables

When to Use INTERVALs

Datetime Conversions

From Strings to Datetimes

From Datetimes to Strings

Working with Time Zones

Requiring a Format Mask to Match Exactly

Easing Up on Exact Matches

Interpreting Two-Digit Years in a Sliding Window

Converting Time Zones to Character Strings

Padding Output with Fill Mode

Date and Timestamp Literals

Interval Conversions

Converting from Numbers to Intervals

Converting Strings to Intervals

Formatting Intervals for Display

Interval Literals

CAST and EXTRACT

The CAST Function

The EXTRACT Function

Datetime Arithmetic

Date Arithmetic with Intervals and Datetimes

Date Arithmetic with DATE Datatypes

Computing the Interval Between Two Datetimes

Mixing DATEs and TIMESTAMPs

Adding and Subtracting Intervals

Multiplying and Dividing Intervals

Using Unconstrained INTERVAL Types

Date/Time Function Quick Reference

11．Records

Records in PL/SQL

Benefits of Using Records

Declaring Records

Programmer-Defined Records

Working with Records

Comparing Records

Trigger Pseudorecords

12．Collections

Collections Overview

Collections Concepts and Terminology

Types of Collections

Collection Examples

Where You Can Use Collections

Choosing a Collection Type

Collection Methods(Built-ins)

The COUNT Method

The DELETE Method

The EXISTS Method

The EXTEND Method

The FIRST and LAST Methods

The LIMIT Method

The PRIOR and NEXT Methods

The TRIM Method

Working with Collections

Declaring Collection Types

Declaring and Initializing Collection Variables

Populating Collections with Data

Accessing Data Inside a Collection

Using String-Indexed Collections

Collections of Complex Datatypes

Multilevel Collections

Working with Collections in SQL

Nested Table Multiset Operations

Testing Equality and Membership of Nested Tables

Checking for Membership of an Element in a Nested Table

Performing High-Level Set Operations

Handling Duplicates in a Nested Table

Maintaining Schema-Level Collections

Necessary Privileges

Collections and the Data Dictionary

13．Miscellaneous Datatypes

The BOOLEAN Datatype

The RAW Datatype

The UROWID and ROWID Datatypes

Getting ROWIDs

Using ROWIDs

The LOB Datatypes

Working with LOBs

Understanding LOB Locators

Empty Versus NULL LOBs

Writing into a LOB

Reading from a LOB

BFILEs Are Different

SecureFiles Versus BasicFiles

Temporary LOBs

Native LOB Operations

LOB Conversion Functions

Predefined Object Types

The XMLType Type

The URI Types

The Any Types

Part Ⅳ．SQL in PL/SQL

14．DML and Transaction Management

DML in PL/SQL

A Quick Introduction to DML

Cursor Attributes for DML Operations

RETURNING Information from DML Statements

DML and Exception Handling

DML and Records

Transaction Management

The COMMIT Statement

The ROLLBACK Statement

The SAVEPOINT Statement

The SET TRANSACTION Statement

The LOCK TABLE Statement

Autonomous Transactions

Defining Autonomous Transactions

Rules and Restrictions on Autonomous Transactions

Transaction Visibility

When to Use Autonomous Transactions

Building an Autonomous Logging Mechanism

15．Data Retrieval

Cursor Basics

Some Data Retrieval Terms

Typical Query Operations

Introduction to Cursor Attributes

Referencing PL/SQL Variables in a Cursor

Choosing Between Explicit and Implicit Cursors

Working with Implicit Cursors

Implicit Cursor Examples

Error Handling with Implicit Cursors

Implicit SQL Cursor Attributes

Working with Explicit Cursors

Declaring Explicit Cursors

Opening Explicit Cursors

Fetching from Explicit Cursors

Column Aliases in Explicit Cursors

Closing Explicit Cursors

Explicit Cursor Attributes

Cursor Parameters

SELECT...FOR UPDATE

Releasing Locks with COMMIT

The WHERE CURRENT OF Clause

Cursor Variables and REF CURSORs

Why Use Cursor Variables?

Similarities to Static Cursors

Declaring REF CURSOR Types

Declaring Cursor Variables

Opening Cursor Variables

Fetching from Cursor Variables

Rules for Cursor Variables

Passing Cursor Variables as Arguments

Cursor Variable Restrictions

Cursor Expressions

Using Cursor Expressions

Restrictions on Cursor Expressions

16．Dynamic SQL and Dynamic PL/SQL

NDS Statements

The EXECUTE IMMEDIATE Statement

The OPEN FOR Statement

About the Four Dynamic SQL Methods

Binding Variables

Argument Modes

Duplicate Placeholders

Passing NULL Values

Working with Objects and Collections

Dynamic PL/SQL

Build Dynamic PL/SQL Blocks

Replace Repetitive Code with Dynamic Blocks

Recommendations for NDS

Use Invoker Rights for Shared Programs

Anticipate and Handle Dynamic Errors

Use Binding Rather than Concatenation

Minimize the Dangers of Code Injection

When to Use DBMS_SQL

Obtain Information About Query Columns

Meeting Method 4 Dynamic SQL Requirements

Minimizing Parsing of Dynamic Cursors

Oracle Database 11g New Dynamic SQL Features

Enhanced Security for DBMS_SQL

Part Ⅴ．PL/SQL Application Construction

17．Procedures,Functions,and Parameters

Modular Code

Procedures

Calling a Procedure

The Procedure Header

The Procedure Body

The END Label

The RETURN Statement

Functions

Structure of a Function

The RETURN Datatype

The END Label

Calling a Function

Functions Without Parameters

The Function Header

The Function Body

The RETURN Statement

Parameters

Defining Parameters

Actual and Formal Parameters

Parameter Modes

Explicit Association of Actual and Formal Parameters in PL/SQL

The NOCOPY Parameter Mode Qualifier

Default Values

Local or Nested Modules

Benefits of Local Modularization

Scope of Local Modules

Sprucing Up Your Code with Nested Subprograms

Subprogram Overloading

Benefits of Overloading

Restrictions on Overloading

Overloading with Numeric Types

Forward Declarations

Advanced Topics

Calling Your Function from Inside SQL

Table Functions

Deterministic Functions

Implicit Cursor Results(Oracle Database 12c)

Go Forth and Modularize!

18．Packages

Why Packages?

Demonstrating the Power of the Package

Some Package-Related Concepts

Diagramming Privacy

Rules for Building Packages

The Package Specification

The Package Body

Initializing Packages

Rules for Calling Packaged Elements

Working with Package Data

Global Within a Single Oracle Session

Global Public Data

Packaged Cursors

Serializable Packages

When to Use Packages

Encapsulate Data Access

Avoid Hardcoding Literals

Improve Usability of Built-in Features

Group Together Logically Related Functionality

Cache Static Session Data

Packages and Object Types

19．Triggers

DML Triggers

DML Trigger Concepts

Creating a DML Trigger

DML Trigger Example:No Cheating Allowed!

Multiple Triggers of the Same Type

Who Follows Whom

Mutating Table Errors

Compound Triggers:Putting It All in One Place

DDL Triggers

Creating a DDL Trigger

Available Events

Available Attributes

Working with Events and Attributes

Dropping the Undroppable

The INSTEAD OF CREATE Trigger

Database Event Triggers

Creating a Database Event Trigger

The STARTUP Trigger

The SHUTDOWN Trigger

The LOGON Trigger

The LOGOFF Trigger

The SERVERERROR Trigger

INSTEAD OF Triggers

Creating an INSTEAD OF Trigger

The INSTEAD OF INSERT Trigger

The INSTEAD OF UPDATE Trigger

The INSTEAD OF DELETE Trigger

Populating the Tables

INSTEAD OF Triggers on Nested Tables

AFTER SUSPEND Triggers

Setting Up for the AFTER SUSPEND Trigger

Looking at the Actual Trigger

The ORA_SPACE_ERROR_INFO Function

The DBMS_RESUMABLE Package

Trapped Multiple Times

To Fix or Not to Fix?

Maintaining Triggers

Disabling,Enabling,and Dropping Triggers

Creating Disabled Triggers

Viewing Triggers

Checking the Validity of Triggers

20．Managing PL/SQL Code

Managing Code in the Database

Overview of Data Dictionary Views

Display Information About Stored Objects

Display and Search Source Code

Use Program Size to Determine Pinning Requirements

Obtain Properties of Stored Code

Analyze and Modify Trigger State Through Views

Analyze Argument Information

Analyze Identifier Usage(Oracle Database 11g's PL/Scope)

Managing Dependencies and Recompiling Code

Analyzing Dependencies with Data Dictionary Views

Fine-Grained Dependency(Oracle Database 11g)

Remote Dependencies

Limitations of Oracle's Remote Invocation Model

Recompiling Invalid Program Units

Compile-Time Warnings

A Quick Example

Enabling Compile-Time Warnings

Some Handy Warnings

Testing PL/SQL Programs

Typical,Tawdry Testing Techniques

General Advice for Testing PL/SQL Code

Automated Testing Options for PL/SQL

Tracing PL/SQL Execution

DBMS_UTILITY.FORMAT_CALL_STACK

UTL_CALL_STACK(Oracle Database 12c)

DBMS_APPLICATION_INFO

Tracing with opp_trace

The DBMS_TRACE Facility

Debugging PL/SQL Programs

The Wrong Way to Debug

Debugging Tips and Strategies

Using Whitelisting to Control Access to Program Units

Protecting Stored Code

Restrictions on and Limitations of Wrapping

Using the Wrap Executable

Dynamic Wrapping with DBMS_DDL

Guidelines for Working with Wrapped Code

Introduction to Edition-Based Redefinition(Oracle Database 11g Release 2)

21．Optimizing PL/SQL Performance

Tools to Assist in Optimization

Analyzing Memory Usage

Identifying Bottlenecks in PL/SQL Code

Calculating Elapsed Time

Choosing the Fastest Program

Avoiding Infinite Loops

Performance-Related Warnings

The Optimizing Compiler

Insights on How the Optimizer Works

Runtime Optimization of Fetch Loops

Data Caching Techniques

Package-Based Caching

Deterministic Function Caching

THe Function Result Cache(Oracle Database 11g)

Caching Summary

Bulk Processing for Repeated SQL Statement Execution

High-Speed Querying with BULK COLLECT

High-Speed DML with FORALL

Improving Performance with Pipelined Table Functions

Replacing Row-Based Inserts with Pipelined Function-Based Loads

Tuning Merge Operations with Pipelined Functions

Asynchronous Data Unloading with Parallel Pipelined Functions

Performance Implications of Partitioning and Streaming Clauses in Parallel Pipelined Functions

Pipelined Functions and the Cost-Based Optimizer

Tuning Complex Data Loads with Pipelined Functions

A Final Word on Pipelined Functions

Specialized Optimization Techniques

Using the NOCOPY Parameter Mode Hint

Using the Right Datatype

Optimizing Function Performance in SQL(12.1 and higher)

Stepping Back for the Big Picture on Performance

22．I/O and PL/SQL

Displaying Information

Enabling DBMS_OUTPUT

Write Lines to the Buffer

Read the Contents of the Buffer

Reading and Writing Files

The UTL_FILE_DIR Parameter

Working with Oracle Directories

Open Files

Is the File Already Open?

Close Files

Read from Files

Write to Files

Copy Files

Delete Files

Rename and Move Files

Retrieve File Attributes

Sending Email

Oracle Prerequisites

Configuring Network Security

Send a Short(32,767 Bytes or Less)Plain-Text Message

Include"Friendly"Names in Email Addresses

Send a Plain-Text Message of Arbitrary Length

Send a Message with a Short(32,767 Bytes or Less)Attachment

Send a Small File(32,767 Bytes or Less)as an Attachment

Attach a File of Arbitrary Size

Working with Web-Based Data(HTTP)

Retrieve a Web Page in"Pieces"

Retrieve a Web Page into a LOB

Authenticate Using HTTP Username/Password

Retrieve an SSL-Encrypted Web Page(via HTTPS)

Submit Data to a Web Page via GET or POST

Disable Cookies or Make Cookies Persistent

Retrieve Data from an FTP Server

Use a Proxy Server

Other Types of I/O Available in PL/SQL

Database Pipes,Queues,and Alerts

TCP Sockets

Oracle's Built-in Web Server

Part Ⅵ．Advanced PL/SQL Topics

23．Application Security and PL/SQL

Security Overview

Encryption

Key Length

Algorithms

Padding and Chaining

The DBMS_CRYPTO Package

Encrypting Data

Encrypting LOBs

SecureFiles

Decrypting Data

Performing Key Generation

Performing Key Management

Cryptographic Hashing

Using Message Authentication Codes

Using Transparent Data Encryption

Transparent Tablespace Encryption

Row-Level Security

Why Learn About RLS?

A Simple RLS Example

Static Versus Dynamic Policies

Using Column-Sensitive RLS

RLS Debugging

Application Contexts

Using Application Contexts

Security in Contexts

Contexts as Predicates in RLS

Identifying Nondatabase Users

Fine-Grained Auditing

Why Learn About FGA?

A Simple FGA Example

Access How Many Columns?

Checking the Audit Trail

Using Bind Variables

Using Handler Modules

24．PL/SQL Architecture

DIANA

How Oracle Executes PL/SQL Code

An Example

Compiler Limits

The Default Packages of PL/SQL

Execution Authority Models

The Definer Rights Model

The Invoker Rights Model

Combining Rights Models

Granting Roles to PL/SQL Program Units(Oracle Database 12c)

"Who Invoked Me?"Functions(Oracle Database 12c)

BEQUEATH CURRENT_USER for Views(Oracle Database 12c)

Constraining Invoker Rights Privileges(Oracle Database 12c)

Conditional Compilation

Examples of Conditional Compilation

The Inquiry Directive

The ＄IF Directive

The ＄ERROR Directive

Synchronizing Code with Packaged Constants

Program-Specific Settings with Inquiry Directives

Working with Postprocessed Code

PL/SQL and Database Instance Memory

The SGA,PGA,and UGA

Cursors,Memory,and More

Tips on Reducing Memory Use

What to Do If You Run Out of Memory

Native Compilation

When to Run in Interpreted Mode

When to Go Native

Native Compilation and Database Release

What You Need to Know

25．Globalization and Localization in PL/SQL

Overview and Terminology

Unicode Primer

National Character Set Datatypes

Character Encoding

Globalization Support Parameters

Unicode Functions

Character Semantics

String Sort Order

Binary Sort

Monolingual Sort

Multilingual Sort

Multilingual Information Retrieval

IR and PL/SQL

Date/Time

Timestamp Datatypes

Date/Time Formatting

Currency Conversion

Globalization Development Kit for PL/SQL

UTL_118N Utility Package

UTL_LMS Error-Handling Package

GDK Implementation Options

26．Object-Oriented Aspects of PL/SQL

Introduction to Oracle's Object Features

Object Types by Example

Creating a Base Type

Creating a Subtype

Methods

Invoking Supertype Methods in Oracle Database 11g and Later

Storing,Retrieving,and Using Persistent Objects

Evolution and Creation

Back to Pointers?

Generic Data:The ANY Types

I Can Do It Myself

Comparing Objects

Object Views

A Sample Relational System

Object View with a Collection Attribute

Object Subview

Object View with Inverse Relationship

INSTEAD OF Triggers

Differences Between Object Views and Object Tables

Maintaining Object Types and Object Views

Data Dictionary

Privileges

Concluding Thoughts from a(Mostly) Relational Developer

27．Calling Java from PL/SQL

Oracle and Java

Getting Ready to Use Java in Oracle

Installing Java

Building and Compiling Your Java Code

Setting Permissions for Java Development and Execution

A Simple Demonstration

Finding the Java Functionality

Building a Custom Java Class

Compiling and Loading into Oracle

Building a PL/SQL Wrapper

Deleting Files from PL/SQL

Using loadjava

Using dropjava

Managing Java in the Database

The Java Namespace in Oracle

Examining Loaded Java Elements

Using DBMS_JAVA

LONGNAME:Converting Java Long Names

GET_,SET_,and RESET_COMPILER_OPTION:Getting and Setting(a Few)Compiler Options

SET_OUTPUT:Enabling Output from Java

EXPORT_SOURCE,EXPORT_RESOURCE,and EXPORT_CLASS:Exporting Schema Objects

Publishing and Using Java in PL/SQL

Call Specs

Some Rules for Call Specs

Mapping Datatypes

Calling a Java Method in SQL

Exception Handling with Java

Extending File I/O Capabilities

Other Examples

28．External Procedures

Introduction to External Procedures

Example: Invoking an Operating System Command

Architecture of External Procedures

Oracle Net Configuration

Specifying the Listener Configuration

Security Characteristics of the Configuration

Setting Up Multithreaded Mode

Creating an Oracle Library

Writing the Call Specification

The Call Spec:Overall Syntax

Parameter Mapping:The Example Revisited

Parameter Mapping:The Full Story

More Syntax:The PARAMETERS Clause

PARAMETERS Properties

Raising an Exception from the Called C Program

Nondefault Agents

Maintaining External Procedures

Dropping Libraries

Data Dictionary

Rules and Warnings

A．Regular Expression Metacharacters and Function Parameters

B．Number Format Models

C．Date Format Models

Index