BEGIN ;
/
CREATE TABLE dept (
deptno NUMBER( 2 ) NOT NULL CONSTRAINT dept_pk PRIMARY KEY ,
dname VARCHAR2( 14 ) CONSTRAINT dept_dname_uq UNIQUE ,
loc VARCHAR2( 13 )
) ;
CREATE TABLE emp (
empno NUMBER( 4 ) NOT NULL CONSTRAINT emp_pk PRIMARY KEY ,
ename VARCHAR2( 10 ) ,
job VARCHAR2( 9 ) ,
mgr NUMBER( 4 ) ,
hiredate DATE ,
sal NUMBER( 7 , 2 ) CONSTRAINT emp_sal_ck CHECK ( sal > 0 ) ,
comm NUMBER( 7 , 2 ) ,
deptno NUMBER( 2 ) CONSTRAINT emp_ref_dept_fk
REFERENCES dept( deptno)
) ;
CREATE TABLE jobhist (
empno NUMBER( 4 ) NOT NULL ,
startdate DATE NOT NULL ,
enddate DATE ,
job VARCHAR2( 9 ) ,
sal NUMBER( 7 , 2 ) ,
comm NUMBER( 7 , 2 ) ,
deptno NUMBER( 2 ) ,
chgdesc VARCHAR2( 80 ) ,
CONSTRAINT jobhist_pk PRIMARY KEY ( empno, startdate) ,
CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY ( empno)
REFERENCES emp( empno) ON DELETE CASCADE ,
CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY ( deptno)
REFERENCES dept ( deptno) ON DELETE SET NULL ,
CONSTRAINT jobhist_date_chk CHECK ( startdate <= enddate)
) ;
CREATE OR REPLACE VIEW salesemp AS
SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN' ;
CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1 ;
GRANT ALL ON emp TO PUBLIC ;
GRANT ALL ON dept TO PUBLIC ;
GRANT ALL ON jobhist TO PUBLIC ;
GRANT ALL ON salesemp TO PUBLIC ;
GRANT ALL ON next_empno TO PUBLIC ;
INSERT INTO dept VALUES ( 10 , 'ACCOUNTING' , 'NEW YORK' ) ;
INSERT INTO dept VALUES ( 20 , 'RESEARCH' , 'DALLAS' ) ;
INSERT INTO dept VALUES ( 30 , 'SALES' , 'CHICAGO' ) ;
INSERT INTO dept VALUES ( 40 , 'OPERATIONS' , 'BOSTON' ) ;
INSERT INTO emp VALUES ( 7369 , 'SMITH' , 'CLERK' , 7902 , '17-DEC-80' , 800 , NULL , 20 ) ;
INSERT INTO emp VALUES ( 7499 , 'ALLEN' , 'SALESMAN' , 7698 , '20-FEB-81' , 1600 , 300 , 30 ) ;
INSERT INTO emp VALUES ( 7521 , 'WARD' , 'SALESMAN' , 7698 , '22-FEB-81' , 1250 , 500 , 30 ) ;
INSERT INTO emp VALUES ( 7566 , 'JONES' , 'MANAGER' , 7839 , '02-APR-81' , 2975 , NULL , 20 ) ;
INSERT INTO emp VALUES ( 7654 , 'MARTIN' , 'SALESMAN' , 7698 , '28-SEP-81' , 1250 , 1400 , 30 ) ;
INSERT INTO emp VALUES ( 7698 , 'BLAKE' , 'MANAGER' , 7839 , '01-MAY-81' , 2850 , NULL , 30 ) ;
INSERT INTO emp VALUES ( 7782 , 'CLARK' , 'MANAGER' , 7839 , '09-JUN-81' , 2450 , NULL , 10 ) ;
INSERT INTO emp VALUES ( 7788 , 'SCOTT' , 'ANALYST' , 7566 , '19-APR-87' , 3000 , NULL , 20 ) ;
INSERT INTO emp VALUES ( 7839 , 'KING' , 'PRESIDENT' , NULL , '17-NOV-81' , 5000 , NULL , 10 ) ;
INSERT INTO emp VALUES ( 7844 , 'TURNER' , 'SALESMAN' , 7698 , '08-SEP-81' , 1500 , 0 , 30 ) ;
INSERT INTO emp VALUES ( 7876 , 'ADAMS' , 'CLERK' , 7788 , '23-MAY-87' , 1100 , NULL , 20 ) ;
INSERT INTO emp VALUES ( 7900 , 'JAMES' , 'CLERK' , 7698 , '03-DEC-81' , 950 , NULL , 30 ) ;
INSERT INTO emp VALUES ( 7902 , 'FORD' , 'ANALYST' , 7566 , '03-DEC-81' , 3000 , NULL , 20 ) ;
INSERT INTO emp VALUES ( 7934 , 'MILLER' , 'CLERK' , 7782 , '23-JAN-82' , 1300 , NULL , 10 ) ;
INSERT INTO jobhist VALUES ( 7369 , '17-DEC-80' , NULL , 'CLERK' , 800 , NULL , 20 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7499 , '20-FEB-81' , NULL , 'SALESMAN' , 1600 , 300 , 30 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7521 , '22-FEB-81' , NULL , 'SALESMAN' , 1250 , 500 , 30 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7566 , '02-APR-81' , NULL , 'MANAGER' , 2975 , NULL , 20 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7654 , '28-SEP-81' , NULL , 'SALESMAN' , 1250 , 1400 , 30 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7698 , '01-MAY-81' , NULL , 'MANAGER' , 2850 , NULL , 30 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7782 , '09-JUN-81' , NULL , 'MANAGER' , 2450 , NULL , 10 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7788 , '19-APR-87' , '12-APR-88' , 'CLERK' , 1000 , NULL , 20 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7788 , '13-APR-88' , '04-MAY-89' , 'CLERK' , 1040 , NULL , 20 , 'Raise' ) ;
INSERT INTO jobhist VALUES ( 7788 , '05-MAY-90' , NULL , 'ANALYST' , 3000 , NULL , 20 , 'Promoted to Analyst' ) ;
INSERT INTO jobhist VALUES ( 7839 , '17-NOV-81' , NULL , 'PRESIDENT' , 5000 , NULL , 10 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7844 , '08-SEP-81' , NULL , 'SALESMAN' , 1500 , 0 , 30 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7876 , '23-MAY-87' , NULL , 'CLERK' , 1100 , NULL , 20 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7900 , '03-DEC-81' , '14-JAN-83' , 'CLERK' , 950 , NULL , 10 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7900 , '15-JAN-83' , NULL , 'CLERK' , 950 , NULL , 30 , 'Changed to Dept 30' ) ;
INSERT INTO jobhist VALUES ( 7902 , '03-DEC-81' , NULL , 'ANALYST' , 3000 , NULL , 20 , 'New Hire' ) ;
INSERT INTO jobhist VALUES ( 7934 , '23-JAN-82' , NULL , 'CLERK' , 1300 , NULL , 10 , 'New Hire' ) ;
ANALYZE dept;
ANALYZE emp;
ANALYZE jobhist;
CREATE OR REPLACE PROCEDURE list_emp
IS
v_empno NUMBER( 4 ) ;
v_ename VARCHAR2( 10 ) ;
CURSOR emp_cur IS
SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
DBMS_OUTPUT. PUT_LINE( 'EMPNO ENAME' ) ;
DBMS_OUTPUT. PUT_LINE( '----- -------' ) ;
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur% NOTFOUND;
DBMS_OUTPUT. PUT_LINE( v_empno || ' ' || v_ename) ;
END LOOP ;
CLOSE emp_cur;
END ;
/
CREATE OR REPLACE PROCEDURE select_emp (
p_empno IN NUMBER
)
IS
v_ename emp. ename% TYPE ;
v_hiredate emp. hiredate% TYPE ;
v_sal emp. sal% TYPE ;
v_comm emp. comm% TYPE ;
v_dname dept. dname% TYPE ;
v_disp_date VARCHAR2( 10 ) ;
BEGIN
SELECT ename, hiredate, sal, NVL( comm, 0 ) , dname
INTO v_ename, v_hiredate, v_sal, v_comm, v_dname
FROM emp e, dept d
WHERE empno = p_empno
AND e. deptno = d. deptno;
v_disp_date := TO_CHAR( v_hiredate, 'MM/DD/YYYY' ) ;
DBMS_OUTPUT. PUT_LINE( 'Number : ' || p_empno) ;
DBMS_OUTPUT. PUT_LINE( 'Name : ' || v_ename) ;
DBMS_OUTPUT. PUT_LINE( 'Hire Date : ' || v_disp_date) ;
DBMS_OUTPUT. PUT_LINE( 'Salary : ' || v_sal) ;
DBMS_OUTPUT. PUT_LINE( 'Commission: ' || v_comm) ;
DBMS_OUTPUT. PUT_LINE( 'Department: ' || v_dname) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT. PUT_LINE( 'Employee ' || p_empno || ' not found' ) ;
WHEN OTHERS THEN
DBMS_OUTPUT. PUT_LINE( 'The following is SQLERRM:' ) ;
DBMS_OUTPUT. PUT_LINE( SQLERRM) ;
DBMS_OUTPUT. PUT_LINE( 'The following is SQLCODE:' ) ;
DBMS_OUTPUT. PUT_LINE( SQLCODE) ;
END ;
/
CREATE OR REPLACE PROCEDURE emp_query (
p_deptno IN NUMBER,
p_empno IN OUT NUMBER,
p_ename IN OUT VARCHAR2,
p_job OUT VARCHAR2,
p_hiredate OUT DATE ,
p_sal OUT NUMBER
)
IS
BEGIN
SELECT empno, ename, job, hiredate, sal
INTO p_empno, p_ename, p_job, p_hiredate, p_sal
FROM emp
WHERE deptno = p_deptno
AND ( empno = p_empno
OR ename = UPPER( p_ename) ) ;
END ;
/
CREATE OR REPLACE PROCEDURE emp_query_caller
IS
v_deptno NUMBER( 2 ) ;
v_empno NUMBER( 4 ) ;
v_ename VARCHAR2( 10 ) ;
v_job VARCHAR2( 9 ) ;
v_hiredate DATE ;
v_sal NUMBER;
BEGIN
v_deptno := 30 ;
v_empno := 0 ;
v_ename := 'Martin' ;
emp_query( v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal) ;
DBMS_OUTPUT. PUT_LINE( 'Department : ' || v_deptno) ;
DBMS_OUTPUT. PUT_LINE( 'Employee No: ' || v_empno) ;
DBMS_OUTPUT. PUT_LINE( 'Name : ' || v_ename) ;
DBMS_OUTPUT. PUT_LINE( 'Job : ' || v_job) ;
DBMS_OUTPUT. PUT_LINE( 'Hire Date : ' || v_hiredate) ;
DBMS_OUTPUT. PUT_LINE( 'Salary : ' || v_sal) ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT. PUT_LINE( 'More than one employee was selected' ) ;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT. PUT_LINE( 'No employees were selected' ) ;
END ;
/
CREATE OR REPLACE FUNCTION emp_comp (
p_sal NUMBER,
p_comm NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN ( p_sal + NVL( p_comm, 0 ) ) * 24 ;
END ;
/
CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER
IS
v_cnt INTEGER := 1 ;
v_new_empno NUMBER;
BEGIN
WHILE v_cnt > 0 LOOP
SELECT next_empno. nextval INTO v_new_empno FROM dual;
SELECT COUNT ( * ) INTO v_cnt FROM emp WHERE empno = v_new_empno;
END LOOP ;
RETURN v_new_empno;
END ;
/
CREATE OR REPLACE FUNCTION hire_clerk (
p_ename VARCHAR2,
p_deptno NUMBER
) RETURN NUMBER
IS
v_empno NUMBER( 4 ) ;
v_ename VARCHAR2( 10 ) ;
v_job VARCHAR2( 9 ) ;
v_mgr NUMBER( 4 ) ;
v_hiredate DATE ;
v_sal NUMBER( 7 , 2 ) ;
v_comm NUMBER( 7 , 2 ) ;
v_deptno NUMBER( 2 ) ;
BEGIN
v_empno := new_empno;
INSERT INTO emp VALUES ( v_empno, p_ename, 'CLERK' , 7782 ,
TRUNC( SYSDATE) , 950.00 , NULL , p_deptno) ;
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
FROM emp WHERE empno = v_empno;
DBMS_OUTPUT. PUT_LINE( 'Department : ' || v_deptno) ;
DBMS_OUTPUT. PUT_LINE( 'Employee No: ' || v_empno) ;
DBMS_OUTPUT. PUT_LINE( 'Name : ' || v_ename) ;
DBMS_OUTPUT. PUT_LINE( 'Job : ' || v_job) ;
DBMS_OUTPUT. PUT_LINE( 'Manager : ' || v_mgr) ;
DBMS_OUTPUT. PUT_LINE( 'Hire Date : ' || v_hiredate) ;
DBMS_OUTPUT. PUT_LINE( 'Salary : ' || v_sal) ;
DBMS_OUTPUT. PUT_LINE( 'Commission : ' || v_comm) ;
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT. PUT_LINE( 'The following is SQLERRM:' ) ;
DBMS_OUTPUT. PUT_LINE( SQLERRM) ;
DBMS_OUTPUT. PUT_LINE( 'The following is SQLCODE:' ) ;
DBMS_OUTPUT. PUT_LINE( SQLCODE) ;
RETURN - 1 ;
END ;
/
CREATE OR REPLACE FUNCTION hire_salesman (
p_ename VARCHAR ,
p_sal NUMERIC ,
p_comm NUMERIC
) RETURNS NUMERIC
AS $$
DECLARE
v_empno NUMERIC ( 4 ) ;
v_ename VARCHAR ( 10 ) ;
v_job VARCHAR ( 9 ) ;
v_mgr NUMERIC ( 4 ) ;
v_hiredate DATE ;
v_sal NUMERIC ( 7 , 2 ) ;
v_comm NUMERIC ( 7 , 2 ) ;
v_deptno NUMERIC ( 2 ) ;
BEGIN
v_empno := new_empno( ) ;
INSERT INTO emp VALUES ( v_empno, p_ename, 'SALESMAN' , 7698 ,
CURRENT_DATE , p_sal, p_comm, 30 ) ;
SELECT INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE empno = v_empno;
RAISE INFO 'Department : %' , v_deptno;
RAISE INFO 'Employee No: %' , v_empno;
RAISE INFO 'Name : %' , v_ename;
RAISE INFO 'Job : %' , v_job;
RAISE INFO 'Manager : %' , v_mgr;
RAISE INFO 'Hire Date : %' , v_hiredate;
RAISE INFO 'Salary : %' , v_sal;
RAISE INFO 'Commission : %' , v_comm;
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'The following is SQLERRM:' ;
RAISE INFO '%' , SQLERRM;
RAISE INFO 'The following is SQLSTATE:' ;
RAISE INFO '%' , SQLSTATE;
RETURN - 1 ;
END ;
$$ LANGUAGE 'plpgsql' ;
/
CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp
DO INSTEAD
INSERT INTO emp VALUES ( NEW. empno, NEW. ename, 'SALESMAN' , 7698 ,
NEW. hiredate, NEW. sal, NEW. comm, 30 ) ;
CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp
DO INSTEAD
UPDATE emp SET empno = NEW. empno,
ename = NEW. ename,
hiredate = NEW. hiredate,
sal = NEW. sal,
comm = NEW. comm
WHERE empno = OLD. empno;
CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp
DO INSTEAD
DELETE FROM emp WHERE empno = OLD. empno;
CREATE OR REPLACE TRIGGER user_audit_trig
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_action VARCHAR2( 24 ) ;
BEGIN
IF INSERTING THEN
v_action := ' added employee(s) on ' ;
ELSIF UPDATING THEN
v_action := ' updated employee(s) on ' ;
ELSIF DELETING THEN
v_action := ' deleted employee(s) on ' ;
END IF ;
DBMS_OUTPUT. PUT_LINE( 'User ' || USER || v_action ||
TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) ) ;
END ;
/
CREATE OR REPLACE TRIGGER emp_sal_trig
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
DECLARE
sal_diff NUMBER;
BEGIN
IF INSERTING THEN
DBMS_OUTPUT. PUT_LINE( 'Inserting employee ' || :NEW. empno) ;
DBMS_OUTPUT. PUT_LINE( '..New salary: ' || :NEW. sal) ;
END IF ;
IF UPDATING THEN
sal_diff := :NEW. sal - :OLD. sal;
DBMS_OUTPUT. PUT_LINE( 'Updating employee ' || :OLD. empno) ;
DBMS_OUTPUT. PUT_LINE( '..Old salary: ' || :OLD. sal) ;
DBMS_OUTPUT. PUT_LINE( '..New salary: ' || :NEW. sal) ;
DBMS_OUTPUT. PUT_LINE( '..Raise : ' || sal_diff) ;
END IF ;
IF DELETING THEN
DBMS_OUTPUT. PUT_LINE( 'Deleting employee ' || :OLD. empno) ;
DBMS_OUTPUT. PUT_LINE( '..Old salary: ' || :OLD. sal) ;
END IF ;
END ;
/
CREATE OR REPLACE PACKAGE emp_admin
IS
FUNCTION get_dept_name (
p_deptno NUMBER
) RETURN VARCHAR2;
FUNCTION update_emp_sal (
p_empno NUMBER,
p_raise NUMBER
) RETURN NUMBER;
PROCEDURE hire_emp (
p_empno NUMBER,
p_ename VARCHAR2,
p_job VARCHAR2,
p_sal NUMBER,
p_hiredate DATE ,
p_comm NUMBER,
p_mgr NUMBER,
p_deptno NUMBER
) ;
PROCEDURE fire_emp (
p_empno NUMBER
) ;
END emp_admin;
/
CREATE OR REPLACE PACKAGE BODY emp_admin
IS
FUNCTION get_dept_name (
p_deptno IN NUMBER
) RETURN VARCHAR2
IS
v_dname VARCHAR2( 14 ) ;
BEGIN
SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno;
RETURN v_dname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT. PUT_LINE( 'Invalid department number ' || p_deptno) ;
RETURN '' ;
END ;
FUNCTION update_emp_sal (
p_empno IN NUMBER,
p_raise IN NUMBER
) RETURN NUMBER
IS
v_sal NUMBER := 0 ;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
v_sal := v_sal + p_raise;
UPDATE emp SET sal = v_sal WHERE empno = p_empno;
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT. PUT_LINE( 'Employee ' || p_empno || ' not found' ) ;
RETURN - 1 ;
WHEN OTHERS THEN
DBMS_OUTPUT. PUT_LINE( 'The following is SQLERRM:' ) ;
DBMS_OUTPUT. PUT_LINE( SQLERRM) ;
DBMS_OUTPUT. PUT_LINE( 'The following is SQLCODE:' ) ;
DBMS_OUTPUT. PUT_LINE( SQLCODE) ;
RETURN - 1 ;
END ;
PROCEDURE hire_emp (
p_empno NUMBER,
p_ename VARCHAR2,
p_job VARCHAR2,
p_sal NUMBER,
p_hiredate DATE ,
p_comm NUMBER,
p_mgr NUMBER,
p_deptno NUMBER
)
AS
BEGIN
INSERT INTO emp( empno, ename, job, sal, hiredate, comm, mgr, deptno)
VALUES ( p_empno, p_ename, p_job, p_sal,
p_hiredate, p_comm, p_mgr, p_deptno) ;
END ;
PROCEDURE fire_emp (
p_empno NUMBER
)
AS
BEGIN
DELETE FROM emp WHERE empno = p_empno;
END ;
END ;
/
COMMIT ;