Contents
Introduction to PL/SQL New Features in Oracle 12c
Invoker’s Rights Functions Can Be Result-Cached
More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause
Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms
More Data Types Have the Same Maximum Size in SQL and PL/SQL
Database Triggers on Pluggable Databases
LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
Objects, Not Types, Are Editioned or Noneditioned
PL/SQL Functions That Run Faster in SQL
Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE
Compilation Parameter PLSQL_DEBUG Is Deprecated
Lab 1.2: PL/SQL Development Environment
Getting Started with SQL Developer
DBMS_OUTPUT.PUT_LINE Statement
Chapter 2 PL/SQL Language Fundamentals
Lab 2.1: PL/SQL Programming Fundamentals
Declare and Initialize Variables
Scope of a Block, Nested Blocks, and Labels
Lab 3.1: DML Statements in PL/SQL
Initialize Variables with SELECT INTO
Using the SELECT INTO Syntax for Variable Initialization
Using a Sequence in a PL/SQL Block
Lab 3.2: Transaction Control in PL/SQL
Using COMMIT, ROLLBACK, and SAVEPOINT
Putting Together DML and Transaction Control
Chapter 4 Conditional Control: IF Statements
Chapter 5 Conditional Control: CASE Statements
Lab 5.3: NULLIF and COALESCE Functions
Chapter 6 Iterative Control: Part I
Premature Termination of the WHILE Loop
Using the IN Option in the Loop
Using the REVERSE Option in the Loop
Premature Termination of the Numeric FOR Loop
Chapter 7 Iterative Control: Part II
Chapter 8 Error Handling and Built-in Exceptions
Lab 9.2: User-Defined Exceptions
Lab 9.3: Exception Propagation
Chapter 10 Exceptions: Advanced Concepts
Lab 10.1: RAISE_APPLICATION_ERROR
Lab 10.2: EXCEPTION_INIT Pragma
Chapter 11 Introduction to Cursors
Making Use of an Implicit Cursor
Making Use of an Explicit Cursor
Making Use of a User-Defined Record
Making Use of Cursor Attributes
Making Use of Cursor FOR LOOPs
Lab 12.1: Parameterized Cursors
Lab 12.2: Complex Nested Cursors
Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors
Chapter 14 Mutating Tables and Compound Triggers
Resolving Mutating Table Issues
Resolving Mutating Table Issues with Compound Triggers
Lab 15.3: Multilevel Collections
Table-Based and Cursor-Based Records
Lab 16.3: Collections of Records
Lab 17.1: EXECUTE IMMEDIATE Statements
Using the EXECUTE IMMEDIATE Statement
How to Avoid Common ORA Errors When Using EXECUTE IMMEDIATE
Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements
Lab 18.2: The BULK COLLECT Clause
Lab 18.3: Binding Collections in SQL Statements
Binding Collections with EXECUTE IMMEDIATE Statements
Binding Collections with OPEN-FOR, FETCH, and CLOSE Statements
Putting Procedure Creation Syntax into Practice
Querying the Data Dictionary for Information on Procedures
Lab 19.2: Passing Parameters IN and OUT of Procedures
Using IN and OUT Parameters with Procedures
Lab 20.2: Using Functions in SQL Statements
Invoking Functions in SQL Statements
Lab 20.3: Optimizing Function Execution in SQL
Defining a Function Using the WITH Clause
Creating a Function with the UDF Pragma
Creating Package Specifications
Lab 21.3: Extending the Package
Extending the Package with Additional Procedures
Lab 21.4: Package Instantiation and Initialization
Creating Package Variables During Initialization
Lab 21.5: SERIALLY_REUSABLE Packages
Using the SERIALLY_REUSABLE Pragma
Lab 22.1: Gathering Information about Stored Code
Getting Stored Code Information from the Data Dictionary
Chapter 23 Object Types in Oracle
Using Object Types with Collections
Chapter 24 Oracle-Supplied Packages
Lab 24.1: Extending Functionality with Oracle-Supplied Packages
Accessing Files within PL/SQL with UTL_FILE
Generating an Explain Plan with DBMS_XPLAN
Generating Implicit Statement Results with DBMS_SQL
Lab 24.2: Error Reporting with Oracle-Supplied Packages
Using the DBMS_UTILITY Package for Error Reporting
Using the UTL_CALL_STACK Package for Error Reporting
Lab 25.2: PL/SQL Optimization Levels
Appendix A PL/SQL Formatting Guide