AIM - PL/SQL functions
Theory:- A function can be used as a part of SQL expression i.e. we can use them with select/update/merge
commands. One most important characteristic of a functionis that unlike procedures, it must return a value.
QUERY
CREATING TABLE FOR FUNCTION
CREATE TABLE employee(emp_id
INT,
fname varchar(50),
lname varchar(50),
start_date date
);
INSERT INTO
employee(emp_id,fname,lname,start_date)VALUES
(1,'Michael','Smith','2001-06-22'),
(2,'Susan', 'Barker','2002-09-12'),
(3,'Robert','Tvler','2000-02-09'),
(4,'Susan','Hawthorne','2002-04-24');
CREATING FUNCTION
DELIMITER //
CREATE FUNCTION no_of_years(date1 date) RETURNS int DETERMINISTICBEGIN
DECLARE date2 DATE; Select current_date()into date2; RETURN year(date2)-year(date1); END //
DELIMITER ;
CALLING FUNCTION
Select emp_id, fname, lname, no_of_years(start_date) as 'years' from employee;

RESULT: The program for PL/SQL PL/SQL Functions in RDBMS is implemented successfully and output is verified