Oracle 11g PL/SQL training

(1)

DTecH IT Education, No.1, 1st Cross, 1st Main, Ashwini Layout, Near Sony World, Koramangala 6th Blk, Bangalore-47. Contact: 080-4150-1359, 080-3221-0825

Oracle 11g PL/SQL training

Course Highlights

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. Students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create anonymous PL/SQL blocks as well as stored procedures and functions. Students learn to develop, execute, and manage PL\SQL stored program units such as procedures, functions, packages, and database triggers.

Why DTecH?

* Trained more than 2000 students on Oracle & DW/BI in past 4 years. * Conducted more than 60,000 hours of trainings in past 4 years. * More than 80 Senior consultant trainers worked with us in 4 years.

Duration : 14 days/ 2-3 hours theory + practicals

Availability: Both Weekend & Weekday trainings available

Infrastructure: High End Servers & Setups available for Lab practice

Trainer Profile: Oracle Certified & Experienced real time Professional working with leading MNC

Audience: Freshers or Beginners with excellent learning capabilities

Pre-requisites:

Good programming logic, analytical and problem solving skills

Good to have knowledge of other programming languages like C++, Java etc.

Course Topics PL/SQL 1. Introduction to PL/SQL About PL/SQL About PL/SQL – Features PL/SQL Environment

(2)

PL/SQL Block Structure Block Types

Create an Anonymous Block Execute an Anonymous Block 2. Declaring PL/SQL Variables Handling Variables in PL/SQL

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

Operators in PL/SQL: Examples

4. Interacting with the Oracle Database Server SQL Statements in PL/SQL

SELECT Statements in PL/SQL Retrieving Data in PL/SQL: Example I Retrieving Data in PL/SQL – Example II Inserting Data: Example

Updating Data: Example Deleting Data: Example SQL Cursor

SQL Cursor Attributes for Implicit Cursors 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

PL/SQL CONTINUE Statement

(3)

6. Working with Composite Data Types Composite Data Types

PL/SQL Records

PL/SQL Records – Syntax PL/SQL Records – Example %ROWTYPE Attribute

%ROWTYPE Attribute: Example DMLs using %ROWTYPE – INSERT DMLs using %ROWTYPE – UPDATE INDEX BY Tables or Associative Arrays Creating an INDEX BY Table

Creating an INDEX BY Table – Example I Creating an INDEX BY Table – Example II Nested Tables

Nested Tables – Syntax Nested Tables – Example Varrays

Varrays – Syntax Varrays – Example 7. Using Explicit Cursors Cursors

Controlling Explicit Cursors in PL/SQL block – Syntax Explicit cursor – Illustration

Cursor Attributes Cursors and Records Cursor Attributes Cursor FOR Loops

Cursor FOR Loops Using Subqueries 8. Handling Exceptions

Example of an Exception Raise Example of an Exception Handling Handling Exceptions with PL/SQL Handling Exceptions

Trapping Exceptions – Syntax

Trapping Predefined Oracle Server Errors

Trapping Predefined Oracle Server Errors – Explicit Common Oracle server Exceptions

Trapping Non-Predefined Oracle Server Errors PRAGMA EXCEPTION_INIT – Example

Functions for Trapping Exceptions Functions for Trapping Exceptions Trapping User-Defined Exceptions

Propagating Exceptions in a Subblock – Case I Propagating Exceptions in a Subblock – Case II

(4)

Propagating Exceptions in a Subblock – Case III RAISE_APPLICATION_ERROR Procedure 9. Creating Procedures

PL/SQL Subprograms PL/SQL Subprograms 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: Example Invoking a Stored Function in PL/SQL: Example Using a Function in a SQL Expression: Example 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 PL/SQL Package Package: Syntax

PL/SQL Packages: Example

PL/SQL Packages: Example continued 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

(5)

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