Notes

--Bigyan Pathak
--Un.Id:18413698
--Week 18
--Database 2


------------------------------------------------------------------------------------------------------------------------------------
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');
--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)


--creating function
CREATE OR REPLACE FUNCTION func_check_retire(in_staff_id staff.staff_id%TYPE,in_dob staff.dob%TYPE) RETURN NUMBER IS
vn_left NUMBER(10);

BEGIN
SELECT FLOOR(67-(SYSDATE-in_dob)/365)
INTO vn_left
FROM staff
WHERE staff_id=in_staff_id;

END func_check_retire;
/

SHOW ERRORS;

--procedure to call function


CREATE OR REPLACE PROCEDURE proc_check_retire(in_staff_id staff.staff_id%TYPE,in_dob staff.dob%TYPE) IS vn_days NUMBER(20);

BEGIN
vn_days := func_check_retire(in_staff_id,in_dob);


END proc_check_retire;
/
SHOW ERRORS;

--EXECUTIHG THR PROCEDURE
EXEC proc_check_retire(2,'12-JAN-1999');


--trigger for updating
CREATE OR REPLACE TRIGGER trig_check_update
AFTER UPDATE OF firstname ON staff
FOR EACH ROW
DECLARE vn_left_days NUMBER;
BEGIN

IF UPDATING THEN
vn_left_days := func_check_retire(staff_id,dob);
DBMS_OUTPUT.PUT_LINE('The left years in company are'||vn_left_days);

END IF;

END trig_check_update;
/
SHOW ERRORS;

UPDATE staff
SET firstname = 'AMRIT'
WHERE staff_id=1;


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

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=1;
--row deleted and message displayed





--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(8);