| Example 1-2 Assigning Values to Variables With the Assignment Operator
|
| Example 1-3 Assigning Values to Variables by SELECTing INTO
|
| Example 1-4 Assigning Values to Variables as Parameters of a Subprogram
|
| Example 1-5 Processing Query Results in a LOOP
|
| Example 1-6 Using %ROWTYPE with an Explicit Cursor
|
| Example 1-7 Using the IF-THEN_ELSE and CASE Statement for Conditional Control
|
| Example 1-8 Using the FOR-LOOP
|
| Example 1-9 Using WHILE-LOOP for Control
|
| Example 1-10 Using the EXIT-WHEN Statement
|
| Example 1-11 Using the GOTO Statement
|
| Example 1-12 Creating a Stored Subprogram
|
| Example 1-13 Creating a Package and Package Body
|
| Example 1-14 Calling a Procedure in a Package
|
| Example 1-15 Using a PL/SQL Collection Type
|
| Example 1-16 Declaring a Record Type
|
| Example 1-17 Defining an Object Type
|
| Example 1-18 Techniques for Calling a Standalone Procedure From SQL*Plus
|
| Example 1-19 Creating a Database Trigger
|
| Example 2-1 NUMBER Literals
|
| Example 2-2 Using BINARY_FLOAT and BINARY_DOUBLE
|
| Example 2-3 Using DateTime Literals
|
| Example 2-4 Using Single-Line Comments
|
| Example 2-5 Using Multi-Line Comments
|
| Example 2-6 Using %TYPE With the Datatype of a Variable
|
| Example 2-7 Using %TYPE With Table Columns
|
| Example 2-8 Using %ROWTYPE With Table Rows
|
| Example 2-9 Using the %ROWTYPE Attribute
|
| Example 2-10 Assigning Values to a Record With a %ROWTYPE Declaration
|
| Example 2-11 Using an Alias for Column Names
|
| Example 2-12 Errors With Duplicate Identifiers in Same Scope
|
| Example 2-13 Case Sensitivity of Identifiers
|
| Example 2-14 Using a Block Label for Name Resolution
|
| Example 2-15 Using a Subprogram Name for Name Resolution
|
| Example 2-16 Scope Rules
|
| Example 2-17 Using a Label Qualifier With Identifiers
|
| Example 2-18 Using Subprogram Qualifier With Identifiers
|
| Example 2-19 PL/SQL Block Using Multiple and Duplicate Labels
|
| Example 2-20 Initialization of Variables and Constants
|
| Example 2-21 Assigning BOOLEAN Values
|
| Example 2-22 Assigning a Query Result to a Variable
|
| Example 2-23 Short-Circuit Evaluation
|
| Example 2-24 Using Comparison Operators
|
| Example 2-25 Using BOOLEAN Variables in Conditional Tests
|
| Example 2-26 Using the WHEN Clause With a CASE Statement
|
| Example 2-27 Using a Search Condition With a CASE Statement
|
| Example 2-28 Using NULLs in Comparisons
|
| Example 2-29 Using the Function DECODE
|
| Example 2-30 Using the Function NVL
|
| Example 2-31 Using the Function REPLACE
|
| Example 2-32 Using Static Constants
|
| Example 2-33 Using DBMS_DB_VERSION Constants
|
| Example 2-34 Using Conditional Compilation With Database Versions
|
| Example 2-35 Using ALTER PROCEDURE to Set PLSQL_CCFLAGS
|
| Example 2-36 Using PRINT_POST_PROCESSED_SOURCE to Display Source Code
|
| Example 3-1 Assigning a Literal Value to a TIMESTAMP Variable
|
| Example 3-2 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
|
| Example 3-3 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable
|
| Example 3-4 Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE
|
| Example 3-5 Assigning Literals to an INTERVAL YEAR TO MONTH Variable
|
| Example 3-6 Assigning Literals to an INTERVAL DAY TO SECOND Variable
|
| Example 3-7 Using Ranges With Subtypes
|
| Example 3-8 Type Compatibility With the NUMBER Datatype
|
| Example 3-9 Constraints Inherited by Subprograms
|
| Example 3-10 Default Value With Subtypes
|
| Example 3-11 Using SUBTYPE With %TYPE and %ROWTYPE
|
| Example 3-12 Implicit Conversion
|
| Example 3-13 Comparing Character Values
|
| Example 3-14 Using the Function RTRIM
|
| Example 4-1 Using a Simple IF-THEN Statement
|
| Example 4-2 Using a Simple IF-THEN-ELSE Statement
|
| Example 4-3 Nested IF Statements
|
| Example 4-4 Using the IF-THEN-ELSEIF Statement
|
| Example 4-5 Extended IF-THEN Statement
|
| Example 4-6 Using the CASE-WHEN Statement
|
| Example 4-7 Using the Searched CASE Statement
|
| Example 4-8 Using an EXIT Statement
|
| Example 4-9 Using EXIT With Labeled Loops
|
| Example 4-10 Using a Simple FOR..LOOP Statement
|
| Example 4-11 Using a Reverse FOR..LOOP Statement
|
| Example 4-12 Changing the Increment of the Counter in a FOR..LOOP Statement
|
| Example 4-13 Specifying a LOOP Range at Run Time
|
| Example 4-14 Scope of the LOOP Counter Variable
|
| Example 4-15 Using a Label for Referencing Variables Outside a Loop
|
| Example 4-16 Using Labels on Loops for Referencing
|
| Example 4-17 Using EXIT in a LOOP
|
| Example 4-18 Using EXIT With a Label in a LOOP
|
| Example 4-19 Using a Simple GOTO Statement
|
| Example 4-20 Using a NULL Statement to Allow a GOTO to a Label
|
| Example 4-21 Using a GOTO Statement to Branch an Enclosing Block
|
| Example 4-22 Using the NULL Statement to Show No Action
|
| Example 4-23 Using NULL as a Placeholder When Creating a Subprogram
|
| Example 5-1 Declaring Collection Types
|
| Example 5-2 Declaring an Associative Array
|
| Example 5-3 Declaring Nested Tables, Varrays, and Associative Arrays
|
| Example 5-4 Declaring Collections with %TYPE
|
| Example 5-5 Declaring a Procedure Parameter as a Nested Table
|
| Example 5-6 Calling a Procedure With a Nested Table Parameter
|
| Example 5-7 Specifying Collection Element Types with %TYPE and %ROWTYPE
|
| Example 5-8 VARRAY of Records
|
| Example 5-9 NOT NULL Constraint on Collection Elements
|
| Example 5-10 Constructor for a Nested Table
|
| Example 5-11 Constructor for a Varray
|
| Example 5-12 Collection Constructor Including Null Elements
|
| Example 5-13 Combining Collection Declaration and Constructor
|
| Example 5-14 Empty Varray Constructor
|
| Example 5-15 Referencing a Nested Table Element
|
| Example 5-16 Referencing an Element of an Associative Array
|
| Example 5-17 Datatype Compatibility for Collection Assignment
|
| Example 5-18 Assigning a Null Value to a Nested Table
|
| Example 5-19 Assigning Nested Tables with Set Operators
|
| Example 5-20 Assigning Values to VARRAYs with Complex Datatypes
|
| Example 5-21 Assigning Values to Tables with Complex Datatypes
|
| Example 5-22 Checking if a Collection Is Null
|
| Example 5-23 Comparing Two Nested Tables
|
| Example 5-24 Comparing Nested Tables with Set Operators
|
| Example 5-25 Multilevel VARRAY
|
| Example 5-26 Multilevel Nested Table
|
| Example 5-27 Multilevel Associative Array
|
| Example 5-28 Checking Whether a Collection Element EXISTS
|
| Example 5-29 Counting Collection Elements With COUNT
|
| Example 5-30 Checking the Maximum Size of a Collection With LIMIT
|
| Example 5-31 Using FIRST and LAST With a Collection
|
| Example 5-32 Using PRIOR and NEXT to Access Collection Elements
|
| Example 5-33 Using NEXT to Access Elements of a Nested Table
|
| Example 5-34 Using EXTEND to Increase the Size of a Collection
|
| Example 5-35 Using TRIM to Decrease the Size of a Collection
|
| Example 5-36 Using TRIM on Deleted Elements
|
| Example 5-37 Using the DELETE Method on a Collection
|
| Example 5-38 Collection Exceptions
|
| Example 5-39 How Invalid Subscripts are Handled With DELETE(n)
|
| Example 5-40 Incompatibility Between Package and Local Collection Types
|
| Example 5-41 Declaring and Initializing a Simple Record Type
|
| Example 5-42 Declaring and Initializing Record Types
|
| Example 5-43 Using %ROWTYPE to Declare a Record
|
| Example 5-44 Returning a Record from a Function
|
| Example 5-45 Using a Record as Parameter to a Procedure
|
| Example 5-46 Declaring a Nested Record
|
| Example 5-47 Assigning Default Values to a Record
|
| Example 5-48 Assigning All the Fields of a Record in One Statement
|
| Example 5-49 Using SELECT INTO to Assign Values in a Record
|
| Example 5-50 Inserting a PL/SQL Record Using %ROWTYPE
|
| Example 5-51 Updating a Row Using a Record
|
| Example 5-52 Using the RETURNING Clause with a Record
|
| Example 5-53 Using BULK COLLECT With a SELECT INTO Statement
|
| Example 6-29 Stored Procedure to Open a Ref Cursor
|
| Example 6-1 Data Manipulation With PL/SQL
|
| Example 6-2 Checking SQL%ROWCOUNT After an UPDATE
|
| Example 6-3 Substituting PL/SQL Variables
|
| Example 6-4 Calling the SQL COUNT Function in PL/SQL
|
| Example 6-5 Using CURRVAL and NEXTVAL
|
| Example 6-6 Using ROWNUM
|
| Example 6-7 Using SQL%FOUND
|
| Example 6-8 Using SQL%ROWCOUNT
|
| Example 6-9 Declaring a Cursor
|
| Example 6-10 Fetching With a Cursor
|
| Example 6-11 Referencing PL/SQL Variables Within Its Scope
|
| Example 6-12 Fetching the Same Cursor Into Different Variables
|
| Example 6-13 Fetching Bulk Data With a Cursor
|
| Example 6-14 Using %FOUND
|
| Example 6-15 Using %ISOPEN
|
| Example 6-16 Using %NOTFOUND
|
| Example 6-17 Using %ROWCOUNT
|
| Example 6-18 Using an Alias For Expressions in a Query
|
| Example 6-19 Using a Subquery in a Cursor
|
| Example 6-20 Using a Subquery in a FROM Clause
|
| Example 6-21 Using a Correlated Subquery
|
| Example 6-22 Passing Parameters to a Cursor FOR Loop
|
| Example 6-23 Passing Parameters to Explicit Cursors
|
| Example 6-24 Cursor Variable Returning a %ROWTYPE Variable
|
| Example 6-25 Using the %ROWTYPE Attribute to Provide the Datatype
|
| Example 6-26 Cursor Variable Returning a Record Type
|
| Example 6-27 Passing a REF CURSOR as a Parameter
|
| Example 6-28 Checking If a Cursor Variable is Open
|
| Example 6-30 Stored Procedure to Open Ref Cursors with Different Queries
|
| Example 6-31 Cursor Variable with Different Return Types
|
| Example 6-32 Fetching from a Cursor Variable into a Record
|
| Example 6-33 Fetching from a Cursor Variable into Collections
|
| Example 6-34 Declaration of Cursor Variables in a Package
|
| Example 6-35 Using a Cursor Expression
|
| Example 6-36 Using COMMIT With the WRITE Clause
|
| Example 6-37 Using ROLLBACK
|
| Example 6-38 Using SAVEPOINT With ROLLBACK
|
| Example 6-40 Using SET TRANSACTION to Begin a Read-only Transaction
|
| Example 6-41 Using CURRENT OF to Update the Latest Row Fetched From a Cursor
|
| Example 6-42 Fetching Across COMMITs Using ROWID
|
| Example 6-43 Declaring an Autonomous Function in a Package
|
| Example 6-44 Declaring an Autonomous Standalone Procedure
|
| Example 6-45 Declaring an Autonomous PL/SQL Block
|
| Example 6-46 Declaring an Autonomous Trigger
|
| Example 6-47 Using Autonomous Triggers
|
| Example 6-48 Calling an Autonomous Function
|
| Example 7-1 Examples of Dynamic SQL
|
| Example 7-2 Dynamic SQL Procedure that Accepts Table Name and WHERE Clause
|
| Example 7-3 Using IN OUT Bind Arguments to Specify Substitutions
|
| Example 7-4 Dynamic SQL with BULK COLLECT INTO Clause
|
| Example 7-5 Dynamic SQL with RETURNING BULK COLLECT INTO Clause
|
| Example 7-6 Dynamic SQL Inside FORALL Statement
|
| Example 7-7 Using Duplicate Placeholders With Dynamic SQL
|
| Example 7-8 Accessing %ROWCOUNT For an Explicit Cursor
|
| Example 7-9 Dynamic SQL Fetching into a Record
|
| Example 8-1 Simple PL/SQL Procedure
|
| Example 8-2 Simple PL/SQL Function
|
| Example 8-3 Forward Declaration for a Nested Subprogram
|
| Example 8-4 Formal Parameters and Actual Parameters
|
| Example 8-5 Subprogram Calls Using Positional, Named, and Mixed Notation
|
| Example 8-6 Using the OUT Mode
|
| Example 8-7 Procedure with Default Parameter Values
|
| Example 8-8 Overloading a Subprogram Name
|
| Example 8-9 Restrictions on Overloading PL/SQL Procedures
|
| Example 8-10 Resolving PL/SQL Procedure Names
|
| Example 8-11 Resolving PL/SQL Functions With Inheritance
|
| Example 8-12 Resolving PL/SQL Functions With Inheritance Dynamically
|
| Example 8-13 Specifying Invoker's Rights With a Procedure
|
| Example 8-14 Resolving External References in an Invoker's Rights Subprogram
|
| Example 8-15 Creating an Object Type With AUTHID CURRENT USER
|
| Example 8-16 Calling an Invoker's Rights Instance Methods
|
| Example 8-17 Calling an External Procedure From PL/SQL
|
| Example 8-18 Calling a Java Function From PL/SQL
|
| Example 8-19 Aliasing from Passing Global Variable with NOCOPY Hint
|
| Example 8-20 Aliasing Passing Same Parameter Multiple Times
|
| Example 8-21 Aliasing from Assigning Cursor Variables to Same Work Area
|
| Example 6-39 Reusing a SAVEPOINT With ROLLBACK
|
| Example 9-1 A Simple Package Specification Without a Body
|
| Example 9-2 Matching Package Specifications and Bodies
|
| Example 9-3 Creating the emp_admin Package
|
| Example 9-4 Using PUT_LINE in the DBMS_OUTPUT Package
|
| Example 9-5 Separating Cursor Specifications With Packages
|
| Example 10-1 Runtime Error Handling
|
| Example 10-2 Managing Multiple Errors With a Single Exception Handler
|
| Example 10-3 Scope of PL/SQL Exceptions
|
| Example 10-4 Using PRAGMA EXCEPTION_INIT
|
| Example 10-5 Raising an Application Error With raise_application_error
|
| Example 10-6 Using RAISE to Force a User-Defined Exception
|
| Example 10-7 Using RAISE to Force a Pre-Defined Exception
|
| Example 10-8 Scope of an Exception
|
| Example 10-9 Reraising a PL/SQL Exception
|
| Example 10-10 Raising an Exception in a Declaration
|
| Example 10-11 Displaying SQLCODE and SQLERRM
|
| Example 10-12 Continuing After an Exception
|
| Example 10-13 Retrying a Transaction After an Exception
|
| Example 10-14 Using a Locator Variable to Identify the Location of an Exception
|
| Example 10-15 Controlling the Display of PL/SQL Warnings
|
| Example 10-16 Using the DBMS_WARNING Package to Display Warnings
|
| Example 11-1 Nesting a Query to Improve Performance
|
| Example 11-2 Issuing DELETE Statements in a Loop
|
| Example 11-3 Issuing INSERT Statements in a Loop
|
| Example 11-4 Using FORALL with Part of a Collection
|
| Example 11-5 Using FORALL with Non-Consecutive Index Values
|
| Example 11-6 Using Rollbacks With FORALL
|
| Example 11-7 Using %BULK_ROWCOUNT With the FORALL Statement
|
| Example 11-8 Counting Rows Affected by FORALL With %BULK_ROWCOUNT
|
| Example 11-9 Bulk Operation That Continues Despite Exceptions
|
| Example 11-10 Retrieving Query Results With BULK COLLECT
|
| Example 11-11 Using the Pseudocolumn ROWNUM to Limit Query Results
|
| Example 11-12 Bulk-Fetching from a Cursor Into One or More Collections
|
| Example 11-13 Bulk-Fetching from a Cursor Into a Collection of Records
|
| Example 11-14 Using LIMIT to Control the Number of Rows In a BULK COLLECT
|
| Example 11-15 Using BULK COLLECT With the RETURNING INTO Clause
|
| Example 11-16 Using FORALL With BULK COLLECT
|
| Example 11-17 Using NOCOPY With Parameters
|
| Example 11-18 Compiling a PL/SQL Procedure for Native Execution
|
| Example 11-19 Checking plsql_code_type For a Compiled Procedure
|
| Example 11-20 Checking for Invalid PL/SQL Units
|
| Example 11-21 Checking for PLSQL Compilation Type
|
| Example 11-22 Assigning the Result of a Table Function
|
| Example 11-23 Using a Pipelined Table Function For a Transformation
|
| Example 11-24 Using Multiple REF CURSOR Input Variables
|
| Example 11-25 Using a Pipelined Table Function as an Aggregate Function
|
| Example 12-1 Working With Object Types
|
| Example 12-2 Declaring Object Types in a PL/SQL Block
|
| Example 12-3 Null Objects in a PL/SQL Block
|
| Example 12-4 Accessing Object Attributes
|
| Example 12-5 Inserting Rows in an Object Table
|
| Example 12-6 Accessing Object Methods
|
| Example 12-7 Updating and Deleting Rows in an Object Table
|
| Example 12-8 Updating Rows in an Object Table With a REF Modifier
|
| Example 12-10 Declaring a Nested Table in SQL
|
| Example 12-11 Creating a Table with a Varray Column
|
| Example 12-12 Varray Constructor Within a SQL Statement
|
| Example 12-13 Nested Table Constructor Within a SQL Statement
|
| Example 12-14 Performing Operations on PL/SQL Nested Tables With CAST
|
| Example 12-15 Using INSERT, UPDATE, DELETE, and SELECT Statements With Nested Tables
|
| Example 12-16 Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays
|
| Example 12-17 Using BULK COLLECT with Nested Tables
|
| Example 12-18 TEAMS Package Using Dynamic SQL for Object Types and Collections
|
| Example 12-19 Calling Procedures from the TEAMS Package
|
| Example A-1 Using the create_wrapped Procedure to Wrap a Package
|
| Example B-1 Resolving Global and Local Variable Names
|
| Example B-2 Using the Dot Notation to Qualify Names
|
| Example 1-1 Declaring Variables in PL/SQL
|