Oracle 11g PL/SQL Training Topics.

Oracle 11g PL/SQL Training

1. Introduction to Oracle PL/SQL
About PL/SQL
The Environment
Block Structure
Block Types
Create an Anonymous Block
Execute an Anonymous Block



2. Declaring PL/SQL Variables
Handling Variables in PL/SQL – Declaration
Scalar Data Types
Declaring Scalar Variables



3. Writing Executable Statements
SQL Functions in PL/SQL
Using Sequences in PL/SQL Expressions
Data Type Conversion – Implicit
Data Type Conversion – Explicit
Nested Blocks
Variable Scope and Visibility
Operators in PL/SQL



4. Interacting with the Oracle Database Server
SQL Statements in PL/SQL
SELECT Statements in blocks
Retrieving Data - Example I
Retrieving Data - Example II
Inserting Data: Example
Updating Data: Example
Deleting Data: Example
SQL Cursor
SQL Cursor Attributes for Implicit Cursors



5. Writing Control Structures
Objectives
Controlling Flow of Execution
IF Statement
IF Statement – 1st Form
IF Statement – 2nd Form
IF Statement – 3rd Form
Iterative Control: LOOP Statements
Basic Loops
WHILE Loops
FOR Loops
FOR Loops: Example
CONTINUE Statement


6. Working with Composite Data Types
Composite Data Types
PL/SQL Records
%ROWTYPE Attribute
%ROWTYPE Attribute: Example
DMLs using %ROWTYPE – INSERT
DMLs using %ROWTYPE – UPDATE
INDEX BY Tables or Associative Arrays
Creating an INDEX BY Table


7. Using Explicit Cursors
Cursors
Controlling Explicit Cursors in block
Explicit cursor –
Cursor Attributes
Cursors and Records
Cursor Attributes
Cursor FOR Loops




8. Handling Exceptions
Example of an Exception Raise
Example of an Exception Handling
Handling Exceptions
PRAGMA EXCEPTION_INIT –
Functions for Trapping Exceptions
Functions for Trapping Exceptions
Trapping User-Defined Exceptions
RAISE_APPLICATION_ERROR Procedure



9. Creating Procedures
Subprograms
Subprogram Types
What Are Procedures?
Parameters and Parameter Modes
Comparing the Parameter Modes
Formal and Actual Parameters
IN Parameter Mode: Example
Executing the procedures with IN mode
Using the OUT Mode: Example
Executing the procedures with OUT mode
Using the DEFAULT Option for the Parameters
Calling Procedures
Removing Procedures: DROP command
Viewing Procedure in the Data Dictionary



10. Creating Functions
Stored Functions
Procedures vs Functions
Creating Functions: Overview
Invoking a Stored Function in SQL*Plus
Invoking a Stored Function in PL/SQL
Using a Function in a SQL Expression
Restrictions When Calling Functions
from SQL Expressions
Removing Functions: DROP command
Functions Using Data Dictionary Views



11. Creating Packages
PL/SQL Packages: Overview
Components of a Package
Package: Syntax
Packages: Example
Visibility of Package component
Calling the Package Subprograms
Creating Packages without Body: Example
Dropping Packages: DROP Statement
Viewing Packages Using the Data Dictionary



12. Creating Triggers
Triggers
Trigger Types
DML Trigger Timing and Event
Statement-Level Triggers vs. Row-Level Triggers
Example of a DML Statement Trigger
Example of DML statement trigger contd.
Creating a DML Row Trigger
Using OLD and NEW Qualifiers: Example
INSTEAD OF Triggers
Creating an INSTEAD OF Trigger: Example
Trigger management: Enable, Disable, Drop
Viewing Trigger Information