best PL SQL training institute in chennai adyar

​

PL/SQL SYLLABUS

Introduction

Course Objectives
About PL/SQL
PL/SQL Environment
Benefits of PL/SQL
Benefits of Subprograms
Invoking Stored Procedures and Functions

Declaring Variables

Writing Executable Statements

Objectives
PL/SQL Block Structure
Executing Statements and PL/SQL Blocks
Block Types
Program Constructs
Use of Variables
Handling Variables in PL/SQL
Types of Variables
Using iSQL*Plus Variables Within PL/SQL Blocks
Types of Variables
Declaring PL/SQL Variables
Guidelines for Declaring PL/SQL Variables
Naming Rules
Variable Initialization and Keywords
Scalar Data Types
Base Scalar Data Types
Scalar Variable Declarations
The %TYPE Attribute
Declaring Variables with the %TYPE Attribute
Declaring Boolean Variables
Composite Data Types
LOB Data Type Variables
Bind Variables
Using Bind Variables
Referencing Non-PL/SQL Variables
DBMS_OUTPUT.PUT_LINE

Objectives
PL/SQL Block Syntax and Guidelines
Identifiers
PL/SQL Block Syntax and Guidelines
Commenting Code
SQL Functions in PL/SQL
SQL Functions ¡n PL/SQL: Examples
Data type Conversion
Nested Blocks and Variable Scope
Identifier Scope
Qualify an Identifier
Determining Variable Scope
Operators ¡n PL/SQL
Programming Guidelines
Indenting Code

Real Time Working Certified Experts | Exposure to MNC Real Time Environment | Technical Support after Placement| Customized materials from faculties | Guidance to Certification

| Placement Assistance | Hands-on Training| International Certification Oriented Courses | Intranet Facility and Internet in classrooms | Communication Skills Training

#84/8 Venkatarathinam Main Street, Venkatarathinam Nagar, Adyar, Chennai - 600 020.

044-42115526 / [email protected] / www.peridotsystems.in

Interacting with the Oracle Server

Writing Control Structures

Objectives
SQL Statements in PL/SQL
SELECT Statements in PL/SQL
Retrieving Data in PL/SQL
Naming Conventions
Manipulating Data Using PL/SQL
Inserting Data
Updating Data
Deleting Data
Merging Rows
Naming Conventions
SQL Cursor
SQL Cursor Attributes
Transaction Control Statements

Objectives
Controlling PL/SQL Flow of Execution
IF Statements
Simple IF Statements
Compound IF Statements
IF-THEN-ELSE Statement Execution Flow
IF-THEN-ELSE Statements
CASE Expressions
CASE Expressions: Example
Handling Nulls
Logic Tables
Boolean Conditions
Iterative Control: LOOP Statements
Basic Loops
WHILE Loops
FOR Loops
Guidelines While Using Loops
Nested Loops and Labels

Working with Composite Data Types

Objectives
Composite Data Types
PL/SQL Records
Creating a PL/SQL Record
PL/SQL Record Structure
The %ROWTYPE Attribute
Advantages of Using %ROWTYPE
The %ROWTYPE Attribute
INDEX BY Tables
Creating an INDEX by Table
INDEX BY Table Structure
Creating an INDEX BY Table
Using INDEX BY Table Methods
INDEX BY Table of Records
Example of PL/SQL Table of Records

Advanced Explicit Cursor Concepts

Objectives
Cursors with Parameters
The FOR UPDATE Clause
The WHERE CURRENT OF Clause
Cursors with Subqueries

Writing Explicit Cursors

Handling Exceptions

Objectives
About Cursors
Explicit Cursor Functions
Controlling Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching Data from the Cursor
Closing the Cursor
Explicit Cursor Attributes
The %ISOPEN Attribute
Controlling Multiple Fetches
The %NOTFOUND and %ROWCOUNT Attributes
Example
Cursors and Records
Cursor FOR Loops
Cursor FOR Loops Using Subqueries

Objectives
Handling Exceptions with PL/SQL
Handling Exceptions
Exception Types
Trapping Exceptions
Trapping Exceptions Guidelines
Trapping Predefined Oracle Server Errors
Predefined Exceptions
Trapping Nonpredefined Oracle Server Errors
Nonpredefined Error
Functions for Trapping Exceptions
Trapping User-Defined Exceptions
User-Defined Exception
Calling Environments
Propagating Exceptions
RAISE_APPLICATION_ERROR Procedure

RAISE_APPLICATION_ERROR

Creating Procedures

Creating Functions

Objectives
PL/SQL Program Constructs
Overview of Subprograms
Block Structure for Anonymous PL/SQL Blocks
Block Structure for PL/SQL Subprograms
PL/SQL Subprograms
Developing Subprograms by Using iSQL*PIus
What Is a Procedure?
Syntax for Creating Procedures
Developing Procedures
Formal Versus Actual Parameters
Procedural Parameter Modes
Creating Procedures with Parameters
IN Parameters: Example
OUT Parameters: Example
Viewing OUT Parameters
IN OUT Parameters
Viewing IN OUT Parameters
Methods for Passing Parameters
DEFAULT Option for Parameters
Examples of Passing Parameters
Declaring Subprograms
Invoking a Procedure from an Anonymous PL/SQL Block
Invoking a Procedure from Another Procedure
Handled Exceptions
Unhandled Exceptions
Removing Procedures
Benefits of Subprograms

Objectives
Overview of Stored Functions
Syntax for Creating Functions
Creating a Function
Creating a Stored Function by Using iSQL*Plus
Creating a Stored Function by Using ¡SQL*PIus: Example
Executing Functions
Executing Functions: Example
Advantages of User-Defined Functions in SQL Expressions
Invoking Functions in SQL Expressions: Example
Locations to Call User-Defined Functions
Restrictions on Calling Functions from SQL Expressions
Restrictions on Calling from SQL
Removing Functions
Procedure or Function?
Comparing Procedures and Functions

Managing Subprogram

Creating Database Trig

Objectives
Required Privileges
Granting Access to Data & Using Invoker’s-Rights
Managing Stored PL/SQL Objects
USER_OBJECTS & USER_ERRORS
List All Procedures and Functions
USER_SOURCE Data Dictionary View
List the Code of Procedures and Functions
Detecting Compilation Errors: Example
List Compilation Errors by Using USER_ERRORS
List Compilation Errors by Using SHOW ERRORS
DESCRIBE in iSQL*Plus & Debugging PL/SQL Program Units

Objectives
Types of Triggers
Guidelines for Designing Triggers
Database Trigger: Example
Creating DML Triggers
DML Trigger Components
Firing Sequence
Syntax for Creating DML Statement Triggers
Creating DML Statement Triggers
Testing SECURE_EMP
Using Conditional Predicates
Creating a DML Row Trigger
Creating DML Row Triggers
Using OLD and NEW Qualifiers
Using OLD and NEW Qualifiers: Example Using Audit_Emp_Table
Restricting a Row Trigger
INSTEAD OF Trigger
Creating an INSTEAD OF Trigger
Differentiating between Database Triggers and Stored Procedures
Differentiating between Database Triggers and Form Builder Triggers
Managing Triggers
DROP TRIGGER Syntax
Trigger Test Cases

Creating Pack

Objectives
Overview of Packages
Components of a Package
Referencing Package Objects
Developing a Package
Creating the Package Specification
Declaring Public Constructs
Creating a Package Specification: Example
Creating the Package Body
Public and Private Constructs
Creating a Package Body: Example
Invoking Package Constructs
Declaring a Bodiless Package
Referencing a Public Variable from a Stand-alone Procedure & Removing Packages
Guidelines for Developing Packages
Advantages of Packages

Address: #8/84 Venkatrathinam Main Street, Venkatrathinam Nagar, Adyar, Chennai, Tamil Nadu 600020