1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
| CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
(last_name VARCHAR2, job_id VARCHAR2,
manager_id NUMBER, salary NUMBER,
commission_pct NUMBER, department_id NUMBER)
RETURN NUMBER IS new_empno NUMBER;
BEGIN
SELECT IDENTIFIANT.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO T_DST_TRA
VALUES (new_empno, '1', '1');
tot_emps := tot_emps + 1;
RETURN(new_empno);
END;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER IS
new_deptno NUMBER;
BEGIN
SELECT IDENTIFIANT.NEXTVAL
INTO new_deptno
FROM dual;
INSERT INTO T_DST_TRA
VALUES (new_deptno, '2', '1');
tot_depts := tot_depts + 1;
RETURN(new_deptno);
END;
PROCEDURE remove_emp (employee_id NUMBER) IS
BEGIN
DELETE FROM T_DST_TRA
WHERE T_DST_TRA.IDE_DST = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
PROCEDURE remove_dept(department_id NUMBER) IS
BEGIN
DELETE FROM T_DST_TRA
WHERE T_DST_TRA.IDE_DST = remove_dept.department_id;
tot_depts := tot_depts - 1;
SELECT COUNT(*) INTO tot_emps FROM T_DST_TRA;
END;
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
curr_sal NUMBER;
BEGIN
SELECT IDE_DST INTO curr_sal FROM T_DST_TRA
WHERE T_DST_TRA.IDE_DST = increase_sal.employee_id;
IF curr_sal IS NULL
THEN RAISE no_sal;
ELSE
UPDATE T_DST_TRA
SET IDE_DST = employee_id
WHERE employee_id = employee_id;
END IF;
END;
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER;
BEGIN
SELECT IDE_DST
INTO curr_comm
FROM T_DST_TRA
WHERE T_DST_TRA.IDE_DST = increase_comm.employee_id;
IF curr_comm IS NULL
THEN RAISE no_comm;
ELSE
UPDATE T_DST_TRA
SET IDE_DST = comm_incr;
END IF;
END;
END emp_mgmt; |