Notes

/*
NAME: Subash Dahal
ID: 18406486
WEEk: 18
DATE: March 21, 2019
*/
--Drop Codes
DROP FUNCTION func_calc_age;
DROP PROCEDURE proc_add_staff;
DROP TRIGGER trig_add_staff;
DROP TRIGGER trig_retirementage;
DROP TRIGGER trig_delete;
DROP TABLE staff;


CREATE TABLE staff(
staff_id NUMBER(6),
firstname VARCHAR2(15),
surname VARCHAR2(15),
username VARCHAR2(10),
role VARCHAR2(10),
date_of_birth DATE);

INSERT INTO staff VALUES (1000, 'RED','FRENDY', 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');


--CHECKING STAFF TABLE
SELECT * FROM STAFF;
--3 rows returned






SET SERVEROUTPUT ON

--creating the function to calculate age
CREATE OR REPLACE FUNCTION func_calc_age(in_date_of_birth staff.date_of_birth%TYPE)
RETURN NUMBER IS
vn_age NUMBER(3);
BEGIN
vn_age := FLOOR(MONTHS_BETWEEN(sysdate, in_date_of_birth)/12);

RETURN vn_age;-- this is where a value is returned

END func_calc_age;
/
SHOW ERRORS;




--Creating a procedure
CREATE OR REPLACE PROCEDURE proc_add_staff(in_staff_id staff.staff_id%TYPE,in_firstname staff.firstname%TYPE,in_surname staff.surname%TYPE,in_username staff.username%TYPE,in_role staff.role%TYPE,in_date_of_birth staff.date_of_birth %TYPE )
IS vn_age NUMBER(3);
BEGIN
vn_age := func_calc_age(in_date_of_birth);
DBMS_OUTPUT.PUT_LINE ('The age of '||in_firstname ||' is='||vn_age);

END proc_add_staff;
/
SHOW ERRORS;

--executing procedure
EXEC proc_add_staff(1233,'ADITYA','GIRI', NULL, 'HEAD','01-JAN-1989');
EXEC proc_add_staff(1234,'MANOJ','GIRI', NULL, 'HEAD','01-JAN-1996');
EXEC proc_add_staff(1234,'SNEHA','MOKTAN', NULL, 'HEAD','25-FEB-1999');
--function is correct.. ADITYA age is 30 is displayed and age of manoj is 23.




--Creating a trigger

CREATE OR REPLACE TRIGGER trig_add_staff
AFTER INSERT ON staff
FOR EACH ROW

DECLARE
vn_age NUMBER(3);
BEGIN
vn_age := func_calc_age(:NEW.date_of_birth);
DBMS_OUTPUT.PUT_LINE ('You are adding ' || :NEW.firstname || ' and His/Her age is ' || vn_age );

END trig_add_staff;
/
SHOW ERRORS;

--Checking the data in staff tales;
SELECT * FROM staff;
--3 rows selected.

--Testing trigger named as trig_add_staff
--Adding two more staffs in table staff.
INSERT INTO staff VALUES (1000, 'ALISON','APPLE', NULL, 'HEAD','01-JAN-1965');
INSERT INTO staff VALUES (1000, 'BICKY','GIRI', NULL, 'HEAD','01-JAN-1985');
--Checking the data in staff tales;
SELECT * FROM staff;
--5 rows selected.




------------------------------------------------------------------
--2.When an existing member of staff’s info is changed automatically display a message showing how long they have left at the company (until they retire)
CREATE OR REPLACE TRIGGER trig_retirementage
AFTER UPDATE ON staff
FOR EACH ROW
DECLARE

vd_today DATE;

BEGIN
SELECT SYSDATE
INTO vd_today
FROM DUAL;

DBMS_OUTPUT.PUT_LINE(:OLD.firstname||' has '||(65-FLOOR((vd_today-:OLD.date_of_birth)/365))||' year left for retirement');
END trig_retirementage;
/
SHOW ERRORS;

--Testing Trigger named as trig_retirementage
UPDATE staff
SET firstname = 'CHERRY'
WHERE surname='CANE';

--Checking the firstname is updated or not.
SELECT * FROM staff;

UPDATE staff
SET surname = 'KC'
WHERE firstname='ALISON';
--Checking the surname is updated or not.
SELECT * FROM staff;









--------------------------------------------------------------------------------------------------------------------------------------------
--3.When a member of staff is being deleted automatically displayed a message to let them know (+BONUS)

CREATE OR REPLACE TRIGGER trig_delete
AFTER DELETE ON staff
FOR EACH ROW
BEGIN

IF DELETING THEN DBMS_OUTPUT.PUT_LINE('You are deleting '||:OLD.firstname);

END IF;

END trig_delete;
/

---TESTING trigger named as trig_delete


DELETE FROM staff
WHERE staff_id=1001;
--row deleted and message displayed

--Checking data in staff table
SELECT * FROM Staff;







-----------------------------------------------------------------------------------------------------------------------

--4.A tool that allows users to input their staff id and see how long they have left before they can retire