Cover image for Oracle® PL/SQL by Example, Fifth Edition

Oracle® PL/SQL by Example, Fifth Edition

Author Elena Rakhimov , Benjamin Rosenzweig
Release Date: 2015/02/01
ISBN: 9780133798548
Topic:
0%
66
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Using PL/SQL for Oracle Database 12c, you can build solutions that deliver unprecedented performance and efficiency in any environment, including the cloud. Oracle® PL/SQL by Example, Fifth Edition, teaches all the PL/SQL skills you’ll need, through real-world labs, extensive examples, exercises, and projects. Now fully updated for the newest version of PL/SQL, it covers everything from basic syntax and program control through the latest optimization and security enhancements.

Step by step, you’ll walk through every key task, mastering today’s most valuable Oracle 12cPL/SQL programming techniques on your own. Start by downloading projects and exercises from informit.com/title/0133796787. Once you’ve done an exercise, the authors don’t just present the answer: They offer an in-depth discussion introducing deeper insights and modern best practices.

This book’s approach fully reflects the authors’ award-winning experience teaching PL/SQL to professionals at Columbia University. New database developers and DBAs can use it to get productive fast; experienced PL/SQL programmers will find it to be a superb Oracle Database 12csolutions reference.

New in This Edition

  • Updated code examples throughout

  • Result-caching of invoker’s right functions for better performance

  • Extended support for PL/SQL-only data types in dynamic SQL, OCI, and JDBC

  • Security enhancements, including ACCESSIBLE BY whitelists, improved privilege control, and Invisible Columns

    • Other topics covered

  • Mastering basic PL/SQL concepts and language fundamentals, and understanding SQL’s role in PL/SQL

  • Using conditional and iterative program control, including CONTINUE and CONTINUE WHEN

  • Efficiently handling errors and exceptions

  • Working with cursors and triggers, including compound triggers

  • Using stored procedures, functions, and packages to write modular code that other programs can run

  • Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced features

    •

    Table of Contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Dedication Page
    5. Contents
    6. Preface
      1. Who This Book Is For
      2. How This Book Is Organized
      3. About the Companion Website
      4. What You Will Need
      5. About the Sample Schema
    7. Acknowledgments
    8. About the Authors
    9. Introduction to PL/SQL New Features in Oracle 12c
      1. Invoker’s Rights Functions Can Be Result-Cached
      2. More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause
      3. ACCESSIBLE BY Clause
      4. FETCH FIRST Clause
      5. Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms
      6. More Data Types Have the Same Maximum Size in SQL and PL/SQL
      7. Database Triggers on Pluggable Databases
      8. LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause
      9. Implicit Statement Results
      10. BEQUEATH CURRENT_USER Views
      11. INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
      12. Invisible Columns
      13. Objects, Not Types, Are Editioned or Noneditioned
      14. PL/SQL Functions That Run Faster in SQL
      15. Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE
      16. Compilation Parameter PLSQL_DEBUG Is Deprecated
    10. 1. PL/SQL Concepts
      1. Lab 1.1: PL/SQL Architecture
        1. PL/SQL Architecture
        2. PL/SQL Block Structure
        3. How PL/SQL Gets Executed
      2. Lab 1.2: PL/SQL Development Environment
        1. Getting Started with SQL Developer
        2. Getting Started with SQL*Plus
        3. Executing PL/SQL Scripts
      3. Lab 1.3: PL/SQL: The Basics
        1. DBMS_OUTPUT.PUT_LINE Statement
        2. Substitution Variable Feature
      4. Summary
    11. 2. PL/SQL Language Fundamentals
      1. Lab 2.1: PL/SQL Programming Fundamentals
        1. PL/SQL Language Components
        2. PL/SQL Variables
        3. PL/SQL Reserved Words
        4. Identifiers in PL/SQL
        5. Anchored Data Types
        6. Declare and Initialize Variables
        7. Scope of a Block, Nested Blocks, and Labels
      2. Summary
    12. 3. SQL in PL/SQL
      1. Lab 3.1: DML Statements in PL/SQL
        1. Initialize Variables with SELECT INTO
        2. Using the SELECT INTO Syntax for Variable Initialization
        3. Using DML in a PL/SQL Block
        4. Using a Sequence in a PL/SQL Block
      2. Lab 3.2: Transaction Control in PL/SQL
        1. Using COMMIT, ROLLBACK, and SAVEPOINT
        2. Putting Together DML and Transaction Control
      3. Summary
    13. 4. Conditional Control: IF Statements
      1. Lab 4.1: IF Statements
        1. IF-THEN Statements
        2. IF-THEN-ELSE Statement
      2. Lab 4.2: ELSIF Statements
      3. Lab 4.3: Nested IF Statements
        1. Logical Operators
      4. Summary
    14. 5. Conditional Control: CASE Statements
      1. Lab 5.1: CASE Statements
        1. CASE Statements
        2. Searched CASE Statements
      2. Lab 5.2: CASE Expressions
      3. Lab 5.3: NULLIF and COALESCE Functions
        1. NULLIF Function
        2. COALESCE Function
      4. Summary
    15. 6. Iterative Control: Part I
      1. Lab 6.1: Simple Loops
        1. EXIT Statement
        2. EXIT WHEN Statement
      2. Lab 6.2: WHILE Loops
        1. Using WHILE Loops
        2. Premature Termination of the WHILE Loop
      3. Lab 6.3: Numeric FOR Loops
        1. Using the IN Option in the Loop
        2. Using the REVERSE Option in the Loop
        3. Premature Termination of the Numeric FOR Loop
      4. Summary
    16. 7. Iterative Control: Part II
      1. Lab 7.1: CONTINUE Statement
        1. Using CONTINUE Statement
        2. CONTINUE WHEN Statement
      2. Lab 7.2: Nested Loops
        1. Using Nested Loops
        2. Using Loop Labels
      3. Summary
    17. 8. Error Handling and Built-in Exceptions
      1. Lab 8.1: Handling Errors
      2. Lab 8.2: Built-in Exceptions
      3. Summary
    18. 9. Exceptions
      1. Lab 9.1: Exception Scope
      2. Lab 9.2: User-Defined Exceptions
      3. Lab 9.3: Exception Propagation
        1. Re-raising Exceptions
      4. Summary
    19. 10. Exceptions: Advanced Concepts
      1. Lab 10.1: RAISE_APPLICATION_ERROR
      2. Lab 10.2: EXCEPTION_INIT Pragma
      3. Lab 10.3: SQLCODE and SQLERRM
      4. Summary
    20. 11. Introduction to Cursors
      1. Lab 11.1: Types of Cursors
        1. Making Use of an Implicit Cursor
        2. Making Use of an Explicit Cursor
      2. Lab 11.2: Cursor Loop
        1. Processing an Explicit Cursor
        2. Making Use of a User-Defined Record
        3. Making Use of Cursor Attributes
      3. Lab 11.3: Cursor FOR LOOPS
        1. Making Use of Cursor FOR LOOPS
      4. Lab 11.4: Nested Cursors
        1. Processing Nested Cursors
      5. Summary
    21. 12. Advanced Cursors
      1. Lab 12.1: Parameterized Cursors
        1. Cursors with Parameters
      2. Lab 12.2: Complex Nested Cursors
      3. Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors
        1. FOR UPDATE Cursor
        2. FOR UPDATE OF in a Cursor
        3. WHERE CURRENT OF in a Cursor
      4. Summary
    22. 13. Triggers
      1. Lab 13.1: What Triggers Are
        1. Database Trigger
        2. BEFORE Triggers
        3. AFTER Triggers
        4. Autonomous Transaction
      2. Lab 13.2: Types of Triggers
        1. Row and Statement Triggers
        2. INSTEAD OF Triggers
      3. Summary
    23. 14. Mutating Tables and Compound Triggers
      1. Lab 14.1: Mutating Tables
        1. What Is a Mutating Table?
        2. Resolving Mutating Table Issues
      2. Lab 14.2: Compound Triggers
        1. What Is a Compound Trigger?
        2. Resolving Mutating Table Issues with Compound Triggers
      3. Summary
    24. 15. Collections
      1. Lab 15.1: PL/SQL Tables
        1. Associative Arrays
        2. Nested Tables
        3. Collection Methods
      2. Lab 15.2: Varrays
      3. Lab 15.3: Multilevel Collections
      4. Summary
    25. 16. Records
      1. Lab 16.1: Record Types
        1. Table-Based and Cursor-Based Records
        2. User-Defined Records
        3. Record Compatibility
      2. Lab 16.2: Nested Records
      3. Lab 16.3: Collections of Records
      4. Summary
    26. 17. Native Dynamic SQL
      1. Lab 17.1: EXECUTE IMMEDIATE Statements
        1. Using the EXECUTE IMMEDIATE Statement
        2. How to Avoid Common ORA Errors When Using EXECUTE IMMEDIATE
      2. Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements
        1. Opening Cursor
        2. Fetching from a Cursor
        3. Closing a Cursor
      3. Summary
    27. 18. Bulk SQL
      1. Lab 18.1: FORALL Statements
        1. Using FORALL Statements
        2. SAVE EXCEPTIONS Option
        3. INDICES OF Option
        4. VALUES OF Option
      2. Lab 18.2: The BULK COLLECT Clause
      3. Lab 18.3: Binding Collections in SQL Statements
        1. Binding Collections with EXECUTE IMMEDIATE Statements
        2. Binding Collections with OPEN-FOR, FETCH, and CLOSE Statements
      4. Summary
    28. 19. Procedures
      1. Benefits of Modular Code
        1. Block Structure
        2. Anonymous Blocks
      2. Lab 19.1: Creating Procedures
        1. Putting Procedure Creation Syntax into Practice
        2. Querying the Data Dictionary for Information on Procedures
      3. Lab 19.2: Passing Parameters IN and OUT of Procedures
        1. Using IN and OUT Parameters with Procedures
      4. Summary
    29. 20. Functions
      1. Lab 20.1: Creating Functions
        1. Creating Stored Functions
        2. Making Use of Functions
      2. Lab 20.2: Using Functions in SQL Statements
        1. Invoking Functions in SQL Statements
        2. Writing Complex Functions
      3. Lab 20.3: Optimizing Function Execution in SQL
        1. Defining a Function Using the WITH Clause
        2. Creating a Function with the UDF Pragma
      4. Summary
    30. 21. Packages
      1. Lab 21.1: Creating Packages
        1. Creating Package Specifications
        2. Creating Package Bodies
        3. Calling Stored Packages
        4. Creating Private Objects
      2. Lab 21.2: Cursor Variables
      3. Lab 21.3: Extending the Package
        1. Extending the Package with Additional Procedures
      4. Lab 21.4: Package Instantiation and Initialization
        1. Creating Package Variables During Initialization
      5. Lab 21.5: SERIALLY_REUSABLE Packages
        1. Using the SERIALLY_REUSABLE Pragma
      6. Summary
    31. 22. Stored Code
      1. Lab 22.1: Gathering Information about Stored Code
        1. Getting Stored Code Information from the Data Dictionary
        2. Overloading Modules
      2. Summary
    32. 23. Object Types in Oracle
      1. Lab 23.1: Object Types
        1. Creating Object Types
        2. Using Object Types with Collections
      2. Lab 23.2: Object Type Methods
        1. Constructor Methods
        2. Member Methods
        3. Static Methods
        4. Comparing Objects
      3. Summary
    33. 24. Oracle-Supplied Packages
      1. Lab 24.1: Extending Functionality with Oracle-Supplied Packages
        1. Accessing Files within PL/SQL with UTL_FILE
        2. Scheduling Jobs with DBMS_JOB
        3. Generating an Explain Plan with DBMS_XPLAN
        4. Generating Implicit Statement Results with DBMS_SQL
      2. Lab 24.2: Error Reporting with Oracle-Supplied Packages
        1. Using the DBMS_UTILITY Package for Error Reporting
        2. Using the UTL_CALL_STACK Package for Error Reporting
      3. Summary
    34. 25. Optimizing PL/SQL
      1. Lab 25.1: PL/SQL Tuning Tools
        1. PL/SQL Profiler API
        2. Trace API
        3. PL/SQL Hierarchical Profiler
      2. Lab 25.2: PL/SQL Optimization Levels
      3. Lab 25.3: Subprogram Inlining
      4. Summary
    35. A. PL/SQL Formatting Guide
      1. Case
      2. White Space
      3. Naming Conventions
      4. Comments
      5. Other Suggestions
    36. B. Student Database Schema
      1. Table and Column Descriptions
    37. Index
    38. Code Snippets