Добавил:
Upload
Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз:
Предмет:
Файл:Programming PL SQL.doc
X
- •Dedication
- •Preface
- •Objectives of This Book
- •Structure of This Book
- •About the Contents
- •What This Book Does Not Cover
- •Conventions Used in This Book
- •Comments and Questions
- •Acknowledgments
- •Which Platform or Version?
- •About the Code
- •Part I: Programming in pl/sql
- •Chapter 1. Introduction to pl/sql
- •1.1 What Is pl/sql?
- •1.2 The Origins of pl/sql
- •1.2.1 The Early Years of pl/sql
- •1.2.2 Improved Application Portability
- •1.2.3 Improved Execution Authority and Transaction Integrity
- •1.2.4 Humble Beginnings, Steady Improvement
- •1.3 So This Is pl/sql
- •1.3.1 Integration with sql
- •1.3.2 Control and Conditional Logic
- •1.3.3 When Things Go Wrong
- •1.4 About pl/sql Versions
- •1.4.1 Oracle8i New Features
- •1.4.1.1 Autonomous transactions
- •1.4.1.2 Invoker rights
- •1.4.1.3 Native dynamic sql (nds)
- •1.4.1.4 Bulk binds and collects
- •1.4.1.5 New trigger capabilities
- •1.4.1.6 Calling Java from pl/sql
- •1.4.2 Oracle9i New Features
- •1.4.2.1 Record-based dml
- •1.4.2.2 Table functions
- •1.4.2.3 New and improved datatypes
- •1.4.2.4 Inheritance for object types
- •1.4.2.5 Enhancements to pl/sql collections
- •1.4.2.6 Native compilation of pl/sql code
- •1.4.3 Working with Multiple Versions of pl/sql
- •1.5 Resources for pl/sql Developers
- •1.5.1 The o'Reilly pl/sql Series
- •1.5.2 Other Printed Resources
- •1.5.3 Pl/sql on the Internet
- •1.5.4 Development Tools and Utilities
- •1.6 Some Words of Advice
- •1.6.1 Don't Be in Such a Hurry!
- •1.6.2 Don't Be Afraid to Ask for Help
- •1.6.3 Take a Creative, Even Radical Approach
- •Chapter 2. Creating and Running pl/sql Code
- •2.1 Sql*Plus
- •Figure 2-1. Sql*Plus in a console session
- •2.1.1 Starting Up sql*Plus
- •Figure 2-2. The gui login screen of sql*Plus
- •Figure 2-3. The iSql*Plus login page
- •2.1.2 Running a sql Statement
- •Figure 2-4. Query with result in iSql*Plus
- •2.1.3 Running a pl/sql Program
- •2.1.4 Running a Script
- •2.1.5 Other sql*Plus Tasks
- •2.1.5.1 Setting your preferences
- •2.1.5.2 Saving output to a file
- •2.1.5.3 Exiting sql*Plus
- •2.1.5.4 Editing a statement
- •2.1.5.5 Loading your own custom environment automatically on startup
- •2.1.6 Error Handling in sql*Plus
- •2.1.7 Why You Will Love and Hate sql*Plus
- •2.2 Performing Essential pl/sql Tasks
- •2.2.1 Creating a Stored Program
- •2.2.2 Executing a Stored Program
- •2.2.3 Showing Stored Programs
- •2.2.4 Managing Grants and Synonyms for Stored Programs
- •2.2.5 Dropping a Stored Program
- •2.2.6 Hiding the Source Code of a Stored Program
- •2.3 Oracle's pl/sql-Based Developer Tools
- •Figure 2-5. The programmer's user interface in Oracle Forms Builder
- •2.3.1 Moving pl/sql Programs Between Client and Server
- •Figure 2-6. The Object Navigator in Oracle Forms shows the result of dragging and dropping two packages from the server to the client
- •2.4 Calling pl/sql from Other Languages
- •2.4.1 C: Using Oracle's Precompiler (Pro*c)
- •2.4.2 Java: Using jdbc
- •2.4.3 Perl: Using Perl dbi and dbd::Oracle
- •2.4.4 Pl/sql Server Pages
- •Figure 2-7. Output from a pl/sql Server Page
- •2.5 And What Else?
- •Chapter 3. Language Fundamentals
- •3.1 Pl/sql Block Structure
- •3.1.1 Sections of the pl/sql Block
- •Figure 3-1. The pl/sql block structure
- •Figure 3-2. A procedure containing all four sections
- •3.1.2 Anonymous Blocks
- •Figure 3-3. An anonymous block without declaration and exception sections
- •3.1.2.1 The structure of an anonymous block
- •3.1.2.2 Examples of anonymous blocks
- •3.1.2.3 Anonymous blocks in different environments
- •3.1.3 Named Blocks
- •3.1.4 Nested Blocks
- •3.1.6.1 "Visible" identifiers
- •Figure 3-4. Anonymous blocks nested three levels deep
- •3.1.5 Scope
- •3.1.6 Visibility
- •3.1.6.2 Qualified identifiers
- •3.1.6.3 Qualifying identifier names with module names
- •3.2 The pl/sql Character Set
- •3.3 Identifiers
- •3.3.1 Reserved Words
- •3.3.1.1 Language keywords
- •3.3.1.2 Identifiers from standard package
- •3.3.1.3 Approaches to avoiding reserved words
- •3.3.2 Whitespace and Keywords
- •3.4 Literals
- •3.4.1 Embedding Single Quotes Inside a String
- •3.4.2 Numeric Literals
- •3.4.3 Boolean Literals
- •3.5 The Semicolon Delimiter
- •3.6 Comments
- •3.6.1 Single-Line Comment Syntax
- •3.6.2 Multiline Comment Syntax
- •3.7 The pragma Keyword
- •3.8 Labels
- •Part II: pl/sql Program Structure
- •Chapter 4. Conditional and Sequential Control
- •4.1 If Statements
- •4.1.1 The if-then Combination
- •4.1.2 The if-then-else Combination
- •4.1.3 The if-then-elsif Combination
- •4.1.4 Nested if Statements
- •4.2 Case Statements
- •4.2.1 Simple case Statements
- •4.2.2 Searched case Statements
- •4.2.3 Nested case Statements
- •4.2.4 Case Expressions
- •4.3 The goto Statement
- •4.3.1 Restrictions on the goto Statement
- •4.3.1.1 At least one executable statement must follow a label
- •4.3.1.2 The target label must be in the same scope as the goto statement
- •4.3.1.3 The target label must be in the same part of the pl/sql block as the goto
- •4.4 The null Statement
- •4.4.1 Improving Program Readability
- •4.4.2 Nullifying a Raised Exception
- •4.4.3 Using null After a Label
- •Chapter 5. Iterative Processing with Loops
- •5.1 Loop Basics
- •5.1.1 Examples of Different Loops
- •5.1.2 Structure of pl/sql Loops
- •Figure 5-1. The boundary and body of the while loop
- •5.2 The Simple Loop
- •5.2.1 Terminating a Simple Loop: exit and exit when
- •5.2.2 Emulating a repeat until Loop
- •5.3 The while Loop
- •5.4 The Numeric for Loop
- •5.4.1 Rules for Numeric for Loops
- •5.4.2 Examples of Numeric for Loops
- •5.4.3 Handling Nontrivial Increments
- •5.5 The Cursor for Loop
- •5.5.1 Example of Cursor for Loops
- •5.6 Loop Labels
- •5.7 Tips for Iterative Processing
- •5.7.1 Use Understandable Names for Loop Indexes
- •5.7.2 The Proper Way to Say Goodbye
- •5.7.3 Obtaining Information About for Loop Execution
- •5.7.4 Sql Statement as Loop
- •Chapter 6. Exception Handlers
- •6.1 How pl/sql Deals with Errors
- •Figure 6-1. Exception-handling architecture
- •6.1.1 Adopting an Exception-Handling Strategy
- •6.1.2 Exception-Handling Concepts and Terminology
- •6.2 Defining Exceptions
- •6.2.1 Declaring Named Exceptions
- •6.2.2 Associating Exception Names with Error Codes
- •6.2.2.1 Using exception_init
- •6.2.2.2 Recommended uses of exception_init
- •6.2.3 About Named System Exceptions
- •6.2.4 Scope of an Exception
- •6.3 Raising Exceptions
- •6.3.1 The raise Statement
- •6.3.1.1 Raising exceptions in nested blocks
- •6.3.2 Using raise_application_error
- •6.4 Handling Exceptions
- •6.4.1 Combining Multiple Exceptions in a Single Handler
- •6.4.2 Unhandled Exceptions
- •6.4.3 Using sqlcode and sqlerrm in Handler Clauses
- •6.4.4 Continuing Past Exceptions
- •Figure 6-2. Sequential deletEs, using two different approaches to scope
- •6.4.5 Propagation of an Unhandled Exception
- •Figure 6-3. Propagation of an exception through nested blocks
- •6.4.5.1 Losing exception information
- •Figure 6-4. Propagation of exception handling to first nested block
- •6.4.5.2 Examples of propagation
- •Figure 6-5. Exception raised in nested block handled by outermost block
- •6.4.6 Using Standardized Error Handler Programs
- •Part III: pl/sql Program Data
- •Chapter 7. Working with Program Data
- •7.1 Naming Your Program Data
- •7.2 Overview of pl/sql Datatypes
- •7.2.1 Character Data
- •7.2.2 Numbers
- •7.2.3 Dates, Timestamps, and Intervals
- •7.2.4 Booleans
- •7.2.5 Binary Data
- •7.2.6 RowiDs
- •7.2.7 Ref Cursors
- •7.2.8 Internet Datatypes
- •7.2.9 "Any" Datatypes
- •7.2.10 User-Defined Datatypes
- •7.3 Declaring Program Data
- •7.3.1 Declaring a Variable
- •7.3.2 Declaring Constants
- •7.3.3 Constrained Declarations
- •7.3.4 The not null Clause
- •7.3.5 Anchored Declarations
- •Figure 7-1. Anchored declarations with %type
- •7.3.6 Anchoring to Cursors and Tables
- •7.3.7 Benefits of Anchored Declarations
- •7.3.7.1 Synchronization with database columns
- •7.3.7.2 Normalization of local variables
- •7.3.8 Anchoring to not null Datatypes
- •7.4 Programmer-Defined Subtypes
- •7.5 Conversion Between Datatypes
- •7.5.1 Implicit Data Conversion
- •Figure 7-2. Implicit conversions performed by pl/sql
- •7.5.1.1 Limitations of implicit conversion
- •7.5.1.2 Drawbacks of implicit conversion
- •7.5.2 Explicit Datatype Conversion
- •7.5.2.1 The chartorowid function
- •7.5.2.2 The cast function
- •Figure 7-3. Casting built-in datatypes
- •7.5.2.3 The convert function
- •7.5.2.4 The hextoraw function
- •7.5.2.5 The rawtohex function
- •7.5.2.6 The rowidtochar function
- •Chapter 8. Strings
- •8.1 The Impact of Character Sets
- •8.1.1 What Is a Character Set?
- •8.1.2 Types of Character Sets
- •8.1.3 Database Character Set Versus National Language Character Set
- •Figure 8-1. Oracle's character set naming convention
- •8.1.4 Character Set Issues
- •8.1.4.1 Bytes versus characters
- •8.1.4.2 Oracle9i string declarations
- •8.1.4.3 Character function semantics
- •8.1.4.4 Code points and code units
- •8.1.4.5 Equality of Unicode strings
- •8.1.4.6 Sort order
- •8.2 String Datatypes
- •8.2.1 The varchar2 Datatype
- •8.2.2 The char Datatype
- •8.2.3 The nvarchar2 and nchar Datatypes
- •8.2.4 String Subtypes
- •8.3 String Issues
- •8.3.1 Empty Strings Are null Strings
- •8.3.2 Mixing char and varchar2 Values
- •8.3.2.1 Database-to-variable conversion
- •8.3.2.2 Variable-to-database conversion
- •8.3.2.3 String comparisons
- •8.3.2.4 Character functions and char arguments
- •8.3.3 Specifying String Constants
- •8.4 String Functions
- •Figure 8-2. Forward and reverse searches with instr
- •Figure 8-3. How arguments are used by substr
- •8.5 Nls Functions
- •Chapter 9. Numbers
- •9.1 Numeric Datatypes
- •9.1.1 The number Type
- •Figure 9-1. A typical fixed-point number declaration
- •Figure 9-2. The effect of scale exceeding precision
- •Figure 9-3. The effect of negative scale
- •9.1.2 The pls_integer Type
- •9.1.3 The binary_integer Type
- •9.1.4 Numeric Subtypes
- •9.2 Number Conversions
- •9.2.1 Number Format Models
- •9.2.2 The to_number Function
- •9.2.2.1 Using to_number with no format
- •9.2.2.2 Using to_number with a format model
- •9.2.2.3 Passing nls settings to to_number
- •9.2.3 The to_char Function
- •9.2.3.1 Using to_char with no format
- •9.2.3.2 Using to_char with a format model
- •9.2.3.3 The V format element
- •Figure 9-4. The V number format element
- •9.2.3.4 Rounding when converting numbers to character strings
- •9.2.3.5 Dealing with spaces when converting numbers to character strings
- •9.2.3.6 Passing nls settings to to_char
- •9.2.4 Using cast
- •9.2.5 Implicit Conversions
- •9.3 Numeric Functions
- •9.3.1 Rounding and Truncation Functions
- •Figure 9-5. Impact of rounding and truncating functions
- •Chapter 10. Dates and Timestamps
- •10.1 Date and Time Datatypes
- •10.1.1 The date Datatype
- •10.1.1.1 Declaring date variables
- •10.1.1.2 When to use date
- •10.1.1.3 Limitations of date
- •10.1.2 The timestamp Datatypes
- •Figure 10-1. Effect of different datetime datatypes
- •10.1.2.1 Declaring timestamp variables
- •10.1.2.2 When to use timestamPs
- •10.1.3 The interval Datatypes
- •10.1.3.1 Declaring interval variables
- •10.1.3.2 When to use intervaLs
- •10.2 Date and Timestamp Conversions
- •10.2.1 Date Format Models
- •10.2.2 String-to-Date Conversions
- •10.2.2.1 To_date
- •10.2.2.2 The to_timestamp family
- •10.2.2.3 Dealing with time zones
- •10.2.2.4 Date and timestamp literals
- •10.2.2.5 The fx element
- •10.2.2.6 The rr element
- •10.2.3 Date-to-String Conversions
- •10.2.3.1 To_char
- •10.2.3.2 Converting time zones to character strings
- •10.2.3.3 The fm element
- •10.2.4 Interval Conversions
- •10.2.4.1 The numto family of functions
- •10.2.4.2 The to_xxInterval functions
- •10.2.4.3 Interval value expressions
- •10.2.4.4 Formatting intervals for display
- •10.2.5 The cast and extract Functions
- •10.2.5.1 The cast function
- •10.2.5.2 The extract function
- •10.3 Date/Time Arithmetic
- •10.3.1 Traditional Date Arithmetic
- •10.3.1.1 Adding and subtracting numeric values
- •10.3.1.2 Computing the difference between two dates
- •10.3.2 Interval Arithmetic
- •10.3.2.1 Adding and subtracting intervals to/from datetimes
- •10.3.2.2 Computing the interval between two datetimes
- •10.3.2.3 CasTing datEs to timestamPs
- •10.3.2.4 Adding and subtracting intervals
- •10.3.2.5 Multiplying and dividing intervals
- •10.3.2.6 Unconstrained interval types
- •10.4 Date/Time Functions
- •10.4.1 Functions to Get the Date and Time
- •10.4.2 The Time Zone Functions
- •10.4.3 The add_months Function
- •10.4.4 The from_tz Function
- •10.4.5 The last_day Function
- •10.4.6 The months_between Function
- •10.4.7 The round and trunc Functions
- •10.4.8 The new_time Function
- •10.4.9 The next_day Function
- •Chapter 11. Records and Collections
- •11.1 Records in pl/sql
- •11.1.1 Benefits of Using Records
- •11.1.1.1 Data abstraction
- •11.1.1.2 Aggregate operations
- •11.1.1.3 Leaner, cleaner code
- •11.1.2 Declaring Records
- •11.1.3 Programmer-Defined Records
- •11.1.3.1 Declaring programmer-defined record typEs
- •11.1.3.2 Declaring the record
- •11.1.3.3 Examples of programmer-defined record declarations
- •11.1.4 Working with Records
- •11.1.4.1 Record-level operations
- •11.1.4.2 Field-level operations
- •11.1.5 Comparing Records
- •11.2 Collections in pl/sql
- •11.2.1 A Simple Collection Example
- •11.2.2 Types of Collections
- •11.2.3 Glossary of Collection Terms
- •11.2.4 Making Sense of Collections
- •11.3 Declaring Collection Types and Collections
- •11.3.1 Declaring an Associative Array
- •11.3.1.1 Defining the table type
- •11.3.1.2 Declaring the collection
- •11.3.2 Declaring a Nested Table or varray
- •11.3.2.1 Examples of declaring nested tables and varraYs
- •11.4 Where Collections Can Be Used
- •11.4.1 Collections as Components of a Record
- •11.4.2 Collections as Program Parameters
- •11.4.3 Collections as Datatypes of a Function's Return Value
- •11.4.4 Collection as "Columns" in a Database Table
- •11.4.5 Collections as Attributes of an Object Type
- •11.5 Collection Built-Ins (Methods)
- •11.5.1 The count Method
- •11.5.1.1 Boundary considerations
- •11.5.1.2 Exceptions possible
- •11.5.2 The delete Method
- •11.5.2.1 Boundary considerations
- •11.5.2.2 Exceptions possible
- •11.5.3 The exists Method
- •11.5.3.1 Boundary considerations
- •11.5.3.2 Exceptions possible
- •11.5.4 The extend Method
- •11.5.4.1 Boundary considerations
- •11.5.4.2 Exceptions possible
- •11.5.5 The first and last Methods
- •11.5.5.1 Boundary considerations
- •11.5.5.2 Exceptions possible
- •11.5.6 The limit Method
- •11.5.6.1 Boundary considerations
- •11.5.6.2 Exceptions possible
- •11.5.7 The prior and next Methods
- •11.5.7.1 Boundary considerations
- •11.5.7.2 Exceptions possible
- •11.5.8 The trim Method
- •11.5.8.1 Boundary considerations
- •11.5.8.2 Exceptions possible
- •11.6 Working with Collections
- •11.6.1 Initializing Collection Variables
- •11.6.1.1 Initializing explicitly with a constructor
- •11.6.1.2 Initializing implicitly during direct assignment
- •11.6.1.3 Initializing implicitly via fetch
- •11.6.1.4 Varray integration
- •11.6.2 Assigning Values to Elements
- •11.6.3 Referencing an Undefined Row
- •11.6.4 Working with Collections of Composites
- •11.6.4.1 Collections of records
- •11.6.4.2 Collections of other complex datatypes
- •11.6.4.3 Multilevel collections
- •11.6.4.4 Unnamed nested collections
- •11.6.5 Sequential and Nonsequential Associative Arrays
- •11.6.5.1 Sequential usage
- •11.6.5.2 Nonsequential usage
- •11.6.6 Passing Associative Arrays as Parameters
- •11.6.7 Pl/sql-to-Server Integration
- •11.6.8 Using varchar2 Associative Arrays
- •11.6.9 Emulating Alternative Indexes in Collections
- •Figure 11-1. Populating and accessing a hash index
- •11.7 Collection Pseudo-Functions
- •11.7.1 The the Pseudo-Function
- •11.7.2 The table Pseudo-Function
- •11.7.3 The cast Pseudo-Function
- •11.7.3.1 Casting a named collection
- •11.7.3.2 Casting an unnamed collection
- •11.7.4 The multiset Pseudo-Function
- •11.7.5 Sorting Contents of Collections
- •11.8 Maintaining Collections
- •11.8.1 Privileges
- •11.8.2 Collections and the Data Dictionary
- •11.9 Choosing a Collection Type
- •Chapter 12. Miscellaneous Datatypes
- •12.1 The boolean Datatype
- •12.2 The raw Datatype
- •12.3 The urowid and rowid Datatypes
- •Figure 12-1. RowiDs take you directly to rows in a table
- •12.3.1 Getting at Rowids
- •12.3.2 Using Rowids
- •12.3.2.1 Do rowids ever change?
- •12.3.2.2 Using rowids in Oracle Forms
- •12.3.2.3 Using rowids in a cursor for loop
- •12.3.2.4 Is the use of rowids worth the effort?
- •12.4 The lob Datatypes
- •12.4.1 The bfile Datatype
- •12.4.2 The blob Datatype
- •12.4.3 The clob Datatype
- •12.4.4 The nclob Datatype
- •12.5 Working with loBs
- •Figure 12-2. The Dryer Hose in Munising, Michigan
- •12.5.1 Understanding lob Locators
- •Figure 12-3. A lob locator points to its associated large object data within the database
- •12.5.2 Empty Versus null loBs
- •12.5.3 Creating a lob
- •12.5.4 Writing into a lob
- •12.5.5 Reading from a lob
- •12.5.6 BfilEs Are Different
- •12.5.6.1 Creating a bfile locator
- •12.5.6.2 Accessing bfilEs
- •12.5.6.3 Using bfilEs to load lob columns
- •12.5.7 Temporary loBs
- •12.5.7.1 Creating a temporary lob
- •12.5.7.2 Freeing a temporary lob
- •12.5.7.3 Checking to see whether a lob is temporary
- •12.5.7.4 Managing temporary loBs
- •12.5.8 Native lob Operations in Oracle9i
- •12.5.8.1 Sql semantics may yield temporary loBs
- •12.5.8.2 Performance impact of using sql semantics
- •12.5.9 Lob Conversion Functions
- •12.6 Predefined Object Types
- •12.6.1 The xmlType Type
- •2 From falls f
- •2 From falls
- •12.6.2 The uri Types
- •12.6.3 The "Any" Types
- •Part IV: sql in pl/sql
- •Chapter 13. Dml and Transaction Management
- •13.1.1 A Quick Introduction to dml
- •13.1.1.1 The insert statement
- •13.1.1.2 The update statement
- •13.1.1.3 The delete statement
- •13.1.2 Cursor Attributes for dml Operations
- •13.1.3 Returning Information from dml Statements
- •13.1.4 Dml and Exception Handling
- •13.1.5 Dml and Records
- •13.1.5.1 Record-based inserts
- •13.1.5.2 Record-based updates
- •13.1.5.3 Using records with the returning clause
- •13.1.5.4 Restrictions on record-based inserts and updates
- •13.2 Bulk dml with the forall Statement
- •Figure 13-1. Context switching between pl/sql and sql
- •Figure 13-2. One context switch with forall
- •13.2.1 The forall Statement
- •13.2.2 Context-Switching Problem Scenarios
- •Figure 13-3. Excessive context switching for multiple updatEs
- •13.2.3 Forall Examples
- •13.2.4 Cursor Attributes for forall
- •13.2.5 Rollback Behavior with forall
- •13.2.6 Continuing Past Exceptions with forall
- •13.3 Transaction Management
- •13.3.1 The commit Statement
- •13.3.2 The rollback Statement
- •13.3.3 The savepoint Statement
- •13.3.4 The set transaction Statement
- •13.3.5 The lock table Statement
- •13.4 Autonomous Transactions
- •Figure 13-4. Flow of transaction control between main, nested, and autonomous transactions
- •13.4.1 Defining Autonomous Transactions
- •13.4.2 When to Use Autonomous Transactions
- •13.4.3 Rules and Restrictions on Autonomous Transactions
- •13.4.3.1 Using autonomous transactions from within sql
- •13.4.3.2 Transaction visibility
- •13.4.4 Autonomous Transactions Examples
- •13.4.4.1 Building an autonomous logging mechanism
- •13.4.4.2 Using autonomous transactions in a database trigger
- •13.4.4.2.1 Creating a database trigger
- •13.4.4.2.2 Fine-tuning the database trigger
- •Chapter 14. Data Retrieval
- •14.1 Cursor Basics
- •14.1.1 Some Data Retrieval Terms
- •14.1.2 Typical Query Operations
- •Figure 14-1. Simplified view of cursor fetch operation
- •14.1.3 Introduction to Cursor Attributes
- •14.1.3.1 The %found attribute
- •14.1.3.2 The %notfound attribute
- •14.1.3.3 The %rowcount attribute
- •14.1.3.4 The %isopen attribute
- •14.1.3.5 The %bulk_rowcount attribute
- •14.1.3.6 The %bulk_exceptions attribute
- •14.1.4 Referencing pl/sql Variables in a Cursor
- •14.1.4.1 Identifier precedence in a cursor
- •14.1.4.2 Using standard naming conventions
- •14.1.5 Choosing Between Explicit and Implicit Cursors
- •14.2 Working with Implicit Cursors
- •14.2.1 Implicit Cursor Examples
- •14.2.2 Error Handling with Implicit Cursors
- •14.2.3 Implicit sql Cursor Attributes
- •14.3 Working with Explicit Cursors
- •14.3.1 Declaring Explicit Cursors
- •14.3.1.1 Naming your cursor
- •14.3.1.2 Declaring cursors in packages
- •14.3.2 Opening Explicit Cursors
- •14.3.3 Fetching from Explicit Cursors
- •14.3.3.1 Examples of explicit cursors
- •14.3.3.2 Fetching past the last row
- •14.3.4 Column Aliases in Explicit Cursors
- •14.3.5 Closing Explicit Cursors
- •14.3.6 Explicit Cursor Attributes
- •14.3.7 Cursor Parameters
- •14.3.7.1 Generalizing cursors with parameters
- •14.3.7.2 Opening cursors with parameters
- •14.3.7.3 Scope of cursor parameters
- •14.3.7.4 Cursor parameter modes
- •14.3.7.5 Default values for parameters
- •14.4 Bulk collect
- •14.4.1 Limiting Rows Retrieved with bulk collect
- •14.4.2 Bulk Fetching of Multiple Columns
- •14.4.3 Using the returning Clause with Bulk Operations
- •14.5 Select...For update
- •14.5.1 Releasing Locks with commit
- •14.5.2 The where current of Clause
- •14.6 Cursor Variables
- •Figure 14-2. Referencing a cursor variable across two programs
- •14.6.1 Why Cursor Variables?
- •14.6.2 Similarities to Static Cursors
- •14.6.3 Declaring ref cursor Types
- •14.6.4 Declaring Cursor Variables
- •Figure 14-3. The referencing character of cursor variables
- •14.6.5 Opening Cursor Variables
- •Figure 14-4. Compatible ref cursor rowtype and select list
- •14.6.6 Fetching from Cursor Variables
- •14.6.6.1 Handling the rowtype_mismatch exception
- •14.6.7 Rules for Cursor Variables
- •14.6.7.1 Compile-time rowtype matching rules
- •14.6.7.2 Runtime rowtype matching rules
- •14.6.7.3 Cursor variable aliases
- •14.6.7.4 Scope of cursor object
- •14.6.8 Passing Cursor Variables as Arguments
- •14.6.8.1 Identifying the ref cursor type
- •14.6.8.2 Setting the parameter mode
- •14.6.9 Cursor Variable Restrictions
- •14.7 Cursor Expressions (Oracle9i)
- •14.7.1 Using Cursor Expressions
- •14.7.2 Restrictions on Cursor Expressions
- •Chapter 15. Dynamic sql and Dynamic pl/sql
- •15.1 Nds Statements
- •15.1.1 The execute immediate Statement
- •15.1.2 The open for Statement
- •15.2 Multirow Queries with Cursor Variables
- •15.2.1 Fetch into Variables or Records
- •15.2.2 The using Clause in open for
- •15.2.3 Generic group by Procedure
- •15.2.4 Generic group by Package
- •15.3 Binding Variables
- •15.3.1 Binding Versus Concatenation
- •15.3.2 Limitations on Binding
- •15.3.3 Argument Modes
- •15.3.4 Duplicate Placeholders
- •15.3.5 Passing null Values
- •15.4 Working with Objects and Collections
- •15.5 Building Applications with nds
- •15.5.1 Sharing nds Programs with Invoker Rights
- •15.5.2 Error Handling
- •15.5.3 Dynamic pl/sql
- •15.5.3.1 Dramatic code reduction
- •15.5.3.2 Generic calculator function
- •15.6 Nds Utility Package
- •15.7 Comparing nds and dbms_sql
- •15.7.1 Eyeballing Equivalent Implementations
- •15.7.2 What Are nds and dbms_sql Good For?
- •Part V: pl/sql Application Construction
- •Chapter 16. Procedures, Functions,and Parameters
- •16.1 Modular Code
- •16.2 Procedures
- •Figure 16-1. The apply_discount procedure
- •16.2.1 Calling a Procedure
- •16.2.2 The Procedure Header
- •16.2.3 The Procedure Body
- •16.2.4 The end Descriptor
- •16.2.5 The return Statement
- •16.3 Functions
- •16.3.1 Structure of a Function
- •Figure 16-2. The tot_sales function
- •16.3.2 The return Datatype
- •16.3.3 The end Descriptor
- •16.3.4 Calling a Function
- •16.3.5 Functions Without Parameters
- •16.3.6 The Function Header
- •16.3.7 The Function Body
- •16.3.8 The return Statement
- •16.3.8.1 Return any valid expression
- •16.3.8.2 Multiple returNs
- •16.3.8.3 Return as last executable statement
- •16.4 Parameters
- •16.4.1 Defining Parameters
- •16.4.2 Actual and Formal Parameters
- •16.4.3 Matching Actual and Formal Parameters in pl/sql
- •16.4.3.1 Positional notation
- •Figure 16-3. Matching actual with formal parameters (positional notation)
- •16.4.3.2 Named notation
- •16.4.3.3 Benefits of named notation
- •16.4.4 Parameter Modes
- •16.4.4.1 In mode
- •16.4.4.2 Out mode
- •16.4.4.3 In out mode
- •16.4.5 The nocopy Parameter Mode Hint
- •16.4.5.1 Restrictions on nocopy
- •16.4.5.2 Impact of nocopy
- •16.4.6 Default Values
- •16.5 Local Modules
- •Figure 16-4. Local modules are hidden and inaccessible outside the program
- •16.5.1 Benefits of Local Modularization
- •16.5.1.1 Reducing code volume
- •16.5.1.2 Improving readability
- •16.5.2 Scope of Local Modules
- •16.5.3 Sprucing Up Your Code with Local Modules
- •16.6 Module Overloading
- •16.6.1 Benefits of Overloading
- •16.6.1.1 Supporting many data combinations
- •16.6.2 Restrictions on Overloading
- •16.7 Forward Declarations
- •16.8 Advanced Topics
- •16.8.1 Calling Your Function Inside sql
- •16.8.1.1 Requirements for calling functions in sql
- •16.8.1.2 Restrictions on user-defined functions in sql
- •16.8.1.3 Replacing decodEs with if statements
- •16.8.1.4 The pragma restrict_references (Oracle8 and earlier)
- •16.8.2 Table Functions
- •16.8.2.1 Calling a function in a from clause
- •16.8.2.2 Creating a pipelined function
- •16.8.2.3 Building a transformative function
- •16.8.2.4 Enabling a function for parallel execution
- •16.8.3 Deterministic Functions
- •16.9 Go Forth and Modularize!
- •Chapter 17. Packages
- •17.1 Why Packages?
- •17.1.1 Demonstrating the Power of the Package
- •17.1.2 Some Package-Related Concepts
- •17.1.3 Diagramming Privacy
- •Figure 17-1. Booch diagram showing public and private package elements
- •17.2 Rules for Building Packages
- •17.2.1 The Package Specification
- •17.2.2 The Package Body
- •17.2.3 Initializing Packages
- •17.2.3.1 Execute complex initialization logic
- •17.2.3.2 Cache static session information
- •17.2.3.3 Avoid side effects when initializing
- •17.2.3.4 When initialization fails
- •17.3 Rules for Calling Packaged Elements
- •17.4 Working with Package Data
- •17.4.1 Global Within a Single Oracle Session
- •17.4.2 Global Public Data
- •17.4.3 Packaged Cursors
- •17.4.3.1 Declaring packaged cursors
- •17.4.3.2 Working with packaged cursors
- •17.4.4 Serializable Packages
- •17.5 When to Use Packages
- •17.5.1 Encapsulating Data Manipulation
- •17.5.2 Avoiding Hardcoding of Literals
- •17.5.3 Improving Usability of Built-in Features
- •17.5.4 Grouping Together Logically Related Functionality
- •17.5.5 Caching Static Session Data
- •17.6 Packages and Object Types
- •Chapter 18. Triggers
- •18.1 Dml Triggers
- •Figure 18-1. Dml triggers fire in response to changes to a database table
- •18.1.1 Dml Trigger Concepts
- •18.1.1.1 Dml trigger scripts
- •18.1.1.2 Transaction participation
- •18.1.2 Creating a dml Trigger
- •18.1.2.1 The when clause
- •18.1.2.2 Working with new and old pseudo-records
- •18.1.2.3 Determining the dml action within a trigger
- •18.1.3 Dml Trigger Example: No Cheating Allowed!
- •18.1.3.1 Applying the when clause
- •18.1.3.2 Using pseudo-records to fine-tune trigger execution
- •18.1.4 Multiple Triggers of the Same Type
- •18.1.5 Mutating Table Errors: Problem and Solution
- •18.1.5.1 Mutating tables and foreign keys
- •18.1.5.2 Getting around the mutating table error
- •Figure 18-2. Using a collection as a work list to bypass mutating trigger errors
- •18.1.5.3 The dwindling mutation zone
- •18.2 Ddl Triggers
- •18.2.1 Creating a ddl Trigger
- •18.2.2 Available Events
- •18.2.3 Available Attributes
- •18.2.4 Working with Events and Attributes
- •18.2.4.1 What column did I touch?
- •18.2.4.2 Lists returned by attribute functions
- •18.2.5 Dropping the Undroppable
- •18.3 Database Event Triggers
- •18.3.1 Creating a Database Event Trigger
- •18.3.2 The startup Trigger
- •18.3.3 The shutdown Trigger
- •18.3.4 The logon Trigger
- •18.3.5 The logoff Trigger
- •18.3.6 The servererror Trigger
- •18.3.6.1 Servererror examples
- •18.3.6.2 Central error handler
- •18.3.7 Impact of Invalid Triggers
- •18.4 Instead of Triggers
- •18.4.1 Creating an instead of Trigger
- •18.4.2 The instead of insert Trigger
- •18.4.3 The instead of update Trigger
- •18.4.4 The instead of delete Trigger
- •18.4.5 Populating the Tables
- •18.5 After suspend Triggers
- •18.5.1 Setting Up for the after suspend Trigger
- •18.5.2 Looking at the Actual Trigger
- •18.5.3 Creating the after suspend Trigger
- •18.5.4 The ora_space_error_info Function
- •18.5.5 The dbms_resumable Package
- •18.5.6 Trapped Multiple Times
- •18.6 Maintaining Triggers
- •18.6.1 Disabling, Enabling, and Dropping Triggers
- •18.6.2 Viewing Triggers
- •18.6.3 Checking the Validity of Triggers
- •Chapter 19. Managing pl/sql Applications
- •19.1 Managing and Analyzing Code in the Database
- •19.1.1 Data Dictionary Views for pl/sql Programmers
- •19.1.2 Displaying Information About Stored Objects
- •19.1.3 Displaying and Searching Source Code
- •19.2 Protecting Stored Code
- •19.2.1 How to Wrap Code
- •19.2.2 Working with Wrapped Code
- •19.3 Using Native Compilation
- •19.3.2 Interpreted Versus Native Compilation Modes
- •19.4 Testing pl/sql Programs
- •19.4.1 Typical, Tawdry Testing Techniques
- •Figure 19-1. The utPlsql architecture
- •19.4.2 For More Information...
- •19.5 Debugging pl/sql Programs
- •19.5.1 The Wrong Way to Debug
- •19.5.1.1 Disorganized debugging
- •19.5.1.2 Irrational debugging
- •19.5.2 Debugging Tips and Strategies
- •19.5.2.1 Use a source code debugger
- •19.5.2.2 Gather data
- •19.5.2.3 Remain logical at all times
- •19.5.2.4 Analyze instead of trying
- •19.5.2.5 Take breaks and ask for help
- •19.5.2.6 Change and test one area of code at a time
- •19.6 Tuning pl/sql Programs
- •19.6.1 Analyzing Performance of pl/sql Code
- •19.6.2 Tracing Execution of Your Code
- •19.6.2.1 Installing dbms_trace
- •19.6.2.2 Dbms_trace programs
- •19.6.2.3 Controlling trace file contents
- •19.6.2.4 Pausing and resuming the trace process
- •19.6.2.5 Format of collected data
- •19.7 Improving Application Performance
- •19.7.1 Avoid Unnecessary Code Execution
- •19.7.1.1 The search for unnecessary code
- •19.7.1.2 Check your loops
- •19.7.1.3 Defer execution until needed
- •19.7.2 Be a Good Listener
- •19.7.3 Use Package Data to Minimize sql Access
- •19.7.4 Use bulk collect and forall
- •Part VI: Advanced pl/sql Topics
- •Chapter 20. Pl/sql's Runtime Architecture
- •20.1 Looking Under the Hood
- •20.1.1 Pl/sql Concepts
- •Figure 20-1. Execution of a do-nothing anonymous block
- •Figure 20-2. Execution of an anonymous block that contains sql
- •Figure 20-3. Execution of a program that calls a stored procedure
- •Figure 20-4. Execution of an anonymous block that calls a natively compiled program
- •20.1.2 Physical Storage of Server-Side pl/sql
- •20.1.3 DianAs Who Grew Too Much
- •20.2 Dependency Management
- •20.2.1 Dependencies in Server-Side pl/sql
- •Figure 20-5. Dependency graph of the bookworm package
- •20.2.2 Healing Invalids
- •20.2.2.1 Recompiling by hand
- •20.2.2.2 Recompiling by script
- •20.2.2.3 Automatic recompilation
- •20.2.3 Dependencies in Client-Side pl/sql
- •Figure 20-6. Viewing "References" information for a package body in a client-side pl/sql library
- •Figure 20-7. "Referenced By" information shows only those dependencies in the current module
- •20.2.4 Remote Dependencies
- •20.3 Pl/sql's Use of Memory in the Oracle Server
- •20.3.1 Server Memory 101
- •Figure 20-8. Oracle memory and process architecture in dedicated vs. Shared server configurations
- •20.3.2 Cursors and Memory
- •20.3.3 Tips on Reducing Memory Use
- •20.3.3.1 Statement sharing
- •20.3.3.2 Bind variables
- •20.3.3.3 Packaging to improve memory use
- •20.3.3.4 Large collections in pl/sql
- •20.3.3.5 Preservation of state
- •20.3.3.6 Global, but only within a single Oracle session
- •Figure 20-9. Two Oracle connections between Oracle Forms and Oracle Graphics
- •20.3.4 A Trace of Memory
- •20.4 The Processing of Server-Side pl/sql
- •20.4.1 Compiling an Anonymous Block
- •20.4.2 Compiling a Stored Object
- •20.4.3 Executing pl/sql
- •20.5 Pl/sql Code on the Client
- •Figure 20-10. Oracle client-side runtime environment invoking a stored procedure
- •20.5.1 Supported Versions and Features
- •20.5.2 Limitations of Oracle's Remote Invocation Model
- •20.5.3 Client-Side pl/sql Libraries
- •20.5.3.1 Client pl/sql libraries at design time
- •20.5.3.2 Client pl/sql libraries at runtime
- •20.6 Execution Authority Models
- •20.6.1 The Definer Rights Model
- •Figure 20-11. Controlling access to data with the definer rights model
- •20.6.1.1 Advantages of definer rights
- •20.6.1.2 Disadvantages of definer rights
- •20.6.1.2.1 Where'd my table go?
- •20.6.1.2.2 How do I maintain all that code?
- •Figure 20-12. Repetitive installations of code needed with definer rights
- •20.6.1.2.3 Dynamic sql and definer rights
- •20.6.2 The Invoker Rights Model
- •Figure 20-13. Use of invoker rights model
- •20.6.2.1 Invoker rights syntax
- •20.6.2.2 Some rules and restrictions
- •20.6.3 Combining Rights Models
- •20.7.1 The Single-Processor Variation
- •20.7.2 The Symmetric Multiprocessor (smp) Variation
- •20.7.3 The Clustered Variation
- •20.8 What You Need to Know
- •Chapter 21. Object-Oriented Aspects of pl/sql
- •21.1 Introduction to Oracle's Object Features
- •21.2 An Extended Example
- •21.2.1 A Tree of Types
- •Figure 21-1. Type hierarchy for a trivial library catalog
- •21.2.1.1 Creating a base type
- •21.2.1.2 Creating a subtype
- •21.2.2 Methods
- •21.2.3 Storing, Retrieving, and Using Persistent Objects
- •21.2.3.1 Object identity
- •21.2.3.2 The value function
- •21.2.3.3 The treat function
- •21.2.4 Evolution and Creation
- •21.2.5 Back to Pointers?
- •21.2.5.1 Using reFs
- •21.2.5.2 The reftohex function
- •21.2.5.3 The utl_ref package
- •21.2.5.4 ReFs and type hierarchies
- •21.2.5.5 Dangling reFs
- •21.2.6 Generic Generics: The any Datatypes
- •21.2.6.1 Processing an anydata value
- •21.2.6.2 Creating a transient type
- •21.2.7 I Can Do It Myself
- •21.2.8 Comparing Objects
- •21.2.8.1 Attribute-level comparison
- •21.2.8.2 The map method
- •21.2.8.3 The order method
- •21.2.8.4 Additional comparison recommendations
- •21.3 Object Views
- •Figure 21-2. Object views allow you to bind an object type definition to (existing) relational tables
- •21.3.1 The Existing Relational System
- •21.3.2 Object View with a Collection Attribute
- •21.3.3 Object Subview
- •21.3.4 Object View with Inverse Relationship
- •21.3.5 Instead of Triggers
- •21.3.5.1 The case against
- •21.3.5.2 The case for
- •21.3.5.3 The bigger question
- •21.3.6 Differences Between Object Views and Object Tables
- •21.3.6.1 Oid uniqueness
- •21.3.6.2 "Storeability" of physical versus virtual reFs
- •21.3.6.3 ReFs to non-unique oiDs
- •21.4 Maintaining Object Types and Object Views
- •21.4.1 Privileges
- •21.4.1.1 The execute privilege
- •21.4.1.2 The under privilege
- •21.4.1.3 The debug privilege
- •21.4.1.4 The dml privileges
- •21.5 Pontifications
- •Chapter 22. Calling Java from pl/sql
- •22.1 Oracle and Java
- •Figure 22-1. Accessing jsPs from within the Oracle database
- •22.2 Getting Ready to Use Java in Oracle
- •22.2.1 Installing Java
- •22.2.2 Building and Compiling Your Java Code
- •22.2.3 Setting Privileges for Java Development and Execution
- •22.2.3.1 Oracle8i Java security
- •22.2.3.2 Oracle9i Java security
- •22.3 A Simple Demonstration
- •22.3.1 Finding the Java Functionality
- •22.3.2 Building a Custom Java Class
- •Figure 22-2. A simple Java class used to delete a file
- •22.3.3 Compiling and Loading into Oracle
- •22.3.4 Building a pl/sql Wrapper
- •22.3.5 Deleting Files from pl/sql
- •22.4 Using loadjava
- •Figure 22-3. Loading Java elements into Oracle
- •22.5 Using dropjava
- •22.6 Managing Java in the Database
- •22.6.1 The Java Namespace in Oracle
- •22.6.2 Examining Loaded Java Elements
- •22.7 Using dbms_ java
- •22.7.1 Longname: Converting Java Long Names
- •22.7.2 Get_, set_, and reset_compiler_option: Getting and Setting Compiler Options
- •22.7.3 Set_output: Enabling Output from Java
- •22.7.4 Export_source, export_resource, and export_class: Exporting Schema Objects
- •22.8 Publishing and Using Java in pl/sql
- •22.8.1 Call Specs
- •22.8.2 Some Rules for Java Wrappers
- •22.8.3 Mapping Datatypes
- •22.8.4 Calling a Java Method in sql
- •22.8.5 Exception Handling with Java
- •22.8.6 Extending File I/o Capabilities
- •22.8.6.1 Polishing up the delete method
- •22.8.6.2 Obtaining directory contents
- •22.8.7 Other Examples
- •Chapter 23. External Procedures
- •23.1 Introduction to External Procedures
- •23.1.1 Example: Invoking an Operating System Command
- •23.1.2 Architecture of External Procedures
- •Figure 23-1. Invoking an external procedure that uses the default agent
- •23.1.3 Limitations of External Procedures
- •23.2 The Oracle Net Configuration
- •23.2.1 Specifying the Listener Configuration
- •23.2.2 Security Characteristics of the Configuration
- •23.3 Creating an Oracle Library
- •23.4 Writing the Call Specification
- •23.4.1 The Call Spec: Overall Syntax
- •23.4.2 Parameter Mapping: The Example Revisited
- •23.4.3 Parameter Mapping: The Full Story
- •23.4.4 More Syntax: The parameters Clause
- •23.4.5 Parameters Properties
- •23.4.5.1 The indicator property
- •23.4.5.2 The length property
- •23.4.5.3 The maxlen property
- •23.4.5.4 The charsetid and charsetform properties
- •23.5 Raising an Exception from the Called c Program
- •23.6 Nondefault Agents
- •23.7 Maintaining External Procedures
- •23.7.1 Dropping Libraries
- •23.7.2 Data Dictionary
- •23.7.3 Rules and Warnings
Part I: Programming in pl/sql
This first part of the book introduces PL/SQL, explains how to create and run PL/SQL code, and presents language fundamentals. Chapter 1 asks the fundamental questions: Where did PL/SQL come from? What is it good for? What are the main features of the PL/SQL language? Chapter 2 is designed to get you and up and running PL/SQL programs as quickly as possible; it contains clear, straightforward instructions for executing PL/SQL code in SQL*Plus and a few other common environments. Chapter 3 answers basic questions about the language structure and keywords: What makes up a PL/SQL statement? What is the PL/SQL block structure all about? How do I write comments in PL/SQL?
Chapter 1
Chapter 2
Chapter 3
Chapter 1. Introduction to pl/sql
This chapter introduces PL/SQL, its origins, and its various versions. It also offers a guide to additional resources for PL/SQL developers and some words of advice.
1.1 What Is pl/sql?
PL/SQL stands for "Procedural Language extensions to the Structured Query Language."SQL is the now-ubiquitous language for both querying and updating—never mind the name—of relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database.
PL/SQL has several defining characteristics:
It is a highly structured, readable, and accessible language
Modeled after the Ada language, PL/SQL incorporates some of the latest and greatest in language design. If you are new to programming, PL/SQL is a great place to start. If you are experienced in other programming languages, you will very easily adapt to the new syntax. The accessibility of PL/SQL also means that you can write code that is easily maintained and enhanced over time, a critical aspect of high-quality software development.
PL/SQL is a standard and portable language for Oracle development
If you write a PL/SQL procedure or function to execute from within the Personal Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). "Write once, run everywhere" was the mantra of PL/SQL long before Java appeared. For PL/SQL, though, "everywhere" means "everywhere there is an Oracle database."
PL/SQL is an embedded language
PL/SQL was not designed to be used as a "standalone" language, but instead to be invoked from within a "host" environment. So, for example, you can run PL/SQL programs from within the database (through, say, the SQL*Plus interface). Alternatively, you can define and execute PL/SQL programs from within an Oracle Developer form or report (this approach is called client-side PL/SQL). You cannot, however, create a PL/SQL "executable" that runs all by itself. Chapter 2 provides all the information you need to get up and running with this embedded language.
PL/SQL is a high-performance, highly integrated database language
These days, you have a number of choices when it comes to writing software to run against the Oracle database. You can use Java and JDBC (or SQLJ); you can use Visual Basic and ODBC; you can go with Delphi, C++, and so on. You will find, however, that it is easier to write highly efficient code to access the Oracle database in PL/SQL than it is in any other language. In particular, Oracle offers certain PL/SQL-specific enhancements such as the FORALL statement that can improve database performance by an order of magnitude or more.
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]