Published by Pearson (February 10, 2015) © 2015
Benjamin Rosenzweig | Elena RakhimovProduct Information
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
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