Oracle PL/SQL by Example, 6th Edition

Oracle PL/SQL by Example, 6th Edition

by Benjamin Rosenzweig, Elena Rakhimov
Released June 2023
Publisher(s): Oracle Press
ISBN: 9780138062958

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

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

Step by step, you'll walk through every key task, mastering today's most valuable Oracle 21c PL/SQL programming techniques on your own. Start by downloading the supporting schema and exercises from informit.com/title/9780138062835. Once you've done an exercise, the author doesn't just present the answer: She offers an in-depth discussion introducing deeper insights and modern best practices.

This book's approach fully reflects the author's award-winning experience teaching PL/SQL to professionals at Columbia University in New York City. 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 21c solutions reference.

New in This Edition

  • Updated code examples throughout

  • New iteration controls for the FOR LOOP statement, such as stepped range, multiple iterations, collection, and cursor iterations

  • Enhancements for PL/SQL qualified expressions

  • Performance enhancements for PL/SQL functions, such as SQL macro, and better control of the result cache

Other Topics Covered

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

  • Using conditional and iterative program controls

  • 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. Cover Page
  2. About This eBook
  3. Halftitle Page
  4. Title Page
  5. Copyright Page
  6. Pearson’s Commitment to Diversity, Equity, and Inclusion
  7. Figure Credits
  8. Contents at a Glance
  9. Contents
  10. Preface
  11. Acknowledgments
  12. About the Author
  13. Introduction to PL/SQL New Features in Oracle 21c
    1. PL/SQL Extended Iterators
    2. PL/SQL Qualified Expressions Enhancements
    3. SQL Macros
    4. New JSON Data Type
    5. New Pragma SUPPRESSES_WARNING_6009
    6. PL/SQL Type Attributes in Non-Persistable User-Defined Types
    7. PL/SQL Function Enhanced Result Cache
  14. Chapter 1. PL/SQL Concepts
    1. Lab 1.1: PL/SQL Architecture
    2. Lab 1.2: PL/SQL Development Environment
    3. Lab 1.3: PL/SQL: The Basics
    4. Summary
  15. Chapter 2. PL/SQL Language Fundamentals
    1. Lab 2.1: PL/SQL Language Components
    2. Lab 2.2: Anchored Data Types
    3. Lab 2.3: Scope of a Variable, Block, Nested Blocks, and Labels
    4. Summary
  16. Chapter 3. SQL in PL/SQL
    1. Lab 3.1: SQL Statements in PL/SQL
    2. Lab 3.2: Transaction Control in PL/SQL
    3. Summary
  17. Chapter 4. Conditional Control: IF Statements
    1. Lab 4.1: IF Statements
    2. Lab 4.2: ELSIF Statements
    3. Lab 4.3: Nested IF Statements
    4. Summary
  18. Chapter 5. Conditional Control: CASE Statements
    1. Lab 5.1: CASE Statements
    2. Lab 5.2: CASE Expressions
    3. Lab 5.3: NULLIF and COALESCE Functions
    4. Summary
  19. Chapter 6. Iterative Control: Part I
    1. Lab 6.1: Simple Loops
    2. Lab 6.2: WHILE Loops
    3. Lab 6.3: Numeric FOR Loops
    4. Summary
  20. Chapter 7. Iterative Control: Part II
    1. Lab 7.1: CONTINUE Statement
    2. Lab 7.2: Nested Loops
    3. Summary
  21. Chapter 8. Error Handling and Built-in Exceptions
    1. Lab 8.1: Handling Errors
    2. Lab 8.2: Built-in Exceptions
    3. Summary
  22. Chapter 9. Exceptions
    1. Lab 9.1: Exception Scope
    2. Lab 9.2: User-Defined Exceptions
    3. Lab 9.3: Exception Propagation
    4. Summary
  23. Chapter 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
  24. Chapter 11. Introduction to Cursors
    1. Lab 11.1: Types of Cursors
    2. Lab 11.2: Table-Based and Cursor-Based Records
    3. Lab 11.3: Cursor FOR Loops
    4. Lab 11.4: Nested Cursors
    5. Summary
  25. Chapter 12. Advanced Cursors
    1. Lab 12.1: Parameterized Cursors
    2. Lab 12.2: Cursor Variables and Expressions
    3. Lab 12.3: FOR UPDATE Cursors
    4. Summary
  26. Chapter 13. Triggers
    1. Lab 13.1: What Triggers Are
    2. Lab 13.2: Types of Triggers
    3. Summary
  27. Chapter 14. Mutating Tables and Compound Triggers
    1. Lab 14.1: Mutating Tables
    2. Lab 14.2: Compound Triggers
    3. Summary
  28. Chapter 15. Collections
    1. Lab 15.1: PL/SQL Tables
    2. Lab 15.2: Varrays
    3. Lab 15.3: Multidimensional Collections
    4. Lab 15.4: Collection Iteration Controls and Qualified Expressions
    5. Summary
  29. Chapter 16. Records
    1. Lab 16.1: User-Defined Records
    2. Lab 16.2: Nested Records
    3. Lab 16.3: Collections of Records
    4. Summary
  30. Chapter 17. Native Dynamic SQL
    1. Lab 17.1: EXECUTE IMMEDIATE Statements
    2. Lab 17.2: OPEN FOR, FETCH, and CLOSE Statements
    3. Summary
  31. Chapter 18. Bulk SQL
    1. Lab 18.1: FORALL Statements
    2. Lab 18.2: The BULK COLLECT Clause
    3. Lab 18.3: Binding Collections in SQL Statements
    4. Summary
  32. Chapter 19. Procedures
    1. Lab 19.1: Creating Nested Procedures
    2. Lab 19.2: Creating Stand-Alone Procedures
    3. Summary
  33. Chapter 20. Functions
    1. Lab 20.1: Creating Nested Functions
    2. Lab 20.2: Creating Stand-Alone Functions
    3. Summary
  34. Chapter 21. Packages
    1. Lab 21.1: Creating Packages
    2. Lab 21.2: Package Instantiation and Initialization
    3. Lab 21.3: SERIALLY_REUSABLE Packages
    4. Summary
  35. Chapter 22. Stored Code Advanced Concepts
    1. Lab 22.1: Subprogram Overloading
    2. Lab 22.2: Result-Cached Functions
    3. Lab 22.3: Invoking PL/SQL Functions from SQL Statements
    4. Summary
  36. Chapter 23. Object Types in Oracle
    1. Lab 23.1: Object Types
    2. Lab 23.2: Object Type Methods
    3. Summary
  37. Chapter 24. Storing Object Types in Tables
    1. Lab 24.1: Storing Object Types in Relational Tables
    2. Lab 24.2: Storing Object Types in Object Tables
    3. Lab 24.3: Type Evolution
    4. Summary
  38. Chapter 25. Dynamic SQL with the DBMS_SQL Package
    1. Lab 25.1: Generating Dynamic SQL with the DBMS_SQL Package
    2. Summary
  39. Appendix A. PL/SQL Formatting Guide
    1. Case
    2. Whitespace
    3. Naming Conventions
    4. Comments
  40. Appendix B. Student Database Schema
    1. Table and Column Descriptions
  41. Index
  42. Code Snippets

Product information

  • Title: Oracle PL/SQL by Example, 6th Edition
  • Author(s): Benjamin Rosenzweig, Elena Rakhimov
  • Release date: June 2023
  • Publisher(s): Oracle Press
  • ISBN: 9780138062958