Contents

About the Author

About the Technical Reviewer

Acknowledgments

Foreword

Introduction (The Big Picture)

 Chapter 1: Relational SQL

Tables

An Entity-Relationship Diagram

Data Definition Language (DDL)

It’s Your Turn to Create a Table

Indexes

DDL Again

It’s Your Turn to Create an Index

Constraints

Column Constraints

Table Constraints

It’s Your Turn to Create a Constraint

Triggers

Views

Insert

Insert . . .Values

It’s Your Turn to Insert with Values

Insert . . . Select

It’s Your Turn to Insert with Select

Update

Fix a Mistake with Update

It’s Your Turn to Update

Update and Subqueries

Delete

A Change in Order

It’s Your Turn to Delete

Select

Joins

It’s Your Turn to Select

Your Working Example

Your Example Narrative

Your Example ERD

Create a Code Table

It’s Your Turn to Create Code Tables

Create a Content Table

It’s Your Turn to Create Content Tables

Create an Intersection Table

It’s Your Turn to Create Intersection Tables

Summary

 Chapter 2: Blocks, Functions, and Procedures

Blocks

Anonymous Blocks

Exceptions

Common Exceptions

Catching an Exception

Functions

Create a Function

It’s Your Turn to Create a Function

Procedures

Create a Procedure

It’s Your Turn to Create a Procedure

Nested Blocks

An Example of Nesting Blocks

Rules for Nesting

Packages

Create a Package Specification

It’s Your Turn to Create a Package Specification

Create a Package Body

It’s Your Turn to Create a Package Body

Benefits of Using Packages

Summary

 Chapter 3: Types, Variables, and Scope

PL/SQL Data Types

Variables

Variable Naming

Variable Declarations

Variable Anchors

Variable Assignments

NULL Value

It’s Your Turn to Declare Variables

Scope

Scope Rules

It’s Your Turn to Scope Things Out

Types

Table Types

Record Types

Multidimensional Arrays

Parameters

Parameter Naming

Parameter Declarations

Parameter Scope

It’s Your Turn to Declare Parameters

Summary

 Chapter 4: Single Row Processing

Inserts

Catching a DUP_VAL_ON_INDEX Exception

Using PL/SQL to Predetect a Duplicate

IF I Don’t Tell You Now, When ELSE Can I?

Using SQL to Predetect a Duplicate

It’s Your Turn to Insert

Updates

Updating a Row

Using SQL to Perform Complex Updates

Deletes

Selects

No Data Found

Too Many Rows

It’s Your Turn to Select

Summary

 Chapter 5: Multirow Processing

Cursors

Cursor Declarations

Fetching Rows from a Cursor Manually

Cursor Records

A Singleton Cursor

It’s Your Turn to Fetch Manually

Fetching Rows from a Cursor Automatically

It’s Your Turn to Fetch Automatically

Bulk Collect

Bulk Collect with a Cursor

Bulk Collect with a Select Statement

It’s Your Turn to Bulk Collect

FORALL

Summary

 Chapter 6: Object-Relational SQL

Information Modeling

Object Orientation

Key Tenets

Reuse

Service Orientation

A Roadmap to Reality

Step 1: Table Packages

Step 2: User-Defined Types

Step 3: Object Views

Step 4: Object Tables

A Change in Order

Object Types

Create a User-Defined Type Specification

Create a User-Defined Type Implementation

It’s Your Turn to Create a User-Defined Type

Object Views

Create an Object View

It’s Your Turn to Create an Object View

Object Tables

Create an Object Table

It’s Your Turn to Create an Object Table

Impedance Mismatch?

Nested Types and Collections

It’s Your Turn to Prove There’s No Impedance Mismatch

Summary

 Chapter 7: Troubleshooting

Prevention

Anchors

Data Type Prefixes

Explicit Conversions

Preparation

Blocking

Bread Crumbs

After the Fact

Success Messages

Failure Messages

It’s Your Turn to Use put_line( )

As It Happens

A DEBUG Table

A DEBUGGER Package

It’s Your Turn to Use Debug Logging

One Step at a Time

Debugging with Oracle SQL Developer

Debugging Anonymous PL/SQL

Debugging with TOAD for Oracle

Debugging with Visual Studio

It’s Your Turn to Use a Debugger

Profiling PL/SQL

Profiler’s Tables

Profiler’s Methods

Profiling Reports

It’s Your Turn to Profile

Profiling SQL

Explain Plan

Physics vs. Explain Plan

It’s Your Turn to Use Explain Plan

TKPROF

Summary

 Chapter 8: Testing

SQL Test Patterns

PL/SQL Test Patterns

A Testing Tool

A TEST Table

A TEST Package Specification

A TEST Package Body

It’s Your Turn to Create a Testing Tool

Testing

Testing a Code Table Package

It’s Your Turn to Test a Code Table Package

Testing a Content Table Package

It’s Your Turn to Test a Content Table Package

Testing an Intersection Table Package

It’s Your Turn to Test an Intersection Table Package

Testing a Type

It’s Your Turn to Test a Type

Automating Testing

Automate Test Processing

It’s Your Turn to Automate Test Processing

Summary

 Chapter 9: Documenting

Indestructible Documentation

SQL*Plus Documentation Tools

GUI Development Environment Tools

Rules for Documentation Comments

Documentation on Demand

A Text-Based Documentation Formatting Tool

Accessing Documentation on Demand

It’s Your Turn to Access Documentation on Demand

Distributable Documentation

An HTML-Based Documentation Formatting Tool

Generating Distributable Documentation

It’s Your Turn to Generate Distributable Documentation

Documentation Distribution

Summary

 Chapter 10: Examples

Polymorphic Commands

Parametric SQL

Code Table Methods

Content Table Methods

Intersection Table Methods

Hierarchical Table Methods

The Black Box

Table Methods

Universal Methods

Divide and Conquer

Data Migration

On-Demand Data Processing

Polling Data Processing

Interfacing

Reporting

Summary

 Appendix A: How to Download, Install, and Use Oracle

How to Download Oracle Database Software

How to Install Oracle Database Software

How to Use SQL*Plus

How to Download This Book’s Source Code

How to Create a New Username

How to Write a SQL*Plus Script

How to Execute a SQL*Plus Script

How to Describe Your Tables and Stored Procedures

Index