Introduction

·       Course Objectives, Course Agenda and Class Account Information

·       The Human Resources (HR) Schema

·       Appendices Used in this Course

·       PL/SQL Development Environments

 

Introduction to PL/SQL

·       Understanding the benefits and structure of PL/SQL

·       Understanding PL/SQL Blocks

·       Generating output messages in PL/SQL

 

Declaring PL/SQL Variables

·       Identifying valid and invalid identifiers

·       Declaring and initializing variables

·       Various data types

·       Identifying the benefits of using the %TYPE attribute

·       Using bind variables

 

Writing Anonymous PL/SQL blocks

·       Lexical Units in a PL/SQL Block

·       Using SQL Functions in PL/SQL

·       Data Type Conversion

·       Using Nested Blocks as Statements

·       Referencing an Identifier Value in a Nested Block

·       Qualifying an Identifier with a Label

·       Operators in PL/SQL

·       Using Sequences in PL/SQL Expressions

 

Using SQL Statements within a PL/SQL Block

·       Using SQL Statements in PL/SQL

·       Retrieving Data in PL/SQL with the SELECT statement

·       Using Naming Conventions in DML Statements and data retrieval

·       Manipulating Data on the Server Using PL/SQL

 

SQL Cursors

·       Using SQL Cursor Attributes to Obtain Feedback on DML

·       Saving and Discarding Transactions

 

Writing Control Structures

·       Controlling PL/SQL Flow of Execution

·       Using IF and CASE Statements for conditional processing

·       Handling Nulls

·       Building Boolean Conditions with Logical Operators

·       Using Iterative Control with Loop Statements

 

Working with Composite Data Types

·       Composite Data Types of PL/SQL Records and Tables

·       Using PL/SQL Records

·       Inserting and Updating PL/SQL Records

·       Using INDEX BY Tables

·       Using Associative arrays

·       Declaring and Using VArrays

 

Using Explicit Cursors

·       Cursors

·       Explicit Cursor Operations

·       Controlling Explicit Cursors

·       Using Explicit Cursors to Process Rows

·       Cursors and Records

·       Cursor FOR Loops Using Subqueries

·       Explicit Cursor Attributes

·       %NOTFOUND and %ROWCOUNT Attributes

 

Handling Exceptions

·       Concept of Exception

·       Handling Exceptions with PL/SQL

·       Predefined Exceptions

·       Trapping Predefined and Non-predefined Oracle Server Errors

·       Functions that Return Information on Exceptions

·       Usage of PRAGMA keyword

·       Trapping User-Defined Exceptions

·       Propagating Exceptions

 

Creating Stored Procedures and Functions

·       Overview of Stored Procedures and Functions

·       Differentiating between anonymous blocks and subprograms

·       CREATE OR REPLACE PROCEDURE | FUNCTION

·       Understanding the Header Area of a Stored Procedure and Function

·       Creating Simple Procedures and Functions

·       Creating a Simple Procedure with an IN Parameter

·       Executing a Procedure and a Function