/* ========================================================= PL/SQL编程 ========================================================= */ --先把scott里面的表弄到test表空间里面来 CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP; CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT; /* 上机1 */ --(1)计算King所交税金 DECLARE V_SHUIJIN NUMBER; --应交税金 V_SAL SCOTT.EMP.SAL%TYPE; --工资 C_QIZHENDIAN CONSTANT NUMBER :=3500; BEGIN SELECT SAL INTO V_SAL FROM SCOTT.EMP WHERE ENAME='KING'; IF (V_SAL-C_QIZHENDIAN)<=1500 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.03-0; ELSIF (V_SAL-C_QIZHENDIAN)>1500 AND (V_SAL-C_QIZHENDIAN)<=4500 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.1-105; ELSIF (V_SAL-C_QIZHENDIAN)>4500 AND (V_SAL-C_QIZHENDIAN)<=9000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.2-555; ELSIF (V_SAL-C_QIZHENDIAN)>9000 AND (V_SAL-C_QIZHENDIAN)<=35000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.25-1005; ELSIF (V_SAL-C_QIZHENDIAN)>35000 AND (V_SAL-C_QIZHENDIAN)<=55000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.3-2755; ELSIF (V_SAL-C_QIZHENDIAN)>55000 AND (V_SAL-C_QIZHENDIAN)<=80000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.35-5505; ELSIF (V_SAL-C_QIZHENDIAN)>80000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.45-13505; END IF; DBMS_OUTPUT.PUT_LINE('KING所交的税金是:'||V_SHUIJIN); END; --(2)根据员工scott入职的时间修改发放奖金列,大于等于6年的奖金为2000,小于6年的奖金是1500 DECLARE V_SCOTT_HIREDATE EMP.HIREDATE%TYPE; --SCOTT的入职时间 V_COMM NUMBER; --奖金 BEGIN SELECT HIREDATE INTO V_SCOTT_HIREDATE FROM EMP WHERE ENAME='SCOTT'; IF (SYSDATE-V_SCOTT_HIREDATE)>=365*6 THEN V_COMM:=2000; ELSE V_COMM:=1500; END IF; --开始修改 UPDATE EMP SET COMM=V_COMM WHERE ENAME='SCOTT'; IF SQL%ROWCOUNT>0 THEN DBMS_OUTPUT.PUT_LINE('修改成功!'); ELSE DBMS_OUTPUT.PUT_LINE('修改失败!'); END IF; END; --(3)查询scott相应的工资级别并显示所在部门名称,薪水,和所在的级别 DECLARE V_SCOTT_SAL EMP.SAL%TYPE; --scott的工资 V_JIBIE NUMBER; --级别 V_DEPTNAME DEPT.DNAME%TYPE; --部门名称 BEGIN SELECT SAL,DNAME INTO V_SCOTT_SAL,V_DEPTNAME FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE ENAME='SCOTT'; IF V_SCOTT_SAL>700 AND V_SCOTT_SAL<=3200 THEN V_JIBIE:=1; --第一级别 ELSIF V_SCOTT_SAL>3200 AND V_SCOTT_SAL<=4400 THEN V_JIBIE:=2; --第二级别 ELSIF V_SCOTT_SAL>4400 AND V_SCOTT_SAL<=5000 THEN V_JIBIE:=3; --第三级别 ELSIF V_SCOTT_SAL>5000 AND V_SCOTT_SAL<=7000 THEN V_JIBIE:=4; --第四级别 ELSIF V_SCOTT_SAL>7000 AND V_SCOTT_SAL<=10000 THEN V_JIBIE:=5; --第五级别 END IF; DBMS_OUTPUT.PUT_LINE('SCOTT所在的部门是:'||V_DEPTNAME||',薪水是:'||V_SCOTT_SAL||',所在的级别是:第'||V_JIBIE||'级别'); END; --(4)位员工scott增加工资,每次增加100,直到增加到10000为止 DECLARE V_SCOTT_SAL EMP.SAL%TYPE; --SCOTT的工资 BEGIN SELECT SAL INTO V_SCOTT_SAL FROM EMP WHERE ENAME='SCOTT'; LOOP --增加工资 V_SCOTT_SAL:=V_SCOTT_SAL+100; EXIT WHEN V_SCOTT_SAL>=10000; END LOOP; --修改scott的工资 UPDATE EMP SET SAL=V_SCOTT_SAL WHERE ENAME='SCOTT'; IF SQL%ROWCOUNT>0 THEN DBMS_OUTPUT.PUT_LINE('增加成功!'); ELSE DBMS_OUTPUT.PUT_LINE('增加失败!'); END IF; END; /* 上机2 预定义异常 公司通过emp表维护职员记录,用以接收职员编号并检索职员姓名, */ DECLARE V_ENAME VARCHAR2(4); BEGIN SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=&EMPNO; DBMS_OUTPUT.PUT_LINE('已找到'||V_ENAME); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('对不起,没有该职员!'); WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('职员名称太长!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出现其他的异常!'); END; /* 上机3自定义异常、 ||输入员工编号,工资,部门编号, ||如果部门代码是10,且工资低于10000,更新员工的工资是10000 ||如果部门代码是10,工资高于10000,显示消息“工资不低于10000” ||如果部门代码不是10则不显示 */ DECLARE V_EMPNO EMP.EMPNO%TYPE; --员工编号 V_DEPTNO EMP.DEPTNO%TYPE; --部门编号 V_SAL EMP.SAL%TYPE; --工资 V_ENAME EMP.ENAME%TYPE; --姓名 V_V_EMPNO EMP.EMPNO%TYPE; --输入员工编号 V_V_DEPTNO EMP.DEPTNO%TYPE; --输入部门编号 V_V_SAL EMP.SAL%TYPE; --输入工资 E_ERROR_DEPTNO EXCEPTION; --自定义异常(部门编号不是10) E_ERROR_EMPNO EXCEPTION; --自定义异常(找不到该员工) V_COUNT NUMBER; --声明一个记录数 BEGIN --输入员工编号 V_EMPNO:=&V_V_EMPNO; --输入工资 V_SAL:=&V_VSAL; --输入部门编号 V_DEPTNO:=&V_V_DEPTNO; IF V_DEPTNO=10 THEN --在进行二次判断(输入员工编号) IF V_SAL<10000 THEN --判断输入的员工编号是否存在,不存在的话报异常,存在的话继续 SELECT COUNT(*) INTO V_COUNT FROM EMP WHERE EMPNO=V_DEPTNO; IF V_COUNT!=1 THEN RAISE E_ERROR_EMPNO; --报异常 ELSE --更新工资为1000 UPDATE EMP SET SAL=10000 WHERE EMPNO=V_EMPNO; IF SQL%ROWCOUNT>0 THEN DBMS_OUTPUT.PUT_LINE('更新成功!!!'); ELSE DBMS_OUTPUT.PUT_LINE('更新失败!!!'); END IF; END IF; ELSIF V_SAL>10000 THEN DBMS_OUTPUT.PUT_LINE('工资不低于10000!!'); END IF; ELSE RAISE E_ERROR_DEPTNO; END IF; EXCEPTION WHEN E_ERROR_DEPTNO THEN DBMS_OUTPUT.PUT_LINE('部门代码不是10!!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出现其他异常,请自行解决!!!'); END; ------------------------------------------------------ SELECT * FROM EMP; DECLARE V_NAME EMP.ENAME%TYPE; E_ERROR EXCEPTION; V_COUNT NUMBER; --记录数 BEGIN SELECT COUNT(*) INTO V_COUNT FROM EMP WHERE EMPNO=7901; IF (V_COUNT=1) THEN DBMS_OUTPUT.PUT_LINE(V_NAME); ELSE RAISE E_ERROR; END IF; EXCEPTION WHEN E_ERROR THEN DBMS_OUTPUT.PUT_LINE('没有记录!'); /*when no_data_found then DBMS_OUTPUT.PUT_LINE('找不到!');*/ END; ------------------------------------------------------------- DECLARE V_NAME VARCHAR2(10); E_ERROR EXCEPTION; BEGIN IF V_NAME IS NULL THEN RAISE E_ERROR; ELSE DBMS_OUTPUT.PUT_LINE(V_NAME); END IF; EXCEPTION WHEN E_ERROR THEN DBMS_OUTPUT.PUT_LINE('没有记录!'); END; /* //上机4使用游标 */ --(1)计算公司应交税金的总额 DECLARE V_SHUIJIN NUMBER; --应交税金 V_SAL SCOTT.EMP.SAL%TYPE; --工资 V_SUM NUMBER(10):=0; --总税金 C_QIZHENDIAN CONSTANT NUMBER :=3500; CURSOR CURSOR_SAL IS SELECT SAL FROM EMP; --所有的员工的工资 BEGIN OPEN CURSOR_SAL; LOOP FETCH CURSOR_SAL INTO V_SAL;--把所有的工资放在V_SAL里面 EXIT WHEN CURSOR_SAL%NOTFOUND; IF (V_SAL-C_QIZHENDIAN)<=1500 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.03-0; ELSIF (V_SAL-C_QIZHENDIAN)>1500 AND (V_SAL-C_QIZHENDIAN)<=4500 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.1-105; ELSIF (V_SAL-C_QIZHENDIAN)>4500 AND (V_SAL-C_QIZHENDIAN)<=9000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.2-555; ELSIF (V_SAL-C_QIZHENDIAN)>9000 AND (V_SAL-C_QIZHENDIAN)<=35000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.25-1005; ELSIF (V_SAL-C_QIZHENDIAN)>35000 AND (V_SAL-C_QIZHENDIAN)<=55000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.3-2755; ELSIF (V_SAL-C_QIZHENDIAN)>55000 AND (V_SAL-C_QIZHENDIAN)<=80000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.35-5505; ELSIF (V_SAL-C_QIZHENDIAN)>80000 THEN V_SHUIJIN:=(V_SAL-C_QIZHENDIAN)*0.45-13505; END IF; V_SUM:=V_SUM+V_SHUIJIN; END LOOP; CLOSE CURSOR_SAL; --关闭游标 DBMS_OUTPUT.PUT_LINE(V_SUM); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出现异常!'); END; --(2)根据员工入职时间修改所有员工发放奖金,大于6年的+2000,小于的1500+ DECLARE V_COMM EMP.COMM%TYPE; --奖金 CURSOR CURSOR_EMP_COMM IS SELECT HIREDATE FROM EMP FOR UPDATE; BEGIN FOR CUR1 IN CURSOR_EMP_COMM LOOP IF (SYSDATE-CUR1.HIREDATE)>=365*6 THEN V_COMM:=2000; ELSE V_COMM:=1500; END IF; --开始修改 UPDATE EMP SET COMM=V_COMM WHERE CURRENT OF CURSOR_EMP_COMM; IF SQL%ROWCOUNT>0 THEN DBMS_OUTPUT.PUT_LINE('更新成功!!!'); ELSE DBMS_OUTPUT.PUT_LINE('更新失败!!!'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出现异常!'); END; --(3)显示员工姓名,所在部门名称,薪水,所在级别 DECLARE C_DNAME CONSTANT VARCHAR2(20):='SALES'; --销售部门 V_JIBIE NUMBER; --级别 CURSOR CURSOR_EMP IS SELECT ENAME,DNAME,SAL FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE DNAME=C_DNAME; BEGIN FOR C1 IN CURSOR_EMP LOOP IF C1.SAL>700 AND C1.SAL<=3200 THEN V_JIBIE:=1; --第一级别 ELSIF C1.SAL>3200 AND C1.SAL<=4400 THEN V_JIBIE:=2; --第二级别 ELSIF C1.SAL>4400 AND C1.SAL<=5000 THEN V_JIBIE:=3; --第三级别 ELSIF C1.SAL>5000 AND C1.SAL<=7000 THEN V_JIBIE:=4; --第四级别 ELSIF C1.SAL>7000 AND C1.SAL<=10000 THEN V_JIBIE:=5; --第五级别 ELSE V_JIBIE:=0; --没有级别 END IF; DBMS_OUTPUT.put_line(C1.ENAME||'在'||C1.DNAME||'部门,'||'薪水是'||C1.SAL||'在第'||V_JIBIE||'级别'); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出现异常!'); END; /* 上机5 存储过程 */ --(1)根据输入的员工编号,删除相应的员工 CREATE OR REPLACE PROCEDURE DEL_EMPNAME ( ENO EMP.EMPNO%TYPE, --输入员工的编号 ON_FLAG OUT NUMBER, --执行状态,-1失败,1成功 0异常 ON_MSG OUT VARCHAR --提示信息 ) IS E_ERROR EXCEPTION; --异常信息 BEGIN DELETE FROM EMP WHERE EMPNO=ENO; IF SQL%NOTFOUND THEN RAISE E_ERROR; ELSE ON_FLAG:=1; --执行成功 ON_MSG:='删除成功!'; END IF; EXCEPTION WHEN E_ERROR THEN ON_FLAG:=0; --执行成功 ON_MSG:='删除失败!'; WHEN OTHERS THEN ON_FLAG:=0; ON_MSG:='出现异常!'; END; DROP PROCEDURE DEL_EMPNAME; --调用存储过程 DECLARE V_EMPNO NUMBER; ENO NUMBER(5); ON_FLAG NUMBER(1); ON_MSG VARCHAR(20); BEGIN ENO:=&EMPNO; --输入编号 DEL_EMPNAME(ENO,ON_FLAG,ON_MSG); DBMS_OUTPUT.PUT_LINE(ON_FLAG); DBMS_OUTPUT.PUT_LINE(ON_MSG); END; --(2)创建输出参数为薪水集合的存储过程,调用并显示所有员工的薪水 CREATE OR REPLACE PROCEDURE get_sals( cur_salary OUT SYS_REFCURSOR, on_Flag OUT number, --执行状态 os_Msg OUT VARCHAR2 --提示信息 ) AS BEGIN OPEN cur_salary FOR SELECT empno,sal FROM employee; on_Flag:=1; os_Msg:='成功'; EXCEPTION WHEN OTHERS THEN on_Flag:=-1; os_Msg:='其他错误,与管理员联系。'; END; DECLARE v_empno employee.empno%type; v_sal employee.sal%type; emp_salary SYS_REFCURSOR; on_Flag number(1); --执行状态 os_Msg VARCHAR2(200); --提示信息 BEGIN get_sals(emp_salary,on_Flag,os_Msg); IF on_flag=1 THEN LOOP FETCH emp_salary INTO v_empno, v_sal; EXIT WHEN emp_salary%notfound; DBMS_OUTPUT.PUT_LINE(v_empno||'的薪水是' ||v_sal); END LOOP; ELSE dbms_output.put_line(os_Msg); END IF; IF emp_salary%ISOPEN THEN CLOSE emp_salary; END IF; END;