Ex. No.
: 6 PROCEDURES AND FUNCTIONS
AIM
To execute program with Procedures and Functions using PL/SQL.
PROCEDURES
A stored procedure or in simple a proc is a named PL/SQL block which performs one or
more specific task. A procedure has a header and a body. The header consists of the name of the
procedure and the parameters passed to the procedure. The body consists of the declaration
section, execution section and exception section. A procedure may or may not return any value.
FUNCTIONS
A function is a named PL/SQL Block which is similar to a procedure. The major difference
between a procedure and a function
SYNTAX
PROCEDURE
To create
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter [,parameter])
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
To execute
Exec<procedure name> (parameters);
Procedures in PL/SQL:
Example 1:
SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome'|| p_name);
END;
/
Procedure created.
SQL> EXEC welcome_msg('sudha');
Welcomesudha
PL/SQL procedure successfully completed.
Example 2:
SQL> create table userinfo(id int,name varchar(20));
Table created.
SQL> create or replace procedure insert_user (id IN NUMBER, name IN VARCHAR2)
is
begin
insert into userinfo values(id,name);
dbms_output.put_line('record inserted successfully');
end;
/
Procedure created.
To call the procedure
SQL> exec insert_user(101,'raghul');
record inserted successfully
PL/SQL procedure successfully completed.
SQL> select * from userinfo;
ID NAME
---------- --------------------
101 raghul
Example 3:
SQL>
create or replace PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
dbms_output.put_line('Minimum value '|| z);
END;
/
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
Example 4:
SQL> 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;
/
Square of (23): 529
PL/SQL procedure successfully completed.
FUNCTION
To create
CREATE [OR REPLACE] FUNCTION function_name
(parameter_name [IN | OUT | IN OUT] type [, ...])
RETURN return_datatype
{
IS | AS
}
BEGIN
< function_body >
END [function_name];
Example 1:
SQL> DECLARE
a number;
b number;
c number;
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;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
Example 2:
SQL> select * from section1;
S_ID S_NAME STRENGTH
---------- -------------------- ----------
1 computer science 20
1 computer science 20
3 geeksforgeeks 60
SQL> CREATE OR REPLACE FUNCTION totalstrength
RETURN number IS
total number := 0;
BEGIN
SELECT sum(strength) into total
FROM section1;
RETURN total;
END;
/
Function created.
SQL> DECLARE
answer number;
BEGIN
answer:= totalstrength();
dbms_output.put_line('Total Strength. of Students is: ' || answer);
END;
/
Total Strength. of Students is: 100
PL/SQL procedure successfully completed
RESULT
Thus the program using procedures and functions are executed.
Ex. No.: 4b DATABASE PROGRAMMING: IMPLICIT AND EXPLICIT CURSORS
AIM
To create implicit and explicit cursors, in PL/SQL block.
CURSOR in PL/SQL
A cursor is a handle or pointer to this context area. PL/SQL allows the programmer to control the
context area and what happens to it as the statement is processed.
SYNTAX
Declaring a cursor
Syntax:
DECLARE
/* output variables to hold the results of the query */
/* bind variables used in the query*/
/* Cursor declaration*/
BEGIN
OPEN cursor_name;
FETCH cursor_name INTO cursor_variable;
.
CLOSE cursor_name;
END;
Each cursor contains the followings 5 parts:
1. Declare Cursor: In this part we declare variables and return a set of values.
2. Open: This is the entering part of the cursor.
3. Fetch: Used to retrieve the data row by row from a cursor.
4. Close: This is an exit part of the cursor and used to close a cursor.
5. Deallocate: In this part we delete the cursor definition and release all the system
resources associated with the cursor
Example:
SQL> create table emp_det (id integer,name varchar2(10),age integer,address
varchar2(20),salary integer);
Table created.
SQL> select * from emp_det;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
101 suresh 35 chennai 45500
302 ramesh 36 chennai 50500
306 sumit 28 hosur 50500
400 sujit 32 bangalore 60500
Explicit cursors:
Example 2:
SQL> set serveroutput on
SQL> DECLARE
e_id emp_det.id%type;
e_name emp_det.name%type;
e_addr emp_det.address%type;
CURSOR c_employees is
SELECT id, name, address FROM emp_det;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees into e_id, e_name, e_addr;
EXIT WHEN c_employees%notfound;
dbms_output.put_line(e_id || ' ' || e_name || ' ' || e_addr);
END LOOP;
CLOSE c_employees;
END;
101 suresh chennai
302 ramesh chennai
306 sumit hosur
400 sujit bangalore
PL/SQL procedure successfully completed.
SQL> select * from bill;
NAME ADDRESS CITY UNIT
---------- -------------------- -------------------- ----------
sudha thanjavur thanjavur 100
kani thanjavur thanjavur 200
arivumalar chennai chennai 200
geetha chennai chennai 100
nithish bangalore bangalore 300
Result:
Thus implicit & explicit cursors were implemented in PL SQL
Ex. No.: 5 EXCEPTION HANDLING
AIM
To write a PL/SQL program with exception handling mechanism.
EXCEPTION HANDLING MECHANISM
PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block
known as exception Handling. Using Exception Handling we can test the code and avoid it
from exiting abruptly.
When an exception occur a message which explains its cause is received. PL/SQL
Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message
SYNTAX
DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;
Program 2:
ZERO_DIVIDE = raises exception WHEN dividing with zero.
SQL> set serveroutput on
SQL> DECLARE
a int:=10;
b int:=0;
answer int;
BEGIN
answer:=a/b;
dbms_output.put_line('the result after division is'||answer);
exception
WHEN zero_divide THEN
dbms_output.put_line('dividing by zero please check the values again');
dbms_output.put_line('the value of a is '||a);
dbms_output.put_line('the value of b is '||b);
END;
dividing by zero please check the values again
the value of a is 10
the value of b is 0
PL/SQL procedure successfully completed
RESULT
Thus the PL/SQL program that handles exception has been implemented and output was
verified.
Ex. No.: 7 TRIGGERS
AIM
To execute program using Triggers.
SYNTAX
Create or replace Trigger< Trigger name> [before/after]
[insert/update/delete] on <table name>
[for each statement/for each row]
[when<condition>]
begin
SQL statements;
end
SQL> create table stdn(rollno number(3),name varchar(2),m1 number(3),m2 number(3),m3
2 number(3),tot number(3),avrg number(3),result varchar(10));
Table created.
SQL> create or replace trigger t1 before insert on stdn
for each row
begin
:new.tot:=:new.m1+:new.m2+:new.m3;
:new.avrg:=:new.tot/3;
if(:new.m1>=50 and :new.m2>=50 and :new.m3>=50) then
:new.result:='pass';
else
:new.result:='Fail';
end if;
end;
12 /
Trigger created.
SQL> insert into stdnvalues(101,'SM',67,89,99,'','','');
1 row created.
SQL> select * from stdn;
ROLLNO NA M1 M2 M3 TOT AVRG RESULT
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
101 SM 67 89 99 255 85 pass
RESULT
Thus the program using Triggers is executed.
