Course Deliverables/syllabus

Introduction to PL/SQL

• Explain the need for PL/SQL
• Explain the benefits of PL/SQL
• Identify the different types of PL/SQL blocks
• Output messages in PL/SQL

Declaring PL/SQL Variables

• Recognize valid and invalid identifiers
• List the uses of variables, declare and initialize variables, use bind variables
• List and describe various data types using the %TYPE attribute

Writing Executable Statements

• Identify lexical units in a PL/SQL block
• Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
• Write nested blocks and qualify variables with labels
• Write readable code with appropriate indentation

Interacting with the Oracle Database Server

• Create PL/SQL executable blocks using DML and transaction control statements
• Make use of the INTO clause to hold the values returned by a SQL statement

Writing Control Structures

• Identify the uses and types of control structures
• Construct and identify loop statements
• Apply guidelines when using conditional control structures

Working with Composite Data Types

• Create user-defined PL/SQL records
• Create a record with the %ROWTYPE attribute
• Create an INDEX BY table and INDEX BY table of records
• Describe the differences among records, tables, and tables of records

Using Explicit Cursors

• Distinguish between usage of implicit and explicit cursors, use SQL cursor attributes
• Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
• Declare and use cursors with parameters
• Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause

Handling Exceptions

• Define PL/SQL exceptions
• Recognize unhandled exceptions
• Handle different types of exceptions
• Propagate exceptions in nested blocks and call applications

Creating Stored Procedures and Functions

• Differentiate between anonymous blocks and subprograms
• Create a simple procedure and invoke it from an anonymous block
• Create a simple function
• Create a simple function that accepts a parameter
• Differentiate between procedures and functions

Creating Procedures

• Create a simple procedure and invoke it from an anonymous block
• Work with procedures
• Handle exceptions in procedures, remove a procedure, and display a procedure's information

Creating Functions

• Differentiate between a procedure and a function
• Describe the uses of functions
• Work with functions (create, invoke and remove functions)

Creating Packages

• Identify the benefits and the components of packages
• Work with packages

Working with Packages

• Overload package subprograms, use forward declarations
• Create an initialization block in a package body
• Manage persistent package data states for the life of a session and use PL/SQL tables and records in packageS

Creating Triggers

• Describe different types of triggers and their uses
• Create database triggers
• Manage triggers

Creating Compound, DDL, and Event Database Triggers

• Create triggers on DDL statements
• Create triggers on system events