EX-5 : USER DEFINED FUNCTIONS AND STORED PROCEDURES
AIM: To implement the user defined functions and stored procedures.
USER DEFINED FUNCTION:
SYNTAX:
CREATE FUNCTION function_name
(parameter1 datatype, parameter2 datatype, …)
RETURNS return_datatype
AS
DECLARE local_variable1 datatype;
DECLARE local_variable2 datatype;
BEGIN
--Function body with SQL statements to determine the Function behaviour
RETURN value;
END;
PROCEDURE:
CREATE OR REPLACE PROCEDURE proc_name (IN param datatype OUT param datatype)
{IS | AS}
-- declaration section (local variables)
BEGIN
-- Procedure body /executable logic
EXCEPTION
-- optional error handlers
END;
//Set serveroutput on to execute put_line() builtin function.
1)Write a PLSQL program to add 2 numbers.
declare
a number;
b number;
c number;
begin
a:=20;
b:=10;
c:=a+b;
dbms_output.put_line('ADDITION OF A AND B IS:'||c);
end;
SQL> @"C:\Users\A12-18\Desktop\plsql2.txt";
ADDITION OF A AND B IS:30
2)Write a PL SQL program to swap 2 numbers.
declare
a number;
b number;
c number;
begin
a:=10;
b:=20;
c:=a;
a:=b;
b:=c;
dbms_output.put_line('After Swapping:a='||a);
dbms_output.put_line('After Swapping:b='||b);
end;
SQL> @"C:\Users\A12-18\Desktop\plsql4.txt";
After Swapping:a=20
After Swapping:b=10
Method-2(Swapping without third variable):
declare
a number;
b number;
begin
a:=&a;
b:=&b;
a:=a+b;
b:=a-b;
a:=a-b;
dbms_output.put_line('After Swapping:a='||a);
dbms_output.put_line('After Swapping:b='||b);
end;
SQL> @"C:\Users\A12-18\Desktop\plsql5.txt";
Enter value for a: 2
old 5: a:=&a;
new 5: a:=2;
Enter value for b: 3
old 6: b:=&b;
new 6: b:=3;
After Swapping:a=3
After Swapping:b=2
3)Create a procedure Sal_Get to get the salary of an employee, passing an empid as an input parameter
( Create employees table with columns like EmpID, Fname,Lname, Age, Salary. Insert 5 rows in the
table)
SQL> CREATE TABLE EMP_TABLE(EMPID NUMBER(5),FNAME VARCHAR(20),LNAME
VARCHAR(20), AGE NUMBER(3), SALARY NUMBER(10,2));
Table created.
SQL> INSERT INTO EMP_TABLE VALUES(101,'AMY','JOHN',25,50000);
1 row created.
SQL> INSERT INTO EMP_TABLE VALUES(102,'RIMMY','JOHN',25,60000);
1 row created.
SQL> INSERT INTO EMP_TABLE VALUES(103,'PAUL','MEJOY',28,55000);
1 row created.
SQL> INSERT INTO EMP_TABLE VALUES(104,'LIJO','ARUN',27,65000);
1 row created.
SQL> INSERT INTO EMP_TABLE VALUES(105,'JENY','MATHEW',30,76000);
1 row created.
SQL> SELECT * FROM EMP_TABLE;
CODE:
CREATE OR REPLACE PROCEDURE SAL_GET (EMP_ID IN NUMBER)
IS
SAL NUMBER;
BEGIN
SELECT SALARY INTO SAL FROM EMP_TABLE WHERE EMPID=EMP_ID;
DBMS_OUTPUT.PUT_LINE(SAL);
END;
SQL> @"C:\Users\A12-18\Desktop\plsql6.txt";
Procedure created.
SQL> EXEC SAL_GET (101);
50000
4)Create a procedure to get the Annual Income of the employee. Employee ID is passed as an input
parameter and Annual Income is retrieved as output parameter.
Procedure:
CREATE OR REPLACE PROCEDURE Get_Annual_Income (
p_emp_id IN NUMBER,
p_annual_income OUT NUMBER)
IS
p_salary NUMBER;
BEGIN
SELECT SALARY INTO p_salary
FROM EMP_TABLE
WHERE EMPID = p_emp_id;
p_annual_income := p_salary * 12;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_annual_income := NULL;
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN OTHERS THEN
p_annual_income := NULL;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
PROCEDURE CALL:
ACCEPT emp_id NUMBER PROMPT ‘ Enter Employee ID: ‘;
DECLARE
v_annual_income NUMBER;
BEGIN
Get_Annual_Income(p_emp_id => :emp_id, p_annual_income => v_annual_income);
DBMS_OUTPUT.PUT_LINE('Annual Income: ' || v_annual_income);
END;
OUTPUT:
Enter Employee ID: 103
Annual Income: 55000
(Or) Another way to call the procedure with in and out parameters:
DECLARE
v_annual_income NUMBER;
BEGIN
Get_Annual_Income(p_emp_id => 103, p_annual_income => v_annual_income);
DBMS_OUTPUT.PUT_LINE('Annual Income is : ' || v_annual_income);
END;
/
5) Create a function to get the salary of an employee when empid is passed as parameter. Use the
function in a query and return the result.
CODE:
CREATE OR REPLACE FUNCTION Get_Salary (p_emp_id IN NUMBER) RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT SALARY INTO v_salary FROM EMP_TABLE WHERE EMPID = p_emp_id;
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
SQL> SELECT Get_Salary(103) AS SALARY ;
OUTPUT:
6)Create a function to display the FirstName and LastName as FirstName,LastName
CODE:
CREATE OR REPLACE FUNCTION Get_FullName (p_emp_id IN NUMBER) RETURN VARCHAR2
IS
v_first_name VARCHAR2(6);
v_last_name VARCHAR2(6);
v_full_name VARCHAR2(6);
BEGIN
SELECT FNAME, LNAME INTO v_first_name, v_last_name
FROM EMP_TABLE WHERE EMPID = p_emp_id;
v_full_name := v_first_name || ', ' || v_last_name;
RETURN v_full_name;
END;
SQL> SELECT Get_FullName (102) AS FULLNAME;
RESULT:
Thus, the functions and procedures are created for the above questions and the output is verified.
