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:
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