| Introduction to PL/SQL | Describing PL/SQL |
| Explaining the Benefits of PL/SQL |
| PL/SQL block structure |
| Types of PL/SQL block |
| Examining an Anonymous block |
| Course objectives and overview |
| Declaring variables | Recognize valid and invalid identifiers |
| List the uses of variables |
| Declare and initialize variables |
| List and describe various data types |
| Identify the benefits of using the %TYPE attribute |
| Declare, use, and print bind variables |
| Writing executable statements | Identify lexical units in a PL/SQL block |
| Use built-in SQL functions in PL/SQL |
| Data type Conversion |
| Describing the Rules of Nested Blocks |
| Write readable code with appropriate indentation |
| Interacting with the Oracle Server | Retrieving data with PL/SQL |
| Manipulating Data with PL/SQL |
| Introducing SQL cursors |
| Writing Control Structures | Identifying the Uses and Types of Control Structures |
| Constructing an IF Statement |
| Constructing and Identifying Different Loop Statements |
| Writing Control Structures | Introducing composite data types |
| Creating User-Defined PL/SQL Records |
| Creating a PL/SQL record with the %ROWTYPE |
| Using Explicit Cursors | Distinguishing Between the Implicit and Explicit Cursors |
| Discuss the reasons for using Explicit Cursors |
| Declare and control Explicit Cursors |
| Writing a Cursor FOR Loop |
| Writing a Cursor that Uses Parameters |
| Determining When a FOR UPDATE Clause in a Cursor Is Required |
| Handling Exceptions | Defining PL/SQL Exceptions |
| Recognizing Unhandled Exceptions |
| Listing and Using Different Types of PL/SQL Exception Handlers |
| Trapping Unanticipated Errors |
| Describing the Effect of Exception Propagation in Nested Blocks |
| Customizing PL/SQL Exception Messages |
| Introduction to Creating Stored Procedures and Functions | Procedures and Functions |
| Differences Between Anonymous Blocks and Subprograms |
| Create a simple Procedure |
| Create a simple Function |
| Create a simple function that accepts a parameter |
| Differentiate between procedures and functions |
| Creating Procedures | Identify the benefits of modularized and layered subprogram design |
| Create and call procedures |
| Use formal and actual parameter |
| Identify the available parameter passing modes |
| Handle exceptions in procedures |
| Remove a procedure |
| Creating Functions | Differentiate between a procedure and a function |
| Describe the uses of functions |
| Create a function |
| Remove a function |
| Creating Packages | Describe packages and list their possible components |
| Creating the package specification and body |
| Invoking the package |
| Remove packages |
| Working with Packages | Overload package procedures and functions |
| Use forward declarations |
| Create an initialization block in a package body |
| Creating Triggers | Working with triggers |
| Triggering Event or Statement |
| Describe different types of triggers |
| Business application scenarios |
| Creating DML trigger |
| Describe database trigger firing rules |
| Types of DML trigger |
| Implementing an Integrity Constraint with a Trigger |
| Status of a Trigger |
| Managing Triggers |
| Testing triggers |
| Creating Compound, DDL, and Event Database Trigger | Working with triggers |
| Working with Compound Trigger |
| Creating Database Triggers |
| Creating Triggers on DDL Statements |
| Creating Triggers on System Events |
| LOGON and LOGOFF Triggers |
| Benefits of Database Triggers |
| Guidelines for designing Triggers |