Introduction to PLSQL

• istory of PL/SQL language
• he anonymous PLSQL block
• ariables & Data types
• SQL operators, Nested blocks
• SELECT … INTO statement

Triggers

• Describe Triggers
• Identify the Trigger Event Types and Body
• How to Manage, Test and Remove Triggers
• Business Application of Triggers
• Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
• Identify Trigger Event Types, Body & Firing
• Differences in Statement & Row Level Triggers
• Create Instead of and Disabled Triggers

Composite data types

• Parsing %ROWTYPE
• SQL Records & Table of Records

Oracle Utilities

• SQL*loader (sqlldr)
• Spool and spool append commands.
• Profiling PL/SQL code using DBMS_HPROF supplied package.

Using Oracle Supplied Packages

• BMS_OUTPUT & DBMS_UTILITY
• TL_FILE, UTL_MAIL
• BMS_RANDOM & EXECUTE IMMEDIATE

Package Features

• Overloading package subprograms
• Forward declaration of subprograms
• Creating bodiless package
• Persistent state of package variables & cursor

Creating Packages (PL/SQL API Development)

• What are packages and what is an API ?
• Package Body & Specification
• Private and Public Package level data
• Calling package functions, procedures
• Referencing package-level constants & variables

Creating Procedures (Stored sub-programs)

• Stored sub-programs (Procedure/Functions)
• d for Procedures – create syntax
• Use of Parameters ( IN , OUT & IN OUT)
• Executing procedures & trapping Exceptions

Creating Functions

• Syntax of CREATE FUNCTION
• Restrictions on function calls
• Dropping functions

Exceptions and Exception handling

• What are Exceptions?
• Need for handling & types of exceptions
• The raise_application_error built-in procedure & User defined exceptions
• Raising & Trapping Exceptions
• Exception propagation in nested calls
• Use of SQLCODE & SQLERRM
• Testing of these statements.
• Use of FOR Loop & WHILE Loop
• How to label a loop and how to nest a loop.

Iterative and Conditional control structures

• sing cursors in procedures
• OPEN. FETCH CLOSE construct.
• The %NOTFOUND operator
• FOR UPDATE cursors, Pl-SQL Case
• Use of IF-THEN-ELSE statement