PLSQL NOTES

PLSQL NOTES

INDEX

• VARIABLE DECLARATION . . . . . . . . 05

• CONTROL STATEMENTS . . . . . . . . . 18

• COMPOSITE VARIABLES . . . . . . . . 26

• EXPLICIT CURSORS . . . . . . . . . . 31

• EXCEPTIONS . . . . . . . . . . . . . 43

• PROCEDURES . . . . . . . . . . . . . 52

• FUNCTIONS . . . . . . . . . . . . . 55

• PACKAGES . . . . . . . . . . . . . . 58

• ORACLE PACKAGES . . . . . . . . . . 67

• DYNAMIC SQL . . . . . . . . . . . . 75

• TRIGGERS . . . . . . . . . . . . . . 77

PLSQL NOTES
Server Connection
 Important Instructions
• Each student has his own unix login id to server.
• Use ssh -l command to login to server.
e.g. ssh -l nagnath 172.24.8.60
• in above case nagnath is unix id.
• Default password is xxxxxx for all students.
• Change password using passwd command after first
login.

• After login in server, set database name in ORACLE_SID

variable.
e.g. export ORACLE_SID=DB11G
• in above case DB11G is database name.

• Conncet to database using your oracle sql login name

and password.
e.g. sqlplus nagnath/nagnath
• In above case username is nagnath and password is also
nagnath.
• For each student oracle login id and password will be
his name.

• Connect to oracle from where you are

writing your
plsql programs.

e.g.[nagnath@server1 ~]$ cd plsql/

[nagnath@server1 ~]$ sqlplus nagnath/nagnath

PLSQL ENGINE

PL/SQL PROCEDURAL
PLSQL BLOCK STATEMENT
BLOCK EXECUTION

SQL statement executor

CONTEXT SWITCH

The PL/SQL engine executes procedural statements and sends

all SQL statements present in the code to the SQL engine.

The SQL engine will parse and execute the query or DML
statement and return the expected output back to the PL/SQL
engine.
This switch between the two engines is called
context switching.

SQL

DATABASE

PLSQL
VARIABLE DECLARATION
DATA DICTIONARY VIEWS

• USER_OBJECTS
• USER_TABLE
• USER_VIEW
• USER_INDEX
• USER_SEQUENCE
• USER_SOURCE
• USER_CONSTRAINTS

Types of variables
CHAR [(maximum length)]
VARCHAR2 (maximum length)
LONG
NUMBER [(precision , scale)]
BINARY INTEGER
PLS_INTEGER
BOOLEAN

BINARY INTEGER:-
Base type for integer between -2,147,483,647
and 2,147,483,647

PLS_INTEGER :-
Base type for signed integers between -
2,147,483,647 and 2,147,483,647.PLS_INTEGER values require
less storage and are faster than NUMBER and BINARY_INTEGER
values

BOOLEAN :-
Base type that stores one of three possible
values used for logical calculations:TRUE,FALSE,NULL
 This is the first program in plsql to print hello
world.

create or replace procedure sp11

as
-- First Program of Plsql
-- This Program Prints
-- Hello World on the Screen

begin
dbms_output.put_line('Hello World');
end;

• To Compile Stored Procedures

• Type @file name/procedure name.sql on SQL Prompt

• To see the output on sql prompt type command

‘set serveroutput on’ when you are logged in.

• TO Execute Stored Procedures

• Type exec procedure_name

--OUTPUT
SQL> @sp11.sql
Procedure created.

No errors.

SQL> exec sp11

Hello Word

PL/SQL procedure successfully completed.

 PLSQL NOTES

This program shows how to check errors in procedures.

create or replace procedure sp11_1

as
--This Program shows how to Read Errors
begin

dbms_output.put_line('Hello World')
--Semi colon missing at the end

end;
/

• To See Errors Type 'show errors' on SQL prompt

• You can write 'show errors' at the end of procedure.

OUTPUT
SQL> @sp11_1.sql
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE SP11_1:
LINE/COL ERROR
-----------------------------------------------------------------
7/1 PLS-00103: Encountered the symbol

"END" when expecting

one of the following:

:= . ( % ;

The symbol ";" was substituted

for "END" to continue.

 Variable declaration and assigning value to variables.

create or replace procedure sp12

--This Program Shows How to declare variables, assign values and

constants.

as
v_myname CHAR(50);
v_myage NUMBER(2) NOT NULL := 21;

v_mycity VARCHAR2(13) := ‘Pune’;

v_salary CONSTANT NUMBER := 1400;

begin

v_myname := ‘Nagnath’;
dbms_output.put_line('My Name is '||v_myname);

dbms_output.put_line('My Age is '||v_myage);

end;

/
show errors

OUTPUT:
SQL> @sp12.sql
Procedure created.
No errors.
SQL> exec sp12
My Name is Nagnath
My Age is 21
PL/SQL procedure successfully completed
 Passing value to variables.

create or replace procedure sp13(l_name

varchar2,l_age number default 100)

--This Program shows how to

--Pass Values to procedures
--Accept values in variable
--without specifying its
--length

as

begin
dbms_output.put_line('Name Entered By User: '||l_name);

dbms_output.put_line('Age Entered By User: '||l_age);

end;
/
show errors

OUTPUT:
SQL> @sp13.sql
Procedure created.
No errors.
SQL> exec sp13('nagnath',21);
Name Entered By User: nagnath
Age Entered By User: 21
PL/SQL procedure successfully completed.
Selecting values from database tables.

create or replace procedure sp13_1

--This Programs Shows how to
--Execute SQL quries from plsql
as

l_employee_id Number := 100;

l_employee_name varchar2(30);
begin

select last_name
into l_employee_name
from Employees
where employee_id = l_employee_id;

--into clause copies selected column's value

--into given variables

dbms_output.put_line('Employees Name is: '||

l_employee_name);
end;
/
show errors

OUTPUT
SQL> @sp13_1.sql
Procedure created.
No errors.
SQL> exec sp13_1
Employees Name is: King
PL/SQL procedure successfully completed.
 Use of %type variable.

create or replace procedure sp15(l_employee_id number)

--This Program shows
--how to use variables with same data type from tables
--Objective
--Accept EmployeeID From User
--Print Name of That Employee

as
l_last_name employees.last_name%type;
begin
select last_name into
l_last_name from
employees
where employee_id = l_employee_id;

dbms_output.put_line('Name of Employee is: '||l_last_name);

end;
/
show errors

SQL> @sp15
Procedure created.
No errors.
SQL> set serveroutput on
SQL> exec sp15(200);
Name of Employee is: Whalen
PL/SQL procedure successfully completed.
 Use of %type variable

create or replace procedure sp14

--This Program shows
--how to declare Variables With %Type Attribute
--Means Same Data Type as in Table
as
l_last_name employees.last_name%type;
--In this case data type of
--l_last_name is same as
--data type of column last_name from employees

begin

select last_name
into l_last_name
from employees
where employee_id = 101;

dbms_output.put_line(l_last_name);

end;
/
show errors

OUTPUT:
SQL> @sp14.sql
Procedure created.
No errors.
SQL> exec sp14
Kochhar
PL/SQL procedure successfully completed.
Scope of variables in procedure.

create or replace procedure sp16

--This Program shows
--Scope Of Variable in Stored Procedure
as
l_no number := 600;
l_msg varchar2(20) := 'Global Variable';
Begin
<<Inner_Block1>>
declare
l_no Number := 1;
l_msg varchar2(20) := 'Local variable';
begin
l_no := l_no +1;
dbms_output.put_line('In Inner Block1');
dbms_output.put_line(l_no);
dbms_output.put_line(l_msg);
end;
<<Inner_Block2>>
Declare
l_no number :=100;
Begin
dbms_output.put_line('In Inner Block2');
dbms_output.put_line(l_no);
dbms_output.put_line(sp16.l_msg);
end;
dbms_output.put_line('In Main');
dbms_output.put_line(l_no);
dbms_output.put_line(l_msg);
end;
/
show errors
• Variables declared in main procedure (Outermost variable) are
global variables.

• Global variables can be accessable in all inner procedures.

• Inner procedures can define their own variables with same name
as global variable and access.

• But it is not good programing practice. 13

OUTPUT

SQL> @sp16.sql

Procedure created.

No errors.

SQL> exec sp16

In Inner

Block2 2

Local Variable

In Inner

Block2 100

Global

Variable In

Inner Main 600

Global Variable
PL/SQL procedure successfully completed
 DML Statements in Procedure

create or replace procedure sp17

--This Programs shows
--Executing DML statement in Plsql
as
begin
--Inserting Data From Plsql Procedure
insert into test(test_id,test_name)
values(1,'sql');

insert into test(test_id,test_name)

values(2,'plsql');

--Updating Data From Plsql Procedure

update test
set test_id = 20
where test_id = 2;

--Deleting Data From Plsql Procedure

delete from test
where test_id = 20;
commit;
end;
/
show errors

--OUTPUT
create table test(test_id number,test_name varchar2(10));
Table created.
SQL> @sp17.sql
Procedure created.
No errors.
SQL> exec sp17
PL/SQL procedure successfully completed.
SQL> select * from test;
TEST_ID TEST_NAME
---------- ----------
1 sql
CONTROL STATEMENTS
 Control Statement : If -Else Condition

create or replace procedure sp21(p_employee_id in number)

as
– This procedure will give a salary raise
-- to an employee. Rules for the raise are as follows
-- 1. 20% for Employees working with us for at least 12 years
-- and whose salary is less than Rs. 6000/-
-- 2. 15% for Employees whose salary less than Rs. 6000/-
-- 3. 10% for employees working with us for at least 12 years

l_hire_date employees.hire_date%type;
l_salary employees.salary%type;
l_years_of_service number := 0;
l_new_salary number := 0;
begin
select hire_date,
salary
into l_hire_date,
l_salary
from employees
where employee_id = p_employee_id;

l_years_of_service := months_between(sysdate, l_hire_date)/12;

if l_salary < 6000 and l_years_of_service > 12 then

l_new_salary := l_salary * 1.2;

dbms_output.put_line('Giving 20% raise');

elsif l_salary < 6000 then

l_new_salary := l_salary * 1.15;

dbms_output.put_line('Giving 15% raise');

elsif l_years_of_service > 12 then

l_new_salary := l_salary * 1.1;

dbms_output.put_line('Giving 10% raise');

else
 l_new_salary := l_salary;
dbms_output.put_line('No salary raise ');
end if;
update employees
set salary = l_new_salary
where employee_id = p_employee_id;
commit;
end;
/

show errors

• IF condition THEN

• statements;

• [ELSIF condition THEN

• statements;]

• [ELSE

• statements;]

• END IF;
OUTPUT
SQL> @sp21.sql
Procedure created.
No errors.
SQL> select salary from employees where employee_id=101;
SALARY
----------
17000
SQL> exec sp21(101);
Giving 10% raise
PL/SQL procedure successfully completed.
SQL> select salary from employees where employee_id=101;
SALARY
----------
18700

Focus Training Services 20

 PLSQL NOTES

Control Statement : Case

create or replace procedure sp22(p_grade in varchar2)

as
-- Case Statement
-----------------
--CASE selector
-- WHEN expression1 THEN result1
-- WHEN expression2 THEN result2
-- ...
-- WHEN expressionN THEN resultN
-- [ELSE resultN+1;]
--END;
--A CASE expression selects a result and returns it

l_appraisal varchar2(100);

begin
l_appraisal := CASE p_grade

WHEN 'A' THEN 'Excellent'

WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LINE ('Grade: '|| p_grade ||

' Appraisal ' || l_appraisal);
end;
/
show errors

OUTPUT
SQL> @sp22.sql
Procedure created.
No errors.
SQL> exec sp22('C');
Grade: C Appraisal Good
PL/SQL procedure successfully completed.

Focus Training Services 21

 PLSQL NOTES

Control Statements : Basic Loop

create or replace procedure sp23 ( p_loop_counter in number)

as
-- Basic Loop construct
------------------------
--LOOP
-- statement1;
-- ...
-- EXIT [WHEN condition ];
--END LOOP;
--Use the basic loop when the statements inside the
-- loop must execute at least once.

i number;
Begin
i := 1;
loop
dbms_output.put_line(to_char(i) );
exit when i >= p_loop_counter;
i := i +
1; end loop;
end;
/
show errors

• Basic loop perform repetative actions.

• Programer can use exit condition to terminate the loop.

OUTPUT
SQL> @sp23.sql
Procedure created.
No errors.
SQL> exec sp23(5);
1 2 3 4 5

PL/SQL procedure successfully completed.

Focus Training Services 22

 PLSQL NOTES

Control Statement : While Loop

create or replace procedure sp24 ( p_loop_counter in number)

as
-- While Loop construct
------------------------
--WHILE condition LOOP
-- statement1;
-- statement2;
-- ...
--END LOOP;
-- Use the WHILE loop if the condition has to be
-- evaluated at the start of each iteration.
i number;
begin
i := 1;
while i<= p_loop_counter
loop
dbms_output.put_line(to_char(i) );
i := i + 1;
end loop;
end;
/
show errors

• While loop performs repetative actions

• until controling condition is no longer True.
• The condition is checked at start of each transaction.

OUTPUT
SQL> @sp24.sql
Procedure created.
No errors.
SQL> exec sp24(5);
1 2 3 4 5

PL/SQL procedure successfully completed.

Focus Training Services 23

 PLSQL NOTES

Control Statement : For Loop

create or replace procedure sp25( p_loop_counter in number)
as
-- For Loop construct
------------------------
--FOR counter IN [REVERSE]
-- lower_bound..upper_bound LOOP
-- ...
--END LOOP;
-- Use a FOR loop if the number of iterations is known.
i number;
begin
-- Naming a loop is optional
<<my_for_loop>>
for i in
1..p_loop_counter loop
sp5(to_char(i) , 2);
end loop my_for_loop;
dbms_output.put_line('------------------------');
-- now the reverse for loop
for i in reverse 1..p_loop_counter
loop
dbms_output.put_line(to_char(i) );
end loop;
end;
/
show errors

OUTPUT
SQL> @sp25.sql
Procedure created.
No errors.
SQL> exec sp25(3);
1 2 3

------------------------
3
2
1
PL/SQL procedure successfully completed.

Focus Training Services 24

 PLSQL NOTES

Infinite Loop

create or replace procedure sp54

as

-- How to write an infinite loop

begin

while (999 = 999)

loop

dbms_output.put_line('Hi');

end loop;

while ( true)

loop

dbms_output.put_line('Hi');

end loop;

end;
/
show errors

• As programer did not mentioned exit condition,

• Proram will repeat printing infinitelly.

• This is bad programming.

Focus Training Services 25

 PLSQL NOTES

COMPOSITE VARIABLES

Focus Training Services 26

 PLSQL NOTES

Composite Variable:Records

create or replace procedure sp31

--This program shows how to
--create composite data types
--First Composite data type is record
as
TYPE emp_sal_record is RECORD
(last_name varchar2(20),
salary number(10));
emp_sal emp_sal_record;

--emp_sal_record can store

--last_name and salary of employee
--in single variable

begin
select last_name,salary
into emp_sal
from employees
where employee_id = 100;
dbms_output.put_line('Employee Name: '||emp_sal.last_name);
dbms_output.put_line('Employees Salary: '||emp_sal.salary);
end;
/
show errors

• Composite Data type Records stores more than one data type under
single record.

OUTPUT:
SQL> @sp31.sql
Procedure created.
No errors.
SQL> set serveroutput on
SQL> exec sp31
Employee Name: King
Employees Salary: 24000
PL/SQL procedure successfully completed.

Focus Training Services 27

 PLSQL NOTES

Composite Data type:%rowtype

create or replace procedure sp32

--This program shows how to
--create composite data types
--Second composite data type is %rowtype
as
emp_record employees%rowtype;
--emp_record stores
--all values of all columns
--from employees table
begin
select *
into emp_record
from employees
where employee_id = 100;

dbms_output.put_line('Name: '||emp_record.last_name);

dbms_output.put_line('Department Id: '||

emp_record.department_id);

end;
/
show errors

• %rowtype variable is used to store all column data types in

single variable.

OUTPUT:
SQL> @sp32.sql
Procedure created.
No errors.
SQL> set serveroutput on
SQL> exec sp32
Name: King
Department Id: 90
PL/SQL procedure successfully completed.

Focus Training Services 28

 PLSQL NOTES

Composite data type: Index by table

create or replace procedure sp33

--This program shows how to
--create composite data types
--Third composite data type is Index by table
--This variable provide array-like access to rows
as
TYPE emp_type IS TABLE OF
employees%rowtype
index by binary_integer;

emp_array emp_type;
begin
for i in 101..104
loop
select *
into emp_array(i)
from employees
where employee_id = i;
end loop;
for i in emp_array.FIRST..emp_array.LAST
loop
dbms_output.put_line(emp_array(i).first_name||
' '||emp_array(i).last_name);
end loop;
end;
/
show errors

OUTPUT
SQL> @sp33.sql
Procedure created.
No errors.
SQL> exec sp33
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
PL/SQL procedure successfully completed.

Focus Training Services 29

 PLSQL NOTES

Composite Data Type : Variable Array

create or replace procedure sp34

--This program shows how to
--Composite data type
--Fourth composite variable is Varray
as
type arr is varray(4) of regions.region_name%type;
--Declaration of variable array with array elements = 4
arr_1 arr; --Initialization of
array create empty array
l_region_name regions.region_name%type;
begin
arr_1 := arr();
for i in 1..4 loop
arr_1.extend(1);
--Extend method to define extra cells
select region_name
into l_region_name
from regions
where region_id = i;

arr_1(i) := l_region_name;
end loop;
dbms_output.put_line(arr_1.count());
dbms_output.put_line(arr_1(1));
end;
/
show errors

• This data type is useful when user knows exact length for array
declaration.

OUTPUT
SQL> @sp34.sql
Procedure created.
No errors.
SQL> exec sp34
4
Europe
PL/SQL procedure successfully completed.

Focus Training Services 30

IMPLICIT CURSORS
 Implicit Cursors in Plsql

create or replace procedure sp18

--This Program shows how to use
--Sql Cursor Attributes(Set by default by sql)
--SQL%ROWCOUNT
--SQL%FOUND
--SQL%NOTFOUND
--SQL%ISOPEN
as
begin
insert into test(test_id,test_name) values(3,'Red Hat');
if sql%found then
dbms_output.put_line('Row is inserted');
end if;
delete
from test;
dbms_output.put_line(sql%rowcount ||'
Rows are selected');
delete
from test;
if sql%notfound then
dbms_output.put_line('No row is deleted');
end if;
end;
/
show errors

• Implicit cursors are implicitly define by oracle.

• They return information about result of query.

• SQL%ROWCOUNT returns number of rows affected by query.

• SQL%FOUND return true if atleast one row is affected by query.

• SQL%NOTFOUND return true if zero rows are affected by query.

16
SQL> @sp18.sql Procedure created. No errors.

SQL> select * from test; TEST_ID TEST_NAME

---------- ----------

1 sql SQL> exec sp18 Row is inserted

2 Rows are selected No row is deleted

PL/SQL procedure successfully completed.

 PLSQL NOTES

EXPLICIT CURSORS

Focus Training Services 31

 PLSQL NOTES

Explicit Cursors

create or replace procedure sp61

--This Program shows
--How to write explicit
cursor --How to open cursor
--How to fetch data from cursor
--How to close cursor
as
cursor c1 is select last_name,salary
from employees
where department_id = 20;
--Declaration of cursor
l_emp_name employees.last_name%type;
l_sal employees.salary%type;
begin
open c1;
--Opening of a Cursor
loop
fetch c1 into l_emp_name,l_sal;
exit when c1%notfound;
--Fetching Data from Cursor dbms_output.put_line(l_emp_name||'
'||
to_char(l_sal));
end loop;
close c1;
--Closing of Cursor
end;
/
show errors

• For every SQL statement execution, certain area in memory is

allocated.
• Programer can give name to that area and as known as cursor.
• Using cursor, fetched rows can be process one by one.

Focus Training Services 32

 PLSQL NOTES

OUTPUT
SQL> !vi sp61.sql
SQL> @sp61.sql
Procedure created.
No errors.
SQL> exec sp61
Hartstein
13000 Fay 6000
PL/SQL procedure successfully completed.

Focus Training Services 33

 PLSQL NOTES

Explicit cursor : Fetching Data into records

create or replace procedure sp62

--This Program shows
--How to fetch data from cursor into records
as
cursor c1 is select *
from employees
where department_id=50;
--Cursor Declaration
rec_c1 c1%rowtype;
--Record Declaration
begin
open c1;
loop
fetch c1 into rec_c1;
exit when c1%notfound;
--Fetching Data from cursor into record
dbms_output.put_line('Name: '||
rec_c1.last_name);
dbms_output.put_line('Salary: '||
rec_c1.salary);
end loop;
close c1;
end;
/
show errors

OUTPUT
SQL> @sp62.sql
Procedure created.
No errors.
SQL> exec sp62
Name: Hartstein
Salary: 13000
Name: Fay
Salary: 6000
PL/SQL procedure successfully completed.

Focus Training Services 34

 PLSQL NOTES

Explicit Cursor : Use of For Loop

create or replace procedure sp63

--This Program shows
--Cursor For
Loop as
cursor c1 is select *
from employees
where department_id = 20;
begin
for rec_c1 in c1 loop
--Implicit Open and Fetch occurs
dbms_output.put_line('Name: '||rec_c1.last_name);

dbms_output.put_line('salary: '||
to_char(rec_c1.last_name));

end loop;
end;
/
show errors

OUTPUT:
SQL> @sp63.sql
Procedure created.
No errors.
SQL> exec sp63
Name: Hartstein
salary:
Hartstein Name:
Fay salary: Fay
PL/SQL procedure successfully completed.

Focus Training Services 35

 PLSQL NOTES

Explicit Cursor : Subqueries

create or replace procedure sp64

--This Program shows
--Cursor for loop using subqueries
as
l_last_name
varchar2(30); begin
for l_last_name in (select last_name from employees where
department_id=20) loop

dbms_output.put_line('Name: '|| l_last_name);

end loop;
end;
/
show errors

OUTPUT:
SQL> @sp64.sql
Procedure created.
No errors.
SQL> exec sp64
Name: Hartstein
Salary: 13000
Name: Fay
Salary: 6000
PL/SQL procedure successfully completed.

Focus Training Services 36

 PLSQL NOTES

Explicit Cursor : Passing parameters to cursors

create or replace procedure sp65(deptno number,job varchar2)

--This Program shows
--How to pass parameters to cursor
as
cursor c1(l_deptno number,l_job varchar2) is
select employee_id,last_name
from employees
where department_id = l_deptno
and job_id = l_job;

rec_c1 c1%rowtype;

--Declaration of cursor
begin
for rec_c1 in c1(90,'AD_VP') loop
--Passing Paramenters to cursor
dbms_output.put_line('Depatment 80 '||' Job
id is SA_MAN');
dbms_output.put_line('Employee ID:
'|| to_char(rec_c1.employee_id));
dbms_output.put_line('Employee Name: '||
rec_c1.last_name);
end loop;
open c1(deptno,job);
--Passing Paramenters to cursor
loop
fetch c1 into rec_c1;
exit when c1%notfound;
dbms_output.put_line(deptno||' Job id is '||
job);
dbms_output.put_line('Employee ID:
'|| to_char(rec_c1.employee_id));
dbms_output.put_line('Employee Name: '||
rec_c1.last_name);
end loop;
close c1;
end;
/
show errors

Focus Training Services 37

 PLSQL NOTES

SQL> @sp65.sql
Procedure created.

No errors.

SQL> exec sp65(60,'IT_PROG');

Depatment 80 Job id is SA_MAN

Employee ID: 101

Employee Name: Kochhar

Depatment 80 Job id is SA_MAN

Employee ID: 102

Employee Name: De Haan

60 Job id is IT_PROG

Employee ID: 103

Employee Name: Hunold

60 Job id is IT_PROG

Employee ID: 104

Employee Name: Ernst

PL/SQL procedure successfully completed.

Focus Training Services 38

 PLSQL NOTES

Explicit Cursor : Update Clause

create or replace procedure sp66

--This Program shows
--The For Update Clause in cursor
as
cursor c1 is
select *
from employees
where department_id = 20 for
update of salary nowait;
rec_c1 c1%rowtype; l_new_sal
number;
begin
dbms_output.put_line(rpad('Employee',10)||
rpad('Old Salary',10)||
rpad('New Salary',10));

open c1;
loop
fetch c1 into rec_c1;
exit when c1%notfound;
if rec_c1.salary < 7000 then l_new_sal
:= rec_c1.salary * 1.25;
update employees set salary = l_new_sal
where employee_id = rec_c1.employee_id;
else
l_new_sal := rec_c1.salary * 1.15;
update employees set salary = l_new_sal
where employee_id = rec_c1.employee_id;
end if;
dbms_output.put_line
(rpad(rec_c1.last_name,10)||
rpad(rec_c1.salary,10)||
rpad(l_new_sal,10));
end loop;
close c1;
end;
/
show errors

Focus Training Services 39

 PLSQL NOTES

• Programer can lock rows

• Before performing any update or delete using cursor.

• While cursor is open no one can access selected rows.

SQL> @sp66.sql

Procedure created.

No errors.

SQL> exec sp66

Employee Old SalaryNew Salary

Hartstein 19771.38 22737.087

Fay 9918.75 11406.5625

PL/SQL procedure successfully completed.

Focus Training Services 40

 PLSQL NOTES

Explicit Cursor : Where current of clause

create or replace procedure sp67

--This Program shows
--The use of where current of clause
as
cursor c1 is
select employee_id,salary
from employees
where department_id = 20 for
update of salary nowait;
l_new_sal number; rec_c1
c1%rowtype;
begin
dbms_output.put_line(rpad('Employee',10)||
rpad('Old Salary',10)||
rpad('New Salary',10));
open c1;
loop
fetch c1 into rec_c1;
exit when c1%notfound;

if rec_c1.salary < 7000 then

l_new_sal := rec_c1.salary * 1.25;
update employees set salary =
l_new_sal
where current of c1;
else
l_new_sal := rec_c1.salary * 1.25;
update employees set salary =
l_new_sal
where current of c1;
end if;
end loop;
close c1;
dbms_output.put_line(rpad(rec_c1.employee_id,10)||
rpad(rec_c1.salary,10)||
rpad(l_new_sal,10));
end;
/
show errors

Focus Training Services 41

 PLSQL NOTES

• Programer can update or delete

• only current row in cursor by defining

• 'where current of clause'

SQL> @sp67.sql

Procedure created.

No errors.

SQL> exec sp67

Employee Old SalaryNew Salary

202 11406.56 14258.2

PL/SQL procedure successfully completed.

Focus Training Services 42

 PLSQL NOTES

EXCEPTIONS

Focus Training Services 43

 PLSQL NOTES

Exceptions: Predefined Exceptions

create or replace procedure sp81

--This Program shows

--How to handle Predefine exceptions

as

l_last_name employees.last_name%type;

begin

select last_name

into l_last_name

from employees

where employee_id = 99999;

dbms_output.put_line(l_last_name);

exception

when no_data_found then

--No_data_found is one of predefined exception

dbms_output.put_line('Employee Not exist');

end;
/
show errors

Focus Training Services 44

 PLSQL NOTES

• Exception is a kind of error that turminates user's program

execution example 'divide by 0'.

• Oracle has defined approximately 20 errors occur most often.

• Known as 'Predefine Exceptions'.

OUTPUT

SQL> @sp81.sql

Procedure created.

No errors.

SQL> exec sp82

Employee Not exist

PL/SQL procedure successfully completed.

Focus Training Services 45

 PLSQL NOTES

Exceptions : Non Predefined Exceptions

create or replace procedure sp82

--This program shows
--How to handle non predefine exceptions
as
duplicate_key exception;
PRAGMA EXCEPTION_INIT
(duplicate_key , -00001);
begin
insert into departments values(20,'New
Department',200,1800);

commit;

exception
when duplicate_key then dbms_output.put_line('Cannot
insert duplicate
department, department already exist');
end;
/
show errors

• Non Predefine exceptions are defined by oracle server,

but has no name.

• Use Pragma exception to give name to respective exception.

SQL> @sp82.sql
Procedure created.
No errors.
SQL> set serveroutput on
SQL> exec sp82
Cannot insert duplicate department, department already exist
PL/SQL procedure successfully completed.

Focus Training Services 46

 PLSQL NOTES

Exceptions : Others

create or replace procedure sp83

as
begin

delete from departments

where department_id = 20;

exception when others then

dbms_output.put_line('In Exception');
dbms_output.put_line(SQLCODE||SQLERRM);
end;
/
show errors

• Exception Others is used when programer does not know

the oracle number associated with error.

• Programer can find error associated number and error message

using SQLCODE , SQLERRM

OUTPUT
SQL> @sp83.sql

Procedure created.

No errors.

SQL> exec sp83

In Exception

-2292ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated -

child record

found

PL/SQL procedure successfully completed.

Focus Training Services 47

 PLSQL NOTES

Exceptions : User Defined Exceptions

create or replace procedure sp84

as

l_last_name employees.last_name%type;
l_salary employees.salary%type;
l_new_sal float;
invalid_raise exception;

begin
select last_name,salary
into l_last_name,l_salary
from employees
where employee_id = 100;

l_new_sal := l_salary * 1.20;

if l_new_sal > 2000 then

raise invalid_raise;
end if;

exception when invalid_raise then

dbms_output.put_line('Not Applicable Salary raise');

end;
/
show errors

• Programer can introduce errors on certain conditions.

• Those errors are known as user define exception.

--OUTPUTSQL> @sp84.sql
Procedure created.
No errors.
SQL> exec sp84
Not Applicable Salary raise
PL/SQL procedure successfully completed.

Focus Training Services 48

 PLSQL NOTES

Exceptions : Raise Application Errors

create or replace procedure sp85

as
l_last_name employees.last_name%type;
l_salary employees.salary%type; l_new_sal
float;
new_exception exception;
PRAGMA EXCEPTION_INIT(new_exception,-20999);
begin
select last_name,salary
into l_last_name,l_salary
from employees
where employee_id = 100;
l_new_sal := l_salary * 1.20;

if l_new_sal > 2000 then

raise_application_error(-20999,'This is not valid

salary increase');
end if;
exception when new_exception then
dbms_output.put_line('in exception');
dbms_output.put_line(SQLCODE||SQLERRM);
end;
/
show errors
• Programer can issue user define error messages
using 'raise_application_error' procedure.
• Progrmer can assign error code and error message
for new exceptions.
• Error code should be greater than 20,000.
--OUTPUT
SQL> @sp85.sql
Procedure created.
No errors.
SQL> exec sp85
in exception
-20999ORA-20999: This is not valid salary increase
PL/SQL procedure successfully completed.

Focus Training Services 49

 PLSQL NOTES

Exception : Flow through Procedures

create or replace procedure sp86(l_employee_id number)

--This program shows
--How exception handling passes from one procedure to other
as

l_salary number; l_last_name

varchar2(30);

begin
dbms_output.put_line('In outer Block');
select salary
into l_salary
from employees
where employee_id = l_employee_id;

begin

dbms_output.put_line('In Inner Block');

select last_name
into l_last_name
from employees;
end;

exception
when no_data_found then
dbms_output.put_line('No data found');

when too_many_rows then

dbms_output.put_line('too many rows');

end;
/
show errors

Focus Training Services 50

 PLSQL NOTES

OUTPUT

SQL> @sp86.sql

Procedure created.

No errors.

SQL> exec sp86(100);

In outer Block

In Inner Block

too many rows

PL/SQL procedure successfully completed.

SQL> exec sp86(1111);

In outer Block

No data found

PL/SQL procedure successfully completed.

Focus Training Services 51

 PLSQL NOTES

PROCEDURES

Focus Training Services 52

 PLSQL NOTES

Procedures : IN , OUT parameters

create or replace procedure sp91(l_emp_id in number,

l_salary out number)
--This Programs shows
--How to use in,out parameter
as
begin
select salary
into l_salary
from employees
where employee_id = l_emp_id;
end;
/
show errors
create or replace procedure sp92
as
salary number;
begin
sp91(100,salary);

--salary used as out parameter in sp91

dbms_output.put_line(salary);
end;
/
show errors

• In parameter used to pass value to procedure.

• Out parameter is used to pass value from parameter.

OUTPUT
SQL> @sp91.sql
Procedure created.
No errors.
SQL> @sp92.sql
Procedure created.
No errors.
SQL> exec sp92
24000
PL/SQL procedure successfully completed.

Focus Training Services 53

 PLSQL NOTES

Procedures : IN OUT Parameters

create or replace procedure sp93

--This program shows
--How to use inout parameter
as
p_phone_no varchar2(20);
begin
p_phone_no := '1234567890';
sp94(p_phone_no);
dbms_output.put_line(p_phone_no);
end;
/
show errors

create or replace procedure sp94

(p_phone_no IN OUT varchar2)
is
begin
p_phone_no := '(' || substr(p_phone_no,1,3) ||
')'||' '|| substr(p_phone_no,4,3) ||
'-' || substr(p_phone_no,7);
end;
/
show errors

• IN OUT parameter used to pass value to procedure and return some

value in the same variable.
• i.e. Programer need only 1 variable.
OUTPUT
SQL> @sp94.sql
Procedure created.
No errors.
SQL> @sp93.sql
Procedure created.
No errors.
SQL> exec sp93
(123) 456-7890
PL/SQL procedure successfully completed.

Focus Training Services 54

 PLSQL NOTES

FUNCTIONS

Focus Training Services 55

 PLSQL NOTES

Functions

create or replace function get_dept_name(dept_no

departments.department_id%type)
return varchar2

--This program shows

--How to write user define functions

is
l_dept_name departments.department_name%type;

begin

select department_name
into l_dept_name
from departments
where department_id = dept_no;

return l_dept_name;
end;
/
show errors

• Functions are used when one task is executed repeatedly.

• Functions are similar to procedures.

• Functions always returns some value to callie.

• After functions are created,

they can be called from sql query also.

Focus Training Services 56

 PLSQL NOTES

OUTPUT
SQL> @sp10_1.sql

Function created.

No errors.

SQL> select last_name,department_name

2 from employees e,departments d

3 where e.department_id = d.department_id

4 and employee_id = 100;

LAST_NAME DEPARTMENT_NAME

------------------------- ------------------------------

King Executive

SQL> select last_name,get_dept_name(department_id)

2 from employees

3 where employee_id = 100;

LAST_NAME GET_DEPT_NAME(DEPARTMENT_ID)

------------------------ ------------------------------

King Executive

Focus Training Services 57

 PLSQL NOTES

PACKAGES

Focus Training Services 58

 PLSQL NOTES

Packages

create or replace package calculator as

--This Programs shows package declaration/specification
--How to create package with public procedures

procedure add(no1 number, no2 number);

procedure subtract(no1 number, no2 number);

end calculator;
/
show errors

• Package use to group related procedures together.

• In Package declaration declare names of procedures and global

variables.

• In Package body write code for those procedures.

create or replace package body calculator is

--This Program shows
--How to declare body of package
procedure add(no1 number,no2 number)
is
begin
dbms_output.put_line('Addition is: '||to_char(no1 +
no2));
end add;

procedure subtract(no1 number,no2 number)

is
begin
dbms_output.put_line('Subtraction is: '||to_char(no1
- no2));
end subtract;
end calculator;
/
show errors

Focus Training Services 59

 PLSQL NOTES

SQL> @cal_pac.sql

Package created.

No errors.

SQL> @cal.sql

Package body

created. No errors.

SQL> exec calculator.add(20,30);

Addition is: 50

PL/SQL procedure successfully completed.

SQL> exec calculator.subtract(30,40);

Subtraction is: -10

PL/SQL procedure successfully completed.

Focus Training Services 60

 PLSQL NOTES

Package

create or replace package calculator as

--This Programs shows

--How to create package with public procedures

--How to define global variables with default values

--How to define public functions

count_add number := 0;

count_subtract number := 0;

procedure add(no1 number, no2 number);

procedure subtract(no1 number, no2 number);

function get_add_count return number;

function get_subtract_count return number;

end calculator;
/
show errors

Focus Training Services 61

 PLSQL NOTES

create or replace package body calculator is

--This Program shows
--How to define private procedures in package body

procedure print(text varchar2);

function get_add_count return number

as
begin
return count_add;
end get_add_count;

function get_subtract_count return number

as
begin
return count_subtract;
end get_subtract_count;

procedure add(no1 number,no2 number)

is
begin
count_add := count_add + 1; print('Addition
is: '||to_char(no1 + no2));
end add;

procedure subtract(no1 number,no2 number)

is
begin
count_subtract := count_subtract + 1;
print('Subtraction is: '||to_char(no1 - no2));
end subtract;

procedure print(text varchar2)

is
begin
dbms_output.put_line(text);
end print;
end calculator;
/
show errors

Focus Training Services 62

 PLSQL NOTES

SQL> @cal1.sql

Package body

created. No errors.

SQL> !vim cal1.sql

SQL> @cal_pac1.sql

Package created.

No errors.

SQL> @cal1.sql

Package body

created. No errors.

SQL> exec calculator.add(5,2);

Addition is: 7

PL/SQL procedure successfully completed.

SQL> exec calculator.subtract(10,5);

Subtraction is: 5

PL/SQL procedure successfully completed.

Focus Training Services 63

 PLSQL NOTES

Package : Procedure Overloading

create or replace package calculator1 as

--This Programs shows

--How to create package with public procedures

--And Procedure overloading

procedure add(no1 number, no2 number);

procedure add(no1 varchar2, no2 varchar2);

procedure add(no1 number, no2 number, no3 number);

end calculator1;

show errors

Focus Training Services 64

 PLSQL NOTES

create or replace package body calculator1 is

procedure add(no1 number,no2 number)

is
begin
dbms_output.put_line('Addition is: '||to_char(no1 +
no2));
end add;
procedure add(no1 varchar2,no2 varchar2)
is
begin
dbms_output.put_line('Concatination is: '||no1 ||
no2);
end add;

procedure add(no1 number,no2 number,no3 number)

is
begin
dbms_output.put_line('Subtraction is: '||to_char(no1
+ no2 + no3));
end add;

end calculator1;
/
show errors

• Procedure Overloading means defining procedure with

same name but with different parameters,data types.

• In above package procedure add is overloaded.

Focus Training Services 65

 PLSQL NOTES

SQL> exec calculator1.add(10,20);

Addition is: 30

PL/SQL procedure successfully completed.

SQL> exec calculator1.add('scott','tiger');

Concatination is: scott tiger

PL/SQL procedure successfully completed.

SQL> exec calculator1.add(10,20,30);

Addition is: 60

PL/SQL procedure successfully completed.

Focus Training Services 66

 PLSQL NOTES

ORACLE PACKAGES

Focus Training Services 67

 PLSQL NOTES

Default Packages : UTL_FILE

create or replace procedure sp11_1

--This Program shows
--How to send mails using UTL_MAIL package
as

ora_no number; ora_msg

varchar2(100);
begin
UTL_MAIL.SEND

( sender => 'gaurav@server1.example.com',

recipients => 'gaurav@server1.example.com',

cc => 'mithilesh@server1.example.com',

bcc => 'krunal@server1.example.com',

subject => 'test mail',

message => 'hi how r u??'

);

dbms_output.put_line('Message send successfully');

exception when others then

ora_no := sqlcode;
ora_msg := sqlerrm;

dbms_output.put_line('Message not send');

dbms_output.put_line(ora_no ||' '||ora_msg);

end;
/
show errors

Focus Training Services 68

 PLSQL NOTES

OUTPUT

SQL> @sp11_1.sql

Procedure created.

No errors.

SQL> exec sp11_1

Message send successfully

PL/SQL procedure successfully completed.

Focus Training Services 69

 PLSQL NOTES

Default Packages : UTL_FILE

create or replace procedure sp11_2

--This programs show
--How to read data from text file
--using UTL_FILE Package
as
v_dir varchar2(200);
--Specify Directory name and path
v_file_name varchar2(100);
--Specify File Name
v_line varchar2(500)
--Accept file line by line in this variable
v_file UTL_FILE.FILE_TYPE;
--File Handler
begin
v_dir := '/home/gaurav/plsql/';

v_file_name :='utl_file.txt';

v_file :=
UTL_FILE.FOPEN(v_dir,v_file_name,'r');
--File Open in read only mode
loop
begin
UTL_FILE.GET_LINE(v_file,v_line);
exception
when no_data_found then
exit;
end;

dbms_output.put_line(v_line);

end loop;
end;
/
show errors

Focus Training Services 70

 PLSQL NOTES

OUTPUT

[gaurav@server1 plsql]$ cat utl_file.txt

hello

how r u??

gaurav

OUTPUT

SQL> !vim sp11_2.sql

SQL> @sp11_2.sql

Procedure created.

No errors.

SQL> exec

sp11_2 hello

how r u??

gaurav

PL/SQL procedure successfully completed.

Focus Training Services 71

 PLSQL NOTES

Default Packages : DBMS_SCHEDULER

create or replace procedure sp11_6

--This procedure shows
--How to use default package
--DBMS_SCHEDULER to schedule some task
as
orr_code number;
orr_msg varchar2(500);
begin
--create_job is inbuild procedure in DBMS_SCHEDULER

DBMS_SCHEDULER.CREATE_JOB (
job_name => 'update_sales',
job_type => 'STORED_PROCEDURE',
job_action => 'sp11_4',
start_date => '20-APR-10 03.10.00.000000000 PM
ASIA/CALCUTTA',
repeat_interval => 'FREQ=SECONDLY;INTERVAL=10',
end_date => '20-APR-10 03.11.00.000000000 PM
ASIA/CALCUTTA',
comments => 'My new job');

exception when others then

orr_code := sqlcode;
orr_msg := sqlerrm;
dbms_output.put_line(orr_code||' '||orr_msg);
end;
/
show errors

Focus Training Services 72

 PLSQL NOTES

OUTPUT
15:04:48 SQL> truncate table test_sch;
Table truncated.
15:04:57 SQL> @sp11_6.sql
Procedure created.
No errors.

15:05:01 SQL> exec sp11_6

PL/SQL procedure successfully completed.

15:05:06 SQL> exec

dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES',
2);

-- requires manage scheduler privilege

PL/SQL procedure successfully completed.

15:05:22 SQL> exec dbms_scheduler.enable('update_sales');

-- enable the job

PL/SQL procedure successfully completed.

15:05:43 SQL> select * from test_sch;

no rows selected

15:07:31 SQL> select count(*) from test_sch;

COUNT(*)
----------
0

15:09:41 SQL> /
COUNT(*)
----------
100

Focus Training Services 73

 PLSQL NOTES

15:10:04 SQL> /

COUNT(*)
----------
200

15:10:14 SQL> /

COUNT(*)
----------
300

15:10:27 SQL> /

COUNT(*)
----------
400

15:10:37 SQL> /

COUNT(*)
----------
500

15:10:46 SQL> /

COUNT(*)
----------
600

15:10:53 SQL> /

COUNT(*)
----------
600

Focus Training Services 74

 PLSQL NOTES

DYNAMIC SQL

Focus Training Services 75

 PLSQL NOTES

Dynamic Sql

create or replace procedure sp12_1(l_table_name varchar2)

--This program shows
--How to build dynamic sql
queries. as
sql_query varchar2(50);
l_count number;
begin

sql_query := 'select count(*) from '||l_table_name;

execute immediate sql_query into l_count;

--Write sql command in a varchar2 variable

--And then use command 'execute immediate'
--Accept returing value in appropriate variable

dbms_output.put_line(l_count);

end;
/
show errors

OUTPUT

SQL> @sp12_1.sql

Procedure created.

No errors.

SQL> exec sp12_1('REGIONS');

PL/SQL procedure successfully completed.

Focus Training Services 76

 PLSQL NOTES

TRIGGERS

Focus Training Services 77

 PLSQL NOTES

Trrigers

create or replace trigger chk_emp_sal

--This programs shows
--How to declare
triggers --for each row

before insert or update

of salary
on employees
for each row

declare
v_error VARCHAR2(2000);
begin

if :new.salary > 25000 then

v_error:=:old.first_name||' cannot have that much!';

raise_application_error(-20999,v_error);
end if;
end;
/
show errors

• Oracle lets you define procedures called triggers

that run implicitly when an INSERT, UPDATE, or
DELETE statement is issued against the associated
table or, in some cases, against a view, or when
database system actions occur.

Focus Training Services 78

 PLSQL NOTES

OUTPUT

SQL> @14_1.sql

Trigger created.

No errors.

SQL> insert into

employees(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY)

2 values(215,'Bhide','abc@gmail.com',sysdate,'IT_PROG',26000);

insert into
employees(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY)

ERROR at line 1:

ORA-20999: cannot have that much!

ORA-06512: at "HR.CHK_EMP_SAL", line 7

ORA-04088: error during execution of trigger 'HR.CHK_EMP_SAL'

Focus Training Services 79

 PLSQL NOTES

Triggers : Updating,Inserting,Deleting

create or replace trigger trig_example

before insert or delete or update on trig_eg

for each row

declare
ChangeType varchar2(10);
begin

/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */

if inserting then
ChangeType := 'I';

elsif updating then

ChangeType := 'U';

else
ChangeType := 'D';
end if;

insert into changes_record values(ChangeType,USER,SYSDATE);

end trig_example;
/
show errors

Focus Training Services 80

 PLSQL NOTES

OUTPUT
SQL> @sp14_2.sql
Trigger created.
No errors.
SQL> select * from CHANGES_RECORD;
no rows selected
SQL> insert into trig_eg values(1,'aaa');
1 row created.
SQL> insert into trig_eg values(2,'bbb');
1 row created.
SQL> update trig_eg set name = 'xxxx' where id = 2;
1 row updated.
SQL> delete from trig_eg where id = 2;
1 row deleted.
SQL> select * from CHANGES_RECORD;
CHANGE USER_NAME CHANGE_DA
---------- ---------- ---------

I GAURAV 18-APR-10

I GAURAV 18-APR-10

U GAURAV 18-APR-10

D GAURAV 18-APR-10

SQL> select * from trig_eg;

ID NAME

---------- ----------

1 aaa