PL/SQL

• PL/SQL is a block-structured language, meaning that

PL/SQL programs are divided and written in logical
blocks of code.
• PL/SQL Blocks contain set of instructions for oracle
to execute, display information to the screen, write
data to file, call other programs, manipulate data
and many more.

1
Blocks

• Types of PL/SQL Blocks

• Anonymous Block
• Named Block
• Anonymous Block
• As the title suggests these anonymous blocks do not have
any names as a result they cannot be stored in database
and referenced later.
• Named Block
• On the other hand Named PL/SQL blocks are the one that
have names and are used when creating subroutines such
as procedures, functions and packages. These subroutines
then can be stored in the database and referenced by
their name later.
2
• Both type of PL/SQL blocks are further divided into 3
different sections which are:
• The Declaration Section (Optional)
• The Execution Section and
• The Exception-handling Section(Optional)
• To display the output
Sql>Set serveroutput on;
• Basic prototype of Anonymous PL/SQL Block
• DECLARE
Declaration Statements
BEGIN
Executable statements
Exception
Exception handling statements
END;
3
Variable Declaration
• All the variable declaration must be done in Declare Section of the
PL/SQL block

DECLARE
test1 VARCHAR(30) := ‘Hello’;
BEGIN
DBMS_OUTPUT.PUT_LINE(test1);
END;

declare
a number(3); clare variable
Begin
a:=10; // assign a value
a:=&a; // input from user
dbms_output.put_line(a); // display o/p
end;

4
Though you can assign value to the variable either in declare
section or in execution section of your PL/SQL block but
the declaration must be done in declare section.

DECLARE
Test1 NUMBER;
— Declaring variable Test1
BEGIN
Test1:= 10;
DBMS_OUTPUT.PUT_LINE (Test1);
END;

5
declare the variable in declaration section and initialize it by fetching
value from the table employees of HR user.
DECLARE
salary NUMBER(5);

BEGIN
SELECT sal INTO salary FROM emp WHERE empno=100;
DBMS_OUTPUT.put_line(‘Salary is ‘||salary);
END;

DECLARE
salary NUMBER(5);
first_name VARCHAR2 (15);

BEGIN
SELECT sal, fname INTO salary, first_name FROM emp WHERE empno=100;
DBMS_OUTPUT.put_line (‘Salary is ‘||salary);
DBMS_OUTPUT.put_line (‘Name is ‘||first_name);
END;

6
Anchored Datatype

• Anchored data types are those data type which you assign to
a variable based on a database object.
• Syntax
• variable_name typed-attribute%type

Example:
• DECLARE
salary [Link]%type;
BEGIN
SELECT sal INTO salary FROM emp WHERE empno=100;
DBMS_OUTPUT.put_line(‘Salary is ‘||salary);
END;

7
Constant
Declare and initialize a constant

DECLARE
v_pi CONSTANT NUMBER(7,6) := 3.141592;
BEGIN
DBMS_OUTPUT.PUT_LINE (v_pi);
END;

8
• NOT NULL
• DECLARE
v_pi CONSTANT NUMBER(7,6) NOT NULL DEFAULT
3.1415926;
BEGIN
DBMS_OUTPUT.PUT_LINE (v_pi);
END;
• DEFAULT
• DECLARE
v_pi CONSTANT NUMBER(7,6) DEFAULT 3.1415926;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_pi);
END;

9
Conditional Control Statements

• In Oracle PL/SQL we have two types of conditional

control statements which are
• IF statements and
• CASE statements

10
IF-THEN Structure

Syntax Example
IF condition THEN SET SERVEROUTPUT ON;
Statement1; DECLARE
… v_num NUMBER := 9;
Statement N; BEGIN
END IF; IF v_num < 10 THEN
DBMS_OUTPUT.PUT_LINE(‘Inside The
IF’);
END IF;
DBMS_OUTPUT.PUT_LINE(‘outside The
IF’);
END;

11
IF-THEN-Else Structure

Syntax SET SERVEROUTPUT ON;

IF condition THEN DECLARE
Statement 1; v_num NUMBER := &enter_a_number;
ELSE BEGIN
Statement 2; IF MOD (v_num, 2) = 0 THEN
END IF; DBMS_OUTPUT.PUT_LINE (v_num || ‘ Is
Statement 3 Even’);
ELSE
DBMS_OUTPUT.PUT_LINE (v_num ||’ is
odd’);
END IF;
DBMS_OUTPUT.PUT_LINE (‘IF THEN ELSE
Construct complete ‘);
END;
12
IF THEN ELSIF
Syntax Example
IF CONDITION 1 THEN DECLARE
STATEMENT 1; v_Place VARCHAR2(30) := ‘&Enter Place’;
ELSIF CONDITION 2 BEGIN
THEN IF v_Place = ‘Metropolis’ THEN
STATEMENT 2; DBMS_OUTPUT.PUT_LINE(‘This City Is Protected By
ELSIF CONDITION 3 Superman’);
THEN ELSIF v_Place = ‘Gotham’ THEN
STATEMENT 3; DBMS_OUTPUT.PUT_LINE(‘This City is Protected By
… Batman’);
ELSE ELSIF v_Place = ‘Amazon’ THEN
STATEMENT N; DBMS_OUTPUT.PUT_LINE(‘This City is protected by
END IF; Wonder Woman’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Please Call Avengers’);
END IF;
DBMS_OUTPUT.PUT_LINE(‘Thanks For Contacting
us’); 13
END;
CASE Statements

• Syntax:
• The syntax for case statement in PL/SQL is:
CASE selector CASE
WHEN 'value1' THEN S1; WHEN selector = 'value1' THEN S1;
WHEN 'value2' THEN S2; WHEN selector = 'value2' THEN S2;
WHEN 'value3' THEN S3; WHEN selector = 'value3' THEN S3; ...
... ELSE Sn; -- default case
ELSE Sn; -- default case END CASE;
END CASE;

14
Example

DECLARE
grade char(1);
BEGIN
Grade:=:grade;
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very good');
when 'C' then dbms_output.put_line('Well done');
when 'D' then dbms_output.put_line('You passed');
when 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
END CASE;
END;
15
Introduction To Loops & Simple Loops In PL/SQL

• Types of Loops in Oracle PL/SQL

• There are 4 types of Loops in Oracle PL/SQL
• Simple Loop
• While Loop
• Numeric For Loop and
• Cursor For loop

16
Simple Loop

Syntax: Loop with EXIT Syntax: Loop with EXIT

LOOP WHEN
Statement 1; LOOP
Statement 2; Statement 1;
… Statement 2;
Statement 3; …
IF condition THEN Statement 3;
EXIT; EXIT WHEN condition ;
END IF; END LOOP;
END LOOP;

17
Terminate Loop with EXIT

DECLARE
v_counter NUMBER :=0;
v_result NUMBER;
BEGIN
LOOP
v_counter := v_counter+1; //increment statement
v_result := 19*v_counter;
DBMS_OUTPUT.PUT_LINE(’19’||’ x ‘||v_counter||’ = ‘||
v_result);
IF v_counter >=10 THEN
EXIT;
END IF;
END LOOP;
END;
18
Terminate the Loop with EXIT WHEN Clause

DECLARE
v_counter NUMBER :=0;
v_result NUMBER;
A number;
BEGIN
A:=:a;
LOOP
v_counter := v_counter+1;
v_result := a*v_counter;
DBMS_OUTPUT.PUT_LINE(a||’ x ‘||v_counter||’ = ‘||
v_result);
EXIT WHEN v_counter>=10;
END LOOP;
END;

19
WHILE Loop In PL/SQL

• Syntax
• WHILE condition LOOP
Statement 1;
Statemen 2;
…
Statement 3;
END LOOP;

20
Example

DECLARE
v_counter NUMBER :=1;
v_result NUMBER ;
BEGIN
WHILE v_counter <= 10
LOOP
v_result := 9 *v_counter;
DBMS_OUTPUT.PUT_LINE(‘9’||’ x ‘||v_counter||’ =
‘||v_result);
v_counter := v_counter+1;
END LOOP;
END;

21
FOR Loop In PL/SQL

Syntax Example 1: FOR loop

FOR loop_counter IN [REVERSE] BEGIN
lower limit.. upper_limit FOR v_counter IN 1..10
LOOP LOOP
Statement 1; DBMS_OUTPUT.PUT_LINE(v_counter);
Statement 2; END LOOP;
… END;
Statement 3;
END LOOP; BEGIN
FOR v_counter IN REVERSE 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(v_counter);
END LOOP;
END;

22
Example 3: Multiplication Table
using FOR loop

DECLARE v_result NUMBER;

BEGIN
FOR v_counter IN 1..10 LOOP
v_result:= 19*v_counter;
DBMS_OUTPUT.PUT_LINE(v_result);
END LOOP;
END;
23
PL/SQL functions

• Functions are named PL/SQL block which means

they can be stored into the database as a database
object and can be reused.
• There are two types of PL/SQL functions in Oracle
Database, these are
• Pass-by-Value Functions and
• Pass-by-Reference functions
• In Oracle Database both types of functions should
have to return some values and these values should
be a valid SQL or PL/SQL datatype.

24
Syntax of PL/SQL Functions

CREATE [OR REPLACE] FUNCTION function_name

(Parameter 1, Parameter 2…) RETURN
datatype
IS
Declare variable, constant etc.
BEGIN
Executable Statements;
Return (Return Value);
END;

25
Example

CREATE OR REPLACE FUNCTION circle_area

(radius NUMBER) RETURN NUMBER
IS
--Declare a constant and a variable
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);
BEGIN

--Area of Circle pi*r*r;

area := pi * (radius * radius);

RETURN area;
END;
26
Function calling block

Declare
Area number;
Begin
Area:=circle_area(5);
Dbms_output.put_line(area);
End;

27
Example
Create or Replace function adder(n1 in number, n2 in
number) return number
is
n3 number(8);
Begin
n3 :=n1+n2;
return n3;
End;

28
Write another program to call the function
DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
OUTPUT:
Addition is: 33
Statement processed.
0.05 seconds

29
PL/SQL function computes and returns the
maximum of two values

Create or Replace FUNCTION findMax(x IN number, y IN

number) RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END; 30
Cont..
DECLARE
a number;
b number;
c number;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
Output:
Maximum of (23,45): 45
31
Create Function:
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;

32
Calling Oracle Function:

DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' ||
c);
END;

Total no. of Customers: 4

33
Oracle Recursive Function
DECLARE
num number;
factorial number;
FUNCTION fact(x number) RETURN number IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END; 34
Cont..
BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' ||
factorial);
END;

Factorial 6 is 720
PL/SQL procedure successfully completed.

35
Oracle Drop Function

If you want to remove your created function from the database,

you should use the following syntax.
DROP FUNCTION function_name;

36
Stored Procedures
• Procedures are named PL/SQL blocks thus they can be
reused because they are stored into the database as a
database object.
• But unlike PL/SQL functions a stored procedure does not
return any value.
CREATE [OR REPLACE] PROCEDURE pro_name
(Parameter – List)
IS
Declare statements
BEGIN
Executable statements
END procedure name;
37
Stored Procedures Example

CREATE OR REPLACE PROCEDURE Proc1

IS
var_name VARCHAR2 (30):= ‘Hello';
var_web VARCHAR2 (30) := ‘Hi';
BEGIN
DBMS_OUTPUT.PUT_LINE(var_name||var_web);
END Proc1;

Call the procedure in anonymous block

BEGIN
Proc1;
END;
38
Example
Create or Replace Procedure addition(n1 in number, n2 in
number)
is
n3 number(8);
Begin
n3 :=n1+n2;
dbms_output.put_line('Addition is: ' || n3);
End;

BEGIN
addition(11,22);
END;
39
Example

CREATE OR REPLACE PROCEDURE proc1 (eno number)

IS
var_name VARCHAR2 (30);
BEGIN
select ename into var_name from emp where empno=eno;
dbms_output.put_line(var_name);
END Proc1;

BEGIN
proc1 (7839);
END;
40
Example
Create or Replace procedure demo (Eno [Link]%type)
is
Salary [Link]%type;
Begin
update emp set sal=sal+1000 where empno = eno;
Select sal into salary from emp where empno=eno;
dbms_output.put_line(salary);
End demo;

begin
demo(7839);
end;
41
Cursor

• A cursor is a pointer to this context area. PL/SQL

controls the context area through a cursor.
• A cursor holds the rows (one or more) returned by a
SQL statement.
• The set of rows the cursor holds is referred to as
the active set.
• There are two types of cursors −
• Implicit cursors
• Explicit cursors

42
Oracle Implicit Cursors
[Link] Attribute & Description
%FOUND
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more
1 rows or a SELECT INTO statement returned one or more rows. Otherwise, it
returns FALSE.

%NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE
2 statement affected no rows, or a SELECT INTO statement returned no rows.
Otherwise, it returns FALSE.

%ISOPEN
3 Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor
automatically after executing its associated SQL statement.

%ROWCOUNT
4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement.

43
Example
• Any SQL cursor attribute will be accessed as sql%attribute_name

DECLARE
total_rows number(2);
BEGIN
UPDATE emp SET sal = sal + 500;
IF sql%notfound THEN
dbms_output.put_line('no rows selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' rows selected ');
END IF;
END;

44
Oracle Explicit Cursors
 A cursor is a pointer to a private SQL area that
stores information about the processing of a SELECT
or DML statements like INSERT, UPDATE, DELETE or
MERGE.
 Cursor is a mechanism which facilitates you to
assign a name to a SELECT statement and
manipulate the information within that SQL
statement.

45
How to declare Explicit cursor
CURSOR cursor_name
IS
SELECT statement;

First create the Account table with set of values.

CURSOR C_emp
IS
SELECT ename
FROM emp
WHERE sal > 4000;

46
How to open a cursor
After the declaration of the cursor, you have to use the
open statement to open the cursor.
OPEN cursor_name;

Example:
OPEN C_emp;

47
How to fetch rows from cursor
This statement is used after declaring and opening
your cursor. It is used to fetch rows from cursor.
FETCH cursor_name INTO variable_list;

cursor_name: It specifies the name of the cursor that

you wish to fetch rows.

variable_list: It specifies the list of variables that you

wish to store the cursor result set in.

48
How to close cursor
CLOSE statement is a final step and it is used to close
the cursor once you have finished using it.
Syntax

CLOSE cursor_name;

Statement for closing cursor

CLOSE c_emp;

49
Example
DECLARE
name [Link]%type;
CURSOR c_emp
is
SELECT ename FROM emp where sal > 2500;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp into name;
EXIT WHEN c_emp%notfound;
dbms_output.put_line(name);
END LOOP;
CLOSE c_emp;
END;
50
Exceptions
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
51
Example
DECLARE
ac_no [Link]%type := 105;
name account.c_name%type;
bal [Link]%type;
BEGIN
SELECT c_name, balance INTO name, bal FROM account WHERE
acno = ac_no;
DBMS_OUTPUT.PUT_LINE ('Name: '|| name);
DBMS_OUTPUT.PUT_LINE (‘Balance: ' || bal);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END; 52
 Oracle SQL
Exception Description
Error CODE
It is raised when a null object is automatically
ACCESS_INTO_NULL 06530 -6530
assigned a value.
It is raised when none of the choices in the
CASE_NOT_FOUND 06592 -6592 WHEN clause of a CASE statement is selected,
and there is no ELSE clause.
It is raised when a SELECT INTO statement returns no rows.
NO_DATA_FOUND 01403 +100

It is raised when duplicate values are

DUP_VAL_ON_INDEX 00001 -1 attempted to be stored in a column with
unique index.
It is raised when attempts are made to make a
INVALID_CURSOR 01001 -1001 cursor operation that is not allowed, such as
closing an unopened cursor.
It is raised when the conversion of a character
string into a number fails because the string
INVALID_NUMBER 01722 -1722 does not represent a valid number.

53
 It is raised when a database call is issued
NOT_LOGGED_ON 01012 -1012 without being connected to the database.

It is raised when PL/SQL has an internal

PROGRAM_ERROR 06501 -6501 problem.

ROWTYPE_MISMAT It is raised when a cursor fetches value in a

06504 -6504 variable having incompatible data type.
CH
It is raised when a member method is
SELF_IS_NULL 30625 -30625 invoked, but the instance of the object type
was not initialized.
It is raised when PL/SQL ran out of memory
STORAGE_ERROR 06500 -6500 or memory was corrupted.

It is raised when a SELECT INTO statement

TOO_MANY_ROWS 01422 -1422 returns more than one row.

It is raised when an arithmetic, conversion,

VALUE_ERROR 06502 -6502 truncation, or sizeconstraint error occurs.

It is raised when an attempt is made to divide

ZERO_DIVIDE 01476 1476 a number by zero.
54
Triggers
• Triggers are stored programs, which are automatically executed or fired
when some events occur.
• Triggers are, in fact, written to be executed in response to any of the
following events:
• A database manipulation DML statement DELETE, INSERT, or UPDATE.
• A database definition DDL statement CREATE, ALTER, or DROP.
• A database operation SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN.

55
Create a Trigger
• CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER |
INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name] ON table_name
[FOR EACH ROW] WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

56
create table log
(user1 varchar(20),
date1 date,
operation varchar(30)
);

57
Example

create or replace trigger trig1

after delete or insert or update on emp
for each row
begin
if inserting then
insert into log values(user, sysdate,'insert');
elsif deleting then
insert into log values(user, sysdate,'delete');
elsif updating then
insert into log values(user, sysdate,'update');
end if;
end; 58
Query Processing
It is the step-by-step process of breaking the high-level language
into a low-level language in which the machine can understand
and perform the requested action for the user.

59
Steps in Query Processing
• Validate and translate the query
• Good syntax.
• All referenced relations exist.
• Translate the SQL to relational algebra.
• Optimizer
• It is a process in which multiple query execution plan for
satisfying a query are examined and most efficient query
plan is satisfied for execution.
• Database catalog stores the execution plans and then
optimizer passes the lowest cost plan for execution.
• Evaluation Engine
• Evaluates the Query and displays the result.

60
Translation Example

Possible SQL Query:

SELECT ename
FROM account
WHERE balance<2500
Possible Relational Algebra Query:
ename(balance<2500(account))

61
Tree Representation of Relational Algebra
• A query tree is a tree data structure representing a
relational algebra expression.
• The tables of the query are represented as leaf nodes.
The relational algebra operations are represented as the
internal nodes. The root represents the query as a whole.
ename(balance<2500(account))

ename
balance<2500

account

62
Example of query tree and Graph
Step-2: Moving SELECT operations down the query
tree.
Step-3: Applying the more restrictive SELECT
operation first.
Step-4: Replacing CARTESIAN PRODUCT and SELECT
with JOIN operations.
Step-5: Moving PROJECT operations down the query
tree.
Cost-Based Optimization

 In problem design, the relations along with the query

requirement scenario is given to you.
 You have to design the SQL statement and transform into
equivalence relational algebraic expressions, which are
known as query equivalence plans.
 For individual query plan, develop the query tree and
apply query optimization process or algorithm to find out
the best plan.
Cont.. Example
Given two relations R(A,B), S(B,C)

SQL> Select A,C

From R,S
Where R.B = S.B
And S.C=3;

Now we convert this SQL into equivalence relational

algebraic expressions.

69
Cont..

Equivalence plan-01
πAC(σC=3(R⋈S))
Equivalence plan-02
πAC(σC=3(S) ⋈ (R))

70
Cont.. Design Query Tree for each plan

π AC π AC

σC=3 ⋈

⋈ σ R
C=3

R S
QUERY TREE-02
QUERY TREE-01
S

71
Cont..

 Here both query trees are known as

equivalence plans as they produce the same
result.
 But the processing speed differs in between
two query trees/plans.
 Out of two query plans, choose an optimal
plan that takes less processing time….
 Less selection time or search time…
 Less matching time or comparison time..

72
Cont..
 Here each unit time is considered as one unit cost.
 So here, our objective is to reduce the query cost
to develop an optimal plan.
 Here we have to apply the query optimization
algorithm in which two main factors are to be
considered.
 Search time during selection operation
 Comparison time during join operation

73
Cont.. Case scenario analysis

 Let M number of tuples are there in relation R and

N number of tuples in relation S.
 Common column in both R and S with
homogeneous values.
 Now we can perform the comparison operation
through natural join operator i.e. number of
comparison=M x N.
 Now calculate
Total CPU time= search time + comparison time.

74
How do we select an Optimal plan

Let R(A,B) and S(B,C) are to relation having 100 tuples

in each and 10 tuples satisfy the imposed condition
i.e. C=3.
SQL> Select A,C
From R,S
Where R.B = S.B
And S.C=3;

75
Cont.. Design Query Tree for each plan

π AC π AC

σ ⋈10x100
C=3 10

⋈ 100x100 10 σ R
C=3

100 100
R S QUERY TREE-02

QUERY TREE-01
S100

76
Cont..
 Total minimum CPU time for QUERY plan-01
 Total minimum CPU time = (100 x 100) + 10 = 10,010 unit
time
 Total minimum CPU time for QUERY plan-02
 Total minimum CPU time= 10+(10x100) = 1010 unit time
 Assume one comparison consumes 1 unit time and one
selection consumes 1 unit time.
 From the above computation, we observe that Plan-02
consumes less minimal CPU time and is considered a better
optimal plan than Plan-1.

77
50 records 100 records
Task
Consider Three relations i.e.
STUDENT (SID, SNAME), COURSE (CID, CNAME),
ASSIGNS (SID, CID)
Both having 1000 tuples in each.

The query is :-
Find the CNAME of Mr. X

and 100 tuples satisfy this condition.

Using query optimization technique, try to design query
execution plans and investigate the optimization factors to
select the optimized query execution plan?

80