Notes

/*Ramesh Kunwar
18413696
Term : 2
Week : 18
Database II
*/

------------------------------------------------------------------------------------------------------------------------------------
SELECT trigger_name FROM user_triggers;
--dropping the old staff table.
DROP TABLE staff;

---
SET SERVEROUTPUT ON;
---
--creating new staff table

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

--1.When a new member of staff is added, automatically display their age on the screen.

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_dob staff.dob%TYPE ) IS
vn_age NUMBER;
BEGIN
INSERT INTO staff(staff_id,firstname,surname,username,role,dob)
VALUES (in_staff_id,in_firstname,in_surname,in_username,in_role,in_dob);
SELECT FLOOR((SYSDATE-in_dob)/365)
INTO vn_age
FROM staff
WHERE staff_id=in_staff_id;
DBMS_OUTPUT.PUT_LINE ('The age of'||in_firstname ||'is'||vn_age);

END proc_add_staff;
/
SHOW ERRORS;



--TESTING
SELECT * FROM staff;
--3 rows selected

EXEC proc_add_staff(6,'RAM','KUNWAR','RAKU','CEO','12-JAN-1999');
EXEC proc_add_staff(8,'PRAGYAN','KARKI','PIKU','CEO','12-JAN-2000');
--The age of staff is 20
--4 rows selected after procedure is

---------------------------------------------------------------------------------------------------------------------------------
--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)
2..
--creating the trigger
CREATE OR REPLACE TRIGGER trig_display
AFTER UPDATE ON staff
FOR EACH ROW
DECLARE

vd_today DATE;

BEGIN
SELECT SYSDATE
INTO vd_today
FROM DUAL;


DBMS_OUTPUT.PUT_LINE((65-FLOOR((vd_today-:OLD.dob)/365))||' year left for retirement');

END trig_display;
/
SHOW ERRORS;



UPDATE staff
SET surname = 'RAMESH'
WHERE staff_id=4;

/*
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

*/




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


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

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

END IF;

END trig_predicates;
/

---TESTING

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

--SET SERVEROUTPUT ON;



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

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

CREATE OR REPLACE FUNCTION func_retire(in_staff_id staff.staff_id%TYPE) RETURN NUMBER IS vn_left NUMBER(10);
BEGIN
SELECT FLOOR(67-(SYSDATE-dob)/365)
INTO vn_left
FROM staff
WHERE staff_id=in_staff_id;
RETURN vn_left;

END func_retire;
/

SHOW ERRORS;

---procedure for function

CREATE OR REPLACE PROCEDURE proc_test_retire(in_staff_id staff.staff_id%TYPE) IS vn_days VARCHAR2(20);

BEGIN
vn_days := func_retire(in_staff_id);
DBMS_OUTPUT.PUT_LINE ('Your remainig days are ' || vn_days||' years');

END proc_test_retire;
/
SHOW ERRORS;


--executing the procedure
EXEC proc_test_retire(7);


--4 bonus


CREATE OR REPLACE FUNCTION func_retire_years(in_staff_id staff.staff_id%TYPE,in_retire_age NUMBER) RETURN NUMBER IS vn_left NUMBER(10);
BEGIN
SELECT FLOOR(in_retire_age-(SYSDATE-dob)/365)
INTO vn_left
FROM staff
WHERE staff_id=in_staff_id;
RETURN vn_left;

END func_retire_years;
/

SHOW ERRORS;

--PROCEDURE

CREATE OR REPLACE PROCEDURE proc_test_retire_age(in_staff_id staff.staff_id%TYPE,in_retire_age NUMBER) IS vn_days VARCHAR2(20);

BEGIN
vn_days := func_retire_years(in_staff_id,in_retire_age);
DBMS_OUTPUT.PUT_LINE ('Your remainig years are ' || vn_days||' years');

END proc_test_retire_age;
/
SHOW ERRORS;

--TESTING

EXEC proc_test_retire_age(7,80);