Certificate Authentication

Introduction to PL/SQL

• What is PL/SQL
• PL/SQL Environment
• Benefits of PL/SQL
• Declaring and using variables
• Identify the different types of variables
• Describe variables and their uses
• Declare PL/SQL variables
• Describe basic syntax
• Use Literals in PL/SQL
• Using SQL functions in PL/SQL

Interacting with the Oracle Server

• Identify the SQL Statements you can use n PL/SQL
• Retrieve Data in PL/SQL with the SELECT Statement
• Manipulate Data in the Server Using PL/SQL
• Describe and use the SQL Cursor

Writing Control Structures

• Control PL/SQL Flow of Execution
• Conditional processing Using IF Statements
• Conditional Processing CASE Statements
• Use Looping Statements

Working with Composite Data Types

• Learn the Composite Data Types of PL/SQL Records and Associative Arrays
• Use PL/SQL Records to Hold Multiple Values of Different Types
• Inserting and Updating with PL/SQL Records
• Use Associative Arrays to hold multiple values of the same datatype

Using Explicit Cursors

• Cursor FOR Loops Using Sub-queries
• Cursors using Parameters
• Use the FOR UPDATE Clause to Lock Rows
• Use the WHERE CURRENT Clause to Reference the Current Row
• Use Explicit Cursors to Process Rows
• Explicit Cursor Attributes
• Cursors and Records

Handling Exceptions

• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Non-predefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To
• Applications

Creating Stored Procedures

• Creating, Calling, and Removing Stored Procedures using the CREATE
• Command
• Using Procedures Parameters and Parameters Modes

Creating Stored Functions

• Creating, Calling, and Removing a Stored Function using the CREATE
• Command
• Identifying the Advantages of Using Stored Functions in SQL Statements
• Identify the steps to create a stored function
• Using User-Defined Functions in SQL Statements
• Restrictions When Calling Functions from SQL statements

Creating Packages

• Listing the Advantages of Packages
• Describing Packages
• The Components of a Package
• Developing a Package
• The Visibility of a Package’s Components
• Creating the Package Specification and Body
• Using the SQL CREATE Statement
• Invoking the Package Constructs
• Overloading Subprograms in PL/SQL
• Using Package Functions in SQL and Restrictions
• Persistent State of Packages
• Using PL/SQL Tables of Records in Packages

Creating Triggers

• Working With Triggers
• Identifying the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Creating DML Triggers Using the CREATE TRIGGER Statement
• Identifying the Trigger Event Types, Body, and Firing (Timing)
• Statement Level Triggers Versus Row Level Triggers
• Creating Instead of and Disabled Triggers
• Managing, Testing, and Removing Triggers

Tuning and Performance

• Understand and influence the compiler
• Tune PL/SQL code
• Using PL/SQL function cache