Notes

--------QUESTION NO ONE TESTING-----------
--creating procedure to call func_calc_age
CREATE OR REPLACE PROCEDURE proc_age(date_of_birth DATE) IS
vn_age NUMBER(3);
BEGIN

vn_age:= func_calc_age(date_of_birth);
END proc_age;
/
SHOW ERRORS

--executing procedure proc_age
EXEC proc_age('12-JUN-1980');

--testing the trigger
SELECT * FROM staff;

INSERT INTO staff VALUES (1000, 'ALISON','APPLE', NULL, 'HEAD','01-JAN-1965');

--testing for data insertion
SELECT * FROM staff;

------QUESTION NO TWO TESTING-----------
--Creating procedure to check func_calc_retirement
CREATE OR REPLACE PROCEDURE proc_retire(in_dob DATE)IS
vn_age NUMBER(3);
vn_time_left NUMBER(3);
BEGIN
vn_time_left:= func_calc_retirement(in_dob);
DBMS_OUTPUT.PUT_LINE(vn_time_left);

END proc_retire;
/
SHOW ERRORS

--executing the procedure
EXEC proc_retire('12-JUN-1980');

--TESTING
SELECT * FROM staff;

--updating on table
UPDATE staff SET firstname = 'Binaya' WHERE staff_id = 1000;

--TESTING
SELECT * FROM staff;

--TESTING
---------------QUESTION NO THREE TESTING-----------
SELECT * FROM staff;
--deleteing entry
DELETE FROM staff WHERE staff_id ='1000';
--test if deleted sucessfullY
SELECT * FROM staff;


---------------QUESTION NO FOUR TESTING-----------
--creating procedure to test the func_calc_retire

CREATE OR REPLACE PROCEDURE proc_retirement(in_staff_id staff.staff_id%TYPE)IS
vn_age NUMBER(3);
vn_time_left NUMBER(3);
BEGIN
vn_time_left:= func_calc_retire(in_staff_id);
DBMS_OUTPUT.PUT_LINE( 'YEARS LEFT FOR RETIREMENT'||vn_time_left);

END proc_retirement;
/
SHOW ERRORS

--EXECUTING PROCEDURE
EXEC proc_retirement(1001);