Introduction to PL/SQL
Overview
Simple Anonymous Block
PL/SQL Identifiers
Types of Identifiers
Declarative Section
Variables
Data Types
%TYPE Attribute
Bind Variables
Executable Statements
Basic PL/SQL Block
Nested Blocks
Operators in PL/SQL
Interaction with the Oracle Server
SELECT Statements in PL/SQL
Retrieve Data in PL/SQL
SQL Cursor
SQL Cursor Attributes
Control Structures
Conditional processing using IF Statements and CASE Statements
Type of Loops - simple Loop, While Loop, For Loop
Continue Statement
Composite Data Types
PL/SQL Records and %ROWTYPE Attribute
DML using PL/SQL Records
INDEX BY Tables
INDEX BY Table of Records
Explicit Cursors
Overview
Declare, Open, Fetch and Close the Cursor
Cursor FOR loop
The %NOTFOUND and %ROWCOUNT Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
Exception
Exceptions - Overview
Predefined and Non-Predefined Oracle Server Errors
User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure
Procedures
Overview
Benefits of using PL/SQL Subprograms
Differences between Anonymous Blocks and Subprograms
Create, Call, and Remove Stored Procedures
Procedures Parameters and Parameters Modes
Functions
Overview
Create, Call, and Remove a Stored Function
Advantages of using Stored Functions
Invoke User-Defined Functions in SQL
Restrictions when calling Functions
How to debug Functions and Procedures?
Packages
Overview and advantages of Packages
Develop a Package
Package Specification and Body
Invoke the Package Constructs
Overloading Subprograms in PL/SQL
Forward Declarations
Examples of some of the Oracle-Supplied Packages
DBMS_OUTPUT, UTL_FILE Packages
Dynamic SQL
Execution Flow of SQL
Dynamically Executing a PL/SQL Block
Configure Native Dynamic SQL to Compile PL/SQL Code
DBMS_SQL Package
EXECUTE IMMEDIATE
Triggers
Overview
Trigger Event Types and Body
Identify the Trigger Event Types, Body, and Timing
Statement Level and Row Level Triggers
Instead of Triggers
DDL Triggers
Database-Event and System-Events Triggers
Manage Dependencies
Overview
USER_DEPENDENCIES View
Invalidation of Dependent Objects
Display the Direct and Indirect Dependencies
Understand Remote Dependencies
