Oracle Database PL/SQL User’s Guide And Reference PLSQLUser
SQL_Plus%20User%E2%80%99s%20Guide%20and%20Reference
User Manual:
Open the PDF directly: View PDF 
Page Count: 496 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- Contents
- Send Us Your Comments
- Preface
- What's New in PL/SQL?
- 1 Overview of PL/SQL
- Advantages of PL/SQL
- Understanding the Main Features of PL/SQL
- Understanding PL/SQL Block Structure
- Understanding PL/SQL Variables and Constants
- Processing Queries with PL/SQL
- Declaring PL/SQL Subprograms
- Declaring Datatypes for PL/SQL Variables
- Understanding PL/SQL Control Structures
- Understanding Conditional Compilation
- Writing Reusable PL/SQL Code
- Inputting and Outputting Data with PL/SQL
- Understanding PL/SQL Data Abstraction
- Understanding PL/SQL Error Handling
- PL/SQL Architecture
- 2 Fundamentals of the PL/SQL Language
- Character Sets and Lexical Units
- Declarations
- PL/SQL Naming Conventions
- Scope and Visibility of PL/SQL Identifiers
- Assigning Values to Variables
- PL/SQL Expressions and Comparisons
- Conditional Compilation
- How Does Conditional Compilation Work?
- Conditional Compilation Control Tokens
- Using Conditional Compilation Selection Directives
- Using Conditional Compilation Error Directives
- Using Conditional Compilation Inquiry Directives
- Using Predefined Inquiry Directives With Conditional Compilation
- Using Static Expressions with Conditional Compilation
- Setting the PLSQL_CCFLAGS Initialization Parameter
- Using DBMS_DB_VERSION Package Constants
- Conditional Compilation Examples
- Conditional Compilation Restrictions
- How Does Conditional Compilation Work?
- Using PL/SQL to Create Web Applications and Server Pages
- Summary of PL/SQL Built-In Functions
- 3 PL/SQL Datatypes
- Overview of Predefined PL/SQL Datatypes
- Overview of PL/SQL Subtypes
- Converting PL/SQL Datatypes
- Differences between the CHAR and VARCHAR2 Datatypes
- 4 Using PL/SQL Control Structures
- 5 Using PL/SQL Collections and Records
- What are PL/SQL Collections and Records?
- Choosing Which PL/SQL Collection Types to Use
- Defining Collection Types and Declaring Collection Variables
- Initializing and Referencing Collections
- Assigning Collections
- Comparing Collections
- Using Multilevel Collections
- Using Collection Methods
- Checking If a Collection Element Exists (EXISTS Method)
- Counting the Elements in a Collection (COUNT Method)
- Checking the Maximum Size of a Collection (LIMIT Method)
- Finding the First or Last Collection Element (FIRST and LAST Methods)
- Looping Through Collection Elements (PRIOR and NEXT Methods)
- Increasing the Size of a Collection (EXTEND Method)
- Decreasing the Size of a Collection (TRIM Method)
- Deleting Collection Elements (DELETE Method)
- Applying Methods to Collection Parameters
- Avoiding Collection Exceptions
- Defining and Declaring Records
- Assigning Values to Records
- 6 Performing SQL Operations from PL/SQL
- Overview of SQL Support in PL/SQL
- Managing Cursors in PL/SQL
- Querying Data with PL/SQL
- Selecting At Most One Row: SELECT INTO Statement
- Selecting Multiple Rows: BULK COLLECT Clause
- Looping Through Multiple Rows: Cursor FOR Loop
- Performing Complicated Query Processing: Explicit Cursors
- Querying Data with PL/SQL: Implicit Cursor FOR Loop
- Querying Data with PL/SQL: Explicit Cursor FOR Loops
- Defining Aliases for Expression Values in a Cursor FOR Loop
- Using Subqueries
- Using Cursor Variables (REF CURSORs)
- What Are Cursor Variables (REF CURSORs)?
- Why Use Cursor Variables?
- Declaring REF CURSOR Types and Cursor Variables
- Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE
- Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL
- Avoiding Errors with Cursor Variables
- Restrictions on Cursor Variables
- Using Cursor Expressions
- Overview of Transaction Processing in PL/SQL
- Doing Independent Units of Work with Autonomous Transactions
- 7 Performing SQL Operations with Native Dynamic SQL
- Why Use Dynamic SQL with PL/SQL?
- Using the EXECUTE IMMEDIATE Statement in PL/SQL
- Using Bulk Dynamic SQL in PL/SQL
- Guidelines for Using Dynamic SQL with PL/SQL
- Building a Dynamic Query with Dynamic SQL
- When to Use or Omit the Semicolon with Dynamic SQL
- Improving Performance of Dynamic SQL with Bind Variables
- Passing Schema Object Names As Parameters
- Using Duplicate Placeholders with Dynamic SQL
- Using Cursor Attributes with Dynamic SQL
- Passing Nulls to Dynamic SQL
- Using Database Links with Dynamic SQL
- Using Invoker Rights with Dynamic SQL
- Using Pragma RESTRICT_REFERENCES with Dynamic SQL
- Avoiding Deadlocks with Dynamic SQL
- Backward Compatibility of the USING Clause
- Using Dynamic SQL With PL/SQL Records and Collections
- 8 Using PL/SQL Subprograms
- What Are Subprograms?
- Advantages of PL/SQL Subprograms
- Understanding PL/SQL Procedures
- Understanding PL/SQL Functions
- Declaring Nested PL/SQL Subprograms
- Passing Parameters to PL/SQL Subprograms
- Overloading Subprogram Names
- How Subprogram Calls Are Resolved
- Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)
- Advantages of Invoker's Rights
- Specifying the Privileges for a Subprogram with the AUTHID Clause
- Who Is the Current User During Subprogram Execution?
- How External References Are Resolved in Invoker's Rights Subprograms
- Overriding Default Name Resolution in Invoker's Rights Subprograms
- Granting Privileges on Invoker's Rights Subprograms
- Using Roles with Invoker's Rights Subprograms
- Using Views and Database Triggers with Invoker's Rights Subprograms
- Using Database Links with Invoker's Rights Subprograms
- Using Object Types with Invoker's Rights Subprograms
- Using Recursion with PL/SQL
- Calling External Subprograms
- Controlling Side Effects of PL/SQL Subprograms
- Understanding Subprogram Parameter Aliasing
- 9 Using PL/SQL Packages
- What Is a PL/SQL Package?
- Advantages of PL/SQL Packages
- Understanding The Package Specification
- Understanding The Package Body
- Some Examples of Package Features
- How Package STANDARD Defines the PL/SQL Environment
- Overview of Product-Specific Packages
- Guidelines for Writing Packages
- Separating Cursor Specs and Bodies with Packages
- 10 Handling PL/SQL Errors
- Overview of PL/SQL Runtime Error Handling
- Advantages of PL/SQL Exceptions
- Summary of Predefined PL/SQL Exceptions
- Defining Your Own PL/SQL Exceptions
- How PL/SQL Exceptions Are Raised
- How PL/SQL Exceptions Propagate
- Reraising a PL/SQL Exception
- Handling Raised PL/SQL Exceptions
- Overview of PL/SQL Compile-Time Warnings
- 11 Tuning PL/SQL Applications for Performance
- Initialization Parameters for PL/SQL Compilation
- How PL/SQL Optimizes Your Programs
- Guidelines for Avoiding PL/SQL Performance Problems
- Avoiding CPU Overhead in PL/SQL Code
- Make SQL Statements as Efficient as Possible
- Make Function Calls as Efficient as Possible
- Make Loops as Efficient as Possible
- Do Not Duplicate Built-in String Functions
- Reorder Conditional Tests to Put the Least Expensive First
- Minimize Datatype Conversions
- Use PLS_INTEGER for Integer Arithmetic
- Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point Arithmetic
- Avoiding Memory Overhead in PL/SQL Code
- Avoiding CPU Overhead in PL/SQL Code
- Profiling and Tracing PL/SQL Programs
- Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
- Writing Computation-Intensive Programs in PL/SQL
- Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
- Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
- Compiling PL/SQL Code for Native Execution
- Before You Begin
- Determining Whether to Use PL/SQL Native Compilation
- How PL/SQL Native Compilation Works
- The spnc_commands File
- Setting up Initialization Parameters for PL/SQL Native Compilation
- Setting Up PL/SQL Native Library Subdirectories
- Setting Up and Testing PL/SQL Native Compilation
- Setting Up a New Database for PL/SQL Native Compilation
- Modifying the Entire Database for PL/SQL Native or Interpreted Compilation
- Setting Up Transformations with Pipelined Functions
- Overview of Pipelined Table Functions
- Writing a Pipelined Table Function
- Using Pipelined Table Functions for Transformations
- Returning Results from Pipelined Table Functions
- Pipelining Data Between PL/SQL Table Functions
- Optimizing Multiple Calls to Pipelined Table Functions
- Fetching from the Results of Pipelined Table Functions
- Passing Data with Cursor Variables
- Performing DML Operations Inside Pipelined Table Functions
- Performing DML Operations on Pipelined Table Functions
- Handling Exceptions in Pipelined Table Functions
- 12 Using PL/SQL With Object Types
- 13 PL/SQL Language Elements
- Assignment Statement
- AUTONOMOUS_TRANSACTION Pragma
- Block Declaration
- CASE Statement
- CLOSE Statement
- Collection Definition
- Collection Methods
- Comments
- COMMIT Statement
- Constant and Variable Declaration
- Cursor Attributes
- Cursor Variables
- Cursor Declaration
- DELETE Statement
- EXCEPTION_INIT Pragma
- Exception Definition
- EXECUTE IMMEDIATE Statement
- EXIT Statement
- Expression Definition
- FETCH Statement
- FORALL Statement
- Function Declaration
- GOTO Statement
- IF Statement
- INSERT Statement
- Literal Declaration
- LOCK TABLE Statement
- LOOP Statements
- MERGE Statement
- NULL Statement
- Object Type Declaration
- OPEN Statement
- OPEN-FOR Statement
- Package Declaration
- Procedure Declaration
- RAISE Statement
- Record Definition
- RESTRICT_REFERENCES Pragma
- RETURN Statement
- RETURNING INTO Clause
- ROLLBACK Statement
- %ROWTYPE Attribute
- SAVEPOINT Statement
- SELECT INTO Statement
- SERIALLY_REUSABLE Pragma
- SET TRANSACTION Statement
- SQL Cursor
- SQLCODE Function
- SQLERRM Function
- %TYPE Attribute
- UPDATE Statement
- A Obfuscating PL/SQL Source Code
- B How PL/SQL Resolves Identifier Names
- C PL/SQL Program Limits
- D PL/SQL Reserved Words and Keywords
- Index