Table Of Content

PL/SQL User’s Guide and Reference Release 9.0.1 June 2001 Part No. A89856-01 PL/SQL User’s Guide and Reference, Release 9.0.1 Part No. A89856-01 Copyright © 1996, 2001, Oracle Corporation. All rights reserved. Primary Authors: Tom Portfolio, John Russell Contributors: Shashaanka Agrawal, Cailein Barclay,Dmitri Bronnikov, Sharon Castledine, Thomas Chang, Ravindra Dani, Chandrasekharan Iyer, Susan Kotsovolos, Neil Le, Warren Li, Chris Racicot, Murali Vemulapati, Guhan Viswanathan, Minghui Yang The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited. Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on behalf of the U.S. Government, the following notice is applicable: Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication, and disclosure of the Programs, including documentation, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs. Oracle, Oracle Store, and SQL*Plus are registered trademarks, and Oracle Call Interface, Oracle Developer, Oracle Forms, Oracle Reports, Oracle8i, Oracle9i, PL/SQL, Pro*C, and Pro*C/C++are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners. Contents Send Us Your Comments................................................................................................................. xix Preface.......................................................................................................................................................... xxi What’s New in PL/SQL?................................................................................................................... xxxi 1 Overview of PL/SQL Understanding the Main Features of PL/SQL............................................................................... 1-2 Block Structure.............................................................................................................................. 1-2 Variables and Constants.............................................................................................................. 1-3 Cursors........................................................................................................................................... 1-5 Cursor FOR Loops........................................................................................................................ 1-6 Cursor Variables........................................................................................................................... 1-6 Attributes....................................................................................................................................... 1-7 Control Structures........................................................................................................................ 1-9 Modularity................................................................................................................................... 1-12 Data Abstraction......................................................................................................................... 1-14 Information Hiding.................................................................................................................... 1-16 Error Handling............................................................................................................................ 1-16 PL/SQL Architecture........................................................................................................................ 1-17 In the Oracle Database Server................................................................................................... 1-18 In Oracle Tools............................................................................................................................ 1-20 Advantages of PL/SQL..................................................................................................................... 1-20 Support for SQL.......................................................................................................................... 1-20 iii Support for Object-Oriented Programming............................................................................ 1-21 Better Performance..................................................................................................................... 1-21 Higher Productivity.................................................................................................................... 1-22 Full Portability............................................................................................................................. 1-23 Tight Integration with SQL....................................................................................................... 1-23 Tight Security.............................................................................................................................. 1-23 2 Fundamentals of PL/SQL Character Set........................................................................................................................................ 2-2 Lexical Units......................................................................................................................................... 2-2 Delimiters....................................................................................................................................... 2-3 Identifiers....................................................................................................................................... 2-4 Literals............................................................................................................................................ 2-7 Comments.................................................................................................................................... 2-10 Declarations....................................................................................................................................... 2-11 Using DEFAULT......................................................................................................................... 2-12 Using NOT NULL....................................................................................................................... 2-13 Using %TYPE.............................................................................................................................. 2-13 Using %ROWTYPE..................................................................................................................... 2-14 Restrictions on Declarations...................................................................................................... 2-17 Naming Conventions....................................................................................................................... 2-17 Synonyms..................................................................................................................................... 2-18 Scoping......................................................................................................................................... 2-18 Case Sensitivity........................................................................................................................... 2-18 Name Resolution......................................................................................................................... 2-18 Scope and Visibility......................................................................................................................... 2-19 Assignments....................................................................................................................................... 2-22 Boolean Values............................................................................................................................ 2-22 Database Values.......................................................................................................................... 2-23 Expressions and Comparisons........................................................................................................ 2-23 Operator Precedence.................................................................................................................. 2-24 Logical Operators........................................................................................................................ 2-25 Comparison Operators............................................................................................................... 2-26 Concatenation Operator............................................................................................................. 2-28 Boolean Expressions................................................................................................................... 2-28 iv CASE Expressions...................................................................................................................... 2-31 Handling Null Values in Comparisons and Conditional Statements................................. 2-33 Built-In Functions............................................................................................................................. 2-35 3 PL/SQL Datatypes Predefined Datatypes......................................................................................................................... 3-2 Number Types.............................................................................................................................. 3-3 Character Types............................................................................................................................ 3-5 National Character Types.......................................................................................................... 3-10 LOB Types................................................................................................................................... 3-13 Boolean Type............................................................................................................................... 3-15 Datetime and Interval Types..................................................................................................... 3-15 Datetime and Interval Arithmetic............................................................................................ 3-20 Avoiding Truncation Problems Using Date and Time Subtypes........................................ 3-21 User-Defined Subtypes.................................................................................................................... 3-21 Defining Subtypes...................................................................................................................... 3-22 Using Subtypes........................................................................................................................... 3-22 Datatype Conversion........................................................................................................................ 3-24 Explicit Conversion.................................................................................................................... 3-24 Implicit Conversion.................................................................................................................... 3-24 Implicit versus Explicit Conversion......................................................................................... 3-26 DATE Values............................................................................................................................... 3-26 RAW and LONG RAW Values................................................................................................. 3-27 4 PL/SQL Control Structures Overview of PL/SQL Control Structures........................................................................................ 4-2 Conditional Control: IF and CASE Statements............................................................................. 4-2 IF-THEN Statement...................................................................................................................... 4-3 IF-THEN-ELSE Statement........................................................................................................... 4-3 IF-THEN-ELSIF Statement.......................................................................................................... 4-4 CASE Statement............................................................................................................................ 4-5 Guidelines for PL/SQL Conditional Statements..................................................................... 4-8 Iterative Control: LOOP and EXIT Statements............................................................................. 4-9 LOOP.............................................................................................................................................. 4-9 WHILE-LOOP............................................................................................................................. 4-11 v FOR-LOOP................................................................................................................................... 4-13 Sequential Control: GOTO and NULL Statements.................................................................... 4-17 GOTO Statement......................................................................................................................... 4-17 NULL Statement......................................................................................................................... 4-21 5 PL/SQL Collections and Records What Is a Collection?.......................................................................................................................... 5-2 Understanding Nested Tables.................................................................................................... 5-2 Nested Tables Versus Index-By Tables...................................................................................... 5-3 Understanding Varrays............................................................................................................... 5-4 Varrays Versus Nested Tables.................................................................................................... 5-4 Defining and Declaring Collections................................................................................................ 5-5 Declaring Collections................................................................................................................... 5-7 Initializing and Referencing Collections....................................................................................... 5-8 Referencing Collection Elements.............................................................................................. 5-10 Assigning and Comparing Collections......................................................................................... 5-11 Comparing Whole Collections.................................................................................................. 5-13 Manipulating Collections................................................................................................................ 5-13 Some Nested Table Examples................................................................................................... 5-13 Some Varray Examples.............................................................................................................. 5-16 Manipulating Individual Collection Elements....................................................................... 5-18 Manipulating Local Collections................................................................................................ 5-20 Using Multilevel Collections.......................................................................................................... 5-21 Using Collection Methods............................................................................................................... 5-23 Using the EXISTS Collection Method...................................................................................... 5-24 Using the COUNT Collection Method.................................................................................... 5-24 Using the LIMIT Collection Method........................................................................................ 5-25 Using the FIRST and LAST Collection Methods.................................................................... 5-25 Using the PRIOR and NEXT Collection Methods.................................................................. 5-26 Using the EXTEND Collection Method................................................................................... 5-27 Using the TRIM Collection Method......................................................................................... 5-27 Using the DELETE Collection Method.................................................................................... 5-28 Applying Methods to Collection Parameters......................................................................... 5-29 Avoiding Collection Exceptions..................................................................................................... 5-30 Reducing Loop Overhead for Collections with Bulk Binds..................................................... 5-32 vi How Do Bulk Binds Improve Performance?.......................................................................... 5-33 Using the FORALL Statement........................................................................................................ 5-34 How FORALL Affects Rollbacks.............................................................................................. 5-35 Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attribute... 5-36 Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute..................... 5-38 Restrictions on FORALL............................................................................................................ 5-39 Retrieving Query Results into Collections with the BULK COLLECT Clause.................... 5-41 Example of a Bulk Fetch from a Cursor.................................................................................. 5-42 Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause.......................... 5-43 Retrieving DML Results into a Collection with the RETURNING INTO Clause............. 5-43 Restrictions on BULK COLLECT............................................................................................. 5-44 Using FORALL and BULK COLLECT Together................................................................... 5-45 Using Host Arrays with Bulk Binds........................................................................................ 5-45 What Is a Record?.............................................................................................................................. 5-46 Defining and Declaring Records.................................................................................................... 5-46 Declaring Records....................................................................................................................... 5-48 Initializing Records.................................................................................................................... 5-48 Referencing Records................................................................................................................... 5-49 Nulling Records.......................................................................................................................... 5-51 Assigning Records...................................................................................................................... 5-51 Comparing Records.................................................................................................................... 5-53 Manipulating Records..................................................................................................................... 5-53 6 Interaction Between PL/SQL and Oracle Overview of SQL Support in PL/SQL............................................................................................ 6-2 Data Manipulation....................................................................................................................... 6-2 Transaction Control...................................................................................................................... 6-2 SQL Functions............................................................................................................................... 6-2 SQL Pseudocolumns.................................................................................................................... 6-3 SQL Operators............................................................................................................................... 6-5 Managing Cursors.............................................................................................................................. 6-6 Overview of Explicit Cursors...................................................................................................... 6-6 Overview of Implicit Cursors................................................................................................... 6-11 Separating Cursor Specs and Bodies with Packages................................................................. 6-12 vii Using Cursor FOR Loops................................................................................................................. 6-13 Using Subqueries Instead of Explicit Cursors........................................................................ 6-14 Using Cursor Subqueries........................................................................................................... 6-14 Defining Aliases for Expression Values in a Cursor FOR Loop.......................................... 6-14 Passing Parameters to a Cursor FOR Loop............................................................................. 6-15 Using Cursor Variables.................................................................................................................... 6-16 What Are Cursor Variables?..................................................................................................... 6-16 Why Use Cursor Variables?...................................................................................................... 6-16 Defining REF CURSOR Types.................................................................................................. 6-17 Declaring Cursor Variables....................................................................................................... 6-18 Controlling Cursor Variables.................................................................................................... 6-19 Cursor Variable Example: Master Table and Details Tables................................................ 6-25 Cursor Variable Example: Client-Side PL/SQL Block.......................................................... 6-26 Cursor Variable Example: Pro*C Program............................................................................. 6-26 Cursor Variable Example: Manipulating Host Variables in SQL*Plus............................... 6-29 Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL.................. 6-30 Avoiding Errors with Cursor Variables.................................................................................. 6-31 Restrictions on Cursor Variables.............................................................................................. 6-33 Using Cursor Attributes................................................................................................................... 6-34 Overview of Explicit Cursor Attributes.................................................................................. 6-34 Overview of Implicit Cursor Attributes.................................................................................. 6-38 Using Cursor Expressions............................................................................................................... 6-41 Restrictions on Cursor Expressions.......................................................................................... 6-41 Example of Cursor Expressions................................................................................................ 6-42 Overview of Transaction Processing in PL/SQL......................................................................... 6-43 How Transactions Guard Your Database............................................................................... 6-44 Making Changes Permanent with COMMIT......................................................................... 6-44 Undoing Changes with ROLLBACK....................................................................................... 6-45 Undoing Partial Changes with SAVEPOINT......................................................................... 6-46 How Oracle Does Implicit Rollbacks....................................................................................... 6-47 Ending Transactions................................................................................................................... 6-48 Setting Transaction Properties with SET TRANSACTION.................................................. 6-48 Overriding Default Locking...................................................................................................... 6-49 Doing Independent Units of Work with Autonomous Transactions...................................... 6-53 Advantages of Autonomous Transactions.............................................................................. 6-53 viii Defining Autonomous Transactions........................................................................................ 6-54 Controlling Autonomous Transactions................................................................................... 6-57 Using Autonomous Triggers.................................................................................................... 6-59 Calling Autonomous Functions from SQL............................................................................. 6-61 Ensuring Backward Compatibility of PL/SQL Programs......................................................... 6-62 7 Handling PL/SQL Errors Overview of PL/SQL Error Handling............................................................................................. 7-2 Advantages of PL/SQL Exceptions.................................................................................................. 7-3 Predefined PL/SQL Exceptions........................................................................................................ 7-4 Defining Your Own PL/SQL Exceptions........................................................................................ 7-7 Declaring PL/SQL Exceptions.................................................................................................... 7-7 Scope Rules for PL/SQL Exceptions.......................................................................................... 7-7 Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT................ 7-8 Defining Your Own PL/SQL Error Messages: Procedure RAISE_APPLICATION_ERROR.... 7-9 Redeclaring Predefined Exceptions......................................................................................... 7-10 How PL/SQL Exceptions Are Raised............................................................................................ 7-11 Raising Exceptions with the RAISE Statement....................................................................... 7-11 How PL/SQL Exceptions Propagate.............................................................................................. 7-12 Reraising a PL/SQL Exception....................................................................................................... 7-14 Handling Raised PL/SQL Exceptions........................................................................................... 7-15 Handling Exceptions Raised in Declarations......................................................................... 7-16 Handling Exceptions Raised in Handlers............................................................................... 7-17 Branching to or from an Exception Handler.......................................................................... 7-17 Retrieving the Error Code and Error Message: SQLCODE and SQLERRM...................... 7-18 Catching Unhandled Exceptions.............................................................................................. 7-19 Tips for Handling PL/SQL Errors.................................................................................................. 7-20 Continuing after an Exception Is Raised................................................................................. 7-20 Retrying a Transaction............................................................................................................... 7-21 Using Locator Variables to Identify Exception Locations.................................................... 7-22 8 PL/SQL Subprograms What Are Subprograms?................................................................................................................... 8-2 Advantages of Subprograms............................................................................................................ 8-3 ix Understanding PL/SQL Procedures................................................................................................ 8-3 Understanding PL/SQL Functions................................................................................................... 8-6 Using the RETURN Statement.................................................................................................... 8-8 Controlling Side Effects of PL/SQL Subprograms.................................................................. 8-9 Declaring PL/SQL Subprograms.................................................................................................... 8-10 Packaging PL/SQL Subprograms Together.................................................................................. 8-11 Actual Versus Formal Subprogram Parameters.......................................................................... 8-12 Positional Versus Named Notation for Subprogram Parameters............................................ 8-13 Using Positional Notation......................................................................................................... 8-13 Using Named Notation.............................................................................................................. 8-13 Using Mixed Notation................................................................................................................ 8-13 Specifying Subprogram Parameter Modes.................................................................................. 8-14 Using the IN Mode..................................................................................................................... 8-14 Using the OUT Mode................................................................................................................. 8-14 Using the IN OUT Mode............................................................................................................ 8-16 Summary of Subprogram Parameter Modes.......................................................................... 8-16 Passing Large Data Structures with the NOCOPY Compiler Hint......................................... 8-17 The Trade-Off for Better Performance with NOCOPY......................................................... 8-18 Restrictions on NOCOPY.......................................................................................................... 8-19 Using Default Values for Subprogram Parameters.................................................................... 8-19 Understanding Subprogram Parameter Aliasing....................................................................... 8-21 Overloading Subprogram Names.................................................................................................. 8-23 Restrictions on Overloading...................................................................................................... 8-24 How Subprogram Calls Are Resolved.......................................................................................... 8-26 How Overloading Works with Inheritance............................................................................ 8-28 Accepting and Returning Multiple Rows with Table Functions........................................... 8-30 Overview of Table Functions.................................................................................................... 8-30 What Are Table Functions?....................................................................................................... 8-31 What Are Pipelined Table Functions?..................................................................................... 8-32 Using Pipelined Table Functions for Transformations......................................................... 8-33 Writing a Pipelined Table Function......................................................................................... 8-34 Returning Results from Table Functions................................................................................. 8-35 Pipelining Data Between PL/SQL Table Functions.............................................................. 8-36 Querying Table Functions......................................................................................................... 8-36 Optimizing Multiple Calls to Table Functions....................................................................... 8-37 x

ebook img

PL/SQL User's Guide and Reference PDF

694 Pages·2001·3.703 MB·English
by  Portfolio T., Russell J.
#Computers #Databases
Save to my drive
Quick download
Download
Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview PL/SQL User's Guide and Reference

PL/SQL User’s Guide and Reference Release 9.0.1 June 2001 Part No. A89856-01 PL/SQL User’s Guide and Reference, Release 9.0.1 Part No. A89856-01 Copyright © 1996, 2001, Oracle Corporation. All rights reserved. Primary Authors: Tom Portfolio, John Russell Contributors: Shashaanka Agrawal, Cailein Barclay,Dmitri Bronnikov, Sharon Castledine, Thomas Chang, Ravindra Dani, Chandrasekharan Iyer, Susan Kotsovolos, Neil Le, Warren Li, Chris Racicot, Murali Vemulapati, Guhan Viswanathan, Minghui Yang The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited. Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on behalf of the U.S. Government, the following notice is applicable: Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication, and disclosure of the Programs, including documentation, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs. Oracle, Oracle Store, and SQL*Plus are registered trademarks, and Oracle Call Interface, Oracle Developer, Oracle Forms, Oracle Reports, Oracle8i, Oracle9i, PL/SQL, Pro*C, and Pro*C/C++are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners. Contents Send Us Your Comments................................................................................................................. xix Preface.......................................................................................................................................................... xxi What’s New in PL/SQL?................................................................................................................... xxxi 1 Overview of PL/SQL Understanding the Main Features of PL/SQL............................................................................... 1-2 Block Structure.............................................................................................................................. 1-2 Variables and Constants.............................................................................................................. 1-3 Cursors........................................................................................................................................... 1-5 Cursor FOR Loops........................................................................................................................ 1-6 Cursor Variables........................................................................................................................... 1-6 Attributes....................................................................................................................................... 1-7 Control Structures........................................................................................................................ 1-9 Modularity................................................................................................................................... 1-12 Data Abstraction......................................................................................................................... 1-14 Information Hiding.................................................................................................................... 1-16 Error Handling............................................................................................................................ 1-16 PL/SQL Architecture........................................................................................................................ 1-17 In the Oracle Database Server................................................................................................... 1-18 In Oracle Tools............................................................................................................................ 1-20 Advantages of PL/SQL..................................................................................................................... 1-20 Support for SQL.......................................................................................................................... 1-20 iii Support for Object-Oriented Programming............................................................................ 1-21 Better Performance..................................................................................................................... 1-21 Higher Productivity.................................................................................................................... 1-22 Full Portability............................................................................................................................. 1-23 Tight Integration with SQL....................................................................................................... 1-23 Tight Security.............................................................................................................................. 1-23 2 Fundamentals of PL/SQL Character Set........................................................................................................................................ 2-2 Lexical Units......................................................................................................................................... 2-2 Delimiters....................................................................................................................................... 2-3 Identifiers....................................................................................................................................... 2-4 Literals............................................................................................................................................ 2-7 Comments.................................................................................................................................... 2-10 Declarations....................................................................................................................................... 2-11 Using DEFAULT......................................................................................................................... 2-12 Using NOT NULL....................................................................................................................... 2-13 Using %TYPE.............................................................................................................................. 2-13 Using %ROWTYPE..................................................................................................................... 2-14 Restrictions on Declarations...................................................................................................... 2-17 Naming Conventions....................................................................................................................... 2-17 Synonyms..................................................................................................................................... 2-18 Scoping......................................................................................................................................... 2-18 Case Sensitivity........................................................................................................................... 2-18 Name Resolution......................................................................................................................... 2-18 Scope and Visibility......................................................................................................................... 2-19 Assignments....................................................................................................................................... 2-22 Boolean Values............................................................................................................................ 2-22 Database Values.......................................................................................................................... 2-23 Expressions and Comparisons........................................................................................................ 2-23 Operator Precedence.................................................................................................................. 2-24 Logical Operators........................................................................................................................ 2-25 Comparison Operators............................................................................................................... 2-26 Concatenation Operator............................................................................................................. 2-28 Boolean Expressions................................................................................................................... 2-28 iv CASE Expressions...................................................................................................................... 2-31 Handling Null Values in Comparisons and Conditional Statements................................. 2-33 Built-In Functions............................................................................................................................. 2-35 3 PL/SQL Datatypes Predefined Datatypes......................................................................................................................... 3-2 Number Types.............................................................................................................................. 3-3 Character Types............................................................................................................................ 3-5 National Character Types.......................................................................................................... 3-10 LOB Types................................................................................................................................... 3-13 Boolean Type............................................................................................................................... 3-15 Datetime and Interval Types..................................................................................................... 3-15 Datetime and Interval Arithmetic............................................................................................ 3-20 Avoiding Truncation Problems Using Date and Time Subtypes........................................ 3-21 User-Defined Subtypes.................................................................................................................... 3-21 Defining Subtypes...................................................................................................................... 3-22 Using Subtypes........................................................................................................................... 3-22 Datatype Conversion........................................................................................................................ 3-24 Explicit Conversion.................................................................................................................... 3-24 Implicit Conversion.................................................................................................................... 3-24 Implicit versus Explicit Conversion......................................................................................... 3-26 DATE Values............................................................................................................................... 3-26 RAW and LONG RAW Values................................................................................................. 3-27 4 PL/SQL Control Structures Overview of PL/SQL Control Structures........................................................................................ 4-2 Conditional Control: IF and CASE Statements............................................................................. 4-2 IF-THEN Statement...................................................................................................................... 4-3 IF-THEN-ELSE Statement........................................................................................................... 4-3 IF-THEN-ELSIF Statement.......................................................................................................... 4-4 CASE Statement............................................................................................................................ 4-5 Guidelines for PL/SQL Conditional Statements..................................................................... 4-8 Iterative Control: LOOP and EXIT Statements............................................................................. 4-9 LOOP.............................................................................................................................................. 4-9 WHILE-LOOP............................................................................................................................. 4-11 v FOR-LOOP................................................................................................................................... 4-13 Sequential Control: GOTO and NULL Statements.................................................................... 4-17 GOTO Statement......................................................................................................................... 4-17 NULL Statement......................................................................................................................... 4-21 5 PL/SQL Collections and Records What Is a Collection?.......................................................................................................................... 5-2 Understanding Nested Tables.................................................................................................... 5-2 Nested Tables Versus Index-By Tables...................................................................................... 5-3 Understanding Varrays............................................................................................................... 5-4 Varrays Versus Nested Tables.................................................................................................... 5-4 Defining and Declaring Collections................................................................................................ 5-5 Declaring Collections................................................................................................................... 5-7 Initializing and Referencing Collections....................................................................................... 5-8 Referencing Collection Elements.............................................................................................. 5-10 Assigning and Comparing Collections......................................................................................... 5-11 Comparing Whole Collections.................................................................................................. 5-13 Manipulating Collections................................................................................................................ 5-13 Some Nested Table Examples................................................................................................... 5-13 Some Varray Examples.............................................................................................................. 5-16 Manipulating Individual Collection Elements....................................................................... 5-18 Manipulating Local Collections................................................................................................ 5-20 Using Multilevel Collections.......................................................................................................... 5-21 Using Collection Methods............................................................................................................... 5-23 Using the EXISTS Collection Method...................................................................................... 5-24 Using the COUNT Collection Method.................................................................................... 5-24 Using the LIMIT Collection Method........................................................................................ 5-25 Using the FIRST and LAST Collection Methods.................................................................... 5-25 Using the PRIOR and NEXT Collection Methods.................................................................. 5-26 Using the EXTEND Collection Method................................................................................... 5-27 Using the TRIM Collection Method......................................................................................... 5-27 Using the DELETE Collection Method.................................................................................... 5-28 Applying Methods to Collection Parameters......................................................................... 5-29 Avoiding Collection Exceptions..................................................................................................... 5-30 Reducing Loop Overhead for Collections with Bulk Binds..................................................... 5-32 vi How Do Bulk Binds Improve Performance?.......................................................................... 5-33 Using the FORALL Statement........................................................................................................ 5-34 How FORALL Affects Rollbacks.............................................................................................. 5-35 Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attribute... 5-36 Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute..................... 5-38 Restrictions on FORALL............................................................................................................ 5-39 Retrieving Query Results into Collections with the BULK COLLECT Clause.................... 5-41 Example of a Bulk Fetch from a Cursor.................................................................................. 5-42 Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause.......................... 5-43 Retrieving DML Results into a Collection with the RETURNING INTO Clause............. 5-43 Restrictions on BULK COLLECT............................................................................................. 5-44 Using FORALL and BULK COLLECT Together................................................................... 5-45 Using Host Arrays with Bulk Binds........................................................................................ 5-45 What Is a Record?.............................................................................................................................. 5-46 Defining and Declaring Records.................................................................................................... 5-46 Declaring Records....................................................................................................................... 5-48 Initializing Records.................................................................................................................... 5-48 Referencing Records................................................................................................................... 5-49 Nulling Records.......................................................................................................................... 5-51 Assigning Records...................................................................................................................... 5-51 Comparing Records.................................................................................................................... 5-53 Manipulating Records..................................................................................................................... 5-53 6 Interaction Between PL/SQL and Oracle Overview of SQL Support in PL/SQL............................................................................................ 6-2 Data Manipulation....................................................................................................................... 6-2 Transaction Control...................................................................................................................... 6-2 SQL Functions............................................................................................................................... 6-2 SQL Pseudocolumns.................................................................................................................... 6-3 SQL Operators............................................................................................................................... 6-5 Managing Cursors.............................................................................................................................. 6-6 Overview of Explicit Cursors...................................................................................................... 6-6 Overview of Implicit Cursors................................................................................................... 6-11 Separating Cursor Specs and Bodies with Packages................................................................. 6-12 vii Using Cursor FOR Loops................................................................................................................. 6-13 Using Subqueries Instead of Explicit Cursors........................................................................ 6-14 Using Cursor Subqueries........................................................................................................... 6-14 Defining Aliases for Expression Values in a Cursor FOR Loop.......................................... 6-14 Passing Parameters to a Cursor FOR Loop............................................................................. 6-15 Using Cursor Variables.................................................................................................................... 6-16 What Are Cursor Variables?..................................................................................................... 6-16 Why Use Cursor Variables?...................................................................................................... 6-16 Defining REF CURSOR Types.................................................................................................. 6-17 Declaring Cursor Variables....................................................................................................... 6-18 Controlling Cursor Variables.................................................................................................... 6-19 Cursor Variable Example: Master Table and Details Tables................................................ 6-25 Cursor Variable Example: Client-Side PL/SQL Block.......................................................... 6-26 Cursor Variable Example: Pro*C Program............................................................................. 6-26 Cursor Variable Example: Manipulating Host Variables in SQL*Plus............................... 6-29 Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL.................. 6-30 Avoiding Errors with Cursor Variables.................................................................................. 6-31 Restrictions on Cursor Variables.............................................................................................. 6-33 Using Cursor Attributes................................................................................................................... 6-34 Overview of Explicit Cursor Attributes.................................................................................. 6-34 Overview of Implicit Cursor Attributes.................................................................................. 6-38 Using Cursor Expressions............................................................................................................... 6-41 Restrictions on Cursor Expressions.......................................................................................... 6-41 Example of Cursor Expressions................................................................................................ 6-42 Overview of Transaction Processing in PL/SQL......................................................................... 6-43 How Transactions Guard Your Database............................................................................... 6-44 Making Changes Permanent with COMMIT......................................................................... 6-44 Undoing Changes with ROLLBACK....................................................................................... 6-45 Undoing Partial Changes with SAVEPOINT......................................................................... 6-46 How Oracle Does Implicit Rollbacks....................................................................................... 6-47 Ending Transactions................................................................................................................... 6-48 Setting Transaction Properties with SET TRANSACTION.................................................. 6-48 Overriding Default Locking...................................................................................................... 6-49 Doing Independent Units of Work with Autonomous Transactions...................................... 6-53 Advantages of Autonomous Transactions.............................................................................. 6-53 viii Defining Autonomous Transactions........................................................................................ 6-54 Controlling Autonomous Transactions................................................................................... 6-57 Using Autonomous Triggers.................................................................................................... 6-59 Calling Autonomous Functions from SQL............................................................................. 6-61 Ensuring Backward Compatibility of PL/SQL Programs......................................................... 6-62 7 Handling PL/SQL Errors Overview of PL/SQL Error Handling............................................................................................. 7-2 Advantages of PL/SQL Exceptions.................................................................................................. 7-3 Predefined PL/SQL Exceptions........................................................................................................ 7-4 Defining Your Own PL/SQL Exceptions........................................................................................ 7-7 Declaring PL/SQL Exceptions.................................................................................................... 7-7 Scope Rules for PL/SQL Exceptions.......................................................................................... 7-7 Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT................ 7-8 Defining Your Own PL/SQL Error Messages: Procedure RAISE_APPLICATION_ERROR.... 7-9 Redeclaring Predefined Exceptions......................................................................................... 7-10 How PL/SQL Exceptions Are Raised............................................................................................ 7-11 Raising Exceptions with the RAISE Statement....................................................................... 7-11 How PL/SQL Exceptions Propagate.............................................................................................. 7-12 Reraising a PL/SQL Exception....................................................................................................... 7-14 Handling Raised PL/SQL Exceptions........................................................................................... 7-15 Handling Exceptions Raised in Declarations......................................................................... 7-16 Handling Exceptions Raised in Handlers............................................................................... 7-17 Branching to or from an Exception Handler.......................................................................... 7-17 Retrieving the Error Code and Error Message: SQLCODE and SQLERRM...................... 7-18 Catching Unhandled Exceptions.............................................................................................. 7-19 Tips for Handling PL/SQL Errors.................................................................................................. 7-20 Continuing after an Exception Is Raised................................................................................. 7-20 Retrying a Transaction............................................................................................................... 7-21 Using Locator Variables to Identify Exception Locations.................................................... 7-22 8 PL/SQL Subprograms What Are Subprograms?................................................................................................................... 8-2 Advantages of Subprograms............................................................................................................ 8-3 ix Understanding PL/SQL Procedures................................................................................................ 8-3 Understanding PL/SQL Functions................................................................................................... 8-6 Using the RETURN Statement.................................................................................................... 8-8 Controlling Side Effects of PL/SQL Subprograms.................................................................. 8-9 Declaring PL/SQL Subprograms.................................................................................................... 8-10 Packaging PL/SQL Subprograms Together.................................................................................. 8-11 Actual Versus Formal Subprogram Parameters.......................................................................... 8-12 Positional Versus Named Notation for Subprogram Parameters............................................ 8-13 Using Positional Notation......................................................................................................... 8-13 Using Named Notation.............................................................................................................. 8-13 Using Mixed Notation................................................................................................................ 8-13 Specifying Subprogram Parameter Modes.................................................................................. 8-14 Using the IN Mode..................................................................................................................... 8-14 Using the OUT Mode................................................................................................................. 8-14 Using the IN OUT Mode............................................................................................................ 8-16 Summary of Subprogram Parameter Modes.......................................................................... 8-16 Passing Large Data Structures with the NOCOPY Compiler Hint......................................... 8-17 The Trade-Off for Better Performance with NOCOPY......................................................... 8-18 Restrictions on NOCOPY.......................................................................................................... 8-19 Using Default Values for Subprogram Parameters.................................................................... 8-19 Understanding Subprogram Parameter Aliasing....................................................................... 8-21 Overloading Subprogram Names.................................................................................................. 8-23 Restrictions on Overloading...................................................................................................... 8-24 How Subprogram Calls Are Resolved.......................................................................................... 8-26 How Overloading Works with Inheritance............................................................................ 8-28 Accepting and Returning Multiple Rows with Table Functions........................................... 8-30 Overview of Table Functions.................................................................................................... 8-30 What Are Table Functions?....................................................................................................... 8-31 What Are Pipelined Table Functions?..................................................................................... 8-32 Using Pipelined Table Functions for Transformations......................................................... 8-33 Writing a Pipelined Table Function......................................................................................... 8-34 Returning Results from Table Functions................................................................................. 8-35 Pipelining Data Between PL/SQL Table Functions.............................................................. 8-36 Querying Table Functions......................................................................................................... 8-36 Optimizing Multiple Calls to Table Functions....................................................................... 8-37 x

See more

The list of books you might like

Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.
DMCA & Copyright: Dear all, most of the website is community built, users are uploading hundred of books everyday, which makes really hard for us to identify copyrighted material, please contact us if you want any material removed.
Copyright @ PDFdrive.to, 2022 | We love our users