PL/SQL经典练习

来源：互联网发布：.net水电管理系统源码编辑：程序博客网时间：2024/06/05 09:24
/*=========================================================                        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年的奖金是1500DECLARE    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）创建输出参数为薪水集合的存储过程，调用并显示所有员工的薪水
1 0