best PL SQL course training institue in chennai

​

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