A Relational Database Management System (RDBMS) is a software system that allows you to create and manage a relational database. Minimum requirements for such a system are defined by both ANSI and ISO. PL/SQL is Oracle’s Procedural Language for SQL. It is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. Oracle 12c focuses on Oracle’s cloud-based offerings with a new architecture – Plugable Databases (PDBs) – designed for rapid deployment and migration in a multi-tenant environment. This courseware is designed to teach both end-users and developers accessible but powerful SQL query and data manipulation (DML) skills, then teach critical programming and optimization techniques using advanced PL/SQL features.

Objectives

Create triggers on database tables.

Use PL/SQL’s datatypes for database and program data.

Use program structure and control flow to design and write PL/SQL programs.

Create PL/SQL stored procedures and functions.

Write robust programs that handle runtime exceptions.

Use PL/SQL’s collection datatypes.

Use cursors to work with database data.

Use the packages supplied with Oracle.

Design and write your own packages.

Maintain and evolve your PL/SQL programs.

Manage the security of your stored PL/SQL programs.

Detailed Outline

1. Course Introduction
   • Course Objectives
   • Course Overview
   • Using the Workbook
   • Suggested References
2. Triggers
   • Beyond Declarative Integrity
   • Triggers
   • Types of Triggers
   • Trigger Sequencing
   • Row-Level Triggers
   • Trigger Predicates
   • Trigger Conditions
   • Using SEQUENCEs
   • Cascading Triggers and Mutating Tables
   • Generating an Error
   • Maintaining Triggers
3. PL/SQL Variables and Datatypes
   • Anonymous Blocks
   • Declaring Variables
   • Datatypes
   • Subtypes
   • Character Data
   • Dates and Timestamps
   • Date Intervals
   • Anchored Types
   • Assignment and Conversions
   • Selecting into a Variable
   • Returning into a Variable
4. PL/SQL Syntax and Logic
   • Conditional Statements – IF/THEN
   • Conditional Statements – CASE
   • Comments and Labels
   • Loops
   • WHILE and FOR Loops
   • SQL in PL/SQL
   • Local Procedures and Functions
5. Stored Procedures and Functions
   • Stored Subprograms
   • Creating a Stored Procedure
   • Procedure Calls and Parameters
   • Parameter Modes
   • Named Parameter Notation
   • Default Arguments
   • Creating a Stored Function
   • Stored Functions and SQL
   • Invoker’s Rights
6. Exception Handling
   • SQLCODE and SQLERRM
   • Exception Handlers
   • Nesting Blocks
   • Scope and Name Resolution
   • Declaring and Raising Named Exceptions
   • User-Defined Exceptions
7. Records and Collections
   • Record Variables
   • Using the %ROWTYPE Attribute
   • User-Defined Object Types
   • VARRAY and Nested TABLE Collections
   • Using Nested TABLEs
   • Using VARRAYs
   • Collections in Database Tables
   • Associative Array Collections
   • Collection Methods
   • Iterating Through Collections
8. Cursors
   • Multi-Row Queries
   • Declaring and Opening Cursors
   • Fetching Rows
   • Closing Cursors
   • The Cursor FOR Loop
   • FOR UPDATE Cursors
   • Cursor Parameters
   • The Implicit (SQL) Cursor
9. Bulk Operations
   • Bulk Binding
   • BULK COLLECT Clause
   • FORALL Statement
   • FORALL Variations
   • Bulk Returns
   • Bulk Fetching with Cursors
10. Using Packages
    • Packages
    • Oracle-Supplied Packages
    • The DBMS_OUTPUT Package
    • The DBMS_UTILITY Package
    • The UTL_FILE Package
    • The DBMS_METADATA Package
    • XML Packages
    • Networking Packages
    • Other Supplied Packages
11. Creating Packages
    • Structure of a Package
    • The Package Interface and Implementation
    • Package Variables and Package State
    • Overloading Package Functions and Procedures
    • Forward Declarations
    • Strong REF CURSOR Variables
    • Weak REF CURSOR Variables
12. Working with LOBs
    • Large Object Types
    • Oracle Directories
    • LOB Locators
    • Internal LOBs
    • LOB Storage and SECUREFILEs
    • External LOBs
    • Temporary LOBs
    • The DBMS_LOB Package
13. Maintaining PL/SQL Code
    • Privileges for Stored Programs
    • Data Dictionary
    • PL/SQL Stored Program Compilation
    • Conditional Compilation
    • Compile-Time Warnings
    • The PL/SQL Execution Environment
    • Dependencies and Validation
    • Maintaining Stored Programs
14. Appendix A – Dynamic SQL
    • Generating SQL at Runtime
    • Native Dynamic SQL vs. DBMS_SQL Package
    • The EXECUTE IMMEDIATE Statement
    • Using Bind Variables
    • Multi-row Dynamic Queries
    • Bulk Operations with Dynamic SQL
    • Using DBMS_SQL
    • DBMS_SQL Subprograms