Oracle PL/SQL Programming
Oracle PL/SQL Programming, Second Edition By Steven Feuerstein
With Bill Pribyl
September 1997
Pages: 1024

| Table of Contents | Index | Sample Chapter | Colophon

Table of Contents

  1. Programming in PL/SQL

    1. Chapter 1 Introduction to PL/SQL

      1. What Is PL/SQL?
      2. The Concept of Programming in Oracle Applications
      3. The Origins of PL/SQL
      4. PL/SQL Versions
      5. Advice for Oracle Programmers
      6. A Few of My Favorite (PL/SQL) Things
      7. Best Practices for PL/SQL Excellence

    2. Chapter 2 PL/SQL Language Fundamentals

      1. The PL/SQL Character Set
      2. Identifiers
      3. Literals
      4. The Semicolon Delimiter
      5. Comments
      6. The PRAGMA Keyword
      7. Block Structure

    3. Chapter 3 Effective Coding Style

      1. Fundamentals of Effective Layout
      2. Formatting SQL Statements
      3. Formatting Control Structures
      4. Formatting PL/SQL Blocks
      5. Formatting Packages
      6. Using Comments Effectively
      7. Documenting the Entire Package

  2. PL/SQL Language Elements

    1. Chapter 4 Variables and Program Data

      1. Identifiers
      2. Scalar Datatypes
      3. NULLs in PL/SQL
      4. Variable Declarations
      5. Anchored Declarations
      6. Programmer-Defined Subtypes
      7. Tips for Creating and Using Variables

    2. Chapter 5 Conditional and Sequential Control

      1. Conditional Control Statements
      2. Sequential Control Statements

    3. Chapter 6 Database Interaction and Cursors

      1. Transaction Management
      2. Cursors in PL/SQL
      3. Implicit and Explicit Cursors
      4. Declaring Cursors
      5. Opening Cursors
      6. Fetching from Cursors
      7. Column Aliases in Cursors
      8. Closing Cursors
      9. Cursor Attributes
      10. Cursor Parameters
      11. SELECT FOR UPDATE in Cursors
      12. Cursor Variables
      13. Working with Cursors

    4. Chapter 7 Loops

      1. Loop Basics
      2. The Simple Loop
      3. The Numeric FOR Loop
      4. The Cursor FOR Loop
      5. The WHILE Loop
      6. Managing Loop Execution
      7. Tips for PL/SQL Loops

    5. Chapter 8 Exception Handlers

      1. Why Exception Handling?
      2. The Exception Section
      3. Types of Exceptions
      4. Determining Exception-Handling Behavior
      5. Raising an Exception
      6. Handling Exceptions
      7. Client-Server Error Communication
      8. NO_DATA_FOUND: Multipurpose Exception
      9. Exception Handler as IF Statement
      10. RAISE Nothing but Exceptions

    6. Chapter 9 Records in PL/SQL

      1. Record Basics
      2. Table-Based Records
      3. Cursor-Based Records
      4. Programmer-Defined Records
      5. Assigning Values to and from Records
      6. Record Types and Record Compatibility
      7. Nested Records

    7. Chapter 10 PL/SQL Tables

      1. PL/SQL Tables and Other Collections
      2. Characteristics of PL/SQL Tables
      3. PL/SQL Tables and DML Statements
      4. Declaring a PL/SQL Table
      5. Referencing and Modifying PL/SQL Table Rows
      6. Filling the Rows of a PL/SQL Table
      7. Clearing the PL/SQL Table
      8. PL/SQL Table Enhancements in PL/SQL Release 2.3
      9. Working with PL/SQL Tables

  3. Built-In Functions

    1. Chapter 11 Character Functions

      1. Character Function Descriptions
      2. Character Function Examples

    2. Chapter 12 Date Functions

      1. Date Function Descriptions
      2. Date Function Examples

    3. Chapter 13 Numeric, LOB, and Miscellaneous Functions

      1. Numeric Function Descriptions
      2. LOB Function Descriptions
      3. Miscellaneous Function Descriptions

    4. Chapter 14 Conversion Functions

      1. Conversion Formats
      2. Conversion Function Descriptions
      3. Conversion Function Examples

  4. Modular Code

    1. Chapter 15 Procedures and Functions

      1. Modular Code
      2. Review of PL/SQL Block Structure
      3. The Anonymous PL/SQL Block
      4. Procedures
      5. Functions
      6. Parameters
      7. Local Modules
      8. Module Overloading
      9. Forward Declarations
      10. Go Forth and Modularize!

    2. Chapter 16 Packages

      1. The Benefits of Packages
      2. Overview of Package Structure
      3. The Package Specification
      4. The Package Body
      5. Package Data
      6. Package Initialization

    3. Chapter 17 Calling PL/SQL Functions in SQL

      1. Looking at the Problem
      2. Syntax for Calling Stored Functions in SQL
      3. Requirements for Stored Functions in SQL
      4. Restrictions on PL/SQL Functions in SQL
      5. Calling Packaged Functions in SQL
      6. Column/Function Name Precedence
      7. Realities: Calling PL/SQL Functions in SQL
      8. Examples of Embedded PL/SQL

  5. New PL/SQL8 Features

    1. Chapter 18 Object Types

      1. Introduction to Oracle8 Objects
      2. Oracle Objects Example
      3. Syntax for Creating Object Types
      4. Manipulating Objects in PL/SQL and SQL
      5. Modifying Persistent Objects
      6. Object Housekeeping
      7. Making the Objects Option Work

    2. Chapter 19 Nested Tables and VARRAYs

      1. Types of Collections
      2. Creating the New Collections
      3. Syntax for Declaring Collection Datatypes
      4. Using Collections
      5. Collection Pseudo-Functions
      6. Collection Built-Ins
      7. Example: PL/SQL-to-Server Integration
      8. Collections Housekeeping
      9. Which Collection Type Should I Use?

    3. Chapter 20 Object Views

      1. Example: Using Object Views
      2. INSTEAD OF Triggers
      3. Syntax for Object Views
      4. Differences Between Object Views and Object Tables
      5. Not All Views with Objects Are Object Views
      6. Schema Evolution
      7. Object Views Housekeeping
      8. Postscript: Using the BFILE Datatype

    4. Chapter 21 External Procedures

      1. Introduction to External Procedures
      2. Steps in Creating an External Procedure
      3. Syntax for External Procedures
      4. Mapping Parameters
      5. OCI Service Routines
      6. External Procedure Housekeeping
      7. Examples

  6. Making PL/SQL Programs Work

    1. Chapter 22 Code Design Tips

      1. Select Meaningful Module and Parameter Names
      2. Build the Most Functional Functions
      3. Take Full Advantage of Local Modularization
      4. Be Wary of Modules Without Any Parameters
      5. Create Independent Modules
      6. Construct Abstract Data Types (ADTs)
      7. Tips for Parameter Design

    2. Chapter 23 Managing Code in the Database

      1. Executing Stored Code
      2. Transaction Integrity and Execute Authority
      3. Module Validation and Dependency Management
      4. Remote Procedure Calls
      5. Managing Stored Objects with SQL*Plus
      6. Using SQL to Examine Stored Objects
      7. Encrypting Stored Code

    3. Chapter 24 Debugging PL/SQL

      1. The Wrong Way to Debug
      2. Debugging Tips and Strategies

    4. Chapter 25 Tuning PL/SQL Applications

      1. Analyzing Program Performance
      2. Tuning Access to Compiled Code
      3. Tuning Access to Your Data
      4. Tuning Your Algorithms
      5. Overview of PL/SQL8 Enhancements

    5. Chapter 26 Tracing PL/SQL Execution

      1. The PL/SQL Trace Facility
      2. Tracing for Production Support
      3. Free Format Filtering
      4. Structured Interface Filtering
      5. Quick-and-Dirty Tracing

  7. Appendixes

    1. Appendix B Calling Stored Procedures from PL/SQL Version 1.1

      1. Using Stubs to Talk to Server-Side PL/SQL
      2. Restrictions on Calling Stored Procedures

    2. Appendix C Built-In Packages

      1. Using the Built-in Packages
      2. DBMS_ALERT
      3. Oracle AQ, the Advanced Queueing Facility
      4. DBMS_DDL
      5. DBMS_ JOB
      6. DBMS_LOB (PL/SQL8 Only)
      7. DBMS_LOCK
      8. DBMS_MAIL
      9. DBMS_OUTPUT
      10. DBMS_PIPE
      11. DBMS_ROWID (PL/SQL8 Only)
      12. DBMS_SESSION
      13. DBMS_SNAPSHOT
      14. DBMS_SQL
      15. DBMS_TRANSACTION
      16. DBMS_UTILITY
      17. UTL_FILE

  1. Colophon

Return to Oracle PL/SQL Programming