RDBMS - Function and Stored Procedure
Function is a group / collection of statements that is built to execute a specific task.
Why do we use functions?
->simplification, reduction of code, reduction of time, error finding and updation of code becomes easier.
->helps in reusability of code.
two parts of a functions:
->function definition
(code behind the function)
->function call
(statement that is used to execute the function definition)
float x = sqrt(9);
no argument - no return
argument - no return
argument - return
no argument - return
arguments : are the inputs that we can provide from the function call to its function definition.
return : is a keyword by using which we can send an output from the function definition to the function call.
---------------------------------------
in PL/SQL two Types of functions:
->functions
(a function will always return a value ; return is a must;)
(a function can only return a single value)
(a function may / may not take args)
->stored procedures
(may / may not return a value)
(can return multiple values)
(may / may not take args)
(there are three types of arguments - IN, OUT & IN OUT) ???
---------------------------------------------
Function definition Syntax in PL/SQL (args - return):
CREATE OR REPLACE FUNCTION functionname
(argument IN datatype, argument IN datatype)
RETURN datatype IS
variable declarations;
variable declarations;
BEGIN
pl/sql block
return var;
EXCEPTION
exception block
END;
/
-------------------
Note: the data type in the arguments section and return section should be written without any precision.
do not write "number(5,2)" or "varchar2(10)"
instead write "number" or "varchar2"
-------------------------------------------
Function definition Syntax in PL/SQL (no args - return):
CREATE OR REPLACE FUNCTION functionname
RETURN datatype IS
variable declarations;
variable declarations;
BEGIN
pl/sql block
return var;
EXCEPTION
exception block
END;
-----------------------------------------------
Function call syntax (args - return):
variable:=functionname(args);
----------------------------------------
Function call syntax (no args - return):
variable:=functionname();
Note: we must catch the return value in a variable on the LHS of the function call statement.
------------------------------------------
Dropping a function
DROP FUNCTION functionname;
-------------------------------------------
steps of working with a function:
Step 1: execute / create the function / save the function in the oracle engine:
(all function get stored in the ORACLE engine)
(once a function is created, we can use that function even after we close that Oracle session)
Step 2: call the function in any pl/sql code block.
-----------------------------------------------
Step 1: Create a function which takes the rollno as input and returns its per (args - return)
CREATE OR REPLACE FUNCTION FUN1
(rl IN number)
RETURN number IS
p number(4,1);
BEGIN
SELECT per INTO p
FROM stutab5
WHERE rollno=rl;
return p;
END;
--------------------------
Step 1: Create a function which takes the prodid as input and returns its prodprice (arg - return)
CREATE OR REPLACE FUNCTION fun_product_1
(pid IN number)
RETURN number IS
pr number(5,2);
BEGIN
/*Get the Prodprice using the prodid*/
SELECT prodprice INTO pr
FROM product
WHERE prodid = pid;
/*return the result*/
return pr;
END;
/
------------------------------------------------------------
Step 2: Create a PL/SQL code block that calls the FUN1 function to find the per of a rollno
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number(4);
p number(4,1);
BEGIN
/*take user input*/
rl:=&rl;
/*call FUN1 function*/
p:=FUN1(rl);
/*display the result*/
DBMS_OUTPUT.PUT_LINE('Percentage of rollno '||rl||' is '||p);
END;
/
-----------------------------------------------------------------------------------------------Step 2: Create a PL/SQL code block that calls the "fun_product_1" function to find the prodprice of a prodid entered by the user.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pr number(5,2);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Product ID: ');
pid:=&pid;
/*call the function - fun_product_1*/
pr:=fun_product_1(pid);
/*show the output*/
DBMS_OUTPUT.PUT_LINE('Product Price of Product ID '||pid||' is '||pr);
END;
/
--------------------------------------------------------------------------------------------------
===============================================================
Define a function that gives the prodid of the product that has the highest price... (no args - return)
CREATE OR REPLACE FUNCTION fun_product_2
RETURN number IS
pid number(3);
BEGIN
/*SELECT query to find the pid of the highest price product*/
SELECT prodid INTO pid
FROM product
WHERE prodprice = (SELECT MAX(prodprice) FROM product);
/*return the result*/
return pid;
END;
/
Define a function that gives the prodid of the product that has the least price... (no args - return)
CREATE OR REPLACE FUNCTION fun_product_3
RETURN number IS
pid number(3);
BEGIN
/*SELECT query to find the pid of theleast price product*/
SELECT prodid INTO pid
FROM product
WHERE prodprice = (SELECT MIN(prodprice) FROM product);
/*return the result*/
return pid;
END;
/
Create a PL/SQL block which displays the details of the product that has the max & min price. use the function "fun_product_2"
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declarations*/
pid number(3);
pnm varchar2(20);
ppr number(5,2);
BEGIN
/*call the function and fetch the product id*/
pid:=fun_product_2();
/*SELECT query to get the other details*/
SELECT prodname, prodprice INTO pnm, ppr
FROM product
WHERE prodid = pid;
/*Display*/
DBMS_OUTPUT.PUT_LINE('**********************************');
DBMS_OUTPUT.PUT_LINE('Product having max price');
DBMS_OUTPUT.PUT_LINE('Product ID: ' || pid);
DBMS_OUTPUT.PUT_LINE('Product Name: ' || pnm);
DBMS_OUTPUT.PUT_LINE('Product Price: ' || ppr);
/*call the function and fetch the product id*/
pid:=fun_product_3();
/*SELECT query to get the other details*/
SELECT prodname, prodprice INTO pnm, ppr
FROM product
WHERE prodid = pid;
/*Display*/
DBMS_OUTPUT.PUT_LINE('**********************************');
DBMS_OUTPUT.PUT_LINE('Product having min price');
DBMS_OUTPUT.PUT_LINE('Product ID: ' || pid);
DBMS_OUTPUT.PUT_LINE('Product Name: ' || pnm);
DBMS_OUTPUT.PUT_LINE('Product Price: ' || ppr);
END;
/
===============================================================
/*Create a function which takes the empid as arg & returns its bsal*/
CREATE OR REPLACE FUNCTION F_GET_BSAL2(eid IN number)
RETURN varchar2 IS
bs number(7,2);
sb varchar2(10);
BEGIN
SELECT to_char(bsal) INTO sb
FROM employeetab
WHERE empid=eid;
return sb;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'no data found';
END;
//////////////////////////////////////////////////
DECLARE
eid number;
res number;
BEGIN
eid:=&eid;
res:=F_GET_BSAL(eid);
dbms_output.put_line('bsal of eid'||eid||'is'||res);
END;
------------------------------------------
Q. enter the rollno ;
retrieve & display the per of that roll;
argument & return function
Step 1: create a function to retrieve per of a roll that is passed as argument.
CREATE OR REPLACE FUNCTION F_GETPER_2(rl IN number)
RETURN number IS
p number(3,1);
BEGIN
SELECT per INTO p FROM stutab
WHERE roll=rl;
RETURN p;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN -1;
END;
/
Step 2: call the function in a PL/SQL code block
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
rl number;
res number(3,1);
BEGIN
/*take user input*/
rl:=&rl;
/*function call*/
res:=F_GETPER_2(rl);
/*check result*/
IF res=-1 THEN
DBMS_OUTPUT.PUT_LINE('ROll '||rl||' not found!');
ELSE
DBMS_OUTPUT.PUT_LINE('Per: '||res);
END IF;
END;
/
-------------------------------------------------------
Practicals:
SQL*Plus: Release 11.2.0.2.0 Production on Sat Feb 16 18:14:30 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> SELECT * FROM stutab;
ROLL NAME DOB M1 M2 PER DEP
---------- --------------- --------- ---------- ---------- ---------- ---
1010 Amit Singh 12-SEP-00 99 88 93.5 CSE
1011 Amer Singh Brar 01-JAN-00 48 96.6 72.3 ECE
1012 Ajmer Singh 11-FEB-00 54.4 66.6 60.5 ECE
1013 John 10-FEB-01 64.4 76.6 70.5 CSE
1014 John 12-MAR-01 74.4 56.6 65.5 CIV
1089 AAAAAA 03-JAN-00 90 90 90 CSE
1025 Simar 45 66 55.5
1090 AWWA 03-JAN-01 49 49 49 CSE
8 rows selected.
SQL> CREATE OR REPLACE FUNCTION F_GETPER
2 (rl IN number)
3 RETURN number IS
4 /*declare variable*/
5 p number(3,1);
6 BEGIN
7 /*retreival of per*/
8 SELECT per INTO p FROM stutab
9 WHERE roll=rl;
10 /*return per*/
11 return per;
12 /*exception handling*/
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 /*return error code*/
16 return -1;
17 END;
18 /
Warning: Function created with compilation errors.
SQL> CREATE OR REPLACE FUNCTION F_GETPER_2
2 (rl IN number)
3 RETURN number IS
4 /*declare variable*/
5 p number;
6 BEGIN
7 /*retreival of per*/
8 SELECT per INTO p FROM stutab
9 WHERE roll=rl;
10 /*return per*/
11 return per;
12 /*exception handling*/
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 /*return error code*/
16 return -1;
17 END;
18 /
Warning: Function created with compilation errors.
SQL> CREATE OR REPLACE FUNCTION F_GETPER_2
2 (rl IN number)
3 RETURN number IS
4 p number;
5 BEGIN
6 SELECT per INTO p FROM stutab
7 WHERE roll=rl;
8 RETURN per;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 RETURN -1;
12 END;
13 /
Warning: Function created with compilation errors.
SQL> CREATE OR REPLACE FUNCTION F_GETPER_2(rl IN number)
2 RETURN number IS
3 p number(3,1);
4 BEGIN
5 SELECT per INTO p FROM stutab
6 WHERE roll=rl;
7 RETURN p;
8 EXCEPTION
9 WHEN NO_DATA_FOUND THEN RETURN -1;
10 END;
11 /
Function created.
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 /*variable declaration*/
3 rl number;
4 res number(3,1);
5 BEGIN
6 /*take user input*/
7 rl:=&rl;
8 /*function call*/
9 res:=F_GETPER_2(rl);
10 /*check result*/
11 IF res=-1 THEN
12 DBMS_OUTPUT.PUT_LINE('ROll '||rl||' not found!');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE('Per: '||res);
15 END;
16 /
Enter value for rl: 1099
old 7: rl:=&rl;
new 7: rl:=1099;
END;
*
ERROR at line 15:
ORA-06550: line 15, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
if
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 /*variable declaration*/
3 rl number;
4 res number(3,1);
5 BEGIN
6 /*take user input*/
7 rl:=&rl;
8 /*function call*/
9 res:=F_GETPER_2(rl);
10 /*check result*/
11 IF res=-1 THEN
12 DBMS_OUTPUT.PUT_LINE('ROll '||rl||' not found!');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE('Per: '||res);
15 END IF;
16 END;
17 /
Enter value for rl: 1099
old 7: rl:=&rl;
new 7: rl:=1099;
ROll 1099 not found!
PL/SQL procedure successfully completed.
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 /*variable declaration*/
3 rl number;
4 res number(3,1);
5 BEGIN
6 /*take user input*/
7 rl:=&rl;
8 /*function call*/
9 res:=F_GETPER_2(rl);
10 /*check result*/
11 IF res=-1 THEN
12 DBMS_OUTPUT.PUT_LINE('ROll '||rl||' not found!');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE('Per: '||res);
15 END IF;
16 END;
17 /
Enter value for rl: 1011
old 7: rl:=&rl;
new 7: rl:=1011;
Per: 72.3
PL/SQL procedure successfully completed.
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 /*variable declaration*/
3 rl number;
4 res number(3,1);
5 BEGIN
6 /*take user input*/
7 rl:=&rl;
8 /*function call*/
9 res:=F_GETPER_2(rl);
10 /*check result*/
11 IF res=-1 THEN
12 DBMS_OUTPUT.PUT_LINE('ROll '||rl||' not found!');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE('Per: '||res);
15 END IF;
16 END;
17 /
0 Comments