Beginning Oracle PL/SQL, Second Edition

Beginning Oracle PL/SQL, Second Edition

by Donald J. Bales
Released May 2015
Publisher(s): Apress
ISBN: 9781484207376

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Beginning Oracle PL/SQL gets you started in using the built-in language that every Oracle developer and database administrator must know. Oracle Database is chock-full of built-in application features that are free for the using, and PL/SQL is your ticket to learning about and using those features from your own code. With it, you can centralize business logic in the database, you can offload application logic, and you can automate database- and application-administration tasks.

Author Don Bales provides in Beginning Oracle PL/SQL a fast-paced and example-filled tutorial. Learn from Don’s extensive experience to discover the most commonly used aspects of PL/SQL, without wasting time on obscure and obsolete features.

The author takes his 20+ years of experience and a wealth of statistics he's gathered on PL/SQL usage over those years and applies the 80/20 rule: cover what's most needed and used by PL/SQL professionals and avoid what's not necessary! The result is a book that covers all the key features of PL/SQL without wasting your time discussing esoteric and obsolete parts of the language. Learn what really matters, so that you can get to work feeling confident with what you know about PL/SQL.

  • Covers the key topics that matter, including variables and datatypes, executing statements, working with cursors, bulk operations, real-world objects, debugging, testing, and more.
  • Teaches you to write production-level, object-oriented PL/SQL. You'll explore relational PL/SQL, but unlike most other books on the subject, this one emphasizes the use of PL/SQLs object-oriented features as well.
  • Guides you in working through real examples of using of PL/SQL. You'll learn PL/SQL by applying it to real-world business problems, not by heavy theory.
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. About IOUG Press
    7. Contents
    8. About the Author
    9. About the Technical Reviewer
    10. Acknowledgments
    11. Foreword
    12. Introduction (The Big Picture)
    13. Chapter 1: Relational SQL
      1. Tables
        1. An Entity-Relationship Diagram
        2. Data Definition Language (DDL)
        3. It’s Your Turn to Create a Table
      2. Indexes
        1. DDL Again
        2. It’s Your Turn to Create an Index
      3. Constraints
        1. Column Constraints
        2. Table Constraints
        3. It’s Your Turn to Create a Constraint
      4. Triggers
      5. Views
      6. Insert
        1. Insert . . .Values
        2. It’s Your Turn to Insert with Values
        3. Insert . . . Select
        4. It’s Your Turn to Insert with Select
      7. Update
        1. Fix a Mistake with Update
        2. It’s Your Turn to Update
        3. Update and Subqueries
      8. Delete
        1. A Change in Order
        2. It’s Your Turn to Delete
      9. Select
        1. Joins
        2. It’s Your Turn to Select
      10. Your Working Example
        1. Your Example Narrative
        2. Your Example ERD
        3. Create a Code Table
        4. It’s Your Turn to Create Code Tables
        5. Create a Content Table
        6. It’s Your Turn to Create Content Tables
        7. Create an Intersection Table
        8. It’s Your Turn to Create Intersection Tables
      11. Summary
    14. Chapter 2: Blocks, Functions, and Procedures
      1. Blocks
        1. Anonymous Blocks
      2. Exceptions
        1. Common Exceptions
        2. Catching an Exception
      3. Functions
        1. Create a Function
        2. It’s Your Turn to Create a Function
      4. Procedures
        1. Create a Procedure
        2. It’s Your Turn to Create a Procedure
      5. Nested Blocks
        1. An Example of Nesting Blocks
        2. Rules for Nesting
      6. Packages
        1. Create a Package Specification
        2. It’s Your Turn to Create a Package Specification
        3. Create a Package Body
        4. It’s Your Turn to Create a Package Body
        5. Benefits of Using Packages
      7. Summary
    15. Chapter 3: Types, Variables, and Scope
      1. PL/SQL Data Types
      2. Variables
        1. Variable Naming
        2. Variable Declarations
        3. Variable Anchors
        4. Variable Assignments
        5. NULL Value
        6. It’s Your Turn to Declare Variables
      3. Scope
        1. Scope Rules
        2. It’s Your Turn to Scope Things Out
      4. Types
        1. Table Types
        2. Record Types
        3. Multidimensional Arrays
      5. Parameters
        1. Parameter Naming
        2. Parameter Declarations
        3. Parameter Scope
        4. It’s Your Turn to Declare Parameters
      6. Summary
    16. Chapter 4: Single Row Processing
      1. Inserts
        1. Catching a DUP_VAL_ON_INDEX Exception
        2. Using PL/SQL to Predetect a Duplicate
        3. IF I Don’t Tell You Now, When ELSE Can I?
        4. Using SQL to Predetect a Duplicate
        5. It’s Your Turn to Insert
      2. Updates
        1. Updating a Row
        2. Using SQL to Perform Complex Updates
      3. Deletes
      4. Selects
        1. No Data Found
        2. Too Many Rows
        3. It’s Your Turn to Select
      5. Summary
    17. Chapter 5: Multirow Processing
      1. Cursors
        1. Cursor Declarations
        2. Fetching Rows from a Cursor Manually
        3. Cursor Records
        4. A Singleton Cursor
        5. It’s Your Turn to Fetch Manually
        6. Fetching Rows from a Cursor Automatically
        7. It’s Your Turn to Fetch Automatically
      2. Bulk Collect
        1. Bulk Collect with a Cursor
        2. Bulk Collect with a Select Statement
        3. It’s Your Turn to Bulk Collect
      3. FORALL
      4. Summary
    18. Chapter 6: Object-Relational SQL
      1. Information Modeling
      2. Object Orientation
        1. Key Tenets
        2. Reuse
      3. Service Orientation
      4. A Roadmap to Reality
        1. Step 1: Table Packages
        2. Step 2: User-Defined Types
        3. Step 3: Object Views
        4. Step 4: Object Tables
      5. A Change in Order
      6. Object Types
        1. Create a User-Defined Type Specification
        2. Create a User-Defined Type Implementation
        3. It’s Your Turn to Create a User-Defined Type
      7. Object Views
        1. Create an Object View
        2. It’s Your Turn to Create an Object View
      8. Object Tables
        1. Create an Object Table
        2. It’s Your Turn to Create an Object Table
      9. Impedance Mismatch?
        1. Nested Types and Collections
        2. It’s Your Turn to Prove There’s No Impedance Mismatch
      10. Summary
    19. Chapter 7: Troubleshooting
      1. Prevention
        1. Anchors
        2. Data Type Prefixes
        3. Explicit Conversions
      2. Preparation
        1. Blocking
        2. Bread Crumbs
      3. After the Fact
        1. Success Messages
        2. Failure Messages
        3. It’s Your Turn to Use put_line( )
      4. As It Happens
        1. A DEBUG Table
        2. A DEBUGGER Package
        3. It’s Your Turn to Use Debug Logging
      5. One Step at a Time
        1. Debugging with Oracle SQL Developer
        2. Debugging Anonymous PL/SQL
        3. Debugging with TOAD for Oracle
        4. Debugging with Visual Studio
        5. It’s Your Turn to Use a Debugger
      6. Profiling PL/SQL
        1. Profiler’s Tables
        2. Profiler’s Methods
        3. Profiling Reports
        4. It’s Your Turn to Profile
      7. Profiling SQL
        1. Explain Plan
        2. Physics vs. Explain Plan
        3. It’s Your Turn to Use Explain Plan
        4. TKPROF
      8. Summary
    20. Chapter 8: Testing
      1. SQL Test Patterns
      2. PL/SQL Test Patterns
      3. A Testing Tool
        1. A TEST Table
        2. A TEST Package Specification
        3. A TEST Package Body
        4. It’s Your Turn to Create a Testing Tool
      4. Testing
        1. Testing a Code Table Package
        2. It’s Your Turn to Test a Code Table Package
        3. Testing a Content Table Package
        4. It’s Your Turn to Test a Content Table Package
        5. Testing an Intersection Table Package
        6. It’s Your Turn to Test an Intersection Table Package
        7. Testing a Type
        8. It’s Your Turn to Test a Type
      5. Automating Testing
        1. Automate Test Processing
        2. It’s Your Turn to Automate Test Processing
      6. Summary
    21. Chapter 9: Documenting
      1. Indestructible Documentation
        1. SQL*Plus Documentation Tools
        2. GUI Development Environment Tools
        3. Rules for Documentation Comments
      2. Documentation on Demand
        1. A Text-Based Documentation Formatting Tool
        2. Accessing Documentation on Demand
        3. It’s Your Turn to Access Documentation on Demand
      3. Distributable Documentation
        1. An HTML-Based Documentation Formatting Tool
        2. Generating Distributable Documentation
        3. It’s Your Turn to Generate Distributable Documentation
      4. Documentation Distribution
      5. Summary
    22. Chapter 10: Examples
      1. Polymorphic Commands
      2. Parametric SQL
        1. Code Table Methods
        2. Content Table Methods
        3. Intersection Table Methods
        4. Hierarchical Table Methods
      3. The Black Box
        1. Table Methods
        2. Universal Methods
      4. Divide and Conquer
        1. Data Migration
        2. On-Demand Data Processing
        3. Polling Data Processing
        4. Interfacing
        5. Reporting
      5. Summary
    23. Appendix A: How to Download, Install, and Use Oracle
      1. How to Download Oracle Database Software
      2. How to Install Oracle Database Software
      3. How to Use SQL*Plus
        1. How to Download This Book’s Source Code
        2. How to Create a New Username
        3. How to Write a SQL*Plus Script
        4. How to Execute a SQL*Plus Script
        5. How to Describe Your Tables and Stored Procedures
    24. Index
    25. General

    Product information

    • Title: Beginning Oracle PL/SQL, Second Edition
    • Author(s): Donald J. Bales
    • Release date: May 2015
    • Publisher(s): Apress
    • ISBN: 9781484207376