PL/SQL

Introduction
 PL/SQL stands for Procedural Language/Structured Query
language.
 The PL/SQL programming language was developed by
Oracle Corporation in the late 1980s as procedural
extension language for SQL.
 It is the superset of the SQL.
 Because it is procedural language it removes many
restrictions of SQL language.
Disadvantage of SQL
 SQL don’t have procedural capabilities like condition
checking, looping, and branching.
 SQL can only manipulate the information stored into
database.

 To overcome the disadvantage of SQL , PL/SQL came

into existence.
Features of PL/SQL
 PL/SQL allows sending an entire block of statements to
the database at one time.
 This reduces network traffic and provides high performance for
the applications.
 PL/SQL give high productivity to programmers as it can
query, transform, and update data in a database.
Difference between PL/SQL and SQL
SQL PL/SQL
1. SQL does not have procedural 1. PL/SQL has all procedural
capabilities i.e. There is no capabilities.
provision of conditional 2. It sends the block of SQL
checking, looping and statements in one go, which
branching. results in increase of speed of
2. SQL statements are passed to processing.
oracle engine one at a time.
Hence leads to slow
processing.
3. It allows programmer to display
3. There is no provision of user-friendly error messages.
handling errors and exception 4. We can store the immediate
4. We cannot store the immediate results of query in variables.
results of queries.
Architecture of PL/SQL execution
PL/SQL BLOCK STRUCTURE
DECLARE (optional)
- variable declarations
BEGIN (required)
- SQL statements
- PL/SQL statements or sub-blocks
EXCEPTION (optional)
- actions to perform when errors occur
END; (required)
Fundamentals of PL/SQL
 Character set
 Operators
 Literals
 Comments
 Variables and constants
 Data types
 Declarations
 Assignments
Character set
 Upper and lower case letters: A-Z, a-z
 Numerals: 0-9
 Symbols: ( ) , . < > ? / : ;’ “ { } [ ] !@#$%^&_+=-
 Tabs and space
PL/SQL Operators: Arithmetic operators

Operator Description Example(A=10, B=5)

+ Adds two operands A + B will give 15

Subtracts second operand from the

- A - B will give 5
first

* Multiply both operands A * B will give 50

/ Divide numerator by de-numerator A / B will give 2

Exponentiation operator, raises one

** A ** B will give 100000
operand to the power of other
Comparison Operators

Operator Description Example

Checks if the value of two operands is equal or not, if yes then

= (A = B) is not true.
condition becomes true.
!=
Checks if the value of two operands is equal or not, if values are not
<> (A != B) is true.
equal then condition becomes true.
~=

Checks if the value of left operand is greater than the value of right
> (A > B) is not true.
operand, if yes then condition becomes true.

Checks if the value of left operand is less than the value of right
< (A < B) is true.
operand, if yes then condition becomes true.

Checks if the value of left operand is greater than or equal to the value
>= (A >= B) is not true.
of right operand, if yes then condition becomes true.

Checks if the value of left operand is less than or equal to the value of
<= (A <= B) is true.
right operand, if yes then condition becomes true.
 Additional Comparison Operators

Operator Description Example

The LIKE operator compares a character, string, or If 'Zara Ali' like 'Z% A_i' returns a
LIKE CLOB value to a pattern and returns TRUE if the Boolean true, whereas, 'Nuha Ali'
value matches the pattern and FALSE if it does not. like 'Z% A_i' returns a Boolean false.

If x = 10 then, x between 5 and 20

The BETWEEN operator tests whether a value lies in
BETWEE returns true, x between 5 and 10
a specified range. x BETWEEN a AND b means that x
N returns true, but x between 11 and
>= a and x <= b.
20 returns false.

If x = 'm' then, x in ('a', 'b', 'c') returns

The IN operator tests set membership. x IN (set)
IN boolean false but x in ('m', 'n', 'o')
means that x is equal to any member of set.
returns Boolean true.

The IS NULL operator returns the BOOLEAN value

TRUE if its operand is NULL or FALSE if it is not If x = 'm', then 'x is null' returns
IS NULL
NULL. Comparisons involving NULL values always Boolean false.
yield NULL.
Logical Operators

Operat
Description Example
or

Called logical AND operator. If both the operands

and (A and B) is false.
are true then condition becomes true.

Called logical OR Operator. If any of the two

or (A or B) is true.
operands is true then condition becomes true.

Called logical NOT Operator. Used to reverse the

not logical state of its operand. If a condition is true not (A and B) is true.
then Logical NOT operator will make it false.
 The PL/SQL Literals
Literal Type Example:

050 78 -14 0 +32767

Numeric Literals 6.6667 0.0 -12.0 3.14159 +7800.00
6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3

Character
'A' '%' '9' ' ' 'z' '('
Literals

'Hello, world!'
String Literals
'19-NOV-12'

BOOLEAN
TRUE, FALSE, and NULL.
Literals

Date and Time DATE '1978-12-25';

Literals TIMESTAMP '2012-10-29 12:01:01';
PL/SQL Variable Types
 Its a named variable which is used to hold some data
value.
 variable-name datatype(size);

DECLARE
a number := 10;
b number := 20;
c number;
Declaring a Constant
 A constant variable cannot be changed throughout the
program.

DECLARE
PI CONSTANT NUMBER := 3.14 -- constant declaration
Data types
 Number- integers and floating point number.
 Char: alphanumeric up to 32767 bytes
 Varchar: variable length alphanumeric
 Date: date and time
 Boolean: true, false or null
Declaration
 Variable declaration
 variable-name datatype(size);
 Constant declaration
 variable-name CONSTANT datatype(size) := value;
Assignment
1. Using assignment operator (:=)
 A :=10;
 Sum := A+B+C;

2. Get value from data base object. Using “SELECT INTO”

clause
 Select salary into SAL from employee where empid=12;
PL/SQL is strongly typed
 All variables must be declared before their use.
 The assignment statement

:=
is not the same as the equality operator
=
 All statements end with a ;
PL/SQL Comments
 Single line comment
 A:=5; -- assign value 5 to variable A.

 Multi-line comments
 A:=b+c; /* the value of variable A and B are added and
assign to variable A */
Important PL/SQL delimiters
+, -, *, / arithmetic operators
; statement terminator
:= assignment operator
=> association operator
|| strings concatenation operator
. component indicator
% attribute operator
‘ character string delimiter
-- single line comment
/*, */ multi line comment delimiters
.. range operator
=, >, >=, <, <= relational operators
!=, ~=, ^=, <> not equal relational operators
is null, like, between PL/SQL relational operators
To display user message on the screen
 SQL> Set Serveroutput ON;

 dbms_output.put_line(A);
 dbms_output.put_line(‘Value of A is:’ || A);
 || is concatenation operator
Read a value during runtime
 Num:= :num;

 This will produce a message on screen

 Enter the value of NUM:
 User can enter any value at run time to NUM.
Example Program: to display message
DECLARE
BEGIN
dbms_output.put_line(‘Welcome to Programming world’);
END;
Quiz
 PL/SQL is a ________ of SQL

A) Subset
B) Superset
C) Powerset
D) None of these
Quiz
 A PL/SQL statement is terminated with

A) End statement
B) Stop Statement
C) Break Statement
D) None of these
Quiz
 Which of the following is the assignment operator in
Oracle

A) =
B) :=
C) ==
D) None of these
To add two numbers
Declare
a number(2);
b number (2);
c number(2);
Begin
a:=5;
b:=2;
c:=a+b;
dbms_output.put_line(‘sum=‘ || c);
End;
To add two numbers (get values from user)
Declare
a number(2);
b number (2);
c number(2);
Begin
a:=:a;
b:=:b;
c:=a+b;
dbms_output.put_line(‘sum=‘ || c);
End;
Create a ‘sty’ table with following parameters:
Select Marks and name from ‘sty’ table and also update ‘sty’ table

declare
a int;
n varchar(20);
begin
select marks,name into a,n from sty where id=3;
dbms_output.put_line(a ||' '||n);
update sty set marks=a+7 where id=3;
dbms_output.put_line(a);
end;
emp table
Emp_name Emp_id TA DA Total Branch_City
abc 10 1200 1345 2545 Delhi
xyz 12 1100 1200 2300 Mumbai
calculate total amount(ta+da) of an
employee, also update the emp table
PL/SQL code to calculate total amount(ta+da)
of an employee, also update the emp table
Declare
a number(5);
b number(5);
t number(5);
Begin
select ta, da into a, b from emp where empid=12;
t:=a+b;
update emp set total =t where empid=12;
end;
Variable attributes
 %type
 %rowtype
%TYPE
 Provide the data type of a variable or column.

 Exp:
 sal employee.salary%TYPE;
declare
a sty.marks%type;
n sty.name%type;
begin
select marks,name into a,n from sty where id=2;
dbms_output.put_line(a ||' '||n);
end;
Declare
a emp.ta%TYPE;
b emp.td%TYPE;
t emp.total%TYPE;
Begin
Select ta, da into a, b from emp where emp_id=12;
t=a+d;
Update emp set total =t where empid=12;
End;
%ROWTYPE
 It provides a record type that represents a row in a table. One
variable to access the complete row of the table.

 Eg:
 dept_rec dept%ROWTYPE; -- declaring record variable.
detp_rec.deptno;
dept_rec.deptname; -- accessing columns

i.e. recordname.colname

 %ROWTYPE has all properties of %TYPE and one additional that we

required only one variable to access any number of columns.
declare
t sty%rowtype;
begin
select * into t from sty where id=1;
dbms_output.put_line(t.name ||' '||t.id||' '||t.marks);
end;
emp table

Emp_name Emp_id TA DA Total Branch_Cit

y
abc 10 1200 1345 2545 Delhi
xyz 12 1100 1200 2300 Mumbai
Declare
record2 emp%ROWTYPE;
Begin
Select * into record2 from emp where empid=12;

record2.total=record2.ta+record2.da;

Update emp set total=record2.total where empid=12;

End;
QUIZ
 Which of the following is optional in PL/SQL
A) Begin
B) Declare
C) Exception
D) Both B and C
Control statements
 Conditional / selection control
 Iterative control
 Sequence control
Conditional / selection
 IF condition Then
Sequence of statements;
End if;

 IF condition then
Sequence of statements;
Else
Sequence of statements;
End if;

 IF condition1 then
Sequence of statements;
Elsif condition2 then
Sequence of statements;
Else
Sequence of statements;
End if;
To find largest of two numbers
Declare
num1 number(2);
num2 number(2);
Begin
num1 := :num1;
num2 := :num2;
If num1>num2 then
dbms_output.put_line(‘greater number is =‘ || num1);
Else
dbms_output.put_line(‘greater number is =‘ || num2);
End if
End;
Even or odd number
declare
n number:=:n;
 
begin
if mod(n,2)=0
then
dbms_output.put_line('number is even');
else
dbms_output.put_line('number is odd');
end if;
end;
 To find largest of three numbers(nested if)
 To display the grade of students according to marks(elsif).
To find largest of three numbers(nested if)
declare
a number:=10;
b number:=12;
c number:=5;
begin
dbms_output.put_line('a='||a||' b='||b||' c='||c);
if a>b AND a>c
then
dbms_output.put_line('a is greatest');
else
if b>a AND b>c
then
dbms_output.put_line('b is greatest');
else
dbms_output.put_line('c is greatest');
end if;
end if;
end;
QUIZ

Which of the following is true about comments in PL/SQL?

A) Comments are explanatory statements.
B) The PL/SQL single-line comments start with the delimiter
- - (double hyphen)
C) Multi-line comments are enclosed by /* and */.
D) All of the above.
 QUIZ

Which of the following is true about the execution section of

a PL/SQL block?

A - It is enclosed between the keywords BEGIN and END.

B - It is a mandatory section.

C - It consists of the executable PL/SQL statements.

D - All of the above.

QUIZ

Which of the following is true about comments in PL/SQL?

A) Comments are explanatory statements.
B) The PL/SQL single-line comments start with the delimiter
- - (double hyphen)
C) Multi-line comments are enclosed by /* and */.
D) All of the above.
 QUIZ
To get the server output result and display it into
the screen, you need to write −
A - set serveroutput on
B - set server output on
C - set dbmsoutput on
D - set dbms output on
Iterative control
Sequence of statements can be executed number of times
 Loop
 While – loop
 For-loop
Loop
Simple loop (Infinite loop)
Loop
Sequence of statements;
Exit when condition;
End loop;

 Two forms of exit

 exit
 Exit to exit immediately
 Exit when
 To exit on condition
To print numbers 1 to 10
Declare
i number(2);
Begin
i:=1;
Loop
dbms_output.put_line(i);
i := i+1;
Exit when i>10;
End loop;
End;
while
Statements are executed when condition is true

While condition
Loop
Sequence of statements;
updation;
End loop;
To print Square of number from 1 to 10
Declare
a number(2);
Begin
a:=1;
While a<=10;
Loop
Dbms_output.put_line(a*a);
a:=a+1;
End loop;
End;
To print multiplication table
Declare
table number := &table;
count number:=1;
result number;
Begin
While count<=10
Loop
result := table*count;
Dbms_output.put_line (table|| ‘*’ || count ||‘=’|| result);
count:=count+1;
End loop;
End;
For loop

FOR counter IN [REVERSE] lower bound..higher bound

Loop
Sequence of statements;
End loop;
Declare
total number(4);
i number(2);
Begin
For i in 1..10
Loop
total := 2*i;
Dbms_output.put_line(‘2*’||i||’=‘||total);
End loop;
End;
Reverse loop
FOR i IN REVERSE 1..10
Loop
Dbms_output.put_line(i); -- prints 10 to 1
End loop
To calculate the factorial of given number
Declare
num number(2);
i number(2);
fact number(4);
Begin
fact := 1;
num := &num;
For i in 1..num
Loop
fact := fact * i;
End loop;
Dbms_output.put_line(fact);
End;
 QUIZ
Which of the following statements can be used
to terminate a PL/SQL loop?

A. GOTO
B. EXIT WHEN
C. CONTINUE WHEN
D. KILL
 Quiz

Which of the following is not true about PL/SQL decision

making structures?
A)The IF statement associates a condition with a sequence of
statements enclosed by THEN and END IF.
B)The IF statement also adds the keyword ELSE followed by
an alternative sequence of statement.
C)The IF-THEN-ELSIF statement allows you to choose
between several alternatives.
D)PL/SQL does not have a CASE statement
Sequence control
To transfer the flow using goto statement

Goto lablename;

Label is marked using tag

<<labelname>>
goto
Declare
num1 number(2);
Num2 number(2);
Begin
num1:=:num1;
num2:=:num2;
If num1> num2 then
Goto p1;
Else
Goto p2;
End if;
<<p1>>
Dbms_output.put_line(‘num1 is bigger’);
goto p3;
<<p2>>
Dbms_output.put_line(‘num2 is bigger’);
<<p3>>
null;
End;
 TRIGGERS
TRIGGERS are stored programs that are fired by Oracle
engine automatically when DML Statements like insert,
update, delete are executed on the table or some events
occur. The code to be executed in case of a trigger can be
defined as per the requirement. You can choose the event
upon which the trigger needs to be fired and the timing of
the execution. The purpose of trigger is to maintain the
integrity of information on the database.
Triggers
 Stored procedures that automatically executed when some
event occurs on a particular table in data base.
 Events can be
 Insert
 Delete
 Update
Trigger vs procedures
 Triggers do not accept parameters.
 Triggers are executed automatically with user calling.
Need and uses of triggers
 Prevent changes
 Log changes
 Audit changes(log of users)
 Enhance changes
 Replicate data
 Enhance performance
Types of Triggers in Oracle
Triggers can be classified based on the following parameters.
•Classification based on the timing
•BEFORE Trigger: It fires before the specified event has occurred.
•AFTER Trigger: It fires after the specified event has occurred.
•INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only
for DML )
•Classification based on the level
•STATEMENT level Trigger: It fires one time for the specified event statement.
•ROW level Trigger: It fires for each record that got affected in the specified event. (only
for DML)
•Classification based on the Event
•DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
•DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
•DATABASE Trigger: It fires when the database event is specified
(LOGON/LOGOFF/STARTUP/SHUTDOWN)
Parts of trigger
 Triggering event or statement
 insert, update or delete event
 Trigger restriction
 Is Boolean value true or false for trigger to fire.(when clause)
 Trigger action
 Code which is executed when event occurs
Types of triggers
 Row trigger
 Fired for each row effected by trigger statement.(once for each
row)
 Statement trigger
 Fired once for triggering statements regardless of number of
rows effected.
Another classification of triggers
 BEFORE trigger
 Trigger executes its trigger action before the triggering
statement
 AFTER trigger
 Trigger executes its trigger action after the triggering statement
Creating a trigger
Create or replace trigger tiggername
BEFORE/AFTER
DELETE/INSERT/UPDATE of column-name
On table
REFFERENCING OLD AS old, NEW AS new
For each row
When condition
Declare
Variable declarations;
Begin
Statements;
Exception
Error handling statements
End;
Create PL/SQL trigger which will tell about
the operation performed on database.
Create or replace trigger t1
Before INSERT or UPDATE or DELETE
ON Student
Begin
IF INSERTING then
Dbms_output.put_line(‘operation performed inserting’);
ELSIF UPDATING then
Dbms_output.put_line(‘operation performed Updating’);
ELSE
Dbms_output.put_line(‘operation performed Deletion’);
End if;
End;
Create PL/SQL trigger which will convert
the name of the student to uppercase before
inserting or updating the name column of
student database.
Create or replace trigger t12
Before INSERT or UPDATE of NAME
ON Student
For each row
Begin
:NEW.NAME := UPPER(:NEW.NAME);
End;
Create PL/SQL trigger which will delete the
detail of the employee from employee table
when particular branch is deleted from dept
Create or replace trigger t22
Before delete
On dept
For each row
Begin
Delete from employee where
branch_id= :OLD.branch_id;
End;
Dropping a trigger
 Drop trigger tiggername;

 Eg
Drop trigger t11
Cursors
 A cursor is a work area where the result of a SQL query is
stored at server side.
 A cursor is a PL/SQL construct that allows us to name
these work area.
 The data stored in the cursor is known as active data set
 Declare a cursor
 Open a cursor
 Fetch or Read from a cursor
 Close cursor
Types of cursors
 Implicit cursor
 It is a work area that is declared, opened and closed internally
by the oracle engine. PL/SQL declared a cursor implicitly for
all SQL data manipulation statements.
 Explicit cursor ( user defined)
 It is a work area that is declared, opened and closed externally
by the user.
 Define in DECLARE section of PL/SQL block
General Cursor attributes
To keep status of a cursor:

 %ISOPEN- returns true if cursor is open

 %FOUND- returns true if record was fetched successfully
 %NOTFOUND- returns true if record was not fetched
successfully
 %ROWCOUNT- returns number of records processed
from cursor.
Implicit cursors
 Implicit cursor attributes are used to access the
information about the status of last insert, update, and
delete or single row select statement. SQL is the default
name for the implicit cursor.
 Implicit cursor attributes:
 SQL%ISOPEN- is always false because oracle engine
closes the implicit cursor automatically after execution.
 SQL%FOUND
 SQL%NOTFOUND
 SQL%ROWCOUNT
Write a PL/SQL block to display a message
that whether a record is updated or not.
Begin
Update customers set Address= 'delhi' where id=1;
If SQL%FOUND then
Dbms_output.put_line('record updated');
End if;
If SQL%NOTFOUND then
Dbms_output.put_line('record not updated');
End if;
End;
Write a PL/SQL block to count the number
of rows affected by an update statement
Declare
Num number(2);
Begin
Update customers set Address ='Kota' where Address
='delhi';
Num := SQL%ROWCOUNT;
Dbms_output.put_line('total rows affected =' || Num);
End;
Explicit cursors
When a query returns multiple rows, user can explicitly
declare a cursor to process the rows.

 %ISOPEN
 %FOUND
 %NOTFOUND
 %ROWCOUNT
Steps of execution
 Declare the cursor
 Open the cursor
 Using loop, fetch the data from cursor one row at a time
and store in memory variable
 Exit from the loop
 Close the cursor
Declaring a cursor
 Cursor cursorname IS select statements.

 Cursor C123 IS select rollno, name from student where

branch=‘CSE’;
Opening a cursor
 Opening a cursor executes the query and identifies the
result set.
 Open cursorname;
 Eg:
Open C123;
Fetching from cursor
Fetch is used to load the rows from result set into memory
variable, but one row at a time.

 FETCH cursorname INTO variable;

Eg:
 FETCH C123 INTO my_rollno, my_name;
Typically fetch statement is used with loop:

Loop
FETCH C123 into my_record;
Exit when C123%notfound;
--Other statements;
End loop;
Closing a cursor
 CLOSE cursorname;

 CLOSE C123;
Write a PL/SQL cursor to display the name
of the students belonging to CSE branch
Declare
Cursor C123 is select name from customers where address ='Delhi';
my_name customers.name%type;
Begin
Open C123;
Loop
Fetch C123 into my_name;
Exit when C123%NotFound;
dbms_output.put_line(my_name);
End loop;
Close C123;
End;
PL/SQL subprograms are named PL/SQL blocks that
can be invoked with a set of parameters. PL/SQL
provides two kinds of subprograms −
Functions − These subprograms return a single value;
mainly used to compute and return a value.
Procedures − These subprograms do not return a value
directly; mainly used to perform an action.
Creating a Procedure
A procedure is created with the CREATE OR
REPLACE PROCEDURE statement. The simplified
syntax for the CREATE OR REPLACE
PROCEDURE statement is as follows −
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Where,
procedure-name specifies the name of the procedure.
[OR REPLACE] option allows the modification of an existing procedure.
The optional parameter list contains name, mode and types of the parameters. 
IN represents the value that will be passed from outside and OUT represents the
parameter that will be used to return a value outside of the procedure.
procedure-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone procedure.
Executing a Standalone Procedure
A standalone procedure can be called in two ways −
•Using the EXECUTE keyword
•Calling the name of the procedure from a PL/SQL block

EXECUTE procedure name;

Deleting a Standalone Procedure

A standalone procedure is deleted with the DROP
PROCEDURE statement. Syntax for deleting a procedure is −
DROP PROCEDURE procedure name;
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT
number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
Write procedure computes the square of
value of a passed value. 
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/
The PL/SQL Function is very similar to PL/SQL Procedure. The main difference
between procedure and a function is, a function must always return a value, and on the
other hand a procedure may or may not return a value. Except this, all the other things
of PL/SQL procedure are true for PL/SQL function too.
Syntax
CREATE [OR REPLACE] FUNCTION function_name [parameters]  
[(parameter_name [IN | OUT | IN OUT] type [, ...])]  
RETURN return_datatype  
{IS | AS}  
BEGIN  
   < function_body >  
END [function_name];  
Function_name: specifies the name of the function.
[OR REPLACE] option allows modifying an existing function.
The optional parameter list contains name, mode and types of the parameters.
IN represents that value will be passed from outside and OUT represents that this
parameter will be used to return a value outside of the procedure.
1.create or replace function adder(n1 in numb
er, n2 in number)    
2.return number    
3.is     
4.n3 number(8);    
5.begin    
6.n3 :=n1+n2;    
7.return n3;    
8.end;    
1.DECLARE    
2.   n3 number(2);    
3.BEGIN    
4.   n3 := adder(11,22);    
5.   dbms_output.put_line('Addition is: ' || n3);  
  
6.END;    
7./    
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;

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;