Preface        xvii
Acknowledgments         xxi
About the Authors         xxiii
Introduction to PL/SQL New Features in Oracle 12c        xxv
Invoker’s Rights Functions Can Be Result-Cached  xxvi
More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause   xxvii
ACCESSIBLE BY Clause   xxvii
FETCH FIRST Clause   xxviii
Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms   xxix
More Data Types Have the Same Maximum Size in SQL and PL/SQL Â Â xxx
Database Triggers on Pluggable Databases   xxx
LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause   xxx
Implicit Statement Results   xxxi
BEQUEATH CURRENT_USER Views   xxxii
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges  xxxii
Invisible Columns   xxxiii
Objects, Not Types, Are Editioned or Noneditioned   xxxiv
PL/SQL Functions That Run Faster in SQL Â Â xxxiv
Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE Â Â xxxvi
Compilation Parameter PLSQL_DEBUG Is Deprecated   xxxvii
Â
Chapter 1: PL/SQL Concepts         1
Lab 1.1: PL/SQL Architecture   2
Lab 1.2: PL/SQL Development Environment   9
Lab 1.3: PL/SQL: The Basics   18
Summary   25
Â
Chapter 2: PL/SQL Language Fundamentals          27
Lab 2.1: PL/SQL Programming Fundamentals  28
Summary   41
Â
Â
Chapter 3: SQL in PL/SQL Â Â Â Â Â Â Â Â 43
Lab 3.1: DML Statements in PL/SQLÂ Â 44
Lab 3.2: Transaction Control in PL/SQLÂ Â 49
Summary   55
Â
Chapter 4: Conditional Control: IF Statements         57
Lab 4.1: IF Statements   58
Lab 4.2: ELSIF Statements   63
Lab 4.3: Nested IF Statements   67
Summary   70
Â
Chapter 5: Conditional Control: CASE Statements         71
Lab 5.1: CASE Statements   71
Lab 5.2: CASE Expressions   80
Lab 5.3: NULLIF and COALESCE Functions   84
Summary   89
Â
Chapter 6: Iterative Control: Part I Â Â Â Â Â Â Â Â Â 91
Lab 6.1: Simple Loops   92
Lab 6.2: WHILE Loops  98
Lab 6.3: Numeric FOR Loops   104
Summary 109
Â
Chapter 7: Iterative Control: Part II Â Â Â Â Â Â Â Â 111
Lab 7.1: CONTINUE Statement   111
Lab 7.2: Nested Loops   118
Summary 122
Â
Chapter 8: Error Handling and Built-in Exceptions         123
Lab 8.1: Handling Errors   124
Lab 8.2: Built-in Exceptions   126
Summary  132
Â
Chapter 9: Exceptions         133
Lab 9.1: Exception Scope   133
Lab 9.2: User-Defined Exceptions   137
Lab 9.3: Exception Propagation   141
Summary  147
Â
Chapter 10: Exceptions: Advanced Concepts         149
Lab 10.1: RAISE_APPLICATION_ERROR Â Â 149
Lab 10.2: EXCEPTION_INIT Pragma  153
Lab 10.3: SQLCODE and SQLERRM Â Â 155
Summary   158
Â
Chapter 11: Introduction to Cursors        159
Lab 11.1: Types of Cursors   159
Lab 11.2: Cursor Loop  165
Lab 11.3: Cursor FOR LOOPs  175
Lab 11.4: Nested Cursors  177
Summary  181
Â
Chapter 12: Advanced Cursors         183
Lab 12.1: Parameterized Cursors  183
Lab 12.2: Complex Nested Cursors   185
Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors  187
Summary  190
Â
Chapter 13: Triggers         191
Lab 13.1: What Triggers Are  191
Lab 13.2: Types of Triggers   205
Summary   211
Â
Chapter 14: Mutating Tables and Compound Triggers         213
Lab 14.1: Mutating Tables   213
Lab 14.2: Compound Triggers   217
Summary  223
Â
Chapter 15: Collections         225
Lab 15.1: PL/SQL Tables  226
Lab 15.2: Varrays  235
Lab 15.3: Multilevel Collections   240
Summary  242
Â
Chapter 16: Records          243
Lab 16.1: Record Types   243
Lab 16.2: Nested Records   250
Lab 16.3: Collections of Records   253
Summary  257
Â
Chapter 17: Native Dynamic SQLÂ Â Â Â Â Â Â Â 259
Lab 17.1: EXECUTE IMMEDIATE Statements   260
Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements   271
Summary  280
Â
Chapter 18: Bulk SQL Â Â Â Â Â Â Â Â Â Â 281
Lab 18.1: FORALL Statements  282
Lab 18.2: The BULK COLLECT Clause  291
Lab 18.3: Binding Collections in SQL Statements   299
Summary  309
Â
Chapter 19: Procedures         311
Benefits of Modular Code   312
Lab 19.1: Creating Procedures   312
Lab 19.2: Passing Parameters IN and OUT of Procedures   315
Summary  319
Â
Chapter 20: Functions        321
Lab 20.1: Creating Functions   321
Lab 20.2: Using Functions in SQL Statements   327
Lab 20.3: Optimizing Function Execution in SQLÂ Â 329
Summary  331
Â
Chapter 21: Packages         333
Lab 21.1: Creating Packages  334
Lab 21.2: Cursor Variables   344
Lab 21.3: Extending the Package   353
Lab 21.4: Package Instantiation and Initialization   366
Lab 21.5: SERIALLY_REUSABLE Packages   368
Summary  371
Â
Chapter 22: Stored Code         373
Lab 22.1: Gathering Information about Stored Code   373
Summary   382
Â
Chapter 23: Object Types in Oracle  385
Lab 23.1: Object Types   386
Lab 23.2: Object Type Methods   394
Summary  404
Â
Chapter 24: Oracle-Supplied Packages         405
Lab 24.1: Extending Functionality with Oracle-Supplied Packages   406
Lab 24.2: Error Reporting with Oracle-Supplied Packages  419
Summary   429
Â
Chapter 25: Optimizing PL/SQL Â Â Â Â Â Â Â Â 431
Lab 25.1: PL/SQL Tuning Tools   432
Lab 25.2: PL/SQL Optimization Levels   438
Lab 25.3: Subprogram Inlining   444
Summary   453
Â
Appendix A: PL/SQL Formatting Guide         455
Case  455
White Space   455
Naming Conventions   456
Comments   457
Other Suggestions   457
Â
Appendix B: Student Database Schema         461
Table and Column Descriptions   461
Â
Index         469
Â