UNIT 5 PL/SQL

PREPARED BY GUNJA DAVE

 PL/SQL

PROCEDURES
FUNCTIONS CURSORS* TRIGGERS
*
WHAT IS PL/SQL

 SQL + procedural PROGRAMMING

 Procedural language extension to SQL. (procedural sql)
 Block structured language, logical block,nested sub blocks.
 Includes loops,conditions,functions,variables,constant, cursors
triggers.
 Processed by PL/SQL engine inside the oracle server.
 SQL executes single statement, pl/sql execute single unit/block.
 ADVANTAGES

 PL/SQL can execute a number of queries in one block using single command
 Can be written using ASCII text editor, so it is preferable to any OS
in which oracle runs.
 Provides high security level.
 More manageable and reusable.
 It offers numerous data types.
 PL/SQL is tightly integrated with SQL.
 It supports OOP.
 It supports the development of web apps and server pages.
BLOCK STRUCTURE OF PL/SQL
(SYNTAX):
 A pl/sql program may have more than one blocks.(called
sub blocks). Each block provides 3 sections:
Declaration section(optional) Variable, constants

Execution section Logics, queries, executable commands,

printing outputs, statements

Exception section (optional) Execptional

handling statements
SYNTAX;

DECLARE
<declaration section>
BEGIN KEYWORDS
<execution commands>
EXCEPTION
<exception handling>
END;
EXAMPLE: PRINTING HELLO WORLD

BEGIN
Package Procedure
Dbms_output.put_line(‘Hello world!’);
END;
/
EXECUTING BLOCK (RUN PL/SQL BLOCK)

 Open any editor.

 To open editor like notepad an EDIT command is used.
Example:
EDIT (ed) e: sql/pl/[Link] -- this will ask you to create a file if it is not
present, click yes. It will create a new file named [Link]
 Write code in file and save it with .sql extension and last statement should be /.
 close the file.
EXECUTING BLOCK (RUN PL/SQL BLOCK)

 To execute this file, use any of the following commands in terminal.

1. Run file name

2. Start file name
3. @ file name
Example:
SQL > @ E:/ SQL/PL/[Link]

NOTE: if output is not visible in prompt write SET SERVEROUTPUT ON then run
the program.
PL/SQL COMMENTS:

DECLARE
--variable declaration
Message varchar2(20):=‘hello world!’;
BEGIN
/*
PL/SQL executable statements(s)
*/
Oracle package Procedure/
Dbms_output.put_line(message);
function

END;
 VARIABLE DECLARATION:

 Variable stores values that can be referenced within

a block.
 Variables and constants must be declared in
DECLARE portion of the program.
 Variable name can not be case-sensitive.
General syntax is:

DECLARE
Identifiername [CONSTANT] datatype(not null) [:=value
expression];
e.g.
message varchar(20) := ‘hello world!’;
DECLARING VARIABLE

DECLARE
a NUMBER; -- a is variable
BEGIN
a := 50; -- we stored 50 in variable a
DBMS_OUTPUT.PUT_LINE('a = '|| a); -- to
print
END;
/
INITIALIZING VARIABLES IN PL/SQL:

 Sales number (10,2);

 Name varchar2(20);
 Count integer := 0;
 Greetings varchar(20) DEFAULT ‘ Have a Good Day’;
TAKING INPUT FROM USER:
DECLARE
a NUMBER;
BEGIN
a := :a; --this will take input from user in apex
/*
a:= &a; this will take input from user in terminal
if there is a string it will be encoded with single quotes. ( ‘&a’)
*/
DBMS_OUTPUT.PUT_LINE(‘ a = ‘ || a);
END;
/
CONTROL STRUCTURES:

 In PL/SQL, the flow of execution can be controlled in three different manners as given
below:
1. Conditional Control
2. Iterative Control
3. Sequential Control
1. CONDITIONAL CONTROL:

 To control the execution of block of code based on some condition, PL/SQL provides the IF
statement. The IF-THEN-ELSEIF-ELSE-END IF construct can be used to execute specific part
of the block based on the condition provided.
 PL/SQL IF
Syntax:
IF condition THEN
Statement : {It is executed when condition is true.}
END IF;

Syntax: IF ELSE

IF condition THEN
Statement : {It is executed when condition is true.}
ELSE
Statement : {It is executed when condition is false.}
END IF;
EXAMPLE simple IF:
DECLARE
age number;
BEGIN
IF age>=18 THEN
dbms_ouput.put_line(‘License
approved’);
END IF;
END;
/
EXAMPLE IF-then-else:
accept x number prompt 'Please enter something: '
DECLARE
num number;
BEGIN
num := &x;
IF mod(num,2)=0 THEN
dbms_output.put_line(‘even number’);
ELSE
dbms_ouput.put_line(‘odd number’);
END IF;
END;
/
 Syntax: IF –THEN-ELSIF

IF condition THEN
Statement : {It is executed when condition is true.}
ELSIF
Statement : {It is executed when condition is true.}
ELSIF
Statement : {It is executed when condition is true.}
ELSIF
Statement : {It is executed when condition is true.}
ELSE
Statement : {It is executed when condition is false.}
END IF;
EXAMPLE IF-then-elsif:
DECLARE
marks number:=95;
grade varchar(3);
BEGIN
if marks>=90 then
grade:='A';
elsif marks>=60 and marks<=90 then
grade :='B';
elsif marks>=33 and marks<=60 then
grade :='C';
else
dbms_output.put_line('Failed');
End if;
dbms_output.put_line('Grade= ' || grade);
END;
/
 PL/SQL CASE STATEMENT
Syntax: CASE DECLARE
CASE expression grade char(1):=‘A’;
WHEN Condition _1 THEN result_1
BEGIN
WHEN Condition _2 THEN result_2
CASE grade
WHEN Condition _3 THEN result_3
… WHEN ‘A’ THEN dbms_output.put_line(‘excellent’);
WHEN ‘B’ THEN dbms_output.put_line(‘Very good’);
WHEN Condition _n THEN result_n
ELSE result WHEN ‘C’ THEN dbms_output.put_line(‘good’);
END CASE; WHEN ‘D’ THEN dbms_output.put_line(‘Passes’);
WHEN ‘E’ THEN dbms_output.put_line(‘Passes with gra

ELSE
dbms_output.put_line(‘Failed’);
END CASE;
END;
2. ITERATIVE CONTROL

 It allows us to repeat a block of code multiple times based on

conditions. It is called looping.
 PL/SQL provides three constructs to implement loops, as listed
below:
 LOOP
 WHILE
 FOR
1. FOR SYNTAX:

FOR var IN initial_value..final_value

LOOP
Statements
END LOOP;
EXAMPLE: PRINTING 1 TO 5 NUMBER.

DECLARE
k NUMBER;
BEGIN
FOR k IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(k);
END LOOP;
END;
/
PRINTING 1 TO5 WITH REVERSE KEY WORD

DECLARE
counter NUMBER;
BEGIN
FOR counter IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('REVERSE VALUE: '|| counter);
END LOOP;
END;
/
NESTED LOOP SYNTAX:

BEGIN
–outer loop
FOR loop_variable1 in start_value1 ..end_value1 LOOP
–inner loop
FOR loop_variable2 in start_value2 ..end_value2 LOOP
set of statements
END LOOP;
–inner loop end
END LOOP;
–outer loop end
END;
/
2. LOOP

 Loop is an infinte [Link] executes commands in its body infinte times.

 So, it requires and EXIT statement within its body to terminate the loop after executing
specific iteration.
SYNTAX:
LOOP
LOOP
--execute commands Statements;
END LOOP EXIT;
{Or Exit WHEN condition;1}
END LOOP;
EXAMPLE: PRINTING 1 TO 10

DECLARE
i number:=1; -- initialization
BEGIN
LOOP
EXIT WHEN i>11; --here loop with exit when it reaches to 11.
Dbms_output.put_line(i);
i:=i+1; --increment
END LOOP;
END;
3. WHILE

 WHILE loop executes commands in its body part as long as the condition remains TRUE.
 The loop terminates when the condition evaluates to FALSE or NULL.
 The EXIT statement can also be used to exit the loop.
SYNTAX WHILE LOOP

WHILE condition
LOOP
--execute commands
END LOOP
EXAMPLE :PRINTING 1 TO 10;

DECLARE
i number:=1;
BEGIN
WHILE i<=10
LOOP
Dbms_output.put_line(i);
i:=i+1;
END LOOP;
END;
DECLARE
a int;
B int;
BEGIN
a:=0;
b:=&b;
WHILE a<b
LOOP
a:=a+1;
dbms_output.put_line(a);
END LOOP;
END;
3] SEQUENTIAL CONTROL:

 Normally, execution proceeds sequentially within the block

of code.
 Sequence can be changed conditionally as well as
unconditionally.
 To alter the sequence unconditionally, the GOTO
statement can be used.
SYNTAX:

GOTO label_name;
..
..
<<label_name>>
Statement;
EXAMPLE:

DECLARE
a number(2):=10;
BEGIN
<<loopstart>>
WHILE a<20
LOOP
dbms_output.put_line('value of a:'||a);
a:=a+1;
IF a = 15 THEN -- will jump on 16
a:=a+1;
GOTO loopstart;
END IF;
END LOOP;
END;
select * from emp;

BEGIN
FOR E IN (select e_name,dept,salary from emp)
LOOP
dbms_output.put_line(E.e_name || ' '||[Link]||'
'||[Link]);
END LOOP;
END;
 CURSOR

 A cursor is and area in memory where the data required to execute SQL statement.
 So, a cursor referred as work area.
 So, the size of cursor will be the same as a size to hold this data.
 A cursor contains information on a select statement and the rows of data accessed by it.
 Used to fetch and process the rows returned by the SQL statement, one at a time.
 PL/SQL Cursor Attributes

Attribute Description
%FOUND Its return value is TRUE if DML statements like
INSERT,UPDATE,DELETE affect at least one row
or more rows or a SELECT INTO statement
returned one or more rows. Otherwise it returns
FALSE.
%NOTFOUND Its return value is TRUE if DML statements like
INSERT,UPDATE,DELETE affect no row, or a
SELECT INTO statement return no rows.
Otherwise it returns FALSE. It is just opposite of
%FOUND.
%ISOPEN It always returns FALSE for implicit cursors,
because the SQL cursor is automatically closed
after executing its associated SQL statements

%ROWCOUNT It returns the number of rows affected by DML

statements like INSERT,DELETE, and UPDATE or
returned by a SELECT INTO statement.
TYPES OF CURSOR:

1. implicit cursor
2. explicit cursor
IMPLICIT CURSOR

 Automatically generated by Oracle while an SQL statement is

executed , if you don’t use an explicit cursor for the statement.
 Created by default to process the statements when DML
statements like INSERT,UPDATE,DELETE etc. are executed.
 SYNTAX to use attributes of implicit cursor can be given as:
sql%attributename
 Before open implicit cursor, its attribute contains NULL as value.
EXAMPLE: TO INCREASE SALARY OF EMPLOYEE AND COUNT
THE RESULT.
Select * from emp;
DECLARE
rowCount number;
BEGIN
update emp set salary=salary+1000;
if sql%found then
rowCount := sql%rowcount;
dbms_output.put_line(‘total’|| rowCount ||’ records are Updated.’);
END IF;
END;
EXAMPLE 2: UPDATING RECORD

 DECLARE
EXPLICIT CURSOR

 When a cursor is opened by a user to process data using PL/SQL

block, it is referred to as an explicit cursor.
 It is opened by user. So, user has to take care about managing it.
 It is used when there is a need to process more than one record
individually.
 Even though the cursor stores multiple records, only one record can
be processed at a time, which is called as current row.
STEPS TO MANAGE AN EXPLICIT CURSOR.

1. Declare cursor
2. Open a cursor
3. Fetching Data
4. Processing data
5. Closing cursor
EXPLICIT CURSORS STEPS:

1. Declare the cursor to initialize in the memory.

CURSOR name IS SELECT statement;
2. Open the cursor to allocate memory.
OPEN cursor_name;
3. Fetch the cursor to retrieve data.
FETCH cursor_name INTO variable_list;
4. Close the cursor to release allocated memory.
CLOSE cursor_name;
Write a PL/SQL block to fetch data from table using explicit cursor.

DECLARE
name emp.e_name%type;
e_salary [Link]%type;
cursor c_emp IS select e_name,salary from
emp;--declaring cursor

BEGIN
open c_emp;
LOOP
fetch c_emp INTO name,e_salary;
EXIT WHEN c_emp%notfound;
dbms_output.put_line(name||' '||e_salary);
END LOOP;
close c_emp;
END;
FUNCTION:

 Function is a block of code that perform a given task and return value.
 A function is same as a procedure except that it returns a value

1. It only runs when it call.

2. It saves our lots of efforts.
PARAMETERS

 IN parameters: The IN parameter can be referenced by the procedure or function. The

value of the parameter cannot be overwritten by the procedure or the function.
 OUT parameters: The OUT parameter cannot be referenced by the procedure or function,
but the value of the parameter can be overwritten by the procedure or function.
 INOUT parameters: The INOUT parameter can be referenced by the procedure or function
and the value of the parameter can be overwritten by the procedure or function.
SYNTAX : FUNCTION

CREATE [OR REPLACE] FUNCTION func_name (argument IN dataType…)

RETURN dataType
IS |AS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END ;
FILE1:CREATING FUNCTION

CREATE OR REPLACE FUNCTION show

RETURN varchar2
IS
--Declare variable if any
BEGIN
return ‘learn coding’;
END;
/
FILE 2 : EXECUTING FUNCTION

BEGIN
Dbms_output.put_line(show()); -- () will be used when calling
a function.
END;
ADD TWO NUMBER FUNCTION

Create or replace function adder(n1 in number, n2 in number)

Return number
Is
n3 number(8);
Begin
n3 :=n1+n2;
Return n3;
End;
/
FUNCTION CALL

DECLARE
Total number;
BEGIN
Total:=adder(34,52);
Dbms_output.put_line(total);
END;
GETTING TOTAL EMPLOYEES OF TABLE

create or replace function gettotalemp

return number
IS
v_count integer:=0;
begin
select count(*) into v_count from emp;
return v_count;
END;
EXECUTION

BEGIN
Dbms_output.put_line(gettotalemp());
END;
PROCEDURE:

 A procedure is a group of PL/SQL statements that performs specific

task.
 A procedure and function is a named PL/SQL block of code. This
block can be compiled and successfully compiled block can be
stored in Oracle database. This procedure and function is called
Stores Procedure or Function.
 We can pass parameters to procedures and functions. So that their
execution can be changed dynamically.
SYNTAX

CREATE [OR REPLACE] PROCEDURE proc_name (argument [IN, OUT, IN OUT]

datatype)
IS |AS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END ;
PROGRAM 1

CREATE OR REPLACE PROCEDURE myfirstproc

IS
BEGIN
Dbms_output.put_line(‘hi’);
END;
EXECUTING PROCEDURE

Syntax:
EXECUTE [or EXEC] procedure_name (parameter);
e.g.
EXECUTE myfirstproc;
DROPPING PROCEDURE

Syntax for drop procedure

DROP PROCEDURE procedure_name;
DROP PROCEDURE myfirstproc;
PROCEDURE 2 INSERTING VALUE IN TABLE

CREATE or REPLACE PROCEDURE INSERTUSER

(
v_id number, v_name varchar2)
IS
BEGIN
Insert into userdetails(id,name) values ( v_id,v_name);
END;
EXECUTING PROCEDURE

 Execute insertuser(2,’jaman’);
PROGRAM 3 MAX VALUE FINDING

CREATE PROCEDURE maxvalue (v_x int, v_y int, v_z out int)
AS
BEGIN
IF (v_x>v_y) THEN
v_z:=v_x;
ELSE
v_z:=v_z;
END IF;
END;
EXECUTING

Declare
Z int;
Begin
Execute maxvalue(10,20,z);
dbms_output.put_line(z);
END;
PROGRAM 4 SQUARE OF A NUMBER

Create procedure sq(v_z in out int)

AS
BEGIN
V_z:=v_z*v_z;
End;
EXECUTING PROCEDURE

DECLARE
Z int;
Begin
Z:=5;
Sq(z);
Dbms_output.put_line(z);
End;
TRIGGERS

 A trigger is named PL/SQL block stored in the oracle Database and executed automatically
when a triggering event takes place.
 The key feature of the trigger is that it is fired automatically when DML statements such as
Insert, Delete, and Update are performed on a table.
 EVENT:
1. DML(INSERT/UPDATE/DELETE)
2. DDL(CREATE / ALTER)
3. System event (STARTUP /SHUTDOWN)
4. User Event (LOGIN /LOGOUT)
:NEW AND : OLD CLAUSE

 In a row level trigger, the trigger fires for each related row. And sometimes it is required to
know the value before and after the DML statement.
 Oracle has provided two clauses in the RECORD-level trigger to hold these values. We can
use these clauses to refer to the old and new values inside the trigger body.
:NEW – It holds a new value for the columns of the base table/view during the trigger execution.
:OLD – It holds old value of the columns of the base table/view during the trigger execution.
 SYNTAX:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT [OR] | UPDATE [OR] | DELETE}
HEADER
ON table_name
FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
BODY
Executable-statements
EXCEPTION
Exception-handling-statements
END;
TYPES OF TRIGGERS: BEFORE, AFTER FOR EACH ROW, FOR
EACH STATEMENT

 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.
 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)
CREATE A TABLE ON WHICH YOU WANT TO TRIGGER AN
EVENT

CREATE TABLE TRIGGERDEMO

(
SH_NAME VARCHAR(20)
);
PROGRAM 1: TRIGGER FOR EVENT: INSERT
Trigger name
CREATE OR REPLACE TRIGGER bi_trigger
BEFORE INSERT ON triggerdemo
FOR EACH ROW Trigger will occur for every new row inserted
DECLARE
v_user varchar(20);
BEGIN
We can get user name from dual table
select user into v_user from dual;
dbms_output.put_line('inserted value by mr./miss ' || v_user);
END;
/
It produces the following result −
Trigger created.
EXECUTING TRIGGER

SYNTAX:
EXECUTE TRIGGER_NAME;

Execute bi_trigger;
 --all three events in one trigger
CREATE OR REPLACE TRIGGER tr_superheros To execute:
BEFORE INSERT OR UPDATE OR DELETE ON
triggerdemo EXECUTE tr_superheros
FOR EACH ROW
DECLARE
v_user varchar(20);
BEGIN
select user into v_user from dual;
IF inserting then
dbms_output.put_line('one row inserted
by' ||v_user);
ELSIF deleting then
dbms_output.put_line('one deleted by'
||v_user);
ELSIF updating then
dbms_output.put_line('one row
Updating by' ||v_user);
ENd if;
O/p:
END;trigger created
 AUDIT TABLE/SPY TABLE PROGRAM
CREATE A SPY TABLE: /*
When trigger will be occur on triggerdemo table
CREATE TABLE SH_AUDIT Like any user perform insert/update/delete,
( the entry will automatically inserted in
new_name varchar(50), Sh_audit table with username and date.
old _name varchar(50), */
user_name varchar(50),
entry_date date,
operation varchar(20)
);
CREATE OR REPLACE trigger superheros_audit
BEFORE INSERT OR DELETE OR UPDATE ON triggerdemo
FOR EACH ROW
DECLARE
v_user vacrhar2(30);
v_date varchar2(30);
BEGIN
select user, To_char( sysdate, 'dd-mm-yy') INTO
v_user, v_date from dual;
/*inserting/updating/deleting is predefined predequite for
PL/SQL for condition we can use*/
IF inserting then
insert into sh_audit
values(:NEW.sh_name,NULL,v_user,v_date,'Insert');
dbms_output.put_line('record inserted');
ELSIF deleting then
insert into sh_audit values

(NUll,:OLD.sh_name,v_user,v_date,'delete');
dbms_output.put_line('record deleted');
ELSIF updating then
insert into sh_audit values

(:NEW.sh_name,:OLD.sh_name,v_user,v_date,'update');
dbms_output.put_line('record updated');
END IF;
TO CHECK TRIGGER IS OCCURRED OR NOT

 Perform any DML statement on triggerdemo table

insert into triggerdemo values('scarlet witch’);

Now check audit/spy table:
select * from sh_audit;