PL/SQL

Wednesday, June 14, 2023 4:48 PM

ORACLE
DATABASE
SQL TABLES
PL/SQL ROWS & COLUMNS

SQL:
• SQL is Non-Procedural Language
• just we write queries. we will not programs [set of statements]

PL/SQL:
• PL => Procedural Language
• SQL => Structured Query Language

• PL/SQL is a procedural language.

• In this, we write a set of statements or programs to

communicate with database.

• PL/SQL = SQL + Programming

• PL/SQL is extension of SQL.

• SQL queries can be written as statements in PL/SQL program.

Advantages of PL/SQL:

• Improves the performance.

• Provides Conditional Control Structures.
• Provides Looping Control Structures.
• Provides Modularity.
• Provides Exception Handling.
• Provides Security.

ORACLE 4PM PLSQL Page 1

Improves the performance:

ORACLE SERVER
Client
request
INSERT
response
DELETE

UPDATE

request
PL/SQL program

INSERT
response
DELETE
UPDATE

• We can group SQL commands as one request in PL/SQL

program.

• PL/SQL reduces no of requests and responses. So

automatically performance will be improved.

Provides Conditional Control Structures:

• Using Conditional Control Structures, we can perform the

actions based on conditions
• PL/SQL provides conditional control structures such as:
IF .. THEN, IF .. THEN .. ELSE, IF .. THEN .. ELSIF

Example:
increase salary
after increment if salary is more than 50000 => ROLLBACK
otherwise => COMMIT

Provides Looping Control Structures:

ORACLE 4PM PLSQL Page 2

• Looping Control Structures are used to execute the
statements repeatedly.
• PL/SQL provides looping control structures such as:
Simple Loop, While Loop and For Loop

Ex:
Update salary to all emps with different hikes

UPDATE => 7369 => 10% on sal

7499 => 15% on sal
7521 => 12% on sal

Modularity:
• Modularity is a programming style.

• Modularity means, dividing large program into small parts

Procedure-1

Procedure-2

.
.

Procedure-3

Advantages:

• improves understandability
• provides reusability
• reduces length of code
better maintenance
ORACLE 4PM PLSQL Page 3
• better maintenance

Exception Handling:

Exception => Run-Time Error

Exception Handling:
The way of handling run-time error is called Exception Handling.

100 lines

…..
…….
… 50th line => Run-Time Error
…..
…… Application will be closed

Abnormal termination will occur.

We may loss some data

To convert abnormal termination to normal termination,

we use Exception Handling.

Provides Security:

• All procedures and functions will be stored in centralized

location [SERVER].
• Only authorized users can use the procedures and functions.

Types of Blocks:

2 Types:

Anonymous Block
ORACLE 4PM PLSQL Page 4
• Anonymous Block
• Named Block

Anonymous Block Named block

BEGIN CREATE PROCEDURE demo AS

--Statements BEGIN
END; --Statements
/ END;
/

Anonymous Block:
A block without name is called "Anonymous Block".

Named Block:
A block with the name is called "Named Block"

Exs:
Procedures, Functions, Triggers, Packages

Syntax of Anonymous Block: / Run

DECLARE
--declare the variables declaration part [optional]
BEGIN
--executable statements
execution part
END;
/

DATA TYPES
DECLARE
ASSIGN
INITIALIZE
PRINT

ORACLE 4PM PLSQL Page 5

PRINT
READ

dbms_output.put_line('hello');

In C:
printf("hello");

In Java:
System.out.println("hello");
PACKAGE dbms_output

PROCEDURE put_line(s STRING)

AS
…….
……
…..

Syntax to call packaged procedure:

package_name.procedure_name(<arguments>);

Ex:
dbms_output.put_line('HELLO');

put_line():
• It is a packaged procedure.
• It is defined in "DBS_OUTPUT" package.
• It is used to print the data.

Developing PL/SQL Program:

ORACLE 4PM PLSQL Page 6

Program to print 'hello':

BEGIN
dbms_output.put_line('HELLO');
END;
/

• Type above program in text editor like Notepad, Edit Plus,

Notepad++ …etc.

• Save it in "D:" Drive, "ORA4PM" Folder, with the name

"HelloDemo.sql"

Compiling & Running PL/SQL Program:

• Open SQL PLUS

• Login as USER.
○ username: c##batch4pm
○ password: nareshit

• At SQL prompt type following commands:

Syntax: @<path_of_sqlfile>

SQL> SET SERVEROUTPUT ON

SQL> @d:\ora4pm\Helloemo.sql --compiling PL/SQL program
Output:
HELLO

SERVEROUTPUT:
• It is a parameter.
• Its default value is: OFF
• If it is OFF, messages cannot be sent to output.
• To send messages to output, it must be ON.

to set serveroutput as on write following command:

SQL> SET SERVEROUTPUT ON

ORACLE 4PM PLSQL Page 7

Data Types in PL/SQL:

Character Related Char(n)

Varchar2(n)
LONG
CLOB
STRING(n) => PL/SQL Only

nChar(n)
nVarchar2(n)
nCLOB

Integer Related Number(p)

INTEGER
INT
PLS_INTEGER => PL/SQL only
BINARY_INTEGER => PL/SQL only

Floating Point Related Number(p,s)

FLOAT
BINARY_FLOAT
BINARY_DOUBLE
REAL

Date & Time Related DATE

TIMESTAMP

Binary Related BFILE

BLOB

Boolean Related Boolean => PL/SQL Only [ORACLE 21c]

from ORACLE 23c version onwards,

BOOLEAN data type will be available for
SQL and PL/SQL

Cursor Related sys_Refcursor => PL/SQL only

Attribute Related %TYPE => PL/SQL only

ORACLE 4PM PLSQL Page 8

Attribute Related %TYPE => PL/SQL only
%ROWTYPE => PL/SQL only

Variable:
• It is an Identifier.
• It is a name of storage location.
• It holds a value.
• It can hold one value only.

Declaring Variable:

Syntax:
<variable> <data_type>;

Exs:
x INT;
s STRING(10);

a NUMBER(4);
b VARCHAR2(10);
c DATE;

Assigning Value:

:= Assignment Operator In C/Java:

x=25;
Syntax:
<variable> := <constant / variable / expression>;
= Assignment Operator
Exs: x y z
x := 25; --25 - constant 25 25 50
y := x; --x - variable
z := x+y; -- x+y - expression

x+y := z; //INVALID

25 := z; //INVALID
ORACLE 4PM PLSQL Page 9
25 := z; //INVALID

Initializing value:

Initialization => giving value at the time of declaration

Examples: x
x
null
25
x INT; --declare 25
x INT := 25;
x := 25; --assign

Printing data:

dbms_output.put_line(x);

Reading data:

x := &x;

Output:
enter value for x: 25
old: &x
new: 25
x := &x
x := 25

DECLARE x INT;
ASSIGN x := 25;
INITIALIZE x INT := 25;
PRINT dbms_output.put_line(x);
READ x := &x;

ORACLE 4PM PLSQL Page 10

READ x := &x;

Program to add 2 numbers:

declare x,y,z as int

x y
20 30 assign 20 to x
assign 30 to y
20+30 => 50
z := x+y add them & store result in z

print result

x y z
DECLARE
x INT; 20 30 50
y INT;
z INT;
BEGIN
x := 20;
y := 30;

z := x+y;

dbms_output.put_line('sum=' || z);
dbms_output.put_line('sum of ' || x || ' and ' || y || ' is ' || z);
END;
/

Output:
sum=50
sum of 20 and 30 is 50

Program to add 2 number. Read 2 numbers at runtiime:

ORACLE 4PM PLSQL Page 11

Program to add 2 number. Read 2 numbers at runtiime:

DECLARE
x INT;
y INT;
z INT;
BEGIN
x := &x;
y := &y;

z := x+y;

dbms_output.put_line('sum=' || z);
dbms_output.put_line('sum of ' || x || ' and ' || y || ' is ' || z);
END;
/

SQL> @d:\ora4pm\ReadDemo.sql
Enter value for x: 5
old 6: x := &x;
new 6: x := 5;
Enter value for y: 4
old 7: y := &y;
new 7: y := 4;
sum=9
sum of 5 and 4 is 9

VERIFY:
• it is a parameter
• its default value is ON
• set it as OFF to avoid OLD and NEW parameters

Type following command to set verify as OFF:

SQL> SET VERIFY OFF

SQL> @d:\ora4pm\ReadDemo.sql
Enter value for x: 10
Enter value for y: 5
sum=15
sum of 10 and 5 is 15

ORACLE 4PM PLSQL Page 12

DECLARE x INT;
ASSIGN x := 20;
INITIALIZE x INT := 20;
PRINT dbms_output.put_line(x);
READ x := &x;

Using SQL commands in PL/SQL:

• DRL, DML, TCL commands can be used directly in

PL/SQL program.

• DDL, DCL commands cannot be used directly in

PL/SQL program.

• To use DDL & DCL commands in PL/SQL, we use

Dynamic SQL.

Syntax of SELECT command in PL/SQL:

SELECT <column_list> INTO <variable_list>

FROM <table_name>
WHERE <condition>;

Example: x y
SELECT ename, sal INTO x, y SMITH 800
FROM emp
WHERE empno=7369;

EMP
EMPNO ENAME SAL HIREDATE
7369 SMITH 800 ..
7499 ALLEN 1600 ..
7521 WARD 2500 ..

ORACLE 4PM PLSQL Page 13

SELECT ename, sal, hiredate INTO x, y, z
x y z
FROM emp
ALLEN 1600 ..
WHERE empno=7499;

Example:

Display the emp name and salary of given empno:

Read empno => 7499

SELECT ename and sal => copy into variables
Print emp name and sal

Program: v_empno v_ename v_sal

EMP
7499 ALLEN 1600
DECLARE EMPNO ENAME SAL HIREDATE
v_empno NUMBER(4); 7369 SMITH 800 ..
v_ename VARCHAR2(10); 7499 ALLEN 1600 ..
v_sal NUMBER(7,2); 7521 WARD 2500 ..
BEGIN
v_empno := &empno;

SELECT ename, sal INTO v_ename, v_sal

FROM emp WHERE empno=v_empno;

dbms_output.put_line(v_ename || ' ' || v_sal);

END;
/

Output:
enter …empno: 7499
ALLEN 1600

Variable Table Column

v_empno NUMBER(2) empno NUMBER(4)

ORACLE 4PM PLSQL Page 14
Variable Table Column

v_empno NUMBER(2) empno NUMBER(4)

field sizes are mismatching

Variable Table Column

v_empno DATE empno NUMBER(4)

data types are mismatching

%TYPE:

Syntax:

<variable> <table_name>.<column_name>%TYPE;

Examples:
EMP
v_empno EMP.EMPNO%TYPE;
--empno NUMBER(4)

"v_empno" variable data type will be taken as "emp"

table's "empno" column's data type.

• %TYPE is an Attribute Related Data Type.

• It is used to declare a variable with table column's

data type and field size.

• It avoids mismatch between variable field size &

column field size.

• It avoids mismatch between variable data type &

table column data type.

ORACLE 4PM PLSQL Page 15

Display the emp name, salary and hiredate
of given empno:

DECLARE
v_empno EMP.EMPNO%TYPE;
v_ename EMP.ENAME%TYPE;
v_sal EMP.SAL%TYPE;
v_hiredate EMP.HIREDATE%TYPE;
BEGIN
v_empno := &empno;

SELECT ename,sal,hiredate INTO v_ename,v_sal,v_hiredate

FROM emp WHERE empno=v_empno;

dbms_output.put_line(v_ename || ' ' || v_sal || ' ' || v_hiredate);

END;
/

%ROWTYPE:

Syntax:

<variable> <TABLE_NAME>%ROWTYPE;

Example:
r EMP%ROWTYPE;

EMPNO ENAME JOB SAL MGR COMM HIREDATE DEPTNO

7499 ALLEN SALESMAN 1600 .. .. .. 30

r.ename
r.sal r.deptno
SELECT * INTO r FROM emp WHERE empno=7499;

ORACLE 4PM PLSQL Page 16

DEPT
DEPTNO DNAME LOC r DEPT%ROWTYPE;
10 ACCOUNTS CHICAGO
20 .. .. r
30 .. ..
40 .. ..
DEPTNO DNAME LOC
10 ACCOUNTS CHICAGO
SELECT * INTO r
FROM dept
WHERE deptno=10; r.dname r.loc
r.deptno

• %ROWTYPE is an Attribute Related Data Type.

• It is used to hold entire row of a table.

• It can hold one row at a time.

• It decreases no of variables.

Example on %ROWTYPE:

Display the emp record of given empno:

r
v_empno
empno ename job sal mgr deptno comm hiredate
7369
7369 SMITH CLERK 800 .. .. .. ..

DECLARE
v_empno EMP.EMPNO%TYPE;
r EMP%ROWTYPE;
BEGIN
v_empno := &empno;

SELECT * INTO r FROM emp

WHERE empno=v_empno;

ORACLE 4PM PLSQL Page 17

dbms_output.put_line(r.ename || ' ' || r.sal || ' ' || r.hiredate);
END;
/

SELECT ename,sal INTO x,y

FROM emp
WHERE empno=7369;

%TYPE:
to declare a variable with table column's data type

Ex:
v_empno EMP.EMPNO%TYPE;

%ROWTYPE:
used to hold entire row

r EMP%ROWTYPE;

r.empno
r.ename

Note:
UPDATE, DELETE and INSERT commands syntaxes in
SQL and PL/SQL are same.

Using UPDAT command in PL/SQL:

Program to increase salary of given empno

with given amount:

enter …. empno: 7369

enter …. amount: 2000
salary updated

ORACLE 4PM PLSQL Page 18

Read empno => v_empno
Read amount => v_amount
UPDATE query
display message => salary updated

DECLARE
v_empno EMP.EMPNO%TYPE;
v_amount FLOAT;
BEGIN
v_empno := &empno;
v_amount := &amount;

UPDATE emp SET sal=sal+v_amount WHERE empno=v_empno;

COMMIT;

dbms_output.put_line('salary increased');
END;
/

Using DELETE command in PL/SQL:

Program to delete an emp record of given empno:

enter value for empno: 7934

record deleted

Read empno

DELETE command

display message => record deleted

DECLARE
v_empno EMP.EMPNO%TYPE;
BEGIN
v_empno := &empno;

ORACLE 4PM PLSQL Page 19

DELETE FROM emp WHERE empno=v_empno;
COMMIT;

dbms_output.put_line('record deleted');
END;
/

Using INSERT command in PL/SQL:

Program to insert record into student table:

STUDENT
sid sname m1

CREATE TABLE student

(
sid NUMBER(4),
sname VARCHAR2(10),
m1 NUMBER(3)
);

DECLARE
r STUDENT%ROWTYPE;
BEGIN
r.sid := &sid;
r.sname := '&sname';
r.m1 := &m1;

INSERT INTO student VALUES(r.sid, r.sname, r.m1);

COMMIT;

dbms_output.put_line('record inserted');
END;
/

Program to find experience of given empno:

ORACLE 4PM PLSQL Page 20

enter value for empno: 7369
7369 experience is: 42 years

enter value for empno: 7788

7788 experience is: 40 years

read empno => v_empno

SELECT hiredate copy into variable v_hiredate

v_Exp := trunc((sysdate-v_hiredate)/365)

display message:

DECLARE
v_empno EMP.EMPNO%TYPE;
v_hiredate DATE;
v_exp INT;
BEGIN
v_empno := &empno;

SELECT hiredate INTO v_hiredate FROM emp

WHERE empno=v_empno;

v_exp := TRUNC((sysdate-v_hiredate)/365);

dbms_output.put_line(v_empno || ' experience is:' ||

v_exp || ' years');
END;
/

Program to display weekday name of given date:

enter value for d: 15-AUG-1947

FRIDAY

enter value for d: 17-JUN-2023

SATURDAY

DECLARE
d DATE;
s STRING(10);
BEGIN
d := to_date('&date');
ORACLE 4PM PLSQL Page 21
d := to_date('&date');

s := to_char(d,'DAY');

dbms_output.put_line(s);
END;
/

DECLARE x FLOAT;
ASSIGN x := 12.34;
INTIALIZE x FLOAT := 12.34;
PRINT dbms_output.put_line(x);
READ x := &x;

SELECT ename,sal INTO x,y

FROM emp
WHERE empno=7369;

INSERT
UPDATE
DELETE

%type to declare a variable with table column type

ex: v_empno emp.empno%type; [Number(4)]
%rowtype to hold table row
ex: r emp%rowtype;
r.ename r.sal

ORACLE 4PM PLSQL Page 22

Control Structures
Saturday, June 17, 2023 5:17 PM

DECLARE DECLARE
m INT; m INT;
BEGIN BEGIN
m := 70; m := 70;

dbms_output.put_line('PASS'); IF m>=40 THEN

dbms_output.put_line('FAIL'); dbms_output.put_line('PASS');
END; ELSE
/ dbms_output.put_line('FAIL');
END IF;
Output: END;
PASS /
FAIL
Output:
PASS

Control Structure Goal => changing sequential execution

Control Structure:

• To change sequential execution, to transfer the control to

our desired location, we use Control Structure.

• Control Structure controls the flow of execution of

statements.

• Which statement executed when and how many times can

be controlled by Control Structure.

PL/SQL provides following Control Structures:

Conditional IF .. THEN
IF .. THEN .. ELSE
IF .. THEN .. ELSIF
NESTED IF
CASE

Looping WHILE
FOR
SIMPLE LOOP

Jumping GOTO
ORACLE 4PM PLSQL Page 23
Jumping GOTO
EXIT WHEN
EXIT
CONTINUE

Conditional Control Structures:

Conditional Control Structures execute the statements

based on conditions.

PL/SQL provide following conditional control structure:

• IF .. THEN
• IF .. THEN .. ELSE
• IF .. THEN .. ELSIF
• NESTED IF
• CASE

IF .. THEN:

Syntax:

IF <condition> THEN
--Statements
END IF;

• The statements in "IF .. THEN" get executed

when condition is TRUE.

Example:

Program to delete an emp record of given empno.

If experience is more than 41 then only delete emp record:

enter value for empno: 7788

experience is: 40

enter value for empno: 7369

ORACLE 4PM PLSQL Page 24
enter value for empno: 7369
experience is: 42
record deleted

DECLARE
v_empno EMP.EMPNO%TYPE;
v_hiredate DATE;
v_exp INT;
BEGIN
v_empno := &empno;

SELECT hiredate INTO v_hiredate FROM emp WHERE

empno=v_empno;

v_exp := TRUNC((sysdate-v_hiredate)/365);

dbms_output.put_line('experience is:' || v_exp);

IF v_exp>41 THEN
DELETE FROM emp WHERE empno=v_empno;
COMMIT;
dbms_output.put_line('record deleted');
END IF;
END;
/

IF .. THEN .. ELSE:

Syntax:

IF <condition> THEN
--Statements
ELSE
--Statements
END IF;

The statements in "IF .. THEN" get executed when the

condition is TRUE.

The statements in "ELSE" get executed when the

condition is FALSE.

ORACLE 4PM PLSQL Page 25

condition is FALSE.

Example on IF .. THEN .. ELSE:

Program to increase salary of given empno based on

job as following:
if job is manager then increase 20% on sal
otherwise, increase 10% on sal

read empno
select job => v_job

IFv_job='MANAGER' THEN
UPDATE => 20%
ELSE
UPDATE => 10%
END IF;

DECLARE
v_empno EMP.EMPNO%TYPE;
v_job EMP.JOB%TYPE;
v_per INT;
BEGIN v_empno v_job v_per
v_empno := &empno;
7902 ANALYST 10
SELECT job INTO v_job FROM emp WHERE
empno=v_empno;

IF v_job='MANAGER' THEN
v_per := 20;
ELSE
v_per := 10;
END IF;

UPDATE emp SET sal=sal+sal*v_per/100

WHERE empno=v_empno;
COMMIT;

dbms_output.put_line('Your job is:' || v_job);

dbms_output.put_line(v_per || '% on salary increased');
ORACLE 4PM PLSQL Page 26
dbms_output.put_line(v_per || '% on salary increased');
END;
/

Assignment:

Write a program to increase salary of given empno

with given amount. After increment, if salary is more than 10000
cancel it. otherwise, save it:

Read empno => v_empno

Read amount => v_amount

UPDATE emp SET sal=sal+v_amount WHERE empno=v_empno;

SELECT sal => v_sal

IF v_sal>10000 THEN
ROLLBACK;
dbms_output.put_line('Tansaction cancelled');
ELSE
COMMIT;
dbms_output.put_line('Tansaction successful');
END IF;

IF .. THEN .. ELSIF:

Syntax:

IF <condition-1> THEN
--Statements
ELSIF <condition-2> THEN
--Statements
.
.
[ELSE
--Statements]
END IF;

ORACLE 4PM PLSQL Page 27

Example on IF .. THEN .. ELSIF:

Program to increase salary of given empno based on job as

following:
If job is MANAGER then increase 20% on sal
If job is CLERK then increase 10% on sal
others => increase 5% on sal

read empno
select job => v_job

if v_job='MANAGER' THEN
v_per := 20;
elsif v_job='CLERK' THEN
v_per := 10;
else
v_per := 5;
END IF;

UPDATE salary
commit

display message => salary increased

Assignment:

Program to increase salary of given empno based on deptno

as following:
if emp working in deptno 10 then increase 10% on sal
if emp working in deptno 20 then increase 20% on sal
if emp working in deptno 30 then increase 15% on sal
other than 10, 20, 30 => increase 5% on sal

NESTED IF:
Writing 'IF' in another 'IF' is called 'Nested If'

Syntax:

IF <condition-1> THEN
IF <condition-2> THEN
inner
outer --Statements condition-1 => T
if
if END IF; condition-2 => T
END IF;

ORACLE 4PM PLSQL Page 28

inner
outer --Statements condition-1 => T
if
if END IF; condition-2 => T
END IF;

Program to calculate total, average and result of given student id.

max marks: 100
min marks: 40 in each sub for pass
in any subject, if marks are <40 => FAIL
if pass check average
if avrg is 60 or more => FIRST DIV
if avrg is b/w 50 to 59 => SECOND DIV
if avrg is b/w 40 to 49 => THIRD
calculate total, avrg and result snd store them in RESULT table.

STUDENT RESULT
SID SNAME M1 M2 M3 sid total avrg result
1 A 60 80 70 1 210 70 FIRST
2 B 50 30 90

enter … sid: 1
result stored in result table

CREATE TABLE student

(
sid NUMBER(4),
sname VARCHAR2(10),
m1 NUMBER(3),
m2 NUMBER(3),
m3 NUMBER(3)
);

INSERT INTO student

VALUES(1,'A',60,80,70);

INSERT INTO student

VALUES(2,'B',50,30,90);

COMMIT;

CREATE TABLE result

(
ORACLE 4PM PLSQL Page 29
(
sid NUMBER(4),
total NUMBER(3),
avrg NUMBER(5,2),
result VARCHAR2(10)
);

Program:

DECLARE
v_sid STUDENT.SID%TYPE;
r1 STUDENT%ROWTYPE;
r2 RESULT%ROWTYPE;
BEGIN
v_sid := &sid;

SELECT * INTO r1 FROm student WHERE sid=v_sid;

r2.total := r1.m1+r1.m2+r1.m3;

r2.avrg := r2.total/3;

IF r1.m1>=40 AND r1.m2>=40 and r1.m3>=40 THEN

IF r2.avrg>=60 THEN
r2.result := 'FIRST';
ELSIF r2.avrg>=50 THEN
r2.result := 'SECOND';
ELSE
r2.result := 'THIRD';
END IF;
ELSE
r2.result := 'FAIL';
END IF;

INSERT INTO result VALUES(v_sid, r2.total, r2.avrg, r2.result);

COMMIT;

dbms_output.put_line('result calculated and stored in result table');

END;
/

ORACLE 4PM PLSQL Page 30

CASE:

It can be used in 2 ways. They are:

• Simple CASE [same as switch in C]
• Searched CASE [same as if else if]

switch if else if
can check equality condn only can check any condition

switch(expression)
{
if(x>0)
case <constant-1>:
//STATEMENTS
break;
else if(x<0)
case <constant-2>:
//STATEMENTS
else if(x==0)
break;
.
.
default:
//Statements
}

Simple CASE:
It can check equality condition only

Syntax:

CASE <expression>
WHEN <constant-1> THEN
--Statements
WHEN <constant-2> THEN
--Statements
.
.
[ELSE
--Statements]
END CASE;

ORACLE 4PM PLSQL Page 31

Searched CASE:
It can check any condition

Syntax:

CASE
WHEN <condition-1> THEN
--Statements
WHEN <condition-2> THEN
--Statements
.
.
[ELSE
--Statements]
END CASE;

Example on Simple CASE:

Program to check whether the given number is even or

odd using Simple CASE:

2,4,6,8,….. EVEN divide with 2 Remainder 0

1,3,5,7,….. ODD divide with 2 Remainder 1

DECLARE
n INT := &n;
BEGIN
CASE mod(n,2)
WHEN 0 THEN
dbms_output.put_line('EVEN');
WHEN 1 THEN
dbms_output.put_line('ODD');
END CASE;
END;
/

Assignment:
program to increase salary of given empno based on
joining year:
if emp joined in 1980, increase 22.5% on sal
if emp joined in 1981, increase 13.5% on sal
ORACLE 4PM PLSQL Page 32
if emp joined in 1981, increase 13.5% on sal
if emp joined in 1982, increase 10.2% on sal
others, increase 8% on sal
do it using SIMPLE CASE

CASE to_char(hiredate,'YYYY')
WHEN 1980 THEN
v_per := 22.5;

.
.

Example on Searched CASE:

Program to check whether the given number is +ve or -ve

or zero using Searched CASE:

-infinite… -4 -3 -2 -1 0 1 2 3 4 5 …….. infinite

n=0
n>0
n<0
zero
positive
negative

DECLARE
n INT := &n;
BEGIN
CASE
WHEN n>0 THEN
dbms_output.put_line('positive');
WHEN n<0 THEN
dbms_output.put_line('negative');
WHEN n=0 THEN
dbms_output.put_line('zero');
END CASE;
END;
/

Assignment:

ORACLE 4PM PLSQL Page 33

Assignment:
Program to increase salary of given empno based on
current salary as following:
if sal is 10000 or more => increase 20% on sal
if sal is b/w 5000 to 9999 => increase 15.5% on sal
if sal is is <5000 => increase 5.3% on sal

CASE
WHEN sal>=10000 THEN
v_per := 20;
WHEN sal>=5000 THEN
v_per := 15.5;
WHEN sal<5000 THEN
v_per := 5.3;
END CASE;

UPDATE

Looping Control Structures:

Looping Control Structures are used to execute the
statements repeatedly.

100 hellos
100 times
d_o.p_l('hello'); hello
LOOP 100 times
d_o.p_l('hello'); hello
d_o.p_l('hello');
d_o.p_l('hello'); hello
END LOOP;
. .
. .
d_o.p_l('hello'); hello
d_o.p_l('hello'); hello
d_o.p_l('hello'); hello

PL/SQL provides following Looping Control

Structures:

• WHILE
• SIMPLE LOOP
• FOR

WHILE:

Syntax:
ORACLE 4PM PLSQL Page 34
Syntax:
In C:
WHILE <condition>
LOOP while(<condition>)
--Statements {
END LOOP; //Statements
}

The statements in WHILE loop get executed as

long as the condition is TRUE.
Loop will be terminated when the condition is
FALSE.

Example on WHILE:

Program to print numbers from 1 to 4:

i=1

i DECLARE
1 i INT;
2 BEGIN
3 i := 1;
4
. dbms_output.put_line(i); --1
. i:=i+1; --i=2
100
dbms_output.put_line(i); --2
i:=i+1; --i=3

dbms_output.put_line(i); --3
i:=i+1; --i=4

dbms_output.put_line(i); --4

END;
/

i
WHILE i<=4
1
LOOP
2
dbms_output.put_line(i);
ORACLE 4PM PLSQL Page 35
WHILE i<=4
1
LOOP
2
dbms_output.put_line(i);
3
i:=i+1;
4
LOOP;

i<=4

Program:

declare
i INT;
begin
i := 1;

WHILE i<=4
LOOP
dbms_output.put_line(i);
i := i+1;
END LOOP;
end;
/

Program to print 2023 calendar:

d1 d2
1-jan-2023 31-dec-2023

WHILE d1<=d2
LOOP
d_o.p_l(d1);
d1:=d1+1;
END LOOP

Program:

DECLARE
d1 DATE := '1-JAN-2023';
d2 DATE := '31-DEC-2023';
BEGIN

WHILE d1<=d2
ORACLE 4PM PLSQL Page 36
WHILE d1<=d2
LOOP
dbms_output.put_line(d1 || ' ' || to_char(d1,'DY'));
d1 := d1+1;
END LOOP:
END;
/

Program to print 2023 calendar. print sundays only:

DECLARE
d1 DATE := '1-JAN-2023';
d2 DATE := '31-DEC-2023';
BEGIN

WHILE d1<=d2
LOOP
IF to_char(d1,'DY') = 'SUN' THEN
dbms_output.put_line(d1 || ' ' || to_char(d1,'DY'));
END IF;
d1 := d1+1;
END LOOP;
END;
/

Simple Loop:

Syntax:

LOOP
--Statements
EXIT WHEN <condition>; [or] EXIT;
END LOOP;

Note:
EXIT WHEN / EXIT are same as "break" in C / Java.
ORACLE 4PM PLSQL Page 37
EXIT WHEN / EXIT are same as "break" in C / Java.

EXIT WHEN:
• it is used to terminate the loop in the middle of
execution
• It can be used in "LOOP" Only.

Syntax:
EXIT WHEN <condition>;

When condition is true, loop will be terminated

EXIT:
• it is used to terminate the loop in the middle of
execution.
• It can be used in "LOOP" Only.

Syntax:
EXIT;

BEGIN
dbms_output.put_line('HI');
EXIT;
dbms_output.put_line('BYE');
END;
/

ERROR: EXIT can be used in loop only

Program to demonstrate simple loop:

print numbers from 1 to 4 using simple loop:

i DECLARE
1 i INT;
2 BEGIN
3 i := 1;
4
LOOP
dbms_output.put_line(i);

ORACLE 4PM PLSQL Page 38

3
4
LOOP
dbms_output.put_line(i);
EXIT WHEN i=4; IF i=4 THEN
i:=i+1; EXIT;
END LOOP; END IF;
END;
/

FOR LOOP:

Syntax:

FOR <variable> IN [REVRSE] <lower> .. <upper>

LOOP
--Statements
END LOOP;

Example on for loop:

Program to print numbers from 1 to 4:

i
BEGIN
1 FOR i IN 1 .. 4
2 LOOP
3 dbms_output.put_line(i);
4 END LOOP;
END;
/

Note:

• We have no need to declare loop variable. It will be

declared implicitly as NUMBER type.

• loop variable is read-only variable.

Example:

BEGIN
FOR i IN 1 .. 10 Output:
ERROR:
ORACLE 4PM PLSQL Page 39
BEGIN
FOR i IN 1 .. 10 Output:
LOOP ERROR:
i := 5; "I" loop variable is read-only variable
dbms_output.put_line(i);
END LOOP;
END;
/

• Loop variable scope is limited to LOOP only.

Example:

BEGIN
FOR i IN 1 .. 5
LOOP
dbms_output.put_line(i);
END LOOP;
dbms_output.put_line(i); --ERROR: loop variable cannot be accessed out of loop
END;
/

• Always INCREMENT BY value [step value] is 1.

It will be increased implicitly.

Example:

BEGIN
FOR i IN 1 .. 20
LOOP
dbms_output.put_line(i);
i := i+2; --ERROR
END LOOP;
END;
/

Jumping Control Structures:

GOTO:

ORACLE 4PM PLSQL Page 40

GOTO:
It transfers the execution to Label

Syntax:

<<LABEL>>

//Statements executes statements repeatedly

GOTO LABEL;

Example on GOTO:

Program to print numbers from 1 to 4:

DECLARE
i
i INT := 1;
1 BEGIN
2
3 <<xyz>>
4 dbms_output.put_line(i);
i := i+1;
IF i<=4 THEN
GOTO xyz;
END IF;

END;
/

CONTINUE:
• It is used to skip current iteration and continue the next
iteration.
• CONTINUE can be used in LOOP only.

BEGIN
dbms_output.put_line('HI');
continue;
dbms_output.put_line('BYE');
END;

ORACLE 4PM PLSQL Page 41

END;
/

Output:
ERROR: Continue can be used in LOOP only

Example on Continue:

Program to print numbers from 1 to 10 except 7:

1 BEGIN
2 FOR i IN 1 .. 10
3 LOOP
4 IF i=7 THEN
5 continue;
6 END IF;
8 dbms_output.put_line(i);
9 END LOOP;
10 END;
/

ORACLE 4PM PLSQL Page 42

CURSORS
Thursday, June 22, 2023 5:15 PM

CURSOR:
v_empno EMP.EMPNO%TYPE;

emp r EMP%ROWTYPE;
empno ename sal
1001 A 5000 r
1002 B 7000
1003 C 6000 empno ename sal

%TYPE used to hold 1 column value

%ROWTYPE used to hold 1 row
CURSOR used to hold multiple rows

GOAL:
• used to hold multiple rows & process them one by one
CURSOR:
• CURSOR is a pointer to the memory location in ORACLE INSTANCE [RAM]. This
memory location has multiple rows.

• To hold multiple rows & process them one by one, we use CURSOR

• Every CURSOR is associated with a SELECT query

ORACLE DB SERVER

INSTANCE DB
PL/SQL 1
Program 2
SELECT ename,sal
c1 FROM emp ename sal emp
A 5000 dept
.
B 6000
.
4. C 7000
address gives to c1

RAM Hard Disk

3
copies selected data
into instance

ORACLE 4PM PLSQL Page 43

Steps to use CURSOR:

4 steps:

• DECLARE
• OPEN
• FETCH
• CLOSE

Declaring Cursor:

ename sal
Syntax:
SMITH 800
CURSOR <name> IS <SELECT query>; ALLEN 1600
WARD 2450

Example:
CURSOR c1 IS SELECT ename,sal FROM emp;

c1

When we declare the cursor,

• cursor variable will be created
• SELECT query will be identified

Opening Cursor:

Syntax:

OPEN <cursor_name>;

Example:
OPEN c1; ORACLE DB SERVER
2 goes to DB
Instance DB

PL/SQL program 1 ename sal

emp
SELECT ename,sal SMITH 800
c1
FROM emp ALLEN 1600
WARD 2450

` 4
memory location address
ORACLE 4PM PLSQL Page 44
ALLEN 1600
WARD 2450

` 4
memory location address RAM HARD DISK
will be given to c1
3
selects data & copies
into instance

When CURSOR is opened,

1. SELECT query will be submitted to ORACLE DB SERVER.
2. ORACLE goes to Database.
3. Selects the data & copies into INSTANCE [RAM]
4. This memory location address will be given to cursor variable.

c1 Instance
Fetching Record from Cursor:

Syntax: BFR

SMITH 800
FETCH <cursor_name> INTO <variable_list>;
ALLEN 1600
WARD 2450
ALR
Example:
FETCH c1 INTO v_ename, v_sal;
stop BFR => Before First Row
when ALR => After Last Row
fetch
is
unsuccessful

When FETCH statement is executed, it goes to next row.

By default, CURSOR points to BFR [Before First Row]

when FETCH is executed for 1st time, it goes to first row
when FETCH is executed for 2nd time, it goes to second row
.
.

NOTE:
One FETCH statement can fetch 1 row only.
To FETCH multiple rows write FETCH statement in LOOP.

ORACLE 4PM PLSQL Page 45

Closing Cursor:
Instance
Syntax:
c1

CLOSE <cursor_name>;
SMITH 800
ALLEN 1600
WARD 2450
Example:
CLOSE c1;

When CURSOR is Closed,

• The data in INSTANCE will be cleared
• Reference to memory location will be gone

DELCARE CURSOR c1 IS SELECT ename,sal FROM emp;

OPEN OPEN c1;

FETCH FETCH c1 INTO v_ename, v_sal;

CLOSE CLOSE c1;

Cursor Attributes:

Cursor Attributes are:

• %FOUND
• %NOTFOUND
• %ISOPEN
• %ROWCOUNT

Syntax to use cursor attribute:

<cursor_name><attribute_name>;
%FOUND %NOTFOUND %ROWCOUNT

Examples: 0
c1%FOUND
c1%NOTFOUND c1 Instance
c1%ISOPEN BFR
c1%ROWCOUNT
SMITH 800

ORACLE 4PM PLSQL Page 46

c1%NOTFOUND c1 Instance
c1%ISOPEN BFR
c1%ROWCOUNT
SMITH 800
ALLEN 1600

%FOUND: WARD 2450

• It returns boolean value.
• If record is found, it returns TRUE.
• If record is not found, it returns FALSE.

%NOTFOUND:
• It returns boolean value.
• If record is not found, it returns TRUE.
• If record is found, it returns FALSE.

%ISOPEN:
• it returns boolean value
• it is used to check whether the cursor is opened or not.
• if cursor is opened, it returns TRUE.
• if cursor is not opened, it returns FALSE.

%ROWCOUNT:
• it returns row number.
• its default value is 0.
• When fetch is successful, it will be incremented by 1.

Examples on CURSOR:

Display all emp names and salaries:

DECLARE
CURSOR c1 IS SELECT ename,sal FROM emp;
v_ename EMP.ENAME%TYPE;
Instance
v_sal EMP.SAL%TYPE; c1
BEGIN v_ename v_sal BFR
OPEN c1; SMITH 800
ALLEN 1600
LOOP
WARD 2450
FETCH c1 INTO v_ename, v_sal;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(v_ename || ' ' || v_sal);
END LOOP;

dbms_output.put_line(c1%ROWCOUNT || ' rows selected');

CLOSE c1;
END;
/

ORACLE 4PM PLSQL Page 47

steps to use cursor:

Program to increase salary of all emps according to HIKE

table:

EMPLOYEE HIKE
empno ename sal empno per
1001 A 6000 1001 10
1002 B 5000 1002 20
1003 C 7000 1003 15

create table employee

(
empno int,
ename varchar(10),
sal numeric(8,2)
);

insert into employee values(1001,'A',6000);

insert into employee values(1002,'B',5000);
insert into employee values(1003,'C',7000);

create table hike

(
empno int,
per int
);

insert into hike values(1001,10);

insert into hike values(1002,20);
insert into hike values(1003,15);

commit;

Program:

DECLARE
CURSOR c1 IS SELECT * FROM hike; Instance
r HIKE%ROWTYPE; c1
r
BEGIN BFR
empno per
OPEN c1;
1003 15 1001 10

LOOP 1002 20
FETCH c1 INTO r; 1003 15
EXIT WHEN c1%notfound;
UPDATE employee SET sal=sal+sal*r.per/100 WHERE empno=r.empno;
END LOOP;
c1%notfound
COMMIT;

ORACLE 4PM PLSQL Page 48

UPDATE employee SET sal=sal+sal*r.per/100 WHERE empno=r.empno;
END LOOP;
c1%notfound
COMMIT;
FALSE
dbms_output.put_line(c1%rowcount || ' rows updated');
c1%rowcount
CLOSE c1;
END; 01 23
/

Program to find total, average and result of all students.

STUDENT
sid sname m1 m2 m3 RESULT
1001 A 50 80 70 sid total avrg result
1002 B 78 30 60

create table student

(
sid number(4),
sname varchar2(10),
m1 number(3),
m2 number(3),
m3 number(3)
);

create table result

(
sid number(4),
total number(3),
avrg number(5,2),
result varchar2(10)
);

insert into student values(1001,'A',50,80,70);

insert into student values(1002,'B',78,30,60);
commit;
RESULT
sid total avrg result
Program: 1001 200 66.66 PASS
1002 168 56 FAIL
DECLARE
CURSOR c1 IS SELECT * FROM student;
c1
r1 STUDENT%ROWTYPE;
r2 RESULT%ROWTYPE; Instance
BEGIN
OPEN c1;
1001 A 50 80 70

ORACLE 4PM PLSQL Page 49

r2 RESULT%ROWTYPE; Instance
BEGIN
OPEN c1;
1001 A 50 80 70

LOOP 1002 B 78 30 60
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND; TRUE

r1
r2.total := r1.m1+r1.m2+r1.m3;
sid sname m1 m2 m3
r2.avrg := r2.total/3; 1002 B 78 30 60

IF r1.m1>=40 AND r1.m2>=40 AND r1.m3>=40 THEN

r2
r2.result := 'PASS';
ELSE sid total avrg result
r2.result := 'FAIL'; 168 56 FAIL
END IF;

INSERT INTO result VALUES(r1.sid, r2.total, r2.avrg, r2.result);

END LOOP;

COMMIT;
dbms_output.put_line('result calculated and stored in result table');

CLOSE c1;

END;
/

Cursor For Loop:

Syntax: Simple For Loop:

FOR <variable> IN <cursor_name>

FOR <variable> IN <lower> .. <upper>
LOOP
LOOP
--Statements
--Statements
END LOOP;
END LOOP;

• If we use CURSOR FOR LOOP,

we have no need to open the cursor, fetch the record from
cursor and close the cursor. All these 3 actions will be done
implicitly.

• We have no need to declare cursor for loop variable.

implicitly, it will be declared as %rowtype variable.

Example Cursor For Loop:

ORACLE 4PM PLSQL Page 50

Display all emp names and salaries using Cursor For Loop:

DECLARE
CURSOR c1 IS SELECT ename,sal FROM emp;
BEGIN
FOR r IN c1
LOOP
dbms_output.put_line(r.ename || ' ' || r.sal);
END LOOP;
END;
/

Inline Cursor:
If SELECT query is specified in cursor for loop then it is
called "Inline Cursor".

Syntax:

FOR <variable> IN (<SELECT query>)

LOOP
--Statements
END LOOP;

Example on Inline Cursor:

BEGIN
FOR r IN (SELECT ename,sal FROM emp)
LOOP
dbms_output.put_line(r.ename || ' ' || r.sal);
END LOOP;
END;
/

Program to find sum of salaries of all emps:

v_sum
SAL v_sal
c1 0 5000 9000 15000
5000
4000
6000

v_Sum := v_Sum + v_sal

v_sum := 0 + 5000 = 5000

v_sum := 5000 + 4000 = 9000
v_sum := 9000 + 6000 = 15000

ORACLE 4PM PLSQL Page 51

v_sum := 0 + 5000 = 5000
v_sum := 5000 + 4000 = 9000
v_sum := 9000 + 6000 = 15000

print v_Sum

DECLARE
CURSOR c1 IS SELECT sal FROM emp;
v_sum FLOAT := 0;
v_sal EMP.SAL%TYPE;
BEGIN
OPEN c1;

LOOP
FETCH c1 INTO v_sal;
EXIT WHEN c1%NOTFOUND;
v_sum := v_sum + NVL(v_sal,0);
END LOOP;

dbms_output.put_line('sum=' || v_sum);

CLOSE c1;
END;
/

Assignment:
Find max salary in all emps using CURSOR:

v_max
v_sal
SAL
0 5000 6000
5000
c1
4000
6000 IF v_sal>v_max THEN
v_max := v_sal;
END IF;

PRINT v_max

ORACLE 4PM PLSQL Page 52

Find min salary in all emps using CURSOR:

Program to concatenate all emp names and separate

them using ,
s
v_ename
c1 SMITH SMITH,ALLEN,WARD,
ALLEN
WARD
s := s || v_ename || ','

s := '' || SMITH || ',' => SMITH,

s := SMITH, || ALLEN || , => SMITH,ALLEN,

s := SMITH,ALLEN, || WARD || , => SMITH,ALLEN,WARD,

dbms_output.put_line(RTRIM(s,','));

SMITH,ALLEN,WARD

DECLARE
CURSOR c1 IS SELECT ename FROM
emp;
s STRING(500) := '';
v_ename EMP.ENAME%TYPE;
BEGIN
OPEN c1;

LOOP
FETCH c1 INTO v_ename;
EXIT WHEN C1%NOTFOUND;
s:= s || v_ename || ',';
END LOOP;

dbms_output.put_line(RTRIM(s,','));

CLOSE c1;
END;
/

ORACLE 4PM PLSQL Page 53

LISTAGG():
• It is an Aggregate Function.
• Introduced in ORACLE 11g version.
• It is used concatenate the strings by using a separator.

Syntax:

LISTAGG(<column>,<delimiter>)
[WITHIN GROUP(ORDER BY <column> ASC/DESC)]

Examples on LISTAGG():
Concatenate all emp names by separate them using ,:

SELECT ListAgg(ename,',') FROM emp;

ename
---------
SMITH SMITH,ALLEN,WARD
ALLEN
WARD

Concatenate all emp names by separate them using ,. Concatenate

them in Alphabetical order:

SELECT ListAgg(ename,',') WITHIN GROUP(ORDER BY ename ASC)

FROM emp;

Concatenate all emp names by separate them using ,. Concatenate

them according to salary descending order:

SELECT ListAgg(ename,',') WITHIN GROUP(ORDER BY sal DESC)

FROM emp;

Display dept wise empnames. Concatenate dept wise emp names

by using separator ,:

deptno ename Output:

10 SMITH
10 ALLEN deptno enames
20 WARD 10 SMITH,ALLEN
20 MILLER 20 WARD,MILLER

SELECT deptno,
LISTAGG(ename,',') WITHIN GROUP(ORDER BY ename ASC)as enames
FROM emp
GROUP BY deptno;

ORACLE 4PM PLSQL Page 54

NOTE:
• To process any DRL command or DML command
cursor is required.

Parameterized Cursor:
• A cursor which is declared using parameter is called "Parameterized
Cursor".

• When we don't the exact value at the time of declaration,

we define parameter to capture that value.

• When we open cursor, we pass value to cursor parameter.

Syntax to declare parameterized cursor:

CURSOR <name>(<parameter_list>) IS <SELECT query>;

Example:
CURSOR c1(n NUMBER) IS SELECT * FROM emp
WHERE deptno=n;

Syntax to Open parameterized cursor:

OPEN <cursor_name>(<parameter_value_list>);

Ex:
OPEN c1(20);

Example on Parameterized Cursor:

Program to display specific dept emp records:

DECLARE
CURSOR c1(n NUMBER) IS SELECT * FROM emp
WHERE deptno=n;
r EMP%ROWTYPE;
BEGIN
OPEN c1(20);

LOOP
FETCH c1 INTO r;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(r.ename || ' ' || r.sal || ' ' || r.deptno);
END LOOP;

ORACLE 4PM PLSQL Page 55

CLOSE c1;
END;
/

Finding sum of salaris of all emps using

Cursor For Loop:

DECLARE
CURSOR c1 IS SELECT sal FROM emp;
v_sum FLOAT := 0;
BEGIN

FOR r IN c1
LOOP
v_sum := v_sum + NVL(r.sal,0);
END LOOP;

dbms_output.put_line('sum=' || v_sum);

END;
/

Ref Cursor:

Simple Cursor Ref Cursor

Cursor c1 => EMP Cursor c1 => Emp

Cursor c2 => Dept => Dept
Cursor c3 => Salgrade => Salgrade

• In Simple Cursor, One CURSOR can be used for one SELECT query only.

• In Ref Cursor, one CURSOR can be used for multiple SELECT queries.

• It reduces number of CURSORS.

• In REF CURSOR, Select query will be specified at the time of opening

cursor.

To use Ref Cursor follow 4 steps:

Declaring Ref Cursor

ORACLE 4PM PLSQL Page 56

• Declaring Ref Cursor
• Opening Ref Cursor
• Fetching Record From Ref Cursor => same as simple cursor
• Closing Ref Cursor => same as simple cursor

Declaring Ref Cursor:

Syntax:

<cursor_name> SYS_REFCURSOR;

Example:
c1 SYS_REFCURSOR;

• SYS_REFCURSOR is a data type.

• It is used to declare Ref Cursor Variable.

Opening Ref Cursor:

Syntax:

OPEN <cursor_name> FOR <SELECT query>;

Example:
OPEN c1 FOR SELECT * FROM emp;
.
.
OPEN c1 FOR SELECT * FROM dept;

Steps Simple Cursor Ref Cursor

Declare CURSOR c1 IS SELECT * FROM emp; c1 SYS_REFCURSOR;
Open OPEN c1; OPEN c1 FOR SELECT * FROM emp;
Fetch FETCH c1 INTO r; FETCH c1 INTO r;
Close CLOSE c1 CLOSE c1;
OPEN c1 FOR SELECT * FROM dept;

Example on Ref Cursor:

Program to display all emp table records and dept table records:

DECLARE

ORACLE 4PM PLSQL Page 57

DECLARE
c1 SYS_REFCURSOR;
r1 EMP%ROWTYPE;
r2 DEPT%ROWTYPE;
BEGIN
OPEN c1 FOR SELECT * FROM emp;

Loop
FETCH c1 INTO r1;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(r1.ename || ' ' || r1.sal);
End Loop;

CLOSE c1;

OPEN c1 FOR SELECT * FROM dept;

LOOP
FETCH c1 INTO r2;
EXIT WHEN c1%notfound;
dbms_output.put_line(r2.deptno || ' ' || r2.dname || ' ' || r2.loc);
END LOOP;

CLOSE c1;
END;
/

Differences between Simple Cursor and Ref Cursor:

ORACLE 4PM PLSQL Page 58

Simple Cursor Ref Cursor

• One Cursor can be used for • Same Cursor can be used for multiple
1 SELECT query only. SELECT queries.

• It is static. • It is Dynamic.
SELECT query is fixed SELECT query can be changed

• No data type is there • SYS_REFCURSOR data type is used for

REF CURSOR

• At the time of declaring cursor, • At the time of Opening Cursor, we

we specify SELECT query specify SELECT query

• It cannot be used as procedure • It can be used as Procedure Parameter

parameter. Because, it has
no data type

Types of Cursors:

2 Types:

• Implicit Cursor
• Explicit Cursor

Implicit Cursor:
• A cursor which is defined implicitly by ORACLE is called
"Implicit Cursor".
• To process any DRL command or DML command a Cursor
will be used implicitly.
• This Implicit Cursor name is: SQL

Example on Implicit Cursor:

BEGIN
UPDATE emp SET sal=sal+1000;
dbms_output.put_line(SQL%ROWCOUNT || ' rows updated');
END;
/

• Explicit Cursor:
A cursor which is defined explicitly by user is called

ORACLE 4PM PLSQL Page 59

• A cursor which is defined explicitly by user is called
"Explicit Cursor".

2 Types:
• Simple Cursor
• Ref Cursor

CURSOR:
GOAL: to hold multiple rows & process them one by one

4 steps:
DECLARE CURSOR c1 IS SELECT * FROM emp;
OPEN OPEN c1;
FETCH FETCH c1 INTO r;

it can fetch 1 record only

to fetch multiple records write this statement in loop

CLOSE CLOSE c1;

Cursor For Loop:

no need to open, fetch & close

Inline Cursor:
we specify select query in cursor for loop

Parameterized Cursor:
• A cursor which is declared with parameter
• At the time of opening cursor we pass value to cursor
parameter

Ref Cursor:
Same cursor can be used for multiple SELECT queries

Cursor Attributes:
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT

ORACLE 4PM PLSQL Page 60

Exception Handling
Tuesday, June 27, 2023 5:21 PM

Types of Errors:

3 Types:

• Compile Time Error

• Logical Error
• Run Time Error

Compile Time Error:

This error occur due to syntax mistakes

Examples:
missing ;
missing )
missing '

Logical Error:
• This error occur due to mistake in logic.
• Due to logic mistake, it gives wrong result.
• Programmer is responsible for writing correct logic.

Example:
withdraw
v_balance := v_balance + v_amount

50000 + 10000 = 60000

Program [100 lines]

Run Time Error:
• This error occur due to several reasons
…..
such as:
…….
○ when we try to divide with zero …….. 100/0 => RTE => 50th line
○ if record is not found ……
○ opening the opened cursor ………
○ fetching record without opening cursor
○ inserting duplicate value in PK
Problem:
Abnormal Termination will occur

Exception => Run Time Error

Problem:
abnormal termination

ORACLE 4PM PLSQL Page 61

Exception Handling:
• The way handling run time errors is called
"Exception handling".
• It avoids abnormal Termination.

• We add "EXCEPTION" block to handle Run

Time Error.

Syntax of Exception Of Handling:

DECLARE
--declare the variables
BEGIN
--executable statements

EXCEPTION
WHEN <exception_name> THEN
--Handling Code
WHEN <exception_name> THEN
--Handling Code
.
.
END;
/

Program to divide 2 numbers:

DECLARE
x NUMBER(4);
y NUMBER(4);
z NUMBER(4);
BEGIN
x := &x;
y := &y;

z := x/y;

dbms_output.put_line('quotient=' || z);

EXCEPTION
WHEN zero_divide THEN
dbms_output.put_line('you cannot divide with 0');
WHEN value_error THEN
dbms_output.put_line('wrong input or value out of range');
WHEN others THEN
dbms_output.put_line('Run Time Error Occurred');
ORACLE 4PM PLSQL Page 62
dbms_output.put_line('Run Time Error Occurred');
END;
/

Output:
enter value for x: 10
enter value for y: 2
quotinet=5

Output:
enter value for x: 10
enter value for y: 2
quotient=5

Output:
enter value for x: 10
enter value for y: 0
you cannot divide with 0

Output:
enter value for x: 123456
enter value for y: 2
wrong input or value out of range

Output:
enter value for x: 'raju'
enter value for y: 2
wrong input or value out of range

OTHERS:
It can handle any type of exception

Example On Others:

program to divide 2 numbers:

DECLARE
x NUMBER(4);
y NUMBER(4);
z NUMBER(4);
BEGIN
x := &x;
y := &y;

z := x/y;

dbms_output.put_line('quotient=' || z);

ORACLE 4PM PLSQL Page 63

EXCEPTION
WHEN others THEN
dbms_output.put_line('Run Time
Error Occured');
END;
/

Output:
enter value for x: 10
enter value for y: 2
quotient=5

Output:
enter value for x: 10
enter value for y: 0
Run Time Error Occured

Output:
enter value for x: 123456
enter value for y: 2
Run Time Error Occured

Output:
enter value for x: 'raju'
enter value for y: 2
Run Time Error Occured

Types of Exceptions:

2 Types:

• Built-In Exception / Predefined Exception

• User-Defined Exception

Built-In Exception / Predefined Exception:

• The Exception which is already defined by Oracle Developers is
called "Built-In Exception".
• These will be raised implicitly by ORACLE.

Examples:
zero_divide
value_error
no_data_found
dup_val_on_index
too_many_rows

ORACLE 4PM PLSQL Page 64

too_many_rows
cursor_already_open
invalid_cursor
invalid_number

User-Defined Exception:
• The exception which is defined by the user is called "User-
Defined Exception".
• We can define our exceptions. These are called "User-Defined
Exceptions".
• User-Defined Exception must be raised explicitly using RAISE
keyword.

Examples:
Sunday_Not_Allow
One_Divide
Invalid_Marks
xyz

Built-In Exceptions:

zero_divide:
When we try to divide with zero, zero_divide exception will
be raised.

Example:
z := 5/0; --raises zero_divide exception

value_error:
when we give wrong input or when value is out of range then
value_error exception will be raised.

Examples:
x NUMBER(4);

x := &x;
enter value for x: 123456 => value_error will be raised

x := &x;
enter value for x: 'raju' => value_error will be raised

No_Data_Found:

ORACLE 4PM PLSQL Page 65

No_Data_Found:
When we retrieve the record if record is not found in table
then NO_Data_Found Exception will be raised.

Example: emp
v_empno := &empno; empno ename
1001
enter value for empno:5678
1002
--No_Data_Found Exception will be raised
..
1010

Example on No_Data_Found:

Program to display emp record of given empno:

DECLARE
v_empno EMP.EMPNO%TYPE;
r EMP%ROWTYPE;
BEGIN
v_empno := &empno;

SELECT * INTO r FROM emp WHERE empno=v_empno;

dbms_output.put_line(r.ename || ' ' || r.sal);

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('employee not existed with this empno');
END;
/

Output:
enter value for empno: 7369
SMITH 3080

Output:
enter value for empno: 1234
employee not existed with this empno

Note:
When Run Time Error Occurs execution goes EXCEPTION block. It searches for
corresponding Exception Name.

ORACLE 4PM PLSQL Page 66

corresponding Exception Name.
If Exception Name is Found that handling code will be executed.

Dup_Val_On_Index:
When we try to insert duplicate value in Primary Key Column
then DUP_VAL_ON_INDEX excpetion will be raised.

Example On Dup_Val_On_Index:

CREATE TABLE student

(
sid NUMBER(4) PRIMARY KEY,
sname VARCHAR2(10)
);

Program to insert student record in STUDENT table:

DECLARE
r STUDENT%ROWTYPE;
BEGIN
r.sid := &sid;
r.sname := '&sname';

INSERT INTO student VALUES(r.sid, r.sname);

COMMIT;

dbms_output.put_line('record inserted');

EXCEPTION
WHEN dup_val_on_index THEN
dbms_output.put_line('PK does not accept duplicate
value');
END;
/

Output:
Enter value for sid: 10
Enter value for sname: AA
record inserted

Output:
Enter value for sid: 10
Enter value for sname: BB
PK does not accept duplicate value

ORACLE 4PM PLSQL Page 67

Too_Many_Rows:
• If Select Query returns multiple rows then
TOO_MANY_ROWS exception will be raised.

Example on TOO_MANY_ROWS:

program to display the emp record based on job:

Cursor_Already_Open:
If we try to open opened cursor then
CURSOR_ALREADY_OPEN exception will be raised

Example On Cursor_Already_Open:

Program display all emp names and salaries:

DECLARE
CURSOR c1 IS SELECT * FROM emp;
r EMP%ROWTYPE;
BEGIN
OPEN c1;

OPEN c1;

LOOP
FETCH c1 INTO r;
EXIT WHEN c1%notfound;
dbms_output.put_line(r.ename || ' ' || r.sal);
END LOOP;

CLOSE c1;

EXCEPTION
WHEN cursor_already_open THEN
dbms_output.put_line('cursor is already opened');
END;
/

Invalid_Cursor:
If we try to fetch the record without opening cursor then
Invalid_Cursor Exception will be raised.

Example on Invalid_Cursor:

DECLARE
CURSOR c1 IS SELECT * FROM emp;
r EMP%ROWTYPE;
BEGIN

ORACLE 4PM PLSQL Page 68

LOOP
FETCH c1 INTO r;
EXIT WHEN c1%notfound;
dbms_output.put_line(r.ename || ' ' || r.sal);
END LOOP;

CLOSE c1;

EXCEPTION
WHEN invalid_cursor THEN
dbms_output.put_line('cursor not opened');

END;
/

Invalid_Number:
When ORACLE is unable to convert string to number
then Invalid_Number Exception will be raised

Example on Invalid_Number:

CREATE TABLE student

(
sid NUMBER(4),
sname VARCHAr2(10),
m1 NUMBER(3)
);

BEGIN
INSERT INTO student VALUES(&sid, '&sname', &m1);
COMMIT;

dbms_output.put_line('record inserted');

EXCEPTION
WHEN invalid_number THEN
dbms_output.put_line('expecting number here');
END;
/

Output:
Enter value for sid: 101
Enter value for sname: AA
Enter value for m1: 55
record inserted

Output:
Enter value for sid: 102
Enter value for sname: BB
ORACLE 4PM PLSQL Page 69
Enter value for sname: BB
Enter value for m1: '90'
record inserted

Output:
Enter value for sid: 103
Enter value for sname: CC
Enter value for m1: 'ABC'
expecting number here

Defining User-Defined Exception:

To define user-define exception follow 3 steps. They are:

• Declare the Exception
• Raise the Exception
• Handle the Exception

Declare the Exception:

Syntax:

<exception_name> EXCEPTION;

Example:
one_divide EXCEPTION;

• EXCEPTION is the data type.

• It is used to declare Exception Name.

Raise the Exception:

Syntax:

RAISE <exception_name>;

Example:
RAISE one_divide;

• RAISE is the keyword

• RAISE keyword is used to raise the exception.
• When exception is raised, execution goes to EXCEPTION block.
• IN EXCEPTION block if it is handled executes Handling Code. If it is
not handled it gives error as "Unhandled User-Defined Exception"

ORACLE 4PM PLSQL Page 70

not handled it gives error as "Unhandled User-Defined Exception"

Handle the Exception:

Syntax:

EXCEPTION
WHEN <exception_name> THEN
--Handling Code

Example:
EXCEPTION
WHEN one_Divide THEN
dbms_output.put_line('denominator cannot be 1');

To handle the exception define EXCEPTION block.

Built-In Exception User-Defined exception

1 step: 3 steps:
Handling the Exception declare
raise
handle

Example on User-Defined Exception:

write a program to divide 2 numbers. if user is giving

denominator as 1 then raise the exception and handle it. if
user is giving denominator as 0 zero_divide exception will be
raised. Handle it:

DECLARE
x NUMBER(4);
y NUMBER(4);
z NUMBER(4);
one_divide EXCEPTION;
BEGIN
x := &x;
y := &y;

IF y=1 THEN

ORACLE 4PM PLSQL Page 71

IF y=1 THEN
RAISE one_divide;
END IF;

z := x/y;

dbms_output.put_line('result=' || z);

EXCEPTION
WHEN one_divide THEN
dbms_output.put_line('denominator cannot be 1');
WHEN zero_divide THEN
dbms_output.put_line('denominator cannot be 0');
END;
/

Output:
Enter value for x: 10
Enter value for y: 2
result=5

Output:
Enter value for x: 10
Enter value for y: 0
denominator cannot be 0

Output:
Enter value for x: 10
Enter value for y: 1
denominator cannot be 1

Example on User-defined Exception:

Program increase salary of given empno with given amount.

If user tries to update on Sunday then raise the exception
and handle it:

DECLARE
v_empno EMP.EMPNO%TYPE;
v_amount FLOAT;
sunday_not_allow EXCEPTION;
BEGIN
v_empno := &empno;
v_amount := &amount;

IF to_char(sysdate,'dy')='sun' THEN

ORACLE 4PM PLSQL Page 72

IF to_char(sysdate,'dy')='sun' THEN
RAISE sunday_not_allow;
END IF;

UPDATE emp SET sal=sal+v_amount WHERE

empno=v_empno;
COMMIT;

dbms_output.put_line('salary increased');

EXCEPTION
WHEN sunday_not_allow THEN
dbms_output.put_line('you cannot update on sunday');
END;
/

on working days [from Monday to Saturday]:

Output:
Enter value for empno: 7369
Enter value for amount: 1000
salary increased

on Sunday:
Output:
Enter value for empno: 7369
Enter value for amount: 1000
you cannot update on sunday

Note:
We can raise the error using 2 ways. They are:
• using RAISE keyword
• using RAISE_APPLICATION_ERROR() procedure

RAISE_APPLICATION_ERROR():
• RAISE_APPLICATION_ERROR() is a built-in procedure.
• It is used to raise the error with our own error code and our own error message.

Syntax:
RAISE_APPLICATION_ERROR(user_defined_error_code,
user_defined error_message);

user_defined_error_code ranges from -20000 to -20999

Example:
RAISE_APPLICATION_ERROR(-20050,'divisor cannot be 1');

ORACLE 4PM PLSQL Page 73

Output:
ERROR
ORA-20050: divisor cannot be 1

Example on Raise_Application_Error():

Program to increase salary of given empno with given amount.

If Sunday raise the error using raise_application_error():

DECLARE
v_empno EMP.EMPNO%TYPE;
v_amount FLOAT;
BEGIN
v_empno := &empno;
v_amount := &amount;

IF to_char(sysdate,'dy')='sun' THEN
RAISE_APPLICATION_ERROR(-20070,'you cannot update on sunday');
END IF;

UPDATE emp SET sal=sal+v_amount WHERE empno=v_empno;

COMMIT;

dbms_output.put_line('salary increased');

END;
/

On Sunday:

Output:
ERROR at line 1:
ORA-20070: you cannot update on sunday
ORA-06512: at line 9

Differences between RAISE & RAISE_APPLICATION_ERROR():

ORACLE 4PM PLSQL Page 74

RAISE RAISE_APPLICATION_ERROR()

• is a keyword • is a procedure

• It raises exception using • It raises exception using error code

name

Example: Example:
RAISE Sunday_not_allow; Raise_Application_Error(-20070,'you cannot update on sunday');

Note:
-1 Error Code
unique constraint violated Error Message
DUP_VAL_ON_INDEX Error Name

-1476 Error Code

divisor is equal to zero Error Message
ZERO_DIVIDE Error Name

-2290 Error Code

check constraint violated Error Message
NO ERROR NAME Defined for it Error Name

-1400 Error Code

cannot insert null Error message
NO ERROR NAME Defined for it Error Name

Pragma Exception_Init():

• Some errors have name. Some errors does not have name.
• TO define name for unnamed Exception, we use
PRAGMA EXCEPTION_INIT()

• To handle the Exception, name is required in EXCEPTION block. If name

is not there we cannot handle it. That's why we need to define a name
for unnamed exception.

Syntax:
PRAGMA EXCEPTION_INIT(<exception_name>, <error_code>)

ORACLE 4PM PLSQL Page 75

Example:

CREATE TABLE student

(
sid NUMBER(4) PRIMARY KEY,
sname VARCHAR2(10),
m1 NUMBER(3) CHECK(m1 BETWEEN 0 AND 100)
);

PRAGMA EXCEPTION_INIT(check_violate, -2290);

PRAGMA EXCEPTION_INIT() => is compiler directive

• Any statement started with PRAGMA is called Compiler

Directive [command].
• It is command to the compiler. PRAGMA tells that
before compiling the program execute this line

Example On Pragma Exception_init():

define name for check constraint violation and handle it:

DECLARE
r STUDENT%ROWTYPE;
check_violate EXCEPTION;
PRAGMA EXCEPTION_INIT(check_violate,-2290);
BEGIN
r.sid := &sid;
r.sname := '&sname';
r.m1 := &m1;

INSERT INTO student VALUES(r.sid, r.sname, r.m1);

COMMIT;

dbms_output.put_line('record inserted');

EXCEPTION
WHEN dup_val_on_index THEN
dbms_output.put_line('PK does not dup value');
WHEN check_violate THEN
dbms_output.put_line('check constraint violated');

END;
/

SQLERRM & SQLCODE:

ORACLE 4PM PLSQL Page 76

SQLERRM & SQLCODE:

SQLERRM SQLERRM function returns error message

SQLCODE SQLCODE function returns error code

Example on SQLERRM:

DECLARE
x NUMBER(4);
y NUMBER(4);
z NUMBER(4);
BEGIN
x := &x;
y := &y;

z := x/y;

dbms_output.put_line('result=' || z);

EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
END;
/

EXCEPTION Run Time Error

EXCEPTION HANDLING • The way of handling run time error
• Exception block is used to handle
the exception

Types of exceptions:

2 Types:
Built-In Exception already defined by oracle developers
these will be raised implicitly by oracle
Examples:
zero_divide
value_error
no_data_found
too_many_rows

ORACLE 4PM PLSQL Page 77

1 step:
Handle the exception
User-Defined exception we can define our own exception
we have to raise it explicitly

3 steps:
declare
raise
handle

Raise_Application_error():
• is a procedure
• used to raise the error with our own error code & message

Pragma Exception_Init():
some exceptions does not have name
to define name to unnamed exception, we use it

ORACLE 4PM PLSQL Page 78

Procedures
Friday, June 30, 2023 4:28 PM

PL/SQL Basics

Control Structures
Coding

Cursors

Exception handling

Coding has to be implemented in Procedures, Functions and Packages

concepts

Procedure:
• Procedure is a named block of statements that gets
executed on calling.

In C:
Example: Function:
dbms_output.put_line('HELLO'); --procedure call is a set of statements
gets executed on calling
dbms_output package
In Java:
put_line() procedure
Method:
is a set of statements
gets executed on calling
PROCEDURE put_line(STRING s) AS
BEGIN
--Statements
END;

Procedure:
• Procedure is a named block of statements that gets
executed on calling.
• Every Procedure is defined to perform specific action.

Examples:
Withdraw Procedure
Deposit Procedure
DELETE_EMP PROCEDURE

Types of Procedures:

ORACLE 4PM PLSQL Page 79

Types of Procedures:

2 Types:
• Stored Procedure
• Packaged Procedure

Stored Procedure:
A procedure which is defined in SCHEMA is called
"Stored Procedure / Stand Alone Procedure".
Note:
Example: Schema = user
SCHEMA c##batch4pm
PROCEDURE withdraw => Stored Procedure

Packaged Procedure:
A procedure which is defined in PACKAGE is called
"Packaged Procedure".

Example:
SCHEMA c##batch4pm
PACKAGE bank
PROCEDURE withdraw => Packaged Procedure

Syntax to define Stored Procedure:

CREATE [OR REPLACE] PROCEDURE <procedure_name>[(<paramter_list>)]

IS / AS
--declare the variables
BEGIN
--executable statements
END;
/

Example on Stored Procedure:

define a stored procedure to add 2 numbers:
Note: don't define sizes for parameters
addition(x NUMBER(4), y NUMBER(4)) => Invalid

CREATE OR REPLACE PROCEDURE

addition(x NUMBER, y NUMBER)
IS
z NUMBER(4);
BEGIN

ORACLE 4PM PLSQL Page 80

BEGIN
z := x+y;
dbms_output.put_line('sum=' || z);
END;
/

• Save above code in "D:" Drive, "ORA4PM" Folder

with the name "ProcedureDemo.sql".

• Compile the procedure as following: ORACLE DB

SQL> d:\ora4pm\ProcedureDemo.sql PROCEDURE addition

Output:
………
Procedure Created compiled code
………

When procedure is created,

procedure's compiled code will be stored in procedure.
This procedure will be stored in ORACLE DB.

Procedure is a Db Object.

Calling Stored Procedure:

3 ways:
• From SQL prompt
• From PL/SQL program [main program]
• From Front-End Application [Java, C#, Python]

From SQL prompt:

SQL> EXEC addition(2,3); --procedure call

Output:
sum=5

EXEC[UTE]:
EXEC command is used to call the procedure from SQL prompt.

From PL/SQL program [main program]:

DECLARE
a INT;
b INT;
BEGIN
a := &a;
b:=&b;

addition(a,b); --procedure call

ORACLE 4PM PLSQL Page 81
addition(a,b); --procedure call
END;
/

Note:
A procedure contains 2 parts. They are:
• Procedure Header
Formal Parameters
• Procedure Body
Procedure
CREATE OR REPLACE PROCEDURE Procedure Header
addition(x NUMBER, y NUMBER)
IS +
z NUMBER(4);
BEGIN Procedure Body
z := x+y;
dbms_output.put_line('sum=' || z);
END;
/

Procedure Call:
EXEC addition(2,3); --2,3 => Actual Parameters

Parameter:
• Parameter is a local variable that is declared in Procedure Header

Syntax to define parameter:

<parameter_name> [<parameter_mode>]<data_type>

parameter modes:
• IN [default]
• OUT
• IN/OUT

Formal Parameter & Actual Parameter:

Formal Parameter:
A parameter which is declared in procedure header is
called "Formal Parameter"

ORACLE 4PM PLSQL Page 82

called "Formal Parameter"

Actual Parameter:
A parameter which is in procedure call is called "Actual parameter"

Parameter Modes:

There are 3 parameter modes. They are:

• IN
• OUT
• IN OUT

IN:
• It is default one. If we don't specify parameter mode default mode
will be taken as IN.
• In Parameter can be also called input parameter.
• It captures input from out of procedure.
• It is used to bring value into procedure from out of procedure.
• In procedure call, it can be variable or constant.
• It is read-only parameter. We cannot change its value.

Example:
CREATE OR REPLACE PROCEDURE
addition(x IN NUMBER, y IN NUMBER)
IS
z NUMBER(4);
BEGIN
x := 100; --ERROR: x is read-only parameter
z := x+y;
dbms_output.put_line('sum=' || z);
END;
/

Output:
procedure created with compilation errors

SQL> show error

expression 'X' cannot be used as an assignment target

OUT:
• OUT keyword is used to declare OUT parameter.
• OUT parameter can be also called as Output Parameter.
• It is used to send the result out of the procedure.
• It is read-write parameter.
• In procedure call, it must be variable only. it cannot be constant

IN OUT:
• IN OUT keyword is used to declare it.
IN OUT parameter can be used to bring value into procedure and
ORACLE 4PM PLSQL Page 83
○ IN OUT parameter can be used to bring value into procedure and
same parameter can be used to the result out of procedure.
○ It is read-write parameter.
○ In procedure call, it must be variable only.

IN used to bring input into procedure from out of procedure

read-only parameter
in procedure call, it can be variable or constant
OUT used to send output to out of procedure
read -write parameter
in procedure call, it must be variable only

Example on OUT parameter:

Define a procedure to add 2 numbers and send the result

out of procedure:

CREATE OR REPLACE PROCEDURE

addition(x IN NUMBER, y IN NUMBER, z OUT NUMBER)
IS
BEGIN
z := x+y;
END;
/

Calling from SQL prompt:

SQL> variable a NUMBER
SQL> EXEC addition(5,4,:a);
SQL> PRINT a
Output:
a
--
9

In above example a is called Bind variable

Bind Variable:
○ A variable which is declared at SQL prompt is called
Bind variable.
○ Its lifetime is limited to session
○ To use bind variable we use bind operator : [colon]

Calling from PL/SQL program [main program]:

ORACLE 4PM PLSQL Page 84

Calling from PL/SQL program [main program]:

DECLARE
a NUMBER(4);
b NUMBER(4);
c NUMBER(4);
BEGIN
a := &a;
b := &b;

addition(a,b,c);

dbms_output.put_line('sum=' || c);
END;
/

Define a procedure to update salary of an employee with specific

amount:

CREATE OR REPLACE PROCEDURE

update_Salary(p_empno NUMBER, p_amount FLOAT)
IS
BEGIN
UPDATE emp SET sal=sal+p_amount WHERE empno=p_empno;
COMMIT;

dbms_output.put_line('salary increased');
END;
/

Calling procedure from SQL prompt:

SQL> EXEC update_salary(7369,2000);

Outtut:
Salary Increased

Assignment:
Define a procedure to delete an employee record

procedure call: delete_emp(7900);

Define a procedure to increase salary of an employee

with specific amount. Updated salary send out of the

ORACLE 4PM PLSQL Page 85

with specific amount. Updated salary send out of the
procedure:

CREATE OR REPLACE PROCEDURE

update_salary(p_empno IN NUMBER, p_amount IN FLOAT,
p_sal OUT NUMBER)
IS
BEGIN select experience(7369)
UPDATE emp SET sal=sal+p_amount WHERE
empno=p_empno; oracle 23c WINDOWS
COMMIT; se

dbms_output.put_line('salary increased');

SELECT sal INTO p_sal FROM emp WHERE

empno=p_empno;

END;
/

Calling procedure from SQL Prompt:

SQL> variable s number

SQL> EXEC update_salary(7499,1000,:s);
SQL> PRINT s
Output:
s
----
7000

ACCOUNT
AcNo Name Balance
1001 A 500000
1002 B 800000

CREATE TABLE account

(
acno NUMBER(4),
name VARCHAR2(10),
balance NUMBER(9,2)
);

ORACLE 4PM PLSQL Page 86

INSERT INTO account VALUES(1001,'A',500000);
INSERT INTO account VALUES(1002,'B',800000);
COMMIT;

Define a procedure to perform withdraw operation:

Procedure:

CREATE OR REPLACE PROCEDURE

withdraw(p_acno NUMBER, p_amount
NUMBER)
AS
v_balance account.balance%type;
BEGIN
SELECT balance INTO v_balance FROM
account WHERE acno=p_acno;

IF p_amount>v_balance THEN
RAISE_APPLICATION_ERROR(-20050,'
Insufficient Balance');
END IF;

UPDATE account SET balance=balance-

p_amount WHERE acno=p_acno;
COMMIT;

dbms_output.put_line('amount debited');
END;
/

Calling Procedure:

SQL> EXEC withdraw(1001,700000);

Output:
ORA-20050: Insufficient Balance

SQL> EXEC withdraw(1001,100000);

Output:
amount debited

ORACLE 4PM PLSQL Page 87

Define a procedure to perform deposit operation:

CREATE OR REPLACE PROCEDURE

deposit(p_acno NUMBER, p_amount NUMBER) AcNo Name Balance
AS
1001 A 500000
BEGIN
UPDATE account SET balance=balance+p_amount 1002 B 800000
WHERE acno=p_acno;
COMMIT;

dbms_output.put_line('amount credited');
END;
/

Calling procedure:

SQL> EXEC deposit(1002,100000);

Output:
amount credited

Define a procedure to perform deposit operation.

After depositing, updated balance send out of the procedure:

CREATE OR REPLACE PROCEDURE

ACCOUNT
deposit(p_acno IN NUMBER, p_amount IN NUMBER,
p_balance OUT NUMBER) ACNO NAME BALANCE
AS 1001 A 400000
BEGIN
UPDATE account SET balance=balance+ p_amount
WHERE acno=p_acno;
COMMIT;
dbms_output.put_line('transaction successful');
deposit(1001,200000,:b);
SELECT balance INTO p_balance FROM account
WHERE acno=p_acno;
END;
/

Calling procedure:

SQL> VARIABLE b NUMBER

SQL> EXEC deposit(1001,200000,:b);

transaction successful

PL/SQL procedure successfully completed.

SQL> print b

ORACLE 4PM PLSQL Page 88

SQL> print b

B
----------
600000

Assignment:
Define a procedure to transfer the funds from one account to another:

AcNo Name Balance

1001 A 500000 procedure call:
fund_transfer(1001,1002,100000);
1002 B 800000

CREATE OR REPLACE PROCEDURE

fund_transfer(p_from NUMBER, p_to NUMBER, p_amount NUMBER)
AS
check sufficient balance is there or not
if sufficient balance is not there raise the error

if sufficient balance is there

UPDATE from account balance=> 1001 => balance-p_amount

UPDATE to account balance => 1002 => balance+p_amount
COMMIT

display message => funds transferred

Example on IN OUT parameter:

Define a procedure to find square value. Take parameter as

IN OUT:

CREATE OR REPLACE PROCEDURE

square(x IN OUT NUMBER)
rewrites as
AS
BEGIN BEGIN
EXEC addition(5,2);
x := x*x; addition(5,2);
END; END;
/ a

Calling Procedure: 5 25
begin
:a := 5;
SQL> variable a number
end;
SQL> exec :a := 5;
SQL> exec square(:a);
begin
SQL> PRINT a
square(:a);
Output:
end;
ORACLE 4PM PLSQL Page 89
SQL> exec :a := 5;
SQL> exec square(:a);
begin
SQL> PRINT a
square(:a);
Output:
end;
a
---
25

Parameter mapping techniques / Parameter association techniques /

Parameter notations:

There are 3 parameter mapping techniques. They are:

• Positional mapping
• Named Mapping
• Mixed mapping

Positional Mapping:
In positional mapping, actual parameter will be mapped with
formal parameter based on position.

Example:

PROCEDURE addition(x INT, y INT, z INT)

positional mapping

procedure call: addition(10,20,30);

Named Mapping:
In named mapping, actual parameter will be mapped with
formal parameter based on name.
=> Association operator
Example:

PROCEDURE addition(x INT, y INT, z INT)

Named mapping

procedure call: addition(z=>10,x=>20,y=>30);

Mixed Mapping:
In mixed mapping, actual parameters will be mapped with

ORACLE 4PM PLSQL Page 90

Mixed Mapping:
In mixed mapping, actual parameters will be mapped with
formal parameters based on positions and names.

Example:

PROCEDURE addition(x INT, y INT, z INT)

positional named

procedure call: addition(10,z=>20,y=>30);

procedure call: addition(z=>10,20,30);

--ERROR: after named mapping we cannot give positional mapping

Example:

CREATE OR REPLACE PROCEDURE

addition(x INT, y INT, z INT)
AS
BEGIN
dbms_output.put_line('sum=' || (x+y+z));
dbms_output.put_line('x=' || x);
dbms_output.put_line('y=' || y);
dbms_output.put_line('z=' || z);
END;
/

procedure calls:

SQL> EXEC addition(10,20,30);

sum=60
x=10
y=20
z=30

SQL> EXEC addition(z=>10,x=>20,y=>30);

sum=60
x=20
y=30
z=10

SQL> EXEC addition(z=>10,20,30);

BEGIN addition(z=>10,20,30); END;

*
ERROR at line 1:

ORACLE 4PM PLSQL Page 91

ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00312: a positional parameter association may not follow a
named association
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> EXEC addition(10,z=>20,y=>30);

sum=60
x=10
y=30
z=20

Pragma Autonomous_Transaction:

Main Program
Procedure update_salary

BEGIN TRANSACTION T1 UPDATE => 7369,2000

UPDATE => 7499,1000 ROLLBACK; --cancels T1 Transaction
update_salary(7369,2000);
commit; --saves T1
END TRANSACTION T1

EMP
0 salaries are updated EMPNO ENAME SAL
7369 SMITH 5000
Rollback cancels Procedure action and main 7499 ALLEN 3000
action. Because, the transaction started in main
program applied for procedure

• By default, A Transaction started in main program will be

applied for procedure also.
• A separated transaction will not be created for procedure
by default.
• To create separate transaction for procedure, we use
"PRAGMA AUTONOMOUS_TRANSACTION".

Main Program
Procedure update_salary

BEGIN TRANSACTION T1 BEGIN TRANSACTION T2

UPDATE => 7499,1000 PRAGMA AUTONOMOUS_TRANSACTION
update_salary(7369,2000); UPDATE => 7369,2000
commit; --saves T1 ROLLBACK; --cancels T2 Transaction
END TRANSACTION T1 END TRANSACTION T2
ORACLE 4PM PLSQL Page 92
UPDATE => 7499,1000 PRAGMA AUTONOMOUS_TRANSACTION
update_salary(7369,2000); UPDATE => 7369,2000
commit; --saves T1 ROLLBACK; --cancels T2 Transaction
END TRANSACTION T1 END TRANSACTION T2

EMP
EMPNO ENAME SAL
1 salary updated 7369 SMITH 5000
7499 ALLEN 3000 4000

Example on Pragma Autonomous_Transaction:

Define a procedure to update salary of an employee with specific

amount:

CREATE OR REPLACE PROCEDURE

update_salary(p_empno NUMBER, p_amount NUMBER)
AS
BEGIN
UPDATE emp SET sal=sal+p_amount WHERE empno=p_empno;
ROLLBACK;
END;
/

main program:

BEGIN
UPDATE emp SET sal=sal+1000 WHERE empno=7499;
update_salary(7369,2000);
COMMIT;
END;
/

In the above program, transaction started in main program

applied for procedure also.

procedure's ROLLBACK is canceling procedure updation [7369]

and main updation [7499].

If we want to create a separate transaction for procedure use

PRAGMA AUTONOMOUS_TRANSACTION

Define a procedure to update salary of an employee with specific

amount. create a separate for procedure:

CREATE OR REPLACE PROCEDURE

update_salary(p_empno NUMBER, p_amount NUMBER)

ORACLE 4PM PLSQL Page 93

update_salary(p_empno NUMBER, p_amount NUMBER)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE emp SET sal=sal+p_amount WHERE empno=p_empno;
ROLLBACK;
END;
/

Pragma compiler directive

directive => command
it is command to compiler
it tells before compiling program execute this line

main program:

BEGIN
UPDATE emp SET sal=sal+1000 WHERE empno=7499;
update_salary(7369,2000);
COMMIT;
END;
/

In the above example, a separate will be created for procedure.

ROLLBACK in procedure cancels only procedure actions.

Dropping a procedure:

Syntax:
DROP PROCEDURE <procedure_name>;

Example:
DROP PROCEDURE update_salary;

Granting permission to other user to call our procedure:

c##batch4pm:

create procedure as follows:

CREATE OR REPLACE PROCEDURE

update_salary(p_empno NUMBER, p_amount NUMBER)
ORACLE 4PM PLSQL Page 94
update_salary(p_empno NUMBER, p_amount NUMBER)
AS
BEGIN
UPDATE emp SET sal=sal+p_amount WHERE empno=p_empno;
COMMIT;
dbms_output.put_line('salary increased');
END;
/

PROCEDURE update_salary
EXEC update_salary(7369,2000); --procedure call

GRANT execute ON update_salary TO c##batch8pm;

c##batch8pm
EXEC c##batch4pm.update_salary(7499,1000);
--ERROR: no permission

EXEC c##batch4pm.update_salary(7499,1000);
Output:
salary increased

user_procedures:
• it is a system table / predefined table / readymade table.
• It maintains all procedures, functions, packages and triggers
information

DESC user_procedures;

SELECT object_name, object_type FROM user_procedures;

user_source:
• It is a system table / readymade table.
• It maintains all procedures, functions, packages and
triggers information

DESC user_source;

to see procedures list:

SELECT DISTINCT NAME, TYPE FROM user_source;

to see procedure's code:

ORACLE 4PM PLSQL Page 95

SELECT LINE, TEXT FROM user_source
WHERE name='UPDATE_SALARY';

Note:
Within the Schema procedure name must be unique

CREATE PROCEDURE • it creates new procedure

demo • if procedure name already used it gives
error

CREATE OR REPLACE • if procedure is not created it creates new

PROCEDURE demo procedure.
• if procedure already created with this
name previous code will be replaced with
new code

Note:
To perform DML operation, define PROCEDURE.

Advantages of Procedure:
• improves the performance. It holds compiled code
• provides reusability
• reduces length of code
• provides security. authorized users only call the procedure
• better maintenance

ORACLE 4PM PLSQL Page 96

Stored Functions
Wednesday, July 5, 2023 4:15 PM

Note:
• Procedure may or may not return the value.
• Returning value is optional in procedure.
• To return value in procedure, we use OUT parameter.
• A procedure can return multiple values.

Function:
• Function is a named block of statements that gets executed on calling.

• Function returns the value. Here, returning value is mandatory.

• A Function can return one value only.

• To perform fetch [select] operations or calculations define FUNCTION

whereas to perform DML operations define PROCEDURE.

• Don't define a function to perform DML operation.

• Always use IN parameters only in FUNCTION.

Don't use OUT or IN PUT parameters in FUNCTION.

Examples:
Opening_Account => INSERT => PROCEDURE
Withdraw => UPDATE => PROCEDURE
Deposit => UPDATE => PROCEDURE
Closing_Account => DELETE => PROCEDURE

Check_Balance => SELECT => FUNCTION

experience => Calculation => FUNCTION

Types of Functions:

2 Types:

ORACLE 4PM PLSQL Page 97

• Stored Function
• Packaged Function

Stored Function:
• A function which is defined in SCHEMA is called "Stored Function".

Example:
SCHEMA c##batch4pm
FUNCTION check_balance => Stored Function

Packaged Function:
• A function which is defined in PACKAGE is called "Packaged Function".

Example:
SCHEMA c##batch4pm
PACKAGE bank
FUNCTION check_balance => Packaged Function

Syntax of defining stored function:

CREATE [OR REPLACE] FUNCTION

<name>(<parameter_list>) RETURN <type>
IS / AS
--declare the variables
BEGIN
--executable statements
RETURN <expression>;
END;
/

Example on defining stored function:

Define a function to multiply 2 numbers:

CREATE OR REPLACE FUNCTION

ORACLE 4PM PLSQL Page 98
CREATE OR REPLACE FUNCTION
product(x INT, y INT) RETURN INT
AS
z INT;
BEGIN
z := x*y;
RETURN z;
END;
/

Save above function in D: Drive, ORA4PM folder with the file

name "FunctionDemo.sql".

Open sqlplus

Oracle DB
compile the function as following:
SQL> @d:\ora4pm\FunctionDemo.sql
Function Product
Output:
Function created ……….
………..
compiled code

Calling a Function:

A function can be called in 3 ways:

• From SQL prompt
• From PL/SQL program [main program]
• From Front-End Application [Java, C#, Python]

From SQL prompt:

SQL> SELECT product(5,4) FROM dual;

Output:
20

From PL/SQL program:

DECLARE

ORACLE 4PM PLSQL Page 99

DECLARE
a INT;
b INT;
c INT;
BEGIN
a := &a;
b := &b;

c := product(a,b); --function call

dbms_output.put_line('product=' || c);
END;
/

Define a Function to check the balance of specific account:

ACCOUNT
ACNO NAME BALANCE
1001 A 600000
1002 B 900000

Check_balance(1001) => 600000

CREATE OR REPLACE FUNCTION

check_balance(p_acno NUMBER) RETURN NUMBER
AS
v_balance ACCOUNT.BALANCE%TYPE;
BEGIN
SELECT balance INTO v_balance FROM account WHERE
acno=p_acno;

RETURN v_balance;
END;
/

Calling Function:

SQL> SELECT check_balance(1002) FROM dual;

ORACLE 4PM PLSQL Page 100

Output:

CHECK_BALANCE(1002)
-------------------
900000

Define a Function to calculate experience of specific employee:

CREATE OR REPLACE FUNCTION

experience(p_empno NUMBER) RETURN NUMBER
AS
v_hiredate DATE;
BEGIN
SELECT hiredate INTO v_hiredate FROM emp WHERE
empno=p_empno;

RETURN TRUNC((sysdate-v_hiredate)/365);
END;
/

Calling Function:

SQL> SELECT experience(7900) FROM dual;

Output:

EXPERIENCE(7900)
----------------
41

SQL> SELECT empno,ename,sal,

experience(empno) as experience FROM emp;

Output:
empno ename sal experience
----------- ------------ ------- -------------------
7369 42
7499 42

Define a function to display emps of specific dept:

ORACLE 4PM PLSQL Page 101

Define a function to display emps of specific dept:

CREATE OR REPLACE FUNCTION

getdept(p_deptno NUMBER) RETURN sys_refcursor
AS
c1 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR SELECT * FROM emp WHERE
deptno=p_deptno;

RETURN c1;
END;
/

Calling Function:

SQL> SELECT getdept(20) FROM dual;

Output:
--displays 20th dept emp records

Define a function to display topn salaried emp records:

CREATE OR REPLACE FUNCTION

topn(n INT) RETURN sys_refcursor
AS
c1 sys_refcursor;
BEGIN
OPEN c1 FOR SELECT * FROM (SELECT empno,ename,sal,
dense_rank() over(order by sal desc) as rank from emp)
where rank<=n;

RETURN c1;
END;
/

Calling Function:

SQL> SELECT topn(3) FROM dual;

Output:
--displays top 3 salaried emp records

ORACLE 4PM PLSQL Page 102

Differences between Procedure & Function:

Procedure Function

Procedure may or may not Function must return a value

return a value

Returning value is optional Returning value is mandatory

It can return multiple values It can return 1 value only

OUT parameter is used to return the RETURN keyword is used to return the
value value

To perform DML operations To perform fetch operations or

define PROCEDURE calculations define FUNCTION

It cannot be called from SQL It can be called from SQL command

command like SELECT, UPDATE like SELECT, UPDATE

EXEC command can be used to EXEC command cannot be used to call

call procedure function

Example: Withdraw() Example: Check_Balance()

Can we perform DML operations using FUNCTION?

Yes. It is not recommended. Because,

If we perform DML operation in FUNCTION it cannot be called

ORACLE 4PM PLSQL Page 103
If we perform DML operation in FUNCTION it cannot be called
from SQL command like SELECT, UPDATE. We can call it from
PL/SQL program.

CREATE OR REPLACE FUNCTION

demo RETURN INT
AS
BEGIN
UPDATE emp SET sal=sal+1000;
COMMIT;
RETURN 5;
END;
/

SQL> SELECT demo FROM dual;

SELECT demo FROM dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "C##BATCH4PM.DEMO", line 5

Can we define OUT parameters in FUNCTION?

Yes. But, it is not recommended.

Function standard is:

Function must return 1 value only

If we take OUT parameters in FUNCTION,

FUNCTION meaning will be changed.

If we take OUT parameters in FUNCTION, it cannot called from

SELECT command

Dropping Function:

Syntax:
DROP FUNCTION <name>;

ORACLE 4PM PLSQL Page 104

DROP FUNCTION <name>;

Example:
DROP FUNCTION product;

user_procedures:
It maintans all procedures, functions, packages and triggers
information

to see list of procedures and functions:

COLUMN object_name FORMAT a15

SELECT object_name, object_type FROM user_procedures;

user_source:
It maintans all procedures, functions, packages and triggers
information

to see list of procedures and functions:

SELECT DISTINCT name, type FROM user_source;

to see function code:

SELECT TEXT FROM user_source

WHERE name='EXPERIENCE';

A procedure or Function can be also called sub program.

Advantages of sub program:

• provides reusability.
• reduces length of code.
• security.
• improves the performance.
• Better Maintenance.

ORACLE 4PM PLSQL Page 105

ORACLE 4PM PLSQL Page 106
PACKAGES
Thursday, July 6, 2023 4:55 PM

PACKAGE:
• PACKAGE is a Database Object.
• PACKAGE is a collection of procedures, functions
and variables … etc.

Example:

PACKAGE bank

PROCEDURE opening_account => INSERT

PROCEDURE withdraw => UPDATE
PROCEDURE deposit => UPDATE
PROCEDURE closing_account => DELETE

FUNCTION check_balance => SELECT

FUNCTION transaction_statement => SELECT

Creating a Package:

To create the package follow 2 steps:

• Define PACKAGE SPECIFICATION

• Define PACKAGE BODY

Defining PACKAGE SPECIFICATION:

• In this, we declare procedures, functions and global variables

• Defining package specification means, we are making

members as public.

Syntax of Package Specification:

ORACLE 4PM PLSQL Page 107

Syntax of Package Specification:

CREATE [OR REPLACE] PACKAGE <name>

IS / AS
--declare the procedures
--declare the functions
--declare the global variables
END;
/

Defining PACKAGE BODY:

In this, we define body of procedures and functions.

Syntax to define Package Body:

CREATE [OR REPLACE] PACKAGE BODY <name>

IS / AS
--define the procedures
--define the functions
END;
/

Example on creating package:

PACKAGE math

PROCEDURE addition
FUNCTION product

Defining Package specification:

CREATE OR REPLACE PACKAGE math

AS
PROCEDURE addition(x INT, y INT);

ORACLE 4PM PLSQL Page 108

PROCEDURE addition(x INT, y INT);
FUNCTION product(x INT, y INT) RETURN INT;
END;
/

Defining Package Body:

CREATE OR REPLACE PACKAGE BODY math

AS
PROCEDURE addition(x INT, y INT)
AS
BEGIN
dbms_output.put_line('sum=' || (x+y));
END addition;

FUNCTION product(x INT, y INT) RETURN INT

AS
BEGIN
RETURN x*y;
END product;
END;
/

Calling Packaged procedure:

SQL> EXEC math.addition(5,4);

Calling Packaged Function:

SQL> SELECT math.product(2,3) FROM dual;

Define a package as following:

ORACLE 4PM PLSQL Page 109

PACKAGE HR

PROCEDURE insert_emp
PROCEDURE update_salary
PROCEDURE delete_emp

FUNCTION experience

Defining Package specification:

CREATE OR REPLACE PACKAGE hr

AS
PROCEDURE insert_emp(p_empno NUMBER, p_ename VARCHAR2);
PROCEDURE update_Salary(p_empno NUMBER, p_amount NUMBER);
PROCEDURE delete_emp(p_empno NUMBER);

FUNCTION experience(p_empno NUMBER) RETURN NUMBER;

END;
/

Defining Package Body:

CREATE OR REPLACE PACKAGE BODY hr

AS
PROCEDURE insert_emp(p_empno NUMBER, p_ename VARCHAR2)
AS
BEGIN
INSERT INTO emp(empno,ename) VALUES(p_empno,p_ename);
COMMIT;
dbms_output.put_line('emp record inserted');
END insert_emp;

PROCEDURE update_Salary(p_empno NUMBER, p_amount NUMBER)

AS
BEGIN
UPDATE emp SET sal=sal+p_amount WHERE empno=p_empno;
ORACLE 4PM PLSQL Page 110
UPDATE emp SET sal=sal+p_amount WHERE empno=p_empno;
COMMIT;
dbms_output.put_line('salary updated');
END update_Salary;

PROCEDURE delete_emp(p_empno NUMBER)

AS
BEGIN
DELETE FROM emp WHERE empno=p_empno;
COMMIT;
dbms_output.put_line('emp record deleted');
END delete_emp;

FUNCTION experience(p_empno NUMBER) RETURN NUMBER

AS
v_hiredate DATE;
BEGIN
SELECT hiredate INTO v_hiredate FROM emp WHERE
empno=p_empno;

RETURN TRUNC((sysdate-v_hiredate)/365);
END experience;
END;
/

Calling packaged procedures and functions:

SQL> EXEC hr.insert_emp(1001,'A');

Output:
emp record inserted

SQL> EXEC hr.update_Salary(7902,2000);

Output:
salary updated

SQL> EXEC hr.delete_emp(1001);

Output:
emp record deleted

ORACLE 4PM PLSQL Page 111

SQL> SELECT hr.experience(7902) FROM dual;

HR.EXPERIENCE(7902)
-------------------
41

Assignment:

Define following package:

PACKAGE bank

PROCEDURE opening_account => INSERT

PROCEDURE withdraw => UPDATE
PROCEDURE deposit => UPDATE
PROCEDURE closing_account => DELETE

FUNCTION check_balance => SELECT

Advantages of Packages:

• We can group related procedures and functions

• provides reusability
• improves the performance
• we can make members as private or public
• we can declare global variables
• Packaged procedures or Packaged functions can be overloaded

Note:
• Stored procedures or Stored Functions cannot be overloaded
Packaged procedure or Packaged Function can be overloaded
ORACLE 4PM PLSQL Page 112
• Packaged procedure or Packaged Function can be overloaded

Function Overloading:
• Defining multiple functions with same name and different
signature is called "Function Overloading".

• Different signature means,

○ change in no of parameters
○ change in data types
○ change in order of parameters

Examples:
change in no of parameters:
Function f1(x INT, y INT)
FUNCTION f1(x INT, y INT, z INT)

change in data types:

FUNCTION f1(x INT, y VARCHAR2)
FUNCTION f1(x DATE, y CHAR)

change in order of parameters:

FUNCTION f1(x INT, y VARCHAR2)
FUNCTION f1(x VARCHAR2,y INT)

Example on Overloading:

PACKAGE mymath

FUNCTION addition(x INT, y INT)

FUNCTION addition(x INT, y INT, z INT)

Package Specification:

ORACLE 4PM PLSQL Page 113

CREATE OR REPLACE PACKAGE mymath
AS
x INT := 500;
FUNCTION addition(x INT, y INT) RETURN INT;
FUNCTION addition(x INT, y INT, z INT) RETURN INT;
END;
/

Package Body:

CREATE OR REPLACE PACKAGE BODY mymath

AS
FUNCTION addition(x INT, y INT) RETURN INT
AS
BEGIN
RETURN x+y;
END addition;

FUNCTION addition(x INT, y INT, z INT) RETURN INT

AS
BEGIN
RETURN x+y+z;
END addition;
END;
/

Calling overloaded functions:

SQL> SELECT mymath.addition(2,3) FROM dual;

MYMATH.ADDITION(2,3)
--------------------
5

SQL> SELECT mymath.addition(1,2,3) FROM dual;

MYMATH.ADDITION(1,2,3)
----------------------
6

ORACLE 4PM PLSQL Page 114

SQL> EXEC dbms_output.put_line(mymath.x);
500

DECLARE
a INT;
b INT;
BEGIN
a := &a;

b := mymath.x + a;

dbms_output.put_line('sum=' || b);
END;
/

Dropping package:

DROP PACKAGE <name>;

Example:

DROP PACKAGE mymath;

user_procedures:

to see package list:

SELECT object_name,procedure_name,object_type
FROM user_procedures
WHERE object_type='PACKAGE';

user_source:

ORACLE 4PM PLSQL Page 115

to see package list:

SELECT DISTINCT name FROM user_source

WHERE type='PACKAGE';

to see package code:

SELECT text FROM user_source

WHERE name='HR';

ORACLE 4PM PLSQL Page 116

Friday, July 7, 2023 4:22 PM

ORACLE DB SEREVR

Instance DB 20 times

call p1 p1,p2,…..p10

f1,f2,…..f10
call p2

10 stored procedures
10 stored functions

Instance DB 1 time

demo.p1 PACKAGE DEMO

p1,p2,, ….., p10
f1, f2, ….., f10
demo.p2

p1, p2, p10

f1, f2, f10

PACKAGE SPECIFICATION
DEMO PACKAGE BODY DEMO

ORACLE 4PM PLSQL Page 117

PROCEDURE p2 PROCEDURE p1
PROCEDURE p2

PROCEDURE p1 private => with in package body only

PROCEDURE p2 public => with in SCHEMA

ORACLE 4PM PLSQL Page 118

TRIGGERS
Friday, July 7, 2023 5:12 PM

TRIGGER:

PROCEDURE update_Salary
Procedure call call
…….
EXEC update_salary(7369,1000); ………
……..

DML
calls TRIGGER t1
UPDATE
……..
……
……..
DELETE

TRIGGER:
• TRIGGER is a named block of statements that gets
executed automatically when we submit DML command.

• TRIGGER is same as PROCEDURE. But,

For procedure execution explicit call is required.
For trigger execution submit DML command. Implicitly
trigger will be called. For trigger execution explicit call is
not required.

Note:
• TO perform DML operations define PROCEDURE
• To control DML operations define TRIGGER

EMP

TRIGGER t1
Trigger can be used for 3 purposes:

• To control DML operations.

Example:
○ Don't allow the user to perform DMLs on sunday
○ After /Before working hours don't allow DMLs [office timings: 10AM to
4PM]
• don't allow anyone to update empno

• To audit the tables or databases.

Example:
user
at which date and time
operations
old data
new data

ORACLE 4PM PLSQL Page 119

• can be used to implement our own business rules [constraints]

Example:
• don't allow the user to decrease salary
• don't allow the duplicates
• don't allow the nulls

Types of Triggers:

3 Types:

• Table Level Trigger / DML Trigger

• Schema Level Trigger / System Trigger / DDL Trigger
• Database Level Trigger / System Trigger / DDL Trigger

Table Level Trigger:

• If a Trigger is created on Table then it is called "Table

Level Trigger".

• There are 2 types in Table Level Trigger. They are:

○ Statement Level Trigger
○ Row Level Trigger

Statement Level Trigger:

• Trigger gets executed once for one DML statement.

Example:
UPDATE emp SET sal=sal+1000
WHERE job='MANAGER';

Output:
3 rows updated

Trigger gets executed 1 time

Row Level Trigger:

• Trigger gets executed once for every row affected by
DML command.

Example:
UPDATE emp SET sal=sal+1000
WHERE job='MANAGER';

Output:
3 rows updated

Trigger gets executed 3 times.

For every row one time trigger gets executed.

Trigger Specification /
Trigger Header

ORACLE 4PM PLSQL Page 120

Trigger Header
Syntax to define Table Level Trigger:

CREATE [OR REPLACE] TRIGGER <trigger_name>

BEFORE / AFTER INSERT OR UPDATE OR DELETE
ON <table_name>
[FOR EACH ROW]
DECLARE
--declare the variables
BEGIN Trigger Body
--executable statements
END;
/

Example on Statement Level Trigger:

CREATE OR REPLACE TRIGGER t1

AFTER UPDATE
ON emp
BEGIN
dbms_output.put_line('statement level trigger executed');
END;
/

UPDATE emp SET sal=sal+1000

WHERE job='MANAGER';

Output:
statement level trigger executed

3 rows updated

Before Trigger • First Trigger gets executed

• DML operation will be performed

After Trigger • DML operation will be performed

• Trigger gets executed

Example on Row Level Trigger:

CREATE OR REPLACE TRIGGER t2

AFTER UPDATE
ON emp
FOR EACH ROW
BEGIN
dbms_output.put_line('row level trigger executed');
END;
/

UPDATE emp SET sal=sal+1000

WHERE job='MANAGER';

Output:

ORACLE 4PM PLSQL Page 121

Output:
row level trigger executed
row level trigger executed
row level trigger executed

3 rows updated

Enabling & Disabling Trigger:

Syntax:
ALTER TRIGGER <name> <enable/disable>;

Example:
ALTER TRIGGER t2 disable;

ALTER TRIGGER t1 disable;

ALTER TRIGGER t2 enable;

ALTER TRIGGER t1 enable;

Define a Trigger not to allow the user to perform DMLs on Sunday:

CREATE OR REPLACE TRIGGER t3

BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
IF to_char(sysdate,'dy')='sun' THEN
raise_application_error(-20050,'you cannot perform DMLs on
sunday');
END IF;
END;
/

Testing:
On Monday:
UPDATE emp SET sal=sal+1000;
Output:
12 rows updated

On Sunday:
UPDATE emp SET sal=sal+1000;
Output:
ERROR:
ORA-20050: you cannot perform DMLs on sunday
ORA-06512: at "C##BATCH4PM.T3", line 3

Define a Trigger not to allow the user to perform DMLs

before or after working hours. [working hours:10 AM to 4PM]:

CREATE OR REPLACE TRIGGER t4

BEFORE INSERT OR UPDATE OR DELETE
ON emp

ORACLE 4PM PLSQL Page 122

ON emp
DECLARE
h INT;
BEGIN
h := to_char(sysdate,'HH24');

IF h NOT BETWEEN 10 AND 15 THEN

RAISE_APPLICATION_ERROR(-20050,'DMLs allowed between
10AM to 4PM only');
END IF;

END;
/

Testing:
before 10am or after 4pm:
update emp set sal=sal+1000;

Output:
ERROR:
ORA-20050: DMLs allowed between 10AM to 4PM only
ORA-06512: at "C##BATCH4PM.T4", line 7

between 10am to 3.59pm:

update emp set sal=sal+1000;

Output:
12 rows updated

Assignment:

Define a trigger not to allow the user to perform DMLs as follows:

If mon to fri => allow DMLs between 10AM to 4PM only
on Saturday => allow DMLs between 10 AM to 2 PM
on Sunday => don't allow the DMLs

d := to_char(sysdate,'d')
h := to_char(sysdate,'hh24')

IF d BETWEEN 2 AND 6 AND h NOT BETWEEN 10 AND 15 THEN

raise the error
ELSIF d=7 AND NOT BETWEEN 10 AND 13 THEN
raise the error
ELSIF d=1 THEN
raise the error
END IF;

:NEW and :OLD variables:

• :NEW and :OLD are system variables

• They can hold entire row
• :NEW holds new row
• :OLD holds old row
• These can be also called as pseudo records.
These can be used in Row Level Trigger only. These cannot

ORACLE 4PM PLSQL Page 123

• These can be used in Row Level Trigger only. These cannot
be used in statement level trigger.

DML :OLD :NEW

INSERT null new row
DELETE old row null
UPDATE old row new row

Define a trigger to record deleted emps in "emp_resign"

table:

EMP_Resign

empno ename job sal DOR DOR => Date_Of_Resignation

7900 .. .. .. sysdate

DELETE FROM emp WHERE empno=7900;

:OLD
empno ename job sal
7900 .. .. ..

CREATE TABLE emp_resign

(
empno NUMBER(4),
ename VARCHAr2(10),
job VARCHAr2(10),
sal NUMBER(7,2),
DOR DATE
);

CREATE OR REPLACE TRIGGER t5

AFTER DELETE
ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_resign
VALUES(:OLD.empno, :OLD.ename, :OLD.job, :OLD.sal,
sysdate);
END;
/

Testing:

delete from emp where empno=7900;

Output:
1 row deleted.

select * from emp_resign;

ORACLE 4PM PLSQL Page 124

select * from emp_resign;

Output:
EMPNO ENAME JOB SAL DOR
---------- ---------- ---------- ---------- ---------
7900 JAMES CLERK 7950 10-JUL-23

Note:
Don't use COMMIT or ROLLBACK in Trigger.

Define a trigger not to allow the user to update the empno:

CREATE OR REPLACE TRIGGER t6

BEFORE UPDATE OF empno
ON emp
BEGIN
RAISE_APPLICATION_ERROR(-20050,'you cannot update empno');
END;
/

Testing:
UPDATE emp SET empno=1001
WHERE empno=7369;

Output:
ERROR:
ORA-20050: you cannot update empno
ORA-06512: at "C##BATCH4PM.T6", line 2

NOTE:
BEFORE UPDATE we cannot update any column value
BEFORE UPDATE OF empno we cannot update empno
BEFORE UPDATE OF empno,ename BEFORE UPDATE OF empno and ename

Example on auditing emp table:

EMP_AUDIT
user_name op_date_time op_type old_Empno old_ename old_sal new_empno new_ename new_sal

c##batch4pm 11-JUL-23 DELETE

4:38:0.0 PM
user systimestamp op
:old.empno :old.ename :old.sal :new.empno :new.ename :new.sal

CREATE TABLE emp_audit

(
user_name VARCHAR2(20),
op_date_time TIMESTAMP,
op_type VARCHAR2(10),
old_empno NUMBER(4),
old_ename VARCHAR2(10),
old_sal NUMBER(7,2),
new_empno NUMBER(4),

ORACLE 4PM PLSQL Page 125

new_empno NUMBER(4),
new_ename VARCHAR2(10),
new_sal NUMBER(7,2)
);

CREATE OR REPLACE TRIGGER t7

AFTER INSERT OR UPDATE OR DELETE
ON emp
FOR EACH ROW
DECLARE
op VARCHAR2(10);
BEGIN
IF inserting THEN
op := 'INSERT';
ELSIF deleting THEN
op := 'DELETE';
ELSIF updating THEN
op := 'UPDATE';
END IF;

INSERT INTO emp_audit VALUES(user, systimestamp,

op, :old.empno, :old.ename, :old.sal, :new.empno, :new.ename, :new.sal);
END;
/

Testing:

INSERT INTO emp(empno,ename,sal)

VALUES(5001,'ABC',7000);

DELETE FROM emp WHERE empno=7902;

UPDATE emp SET sal=sal+2000

WHERE empno=7499;

COMMIT;

SELECT * FROM emp_audit;

Define a Trigger not to allow the user to decrease the salary:

EMP
EMPNO ENAME SAL :old
7499 ALLEN 12000 EMPNO ENAME SAL
7369 .. .. 7499 ALLEN 12000
7521 .. ..

:new
UPDATE emp EMPNO ENAME SAL
SET sal=sal-2000 7499 ALLEN 10000
WHERE empno=7499;

:new.sal<:old.sal => T => decreasing =>

Raise Error

ORACLE 4PM PLSQL Page 126

CREATE OR REPLACE TRIGGER t8
BEFORE UPDATE
ON emp
FOR EACH ROW
BEGIN
IF :new.sal<:old.sal THEN
raise_application_error(-20070,'You cannot decrease the
salary');
END IF;
END;
/

Testing:
update emp set sal=sal-5000;

Output:
ERROR:
ORA-20070: You cannot decrease the salary
ORA-06512: at "C##BATCH4PM.T8", line 3

Schema Level Trigger:

• Schema => User

• Schema Level Trigger can be also called as "System Trigger /

DDL Trigger".

• It is defined by DBA.

• If trigger is created on SCHEMA then it is called "Schema Level

Trigger".

• To control 1 user we define it.

Syntax of Schema Level Trigger:

CREATE OR REPLACE TRIGGER <trigger_name>

BEFORE/AFTER DROP OR ALTER OR CREATE OR TRUNCATE
ON <schema_name>.SCHEMA
DECLARE
--declare the variables
BEGIN
--executable statements
END;
/

Example on Schema Level Trigger:

Define a Trigger not to allow the c##batch4pm user to drop the DB

Objects like tables, views, procedures, functions, triggers:

Login as DBA:
username: system
password: nareshit

CREATE OR REPLACE TRIGGER t9

BEFORE DROP
ON c##batch4pm.SCHEMA

ORACLE 4PM PLSQL Page 127

ON c##batch4pm.SCHEMA
BEGIN
raise_Application_error(-20080,'you cannot drop any DB oBject');
END;
/

Testing:

Login as c##batch4pm:

DROP TRIGGER t7;

ERROR

DROP TABLE emp;

ERROR

Define a Trigger not to allow the c##batch4pm user to

drop the tables:

System Variable Purpose

ora_dict_obj_type It holds object type
Exs:
TABLE, VIEW, PROCEDURE, TRIGGER
ora_dict_obj_name It holds object name
Exs:
EMP, DEPT, T1, WITHDRAW

Login as DBA:

CREATE OR REPLACE TRIGGER t9

BEFORE DROP
ON c##batch4pm.SCHEMA
BEGIN
IF ora_dict_obj_type='TABLE' THEN
raise_Application_error(-20080,'you cannot drop the table');
END IF;
END;
/

Testing:

Login as user c##batch4pm

DROP TRIGGER t7;

Output:
Trigger Dropped

DROP TABLE emp;

ERROR:

Database Level Trigger:

• If a Trigger is created on database then it is called "Database

Level Trigger".

ORACLE 4PM PLSQL Page 128

• It can be also called as "System Trigger / DDL Trigger".

• DBA creates it.

• To control all users or multiple users this trigger will be created.

Syntax of DB Level Trigger:

CREATE OR REPLACE TRIGGER <trigger_name>

BEFORE / AFTER DROP OR CREATE OR ALTER OR TRUNCATE
ON database
DECLARE
--declare the variables
BEGIN
--executable statements
END;
/

Example on Db Level Trigger:

Define a trigger no to allow following users to drop

any Db Object:
c##batch4pm, c##batch11am, c##batch8pm

CREATE OR REPLACE TRIGGER t11

BEFORE DROP
ON DATABASE
BEGIN
IF user IN('C##BATCH4PM', 'C##BATCH11AM',
'C##BATCH8PM') THEN
raise_application_error(-20050,'you cannot drop
any DB OBject');
END IF;
END;
/

Testing:
Log in as c##batch4pm:

DROP TABLE emp;

Output:
ERROR

Compound Trigger:
• Introduced in ORACLE 11g.
• Compound Trigger is a set of triggers.

Example:
EMP Table

BEFORE Statement Level Trigger => T1

ORACLE 4PM PLSQL Page 129

BEFORE Statement Level Trigger => T1
BEFORE Row Level Trigger => T2

AFTER Statement Level Trigger => T3

AFTER Row Level Trigger => T4

Instead of defining 4 different triggers on "emp" table,

define one compound trigger for all these 4 actions.

Syntax to define Compound Trigger:

CREATE OR REPLACE TRIGGER <trigger-name>

FOR <trigger-action> ON <table-name>
COMPOUND TRIGGER
-- Global declaration.
g_global_variable VARCHAR2(10);

BEFORE STATEMENT IS
BEGIN
NULL; -- Do something here.
END BEFORE STATEMENT;

BEFORE EACH ROW IS

BEGIN
NULL; -- Do something here.
END BEFORE EACH ROW;

AFTER EACH ROW IS

BEGIN
NULL; -- Do something here.
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
NULL; -- Do something here.
END AFTER STATEMENT;

END <trigger-name>;
/

Example on Compound Trigger:

CREATE OR REPLACE TRIGGER t12

FOR INSERT OR UPDATE OR DELETE ON
emp
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('before statement
level trigger executed');
END BEFORE STATEMENT;

BEFORE EACH ROW IS

BEGIN
dbms_output.put_line('before row level

ORACLE 4PM PLSQL Page 130

dbms_output.put_line('before row level
trigger executed');
END BEFORE EACH ROW;

AFTER EACH ROW IS

BEGIN
dbms_output.put_line('after row level
trigger executed');
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
dbms_output.put_line('after statement
level trigger executed');
END AFTER STATEMENT;

END t12;
/

Testing:

UPDATE emp SET sal=sal+1000

WHERE job='MANAGER';

Output:
before statement level trigger executed
before row level trigger executed
after row level trigger executed
before row level trigger executed
after row level trigger executed
after statement level trigger executed

2 rows updated.

Dropping A Trigger:

Syntax:
DROP TRIGGER <trigger_name>;

Example:
DROP TRIGGER t12;

user_triggers:
• is a system table
• it maintains all triggers information
• it maintains the table name, column name, trigger type ..etc

DESC user_triggers;

SELECT trigger_name, trigger_type, triggering_event,

table_name, column_name
FROM user_triggers;

user_source:

It maintains trigger code.

ORACLE 4PM PLSQL Page 131

• It maintains trigger code.

to see trigger names:

SELECT DISTINCT name FROM user_source

WHERE type='TRIGGER';

to see trigger code:

SELECT text FROM user_Source

WHERE name='T1';

ORACLE 4PM PLSQL Page 132

Dynamic SQL
Wednesday, July 12, 2023 5:07 PM

SELECT * FROM emp --static query

'SELECT * FROM ' || n --dynamic query

DROP TABLE emp --static query

'DROP TABLE ' || n --dynamic query

• DRL, DML, TCL commands can be used directly in PL/SQL

program.

• DDL, DCL commands cannot be used directly in PL/SQL program.

To use them in PL/SQL we use "Dynamic SQL".

• "EXECUTE IMMEDIATE" command is used to execute

the dynamic query. Submit dynamic query as string to "execute
immediate" command.

• The query which is built at runtime is called "Dynamic Query"

Examples on Dynamic SQL:

Define a procedure to drop the specific table:

CREATE OR REPLACE PROCEDURE

drop_table(n VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || n;
dbms_output.put_line(n || ' table dropped');
END;

ORACLE 4PM PLSQL Page 133

END;
/

Calling procedure:
SQL> exec drop_table('dept');
dept table dropped

define a procedure to drop any db object:

DROP TABLE t1;

DROP VIEW v1;

DROP PROCEDURE p1;

DROP FUNCTION f1;

CREATE OR REPLACE PROCEDURE

drop_object(t VARCHAR2, n VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || t || ' ' || n;

dbms_output.put_line(n || ' ' || t || ' dropped');

END;
/

Calling procedure:

SQL> exec drop_object('table','emp_resign');

Output:
emp_resign table dropped

SQL> EXEC drop_object('trigger','t2');

Output:
t2 trigger dropped

ORACLE 4PM PLSQL Page 134

Define a procedure to drop all tables:

CREATE OR REPLACE PROCEDURE

drop_all_tables
IS
CURSOR c1 is SELECT table_name FROM user_tables;
n VARCHAR2(20);
BEGIN
OPEN c1;

LOOP
FETCH c1 INTO n;
EXIT WHEN c1%NOTFOUND;
EXECUTE IMMEDIATE 'DROP TABLE ' || n || ' cascade constraints';
END LOOP;

dbms_output.put_line('all tables dropped');

CLOSE c1;
END;
/

Procedure Call:
EXEC drop_all_tables

ORACLE 4PM PLSQL Page 135

Working with LOBs
Thursday, July 13, 2023 4:13 PM

BFILE & BLOB:

• BFILE & BLOB data types are used to maintain

unstructured data like images, audios, videos, animations
…etc.

BFILE:
• BFILE stands for Binary File Large Object.

• It is used maintain multimedia object's [LOB's] path

• It is a pointer to multimedia object

• To maintain multimedia object path, we use "bfilename()"

Database
EMP1 D: Drive
EMPNO ENAME EPHOTO [BFILE] Images Folder
1001 Ravi bfilename('D1','Ravi.jpg')
Ravi.jpg

D1 = D:\Images

• BFILE can be also called as External Large Object.

Because, Multimedia object will be stored out of
the database.

• Just multimedia object's path will be stored inside

of Database

• It is not secured one.

bfilename():
• it is a function
• it is used to maintain multimedia object's path

Syntax:
bfilename(<directory_object>, <file_name>)

Example:
ORACLE 4PM PLSQL Page 136
Example:
bfilename('D1','Ravi.jpg')

Directory Object:
• It is a Database Object
• It is a pointer to particular folder

Syntax to create Directory Object:

CREATE DIRECTORY <directory_name> AS <folder_path>;

Creating Directory Object:

Login as DBA:

username: system
password: nareshit

CREATE DIRECTORY d1 AS 'D:\Images';

GRANT read, write

ON DIRECTORY d1
TO c##batch4pm;

Login as c##batch4pm:

CREATE TABLE emp1

(
empno NUMBER(4),
ename VARCHAR2(10),
ephoto BFILE
);

INSERT INTO emp1

VALUES(1001,'A',bfilename('D1','ellison.jpg'));

SELECT * FROM emp1;

empno ename ephoto

1001 A bfilename('D1','ellison.jpg')

ORACLE 4PM PLSQL Page 137

BLOB:

• BLOB stands for Binary Large Object

• It is used to maintain multimedia object [LOB] inside of

database.

• It can be also called as Internal Large Object.

• It is secured one.

Database
D: Drive
EMP2 Images Folder
EMPNO ENAME EPHOTO [BLOB]
1001 Ravi 57A23F456E123A
Ravi.jpg

Example on BLOB:

CREATE TABLE emp2

(
empno NUMBER(4),
ename VARCHAR2(10),
ephoto BLOB
);

INSERT INTO emp2 VALUES(1001,'A',empty_blob());

number null
char ''
blob empty_blob() =>
• it is a function
• inserts null in blob type column

Define a procedure to update emp photo:

DBMS_LOB package

OPEN procedure used to open the file in specific mode

ORACLE 4PM PLSQL Page 138
OPEN procedure used to open the file in specific mode
CLOSE procedure used to close opened file
GETLENGTH function used to find size of the file
LOADFROMFILE used to read specified number of bytes from file and store in
procedure variable

Steps to update emp photo:

• Select the LOB LOCATOR and copy into t variable [BLOB type]
and Lock the record

SELECT ephoto INTO t FROM emp2 WHERE empno=p_empno

FOR UPDATE;

FOR UPDATE Locks the record

• Open LOB [Image] file in read mode

s BFILE;
s := bfilename('D1','ellison.jpg');
dbms_lob.open(s, dbms_lob.lob_readonly);

• Find file size [file length]

length number;
length := dbms_lob.getlength(s); //5683 [no of bytes]

• read binary data from s file write into t variable

dbms_lob.LoadFromFile(t,s,length);
now t has s image's binary data

• Update t image data in table

UPDATE emp2 SET ephoto=t WHERE empno=p_empno;

• Close the opened file

dbms_lob.close(s);

CREATE OR REPLACE PROCEDURE

ORACLE 4PM PLSQL Page 139

CREATE OR REPLACE PROCEDURE
update_photo(p_empno NUMBER, n VARCHAR2)
AS
s BFILE;
t BLOB;
length NUMBER;
BEGIN
SELECT ephoto INTO t FROM emp2 WHERE empno=p_empno
FOR UPDATE;

s := bfilename('D1',n);
dbms_lob.open(s, dbms_lob.lob_readonly);

length := dbms_lob.getlength(s);

dbms_lob.loadfromfile(t,s,length);

UPDATE emp2 SET ephoto=t WHERE empno=p_empno;

dbms_lob.close(s);

dbms_output.put_line('emp photo saved in table');

END;
/

procedure call:
EXEC update_photo(1001,'ellison.jpg');

SQL> select * from emp2;

EMPNO ENAME EPHOTO

---------- ---------- ---------------------------------------------------------------------------------------
1001 A FFD8FFE000104A46494600010100000100010000FFDB0084000A
0708151512181512121818181818181918181818181818191818181A1A18
1A18181C212E251C1E2B21181A2638262B2F313535351A24

SQL> SELECT length(ephoto) FROM emp2;

LENGTH(EPHOTO)
--------------
5683

ORACLE 4PM PLSQL Page 140

Collections
Friday, July 14, 2023 4:18 PM
x

x INT; 20 30

x:=20;
.
.
x:=30;

Collection:
• Collection is a set of elements of same type.

• A variable can hold 1 value only. To hold multiple

values use "COLLECTION".

• To identify collection element uniquely, use INDEX

Example:
50 90 70 60 80
x(1) x(2) x(3) x(4) x(5)

Types of Collections:

3 Types:

• Associative Array / Index By Table / PL-SQL TABLE

• Nested Table
• V-Array [Variable Size Array]

Associative Array / Index By Table / PL-SQL TABLE:

• Associative is a table of 2 columns. First column is

INDEX. Second column is ELEMENT.

• INDEX can be also called as KEY.

• ELEMENT can be also called as VALUE.

• Associative Array holds KEY-VALUE pairs.

• Indexing can be started from anywhere.

• Index can be integer or char.

ORACLE 4PM PLSQL Page 141

Example of Associative Array:

INDEX ELEMENT
[KEY] [VALUE]
1 50
2 80
3 40
4 90

Creating Associative Array:

follow 2 steps:
• Define data type for associative array
• Declare variable for that associative array

Syntax to Define data type for associative array:

TYPE <name> IS TABLE OF <element_type> INDEX BY

<index_type>;

Example:
TYPE num_array IS TABLE OF NUMBER(4) INDEX BY binary_integer;

Note:
INDEX ELEMENT If index is number type then we can use
1 10 binary_integer or pls_integer
2 70
3 50

Syntax to Declare variable for that associative array:

Syntax:
<name> <collection_type>;

Example:
x NUM_ARRAY;

x NUMBER(4);
y VARCHAR2(10);

ORACLE 4PM PLSQL Page 142

Example on creating associative array:

Create an associative array and hold a set of numbers in it:

DECLARE
TYPE num_array IS TABLE OF NUMBER(4) INDEX BY binary_integer;
x NUM_ARRAY;
BEGIN x => associative array
x(1) := 50;
INDEX ELEMENT
x(2) := 90;
1 50
x(3) := 70;
2 90
FOR i IN x.first .. x.last 3 70
LOOP
dbms_output.put_line(x(i));
END LOOP;

dbms_output.put_line('first index=' || x.first);

dbms_output.put_line('last index=' || x.last);

dbms_output.put_line('prev index of 2=' || x.prior(2));

dbms_output.put_line('next index of 2=' || x.next(2));
END;
/

Output:
50
90
70
first index=1
last index=3
prev index of 2=1
next index of 2=3

Create an Associative array and hold all dept names in it:

INDEX ELEMENT
1 ACCOUNTING
2 RESEARCH
3 SALES
4 OPERATIONS

DECLARE

ORACLE 4PM PLSQL Page 143

DECLARE
TYPE dept_array IS TABLE OF varchar2(10) INDEX BY binary_integer;
d DEPT_ARRAY;
BEGIN
SELECT dname INTO d(1) FROM dept WHERE deptno=10;
SELECT dname INTO d(2) FROM dept WHERE deptno=20;
SELECT dname INTO d(3) FROM dept WHERE deptno=30;
SELECT dname INTO d(4) FROM dept WHERE deptno=40;

FOR i IN d.first .. d.last d

LOOP INDEX ELEMENT
dbms_output.put_line(d(i)); 1 ACCOUNTING
END LOOP;
2 RESEARCH
END;
3 SALES
/
4 OPERATIONS

PL/SQL Program
PL/SQL Engine
SQL statements
PL/SQL statement executor
PL/SQL statements

SQL Engine context switch

SQL stmt executor

If no of context switches are increased performance will be

degraded

In above program to fetch all dept names and store in

collection d 4 context switches will occur.

To reduce no of context switches, we use BULK COLLECT

DECLARE
TYPE dept_array IS TABLE OF varchar2(10) INDEX BY
binary_integer;
d DEPT_ARRAY;
BEGIN
SELECT dname BULK COLLECT INTO d FROM dept;

FOR i IN d.first .. d.last

LOOP INDEX ELEMENT
dbms_output.put_line(d(i)); 1 ACCOUNTING
END LOOP; 2 RESEARCh
END; 3 SALES
/
4 OPERATIONS

ORACLE 4PM PLSQL Page 144

BULK COLLECT:

• BULK COLLECT clause used to collect entire data at a

time and store in collection.

• With 1 context switch, it collects entire data from table.

• It reduces no of context switches. So, performance will

• be improved.

Create an Associative array and hold all emp table

records in it and print them:

INDEX ELEMENT

empno ename job sal deptno hiredate .. ..

1
7369 SMITH .. ..

empno ename job sal deptno hiredate .. ..

2
7499 ALLEn .. ..

3 empno ename job sal deptno hiredate .. ..

7521 WARD .. ..

emp%rowtype
binary_integer

DECLARE
TYPE emp_array IS TABLE OF emp%rowtype INDEX BY
binary_integer;
e EMP_ARRAY;
BEGIN

SELECT * BULK COLLECT INTO e FROM emp;

FOR i IN e.first .. e.last

LOOP
dbms_output.put_line(e(i).ename || ' ' || e(i).sal);
END LOOP;

dbms_output.put_line(e(5).ename || ' ' || e(5).sal);

ORACLE 4PM PLSQL Page 145

END;
/

Create an Associative Array hold hike table records in it.

According to this data, increase salary to all emps:

employee HIKE
empno ename sal EMPNO PER
1001 A 6000 1001 10
1002 B 5000 1002 20
1003 C 7000 1003 15

create table employee

(
empno int,
ename varchar(10),
sal numeric(8,2)
);

insert into employee values(1001,'A',6000);

insert into employee values(1002,'B',5000);
insert into employee values(1003,'C',7000);

create table hike

(
empno int,
per int
);

insert into hike values(1001,10);

insert into hike values(1002,20);
insert into hike values(1003,15);
commit;

INDEX ELEMENT

empno per
1
1001 10
hike%rowtype

empno per
binary_integer 2 1002 20

empno per
3
1003 15

ORACLE 4PM PLSQL Page 146

empno per
3
1003 15

DECLARE
TYPE hike_array IS TABLE OF hike%rowtype INDEX BY pls_integer;
h HIKE_ARRAY;
BEGIN

SELECT * BULK COLLECT INTO h FROM hike;

FOR i IN h.first .. h.last

LOOP
UPDATE employee SET sal=sal+sal*h(i).per/100 WHERE empno=h(i).empno;
END LOOP;

dbms_output.put_line('salary increased to all emps');

END;
/

In Above Example,
For loop will be executed by PL/SQL statement executor.
UPDATE command will be executed by SQL statement executor.

To update 3 records, 3 context switches will occur.

To update 20 records, 20 context switches will occur.

If no of context switches are increased, performance will be degraded.

To improve the performance, use BULK BIND

BULK BIND:
• For BULK BIND, we use FORALL.

• FORALL is used to submit BULK INSERT / BULK DELETE

/ BULK UPDATE commands

• with 1 context switch all commands will be executed

• it reduces no of context switches. So, performance will

be improved.

Syntax of FORALL:

FORALL <variable> IN <lower> .. <upper>

ORACLE 4PM PLSQL Page 147

FORALL <variable> IN <lower> .. <upper>
--DML statement

Example:

FORALL i IN h.first .. h.last

UPDATE employee SET sal=sal+sal*h(i).per/100 WHERE empno=h(i).empno;

DECLARE
TYPE hike_array IS TABLE OF hike%rowtype INDEX BY pls_integer;
h HIKE_ARRAY;
BEGIN

SELECT * BULK COLLECT INTO h FROM hike;

FORALL i IN h.first .. h.last

UPDATE employee SET sal=sal+sal*h(i).per/100 WHERE empno=h(i).empno;

dbms_output.put_line('salary increased to all emps');

END;
/

In above program, with 1 context switch all emp records will be updated.
It improves the performance.

Nested Table / Nested Array:

• It contains 1 column. i.e. element

• index is always number

• Indexing starts from 1

• It is same as single dimensional array in C

Example:

x(1)
10
20
90
50

ORACLE 4PM PLSQL Page 148

To create nested table follow 2 steps:
• define the data type of nested table
• declare variable for that data type

define the data type of nested table:

Syntax:
TYPE <type_name> IS TABLE OF <elent_type>;

Example:
TYPE num_array IS TABLE OF NUMBER(4);

declare variable for that data type:

Syntax:
<variable> <type>;

Ex:
x NUM_ARRAY;

Program to demonstrate nested table:

DECLARE
TYPE num_array IS TABLE OF number(4);
x NUM_ARRAY;
BEGIN
x := num_array(10,50,90,30,40);

for i in x.first ..x.last

loop
dbms_output.put_line(x(i));
end loop;

END;
/

num_array(10,50,90,30,40) • num_array is collection constructor

• when num_array data type is defined
implicitly num_array constructor will be
defined.
• it is a special function
• it initializes the collection

DECLARE
TYPE emp_array IS TABLE OF emp%rowtype;

ORACLE 4PM PLSQL Page 149

TYPE emp_array IS TABLE OF emp%rowtype;
e EMP_ARRAY;
BEGIN
SELECT * BULK COLLECT INTO e FROM emp;

FOR i IN e.first .. e.last

LOOP
dbms_output.put_line(e(i).ename || ' ' || e(i).sal);
END LOOP;
END;
/

V-Array:
• V-Array => Variable Size Array

• When we create V-Array Type, we must specify the size.

• When we know exact size then use V-Array

To create V-Array follow 2 steps. They are:

• Define V-Array Type
• Declare variable for that V-Array Type

• Define V-Array Type:

Syntax:
TYPE <type_name> IS VARRAY(<size>) OF <element_type>;

Example:
TYPE num_array IS VARRAY(10) OF NUMBER(4);

• Declare variable for that V-Array Type:

Syntax:
<variable> <data_type>;

Example:
x NUM_ARRAY;

Example on V-Array:

DECLARE
TYPE num_array IS VARRAY(10) OF NUMBER(4);

x NUM_ARRAY;

ORACLE 4PM PLSQL Page 150

x NUM_ARRAY;
BEGIN
x := num_array(10,50,20,30);

FOR i IN x.first .. x.last

LOOP
dbms_output.put_line(x(i));
END LOOP;
END;
/

Create one V-Array and store emp records in it:

DECLARE
TYPE emp_array IS VARRAY(20) OF emp%rowtype ;
e EMP_ARRAY;
BEGIN
SELECT * BULK COLLECT INTO e FROM emp;

FOR i IN e.first .. e.last

LOOP
dbms_output.put_line(e(i).ename || ' ' || e(i).sal);
END LOOP;
END;
/

Differences between Cursor & Collection:

CURSOR COLLECTION

• fetches row by row • fetches all rows at a time and copies

into collection

• It can move forward only • It can move forward or backward

• supports to sequential • supports to random accessing

accessing.

• Slower • Faster

ORACLE 4PM PLSQL Page 151

Collection_Type Index no_of_elements sparse / dense
Associative Array binary_integer /pls_integer / unlimited sparse or dense
VARCHAR2(n)

Nested Table binary_integer /pls_integer unlimited starts as dense

it can become sparse

V-Array binary_integer /pls_integer Limited dense

dense no gaps can be there

sparse gaps can be there

sparse dense

x(10) x(1)
x(20) x(2)
x(30) x(3)
x(37) x(4)

ORACLE 4PM PLSQL Page 152