Notes

--Name:sudeep bagale
--ID:18417139

--creating new sataff table ☺
CREATE TABLE staff(
staff_id NUMBER(6),
firstname VARCHAR2(15),
surname VARCHAR2(15),
username VARCHAR2(10),
role VARCHAR2(10),
date_of_birth DATE);

--Inserting data into staff table
INSERT INTO staff VALUES (1000, 'ALISON','APPLE', NULL, 'HEAD','01-JAN-1965');
INSERT INTO staff VALUES (1001, 'BERTY','BANANA', 'BBANA', 'WORKER BEE','02-MAR-1989');
INSERT INTO staff VALUES (1002, 'CANDY','CANE', NULL,'CONTROL','12-JUN-1980');
INSERT INTO staff VALUES (1003, 'SUDEEP','CANE', NULL,'CONTROL','03-FEB-1998');

--displaying staff table
SELECT * from staff;
--3 rows returned

--creating function named functo_display to display the age of the staff
CREATE OR REPLACE FUNCTION functo_display(in_date_of_birth staff.date_of_birth%type)RETURN NUMBER IS
vn_age NUMBER(2);

BEGIN
vn_age:= FLOOR(MONTHS_BETWEEN(sysdate, in_date_of_birth)/12);
RETURN vn_age;

END functo_display;
/
SHOW ERRORS;


--creating procedure
CREATE OR REPLACE PROCEDURE proc_display(in_date_of_birth staff.date_of_birth%type)IS
BEGIN
DBMS_OUTPUT.PUT_LINE('The staff age is ' || functo_display(in_date_of_birth));
END proc_display;
/
SET SERVEROUTPUT ON ;
exec proc_display('02-MAR-1998');

--making trigger
CREATE OR REPLACE TRIGGER triggerto_display
BEFORE INSERT ON staff
FOR EACH ROW
WHEN(NEW.date_of_birth IS NOT NULL)

BEGIN
DBMS_OUTPUT.PUT_LINE('NAME: ' ||:NEW.firstname || 'DATE_OF_BIRTH: ' || functo_display(:NEW.date_of_birth));
END triggerto_display;
/
SHOW ERRORS;