You are previewing Oracle PL/SQL Language Pocket Reference.
O'Reilly logo
Oracle PL/SQL Language Pocket Reference

Book Description

This pocket reference provides quick-reference information that will help you use Oracle's PL/SQL language. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling book, Oracle PL/SQL Programming, and includes coverage of the latest version of Oracle, Oracle8i. Oracle is the most popular database management system in use today, and PL/SQL plays a pivotal role in current and projected Oracle products and applications. PL/SQL provides procedural extensions to the SQL relational database language and to an ever-growing number of Oracle development tools. It offers a myriad of data types, and such features as cursors, loops, conditional and sequential control statements, exception handlers, records, tables, and constructs for developing modular code (functions, procedures, and packages). Originally a rather limited tool, PL/SQL became with Oracle7 a mature and effective language for developers. Now, with the introduction of Oracle8, PL/SQL has taken the next step towards becoming a fully realized programming language providing sophisticated object-oriented capabilities. The book on which this pocket reference is based, Oracle PL/SQL Programming, filled a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL -- and building them the right way. That book has become the bible for PL/SQL developers who have raved about its completeness, readability, and practicality. The book is packed with strategies, code architectures, tips, techniques, and fully realized code. The second edition of the book updated the text with the new Oracle8 PL/SQL features, including object types, collections, external procedures, and large object data types and functions. The pocket reference boils down the most vital information from the large (nearly 1000 pages) book into an accessible quick reference that summarizes the basics of PL/SQL -- its block structure, fundamental language elements (e.g., identifiers, declarations, defaults), data structures (including Oracle8 objects), and statements for program control, loops, exception handling, and database access. It also covers the the basics of using Oracle's procedures, functions, and packages, and the latest features for Oracle8i.

Table of Contents

  1. Oracle PL/SQL Language Pocket Reference
    1. 1. Oracle PL/SQL Language Pocket Reference
      1. Introduction
      2. Acknowledgments
      3. Conventions
      4. PL/SQL Language Fundamentals
        1. The PL/SQL Character Set
          1. Identifiers
          2. Literals
          3. Delimiters
          4. Comments
          5. Pragmas
          6. Statements
          7. Block structure
      5. Variables and Program Data
        1. Scalar Datatypes
          1. Numeric datatypes
          2. Character datatypes
          3. Date-time datatypes
          4. Boolean datatype
        2. NLS Character Datatypes
        3. LOB Datatypes
        4. NULLs in PL/SQL
        5. Declaring Variables
          1. Constrained declarations
          2. Constants
          3. Default values
        6. Anchored Declarations
        7. Programmer-Defined Subtypes
      6. Conditional and Sequential Control
        1. Conditional Control Statements
          1. IF-THEN combination
          2. IF-THEN-ELSE combination
          3. IF-THEN-ELSIF combination
        2. Sequential Control Statements
      7. Loops
        1. The Simple Loop
        2. The Numeric FOR Loop
        3. The Cursor FOR Loop
        4. The WHILE Loop
        5. The REPEAT UNTIL Loop Emulation
        6. The EXIT Statement
        7. Loop Labels
      8. Database Interaction and Cursors
        1. Transaction Management
          1. COMMIT
          2. ROLLBACK
          3. SAVEPOINT
          4. SET TRANSACTION
          5. LOCK TABLE
        2. Native Dynamic SQL (Oracle8i)
        3. Autonomous Transactions (Oracle8i)
      9. 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. The SELECT FOR UPDATE clause
          2. The WHERE CURRENT OF clause
        3. Cursor Variables
      10. Exception Handling
        1. Declaring Exceptions
        2. Raising Exceptions
        3. Scope
        4. Propagation
          1. The WHEN OTHERS clause
          2. SQLCODE and SQLERRM
          3. Exceptions and DML
      11. Records in PL/SQL
        1. Declaring Records
        2. Referencing Fields of Records
        3. Record Assignment
        4. Nested Records
      12. Named Program Units
        1. Procedures
        2. Functions
        3. Parameters
          1. Datatype
          2. Mode
          3. Default values
          4. Parameter-passing notations
          5. Local program
          6. Program overloading
          7. Forward declarations
          8. Compiling stored PL/SQL programs
          9. Privileges and stored PL/SQL
      13. Triggers
        1. Creating Triggers
        2. Trigger Predicates
        3. DML Events
        4. DDL Events (Oracle8i)
        5. Database Events (Oracle8i)
      14. Packages
        1. Overview of Package Structure
        2. Referencing Package Elements
        3. Package Data
          1. The SERIALLY_REUSABLE pragma
        4. Package Initialization
      15. Calling PL/SQL Functions in SQL
        1. Syntax for Calling Stored Functions in SQL
        2. Requirements and Restrictions on Stored Functions in SQL
        3. Calling Packaged Functions in SQL
          1. Column/function name precedence
      16. Oracle8 Objects
        1. Object Types
        2. Methods
        3. Manipulating Objects in PL/SQL and SQL
          1. The REF operator
          2. The VALUE operator
          3. The DEREF operator
        4. Changing Object Types
      17. Collections
        1. Syntax for Declaring Collection Datatypes
        2. Initializing Collections
        3. Adding and Removing Elements
        4. Collection Pseudo-Functions
        5. Collection Methods
        6. Privileges
        7. Bulk Binds (Oracle8i)
      18. External Procedures
        1. Creating an External Procedure
          1. Set up the listener
          2. Identify or create the shared library or DLL
          3. Create the library in the database
          4. Create the PL/SQL wrapper for the external procedure
        2. Parameters
      19. Java Language Integration
        1. Example
        2. Publishing Java to PL/SQL
        3. Data Dictionary