PL/SQL
-Ashu Mehta
Database Systems
INTRODUCTION
PL/SQL stands for procedural language/structured
query language
It is an extension of the SQL language
It is the superset of structured query language
With the use of SQL user can only manipulate the
information stored in the database
PL/SQL extends SQL by adding control structures
found in other procedural languages.
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.
Applications written in PL/SQL are fully portable.
PL/SQL provides access to predefined SQL packages.
PL/SQL provides support for Object-Oriented
Programming.
PL/SQL provides support for Developing Web
Applications and Server Pages.
Architecture of pl/sql
Pl/sql engine executes pl/sql blocks.
Pl/sql engine executes only the procedural statements
and send the sql statements to sql statement executor
in the oracle server
Pl/sql engine resides in the oracle server
The call to the oracle engine needs to be made only
once to execute any number of sql statements
Since the oracle engine is called only one for each
block, resulting increased speed of processing as
compared to call for each sql sentence.
Structure of PL/SQL Language
Consist of following sections
Declare(optional)
Used to declare variables and constants
Is an optional section
Begin(required)
It is the executable section containing the code which is
executed when block is run
Exception(optional)
Handles exceptions occurring during processing
Is an optional section.
End;(required)
PL/SQL block types
PL/SQL operators
Relational operators
Logical operators
Variable Declaration
DECLARE
a number := 10;
b number := 20;
c number;
Declaring a Constant
PI CONSTANT NUMBER := 3.141592654;
Example Program
DECLARE
message varchar2(30):= ‘Hello World’;
BEGIN
dbms_output.put_line(message);
END;
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-
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;
Select into
Used to fetch values from some existing tables.
Syntax-
Select column_name into variable_name from table_name
where condition;
Example-
declare
name varchar(10);
begin
select first_name into name from student where r_no=1;
dbms_output.put_line(name);
end;
Control statements
Conditional / selection
Iterative
Sequence
Conditional / selection
IF condition then
Sequence of statements;
Else
Sequence of statements;
End if;
Example-
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;
Iterative
Loop
While – loop
For-loop
Loop
Sequence of statements;
Exit when condition;
End loop;
Loop Example-
Declare
i number(2);
Begin
i:=1;
Loop
Dbms_output.put_line(i);
i:=i+1;
Exit when i>10;
End loop;
End;
While Loop Example-
Declare
A number(2);
Begin
A:=1;
While a<=10
Loop
Dbms_output.put_line(a*a);
A:=a+1;
End loop;
End;
For loop Example-
Declare
Total number(4);
Begin
For i in 1..10
Loop
Total:=2*i;
Dbms_output.put_line(‘2*’||i||’=‘||total);
End loop;
End;
Go To Example-
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>>
Dbms_output.put_line(‘End of Program ‘);
End;
Subprograms
Named pl/sql blocks that can accept parameters and
be invoked are called as subprograms.
Two types
Procedure
Function
Stored procedure: a procedure that has been compiled
and stored in any of the oracle engine’s system table.
Procedure Vs Function
A procedure or a function is a group of sql and pl/sql
statements that is used to perform a specific task.
A procedure is used to perform an action whereas a
function is used to compute a value.
A procedure may return no value whereas a function
must return a value.
Why do we use subprograms?
Modularity: It allows a programmer to divide a
program into more than one well defined units called
modules.
Reusability: it enables a subprogram to be used in
any number of applications.
Procedure
Declare
Global variables declaration;
Procedure procedure name
(Arguments IN/OUT/IN OUT data types)
IS/AS
Variable and constant declaration;
Begin
PL/SQL statements;
Exception
Statements;
End procedure name;
Begin
Executable statements;
Procedure calling;
End;
Procedure- example
declare
num1 number(2);
num2 number(2);
mul number(4);
procedure multiplication (num1 in number, num2 in number, mul out number)is
begin
mul:=num1*num2;
end multiplication;
begin
num1:=:num1;
num2:=:num2;
multiplication(num1, num2, mul);
dbms_output.put_line(mul);
end;
Stored procedure- Example
create or replace procedure addition(num1 in number,
num2 in number, sum1 out number)
is
begin
sum1:=num1+num2;
end;
declare
num1 number(4);
num2 number(4);
sum1 number(4);
begin
num1:=:num1;
num2:=:num2;
addition(num1, num2, sum1);
dbms_output.put_line(sum1);
end;
Function
Declare
Global variables declaration;
Function Function name
(Arguments IN data types…….)
Return data type
IS/AS
Variable and constant declaration;
Begin
PL/SQL statements;
Exception
Statements;
End function name;
Begin
Executable statements;
Function calling;
End;
Function-Example
Declare
Num1 number(2);
Num2 number(2);
Mul number(4);
Function multiplication(num1 in number, num2 in number)
Return number is mul number;
Begin
Mul:=num1 * num2;
Return(mul);
End multiplication;
Begin
Num1:=:num1;
Num2:=num2;
Mul:=multiplication(num1, num2);
Dbms_output.put_line(mul);
End;
Function-Example
Declare
Fact number(4);
N number(2);
Function factorial(n in number)
Return number is I number(2);
Begin
Fact:=1;
For I in 1..n loop
Fact:=fact*I;
End loop;
Return(fact);
End factorial;
Begin
N:=:n;
Fact:=factorial(n);
Dbms_output.put_line(fact);
End;
%TYPE
Provide the data type of a variable or column.
Example-
sal employee.salary%TYPE;
Example-
Declare
a emp.ta%TYPE;
b emp.da%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
%ROWTYPE has all properties of %TYPE and one
additional that we required only one variable to access
any number of columns.
Example-
dept_rec dept%ROWTYPE; -- declaring record variable.
detp_rec.deptno;
dept_rec.deptname; -- accessing coloums
Declare
Record1 emp%ROWTYPE;
Begin
Select * into record1 from emp where empid=12;
Record1.total:=record1.ta+record1.da;
Update emp set total=record1.total where empid=12;
End;
Exception handling- example
create table emp(id int, name varchar(10));
insert into emp values(502, 'a');
insert into emp values(502, 'a');
insert into emp values(502, 'a');
declare
n emp.name%type;
begin
select name into n from emp where id=502;
dbms_output.put_line('empname :='||n);
exception
when too_many_rows then
dbms_output.put_line('more than one row returned');
end;
Exception handling- example
declare
a number;
b number;
c number;
e exception;
begin
a:=:a;
b:=:b;
if b=0 then
raise e;
end if;
c:=a/b;
dbms_output.put_line('result='||c);
exception when e then
dbms_output.put_line('error!- your divisor is zero');
end;
