年终奖(BONUS)计算方法如下:
i:基础值(按JOB分):
CLERK:SAL * 2.5
SALLESMAN:SAL * 2 + COMM * 4
ANALYST: SAL * 4
MANAGER: 按部门分:
ACCOUNTING : SAL * 5;
RESEATCH: SAL * 4;
SALES: SAL * 3;
OPERATIONS: SAL * 2;
其他: SAL * 1.5;
ii:根据工龄:
如果入社时间大于等于5年,在上述基础上加2000;
如果入社时间大于等于3年,小于5年,在上述基础上加1000;如果入社时间小于3年,在上述基础上加500;iii:根据请假情况(ATTENDANCE表):
如果某位员工请柬天数总计大于5天,那么扣500;
如果全勤的,加500。
CREATE OR REPLACE PROCEDURE CAL_REVENUES_072(V_YEARLY IN REVENUES_07.YEARLY%TYPE:='2009') ISCURSOR ALL_CURSOR IS SELECT E.EMPNO,E.ENAME,E.JOB,D.DNAME,E.HIREDATE,A.DDAY,E.SAL,E.COMM FROM EMP E,DEPT D,ATTENDANCE A WHERE E.DEPTNO=D.DEPTNO AND E.EMPNO=A.EMPNO;V_BONUS REVENUES_07.BONUS%TYPE;
P_BONUS REVENUES_07.BONUS%TYPE;
S_BONUS REVENUES_07.BONUS%TYPE;E_DELETE_COUNT EXCEPTION;
BEGIN
----1. 删除REVENUES_XX表2009年的数据。
DELETE REVENUES_07 WHERE YEARLY=V_YEARLY;
IF SQL%ROWCOUNT=0 THEN RAISE E_DELETE_COUNT;
END IF;
----3.1 先插入YEARLY,EMPNO,ENAME.DNAME,BOUNS
FOR V_ALL IN ALL_CURSOR LOOP
BEGIN
INSERT INTO REVENUES_07(YEARLY,EMPNO,ENAME,DNAME,BONUS) VALUES(V_YEARLY,V_ALL.EMPNO,V_ALL.ENAME,V_ALL.DNAME,S_BONUS);
if V_ALL.JOB='CLERK' THEN V_BONUS:=V_ALL.SAL*2.5;
elsif V_ALL.JOB='SALESMAN' THEN V_BONUS:=V_ALL.SAL*2+V_ALL.COMM*4;
elsif V_ALL.JOB='ANALYST' THEN V_BONUS:=V_ALL.SAL*4;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='ACCOUNTING' THEN V_BONUS:=V_ALL.SAL*5;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='RESEARCH' THEN V_BONUS:=V_ALL.SAL*4;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='SALES' THEN V_BONUS:=V_ALL.SAL*3;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='OPERATIONS' THEN V_BONUS:=V_ALL.SAL*2;
end if;
CASE
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')>=5 THEN P_BONUS:=V_BONUS+2000;
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')>=3 THEN P_BONUS:=V_BONUS+1000;
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')<3 THEN P_BONUS:=V_BONUS+500;
END CASE;
CASE
WHEN V_ALL.DDAY>5 THEN S_BONUS:=P_BONUS-500;
WHEN V_ALL.DDAY>0 THEN S_BONUS:=P_BONUS+0;
WHEN V_ALL.DDAY=0 THEN S_BONUS:=P_BONUS+500;
END CASE;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN E_DELETE_COUNT THEN DBMS_OUTPUT.put_line('该表已无'||V_YEARLY||'年数据!!');
END;
现在运行说,BONUS不能插入空值是不是S_BONUS传不上去。各位大哥怎么解决啊
i:基础值(按JOB分):
CLERK:SAL * 2.5
SALLESMAN:SAL * 2 + COMM * 4
ANALYST: SAL * 4
MANAGER: 按部门分:
ACCOUNTING : SAL * 5;
RESEATCH: SAL * 4;
SALES: SAL * 3;
OPERATIONS: SAL * 2;
其他: SAL * 1.5;
ii:根据工龄:
如果入社时间大于等于5年,在上述基础上加2000;
如果入社时间大于等于3年,小于5年,在上述基础上加1000;如果入社时间小于3年,在上述基础上加500;iii:根据请假情况(ATTENDANCE表):
如果某位员工请柬天数总计大于5天,那么扣500;
如果全勤的,加500。
CREATE OR REPLACE PROCEDURE CAL_REVENUES_072(V_YEARLY IN REVENUES_07.YEARLY%TYPE:='2009') ISCURSOR ALL_CURSOR IS SELECT E.EMPNO,E.ENAME,E.JOB,D.DNAME,E.HIREDATE,A.DDAY,E.SAL,E.COMM FROM EMP E,DEPT D,ATTENDANCE A WHERE E.DEPTNO=D.DEPTNO AND E.EMPNO=A.EMPNO;V_BONUS REVENUES_07.BONUS%TYPE;
P_BONUS REVENUES_07.BONUS%TYPE;
S_BONUS REVENUES_07.BONUS%TYPE;E_DELETE_COUNT EXCEPTION;
BEGIN
----1. 删除REVENUES_XX表2009年的数据。
DELETE REVENUES_07 WHERE YEARLY=V_YEARLY;
IF SQL%ROWCOUNT=0 THEN RAISE E_DELETE_COUNT;
END IF;
----3.1 先插入YEARLY,EMPNO,ENAME.DNAME,BOUNS
FOR V_ALL IN ALL_CURSOR LOOP
BEGIN
INSERT INTO REVENUES_07(YEARLY,EMPNO,ENAME,DNAME,BONUS) VALUES(V_YEARLY,V_ALL.EMPNO,V_ALL.ENAME,V_ALL.DNAME,S_BONUS);
if V_ALL.JOB='CLERK' THEN V_BONUS:=V_ALL.SAL*2.5;
elsif V_ALL.JOB='SALESMAN' THEN V_BONUS:=V_ALL.SAL*2+V_ALL.COMM*4;
elsif V_ALL.JOB='ANALYST' THEN V_BONUS:=V_ALL.SAL*4;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='ACCOUNTING' THEN V_BONUS:=V_ALL.SAL*5;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='RESEARCH' THEN V_BONUS:=V_ALL.SAL*4;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='SALES' THEN V_BONUS:=V_ALL.SAL*3;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='OPERATIONS' THEN V_BONUS:=V_ALL.SAL*2;
end if;
CASE
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')>=5 THEN P_BONUS:=V_BONUS+2000;
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')>=3 THEN P_BONUS:=V_BONUS+1000;
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')<3 THEN P_BONUS:=V_BONUS+500;
END CASE;
CASE
WHEN V_ALL.DDAY>5 THEN S_BONUS:=P_BONUS-500;
WHEN V_ALL.DDAY>0 THEN S_BONUS:=P_BONUS+0;
WHEN V_ALL.DDAY=0 THEN S_BONUS:=P_BONUS+500;
END CASE;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN E_DELETE_COUNT THEN DBMS_OUTPUT.put_line('该表已无'||V_YEARLY||'年数据!!');
END;
现在运行说,BONUS不能插入空值是不是S_BONUS传不上去。各位大哥怎么解决啊
P_BONUS REVENUES_07.BONUS%TYPE;
S_BONUS REVENUES_07.BONUS%TYPE;E_DELETE_COUNT EXCEPTION;
BEGIN
----1. 删除REVENUES_XX表2009年的数据。
DELETE REVENUES_07 WHERE YEARLY=V_YEARLY;
IF SQL%ROWCOUNT=0 THEN RAISE E_DELETE_COUNT;
END IF;
----3.1 先插入YEARLY,EMPNO,ENAME.DNAME,BOUNS
FOR V_ALL IN ALL_CURSOR LOOP
BEGIN
if V_ALL.JOB='CLERK' THEN V_BONUS:=V_ALL.SAL*2.5;
elsif V_ALL.JOB='SALESMAN' THEN V_BONUS:=V_ALL.SAL*2+V_ALL.COMM*4;
elsif V_ALL.JOB='ANALYST' THEN V_BONUS:=V_ALL.SAL*4;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='ACCOUNTING' THEN V_BONUS:=V_ALL.SAL*5;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='RESEARCH' THEN V_BONUS:=V_ALL.SAL*4;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='SALES' THEN V_BONUS:=V_ALL.SAL*3;
elsif V_ALL.JOB='MANAGER' and V_ALL.DNAME='OPERATIONS' THEN V_BONUS:=V_ALL.SAL*2;
end if;
CASE
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')>=5 THEN P_BONUS:=V_BONUS+2000;
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')>=3 THEN P_BONUS:=V_BONUS+1000;
WHEN TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(V_ALL.HIREDATE, 'YYYY')<3 THEN P_BONUS:=V_BONUS+500;
END CASE;
CASE
WHEN V_ALL.DDAY>5 THEN S_BONUS:=P_BONUS-500;
WHEN V_ALL.DDAY>0 THEN S_BONUS:=P_BONUS+0;
WHEN V_ALL.DDAY=0 THEN S_BONUS:=P_BONUS+500;
END CASE;
INSERT INTO REVENUES_07(YEARLY,EMPNO,ENAME,DNAME,BONUS) VALUES(V_YEARLY,V_ALL.EMPNO,V_ALL.ENAME,V_ALL.DNAME,S_BONUS);--放到这试下END;
END LOOP;
COMMIT;
EXCEPTION
WHEN E_DELETE_COUNT THEN DBMS_OUTPUT.put_line('该表已无'||V_YEARLY||'年数据!!');
END;