Oracle PL/SQL by Example (5th Edition) (Prentice Hall Professional Oracle Series) by Rosenzweig, Benjamin -- Read -- Imperial Library of Trantor

Index

About This eBook Title Page Copyright Page Dedication Page Contents Preface

Who This Book Is For How This Book Is Organized About the Companion Website What You Will Need About the Sample Schema

Acknowledgments About the Authors 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 ACCESSIBLE BY Clause FETCH FIRST 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 Implicit Statement Results BEQUEATH CURRENT_USER Views INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges Invisible Columns 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

1. PL/SQL Concepts

Lab 1.1: PL/SQL Architecture

PL/SQL Architecture PL/SQL Block Structure How PL/SQL Gets Executed

Lab 1.2: PL/SQL Development Environment

Getting Started with SQL Developer Getting Started with SQL*Plus Executing PL/SQL Scripts

Lab 1.3: PL/SQL: The Basics

DBMS_OUTPUT.PUT_LINE Statement Substitution Variable Feature

Summary

2. PL/SQL Language Fundamentals

Lab 2.1: PL/SQL Programming Fundamentals

PL/SQL Language Components PL/SQL Variables PL/SQL Reserved Words Identifiers in PL/SQL Anchored Data Types Declare and Initialize Variables Scope of a Block, Nested Blocks, and Labels

Summary

3. SQL in PL/SQL

Lab 3.1: DML Statements in PL/SQL

Initialize Variables with SELECT INTO Using the SELECT INTO Syntax for Variable Initialization Using DML in a PL/SQL Block 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

Summary

4. Conditional Control: IF Statements

Lab 4.1: IF Statements

IF-THEN Statements IF-THEN-ELSE Statement

Lab 4.2: ELSIF Statements Lab 4.3: Nested IF Statements

Logical Operators

Summary

5. Conditional Control: CASE Statements

Lab 5.1: CASE Statements

CASE Statements Searched CASE Statements

Lab 5.2: CASE Expressions Lab 5.3: NULLIF and COALESCE Functions

NULLIF Function COALESCE Function

Summary

6. Iterative Control: Part I

Lab 6.1: Simple Loops

EXIT Statement EXIT WHEN Statement

Lab 6.2: WHILE Loops

Using WHILE Loops Premature Termination of the WHILE Loop

Lab 6.3: Numeric FOR Loops

Using the IN Option in the Loop Using the REVERSE Option in the Loop Premature Termination of the Numeric FOR Loop

Summary

7. Iterative Control: Part II

Lab 7.1: CONTINUE Statement

Using CONTINUE Statement CONTINUE WHEN Statement

Lab 7.2: Nested Loops

Using Nested Loops Using Loop Labels

Summary

8. Error Handling and Built-in Exceptions

Lab 8.1: Handling Errors Lab 8.2: Built-in Exceptions Summary

9. Exceptions

Lab 9.1: Exception Scope Lab 9.2: User-Defined Exceptions Lab 9.3: Exception Propagation

Re-raising Exceptions

Summary

10. Exceptions: Advanced Concepts

Lab 10.1: RAISE_APPLICATION_ERROR Lab 10.2: EXCEPTION_INIT Pragma Lab 10.3: SQLCODE and SQLERRM Summary

11. Introduction to Cursors

Lab 11.1: Types of Cursors

Making Use of an Implicit Cursor Making Use of an Explicit Cursor

Lab 11.2: Cursor Loop

Processing an Explicit Cursor Making Use of a User-Defined Record Making Use of Cursor Attributes

Lab 11.3: Cursor FOR LOOPS

Making Use of Cursor FOR LOOPS

Lab 11.4: Nested Cursors

Processing Nested Cursors

Summary

12. Advanced Cursors

Lab 12.1: Parameterized Cursors

Cursors with Parameters

Lab 12.2: Complex Nested Cursors Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors

FOR UPDATE Cursor FOR UPDATE OF in a Cursor WHERE CURRENT OF in a Cursor

Summary

13. Triggers

Lab 13.1: What Triggers Are

Database Trigger BEFORE Triggers AFTER Triggers Autonomous Transaction

Lab 13.2: Types of Triggers

Row and Statement Triggers INSTEAD OF Triggers

Summary

14. Mutating Tables and Compound Triggers

Lab 14.1: Mutating Tables

What Is a Mutating Table? Resolving Mutating Table Issues

Lab 14.2: Compound Triggers

What Is a Compound Trigger? Resolving Mutating Table Issues with Compound Triggers

Summary

15. Collections

Lab 15.1: PL/SQL Tables

Associative Arrays Nested Tables Collection Methods

Lab 15.2: Varrays Lab 15.3: Multilevel Collections Summary

16. Records

Lab 16.1: Record Types

Table-Based and Cursor-Based Records User-Defined Records Record Compatibility

Lab 16.2: Nested Records Lab 16.3: Collections of Records Summary

17. Native Dynamic SQL

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

Opening Cursor Fetching from a Cursor Closing a Cursor

Summary

18. Bulk SQL

Lab 18.1: FORALL Statements

Using FORALL Statements SAVE EXCEPTIONS Option INDICES OF Option VALUES OF Option

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

Summary

19. Procedures

Benefits of Modular Code

Block Structure Anonymous Blocks

Lab 19.1: Creating Procedures

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

Summary

20. Functions

Lab 20.1: Creating Functions

Creating Stored Functions Making Use of Functions

Lab 20.2: Using Functions in SQL Statements

Invoking Functions in SQL Statements Writing Complex Functions

Lab 20.3: Optimizing Function Execution in SQL

Defining a Function Using the WITH Clause Creating a Function with the UDF Pragma

Summary

21. Packages

Lab 21.1: Creating Packages

Creating Package Specifications Creating Package Bodies Calling Stored Packages Creating Private Objects

Lab 21.2: Cursor Variables 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

Summary

22. Stored Code

Lab 22.1: Gathering Information about Stored Code

Getting Stored Code Information from the Data Dictionary Overloading Modules

Summary

23. Object Types in Oracle

Lab 23.1: Object Types

Creating Object Types Using Object Types with Collections

Lab 23.2: Object Type Methods

Constructor Methods Member Methods Static Methods Comparing Objects

Summary

24. Oracle-Supplied Packages

Lab 24.1: Extending Functionality with Oracle-Supplied Packages

Accessing Files within PL/SQL with UTL_FILE Scheduling Jobs with DBMS_JOB 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

Summary

25. Optimizing PL/SQL

Lab 25.1: PL/SQL Tuning Tools

PL/SQL Profiler API Trace API PL/SQL Hierarchical Profiler

Lab 25.2: PL/SQL Optimization Levels Lab 25.3: Subprogram Inlining Summary

A. PL/SQL Formatting Guide

Case White Space Naming Conventions Comments Other Suggestions

B. Student Database Schema

Table and Column Descriptions

Index Code Snippets

← Prev
Back
Next →

← Prev
Back
Next →