You are previewing Oracle PL/SQL Language Pocket Reference, 4th Edition.
O'Reilly logo
Oracle PL/SQL Language Pocket Reference, 4th Edition

Book Description

The fourth edition of this popular pocket guide provides quick-reference information that will help you use Oracle's PL/SQL language, including the newest Oracle Database 11g features. It's a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. This concise guide boils down the most vital PL/SQL information into an accessible summary of:

  • Fundamental language elements (e.g., block structure, datatypes, declarations)

  • Statements for program control, cursor management, and exception handling

  • Records, procedures, functions, triggers, and packages

  • Calling PL/SQL functions in SQL

  • Compilation options, object-oriented features, collections, and Java integration

The new edition describes such Oracle Database 11g elements as PL/SQL's function result cache, compound triggers, the CONTINUE statement, the SIMPLE_INTEGER datatype, and improvements to native compilation, regular expressions, and compiler optimization (including intra-unit inlining). In addition, this book now includes substantial new sections on Oracle's built-in functions and packages.

When you need answers quickly, the Oracle PL/SQL Language Pocket Reference will save you hours of frustration.

Table of Contents

  1. Oracle PL/SQL Language Pocket Reference, 4th Edition
  2. 1. Oracle PL/SQL Language Pocket Reference
    1. Introduction
      1. Acknowledgments
      2. Conventions
    2. PL/SQL Language Fundamentals
      1. PL/SQL Character Set
      2. Identifiers
      3. Boolean, Numeric, and String Literals
      4. Numeric Literals
      5. Datetime Interval Literals
      6. Delimiters
      7. Comments
      8. Pragmas
      9. Statements
      10. Block Structure
    3. Variables and Program Data
      1. Scalar Datatypes
        1. Numeric datatypes
        2. Character datatypes
        3. Unicode character datatypes
        4. Datetime datatypes
        5. BOOLEAN datatype
      2. LOB Datatypes
      3. Implicit Datatype Conversions
      4. NULLs in PL/SQL
      5. Declaring Variables
        1. Constrained declarations
        2. Constants
        3. Default values
      6. Anchored Declarations
      7. Programmer-Defined Subtypes
    4. Conditional and Sequential Control
      1. Conditional Control Statements
        1. IF-THEN combination
        2. IF-THEN-ELSE combination
        3. IF-THEN-ELSIF combination
        4. CASE statement
        5. CASE expression
      2. Sequential Control Statements
        1. GOTO
        2. NULL
    5. Loops
      1. Simple Loop
      2. Numeric FOR Loop
      3. Cursor FOR Loop
      4. WHILE Loop
      5. REPEAT UNTIL Loop Emulation
      6. EXIT Statement
      7. CONTINUE Statement (Oracle Database 11g)
      8. Loop Labels
    6. Database Interaction
      1. Sequences in PLSQL
      2. Transaction Management
        1. COMMIT
        2. ROLLBACK
        3. SAVEPOINT
        4. SET TRANSACTION
        5. LOCK TABLE
      3. Autonomous Transactions
    7. Cursors in PL/SQL
      1. Explicit Cursors
        1. Declaring explicit cursors
        2. Opening explicit cursors
        3. Fetching from explicit cursors
        4. Closing explicit cursors
        5. Explicit cursor attributes
      2. Implicit Cursors
        1. SELECT FOR UPDATE clause
        2. WHERE CURRENT OF clause
      3. Dynamic Cursors
      4. DBMS_SQL
      5. SQL Injection and Bind Variables
      6. Cursor Variables
      7. Cursor Expressions
    8. Exception Handling
      1. Declaring Exceptions
      2. Raising Exceptions
      3. Scope
      4. Propagation
        1. WHEN OTHERS clause
        2. SQLCODE, SQLERRM, and DBMS_UTILITY.FORMAT_CALL_STACK
        3. Exceptions and DML
    9. Records in PL/SQL
      1. Declaring Records
      2. Referencing Fields of Records
      3. Assigning Records
      4. Records and DML
      5. Nested Records
    10. Collections in PL/SQL
      1. Declaring a Collection
      2. Initializing a Collection
      3. Adding and Removing Elements
      4. Nested Table Functions
      5. Collection Methods
      6. Collections and Privileges
      7. Nested Collections
      8. Bulk Binds
    11. Built-in Functions and Packages
      1. Built-in Functions
        1. Conversion functions
        2. String functions
        3. Numeric functions
        4. Datetime functions
      2. Built-in Regular Expression Functions
        1. Metacharacters
        2. REGEXP_COUNT (Oracle Database 11g)
        3. REGEXP_LIKE
        4. REGEXP_INSTR
        5. REGEXP_SUBSTR
        6. REGEXP_REPLACE
        7. Match modifiers
      3. Built-in Packages
    12. Stored Procedures and Functions
      1. Procedures
      2. Functions
      3. Parameters
        1. Datatype
        2. Mode
        3. Default values
        4. Parameter-passing notations
      4. Local Programs
      5. Program Overloading
      6. Forward Declarations
      7. Table Functions
      8. Function Result Cache
      9. Privileges and Stored PL/SQL
    13. Triggers
      1. Creating Triggers
      2. Trigger Predicates
      3. DML Events
      4. Compound DML Triggers
      5. DDL Events
      6. Database Events
    14. Packages
      1. Package Structure
      2. Referencing Package Elements
      3. Package Data
      4. SERIALLY_REUSABLE Pragma
      5. Package Initialization
    15. Calling PL/SQL Functions in SQL
      1. Calling a Function
      2. Calling Packaged Functions in SQL
      3. Column/Function Name Precedence
    16. Object-Oriented Features
      1. Object Types
      2. Type Inheritance
      3. Methods
        1. Member methods
        2. Static methods
        3. Constructor methods
        4. Comparison methods
      4. Methods in Subtypes
      5. Manipulating Objects in PL/SQL and SQL
      6. Upcasting and Downcasting
        1. REF operator
        2. VALUE operator
        3. DEREF operator
      7. Changing Object Types
    17. Compilation
      1. Compiling Stored PL/SQL Programs
      2. Conditional Compilation
      3. Compiler Warnings
      4. Optimizing Compiler
      5. Performing Native Compilation of PL/SQL
        1. Oracle9i Database
        2. Oracle Database 10g
        3. Oracle Database 11g
    18. Java Language Integration
      1. Example
      2. Publishing Java to PL/SQL
      3. Data Dictionary
  3. About the Authors
  4. Copyright