PL/SQL经典练习

简介: PL/SQL经典练习
/*
=========================================================
                        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;


相关文章
|
6月前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量:数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中,变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”,用于存储程序运行时的动态数据,通过`DECLARE`定义,可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”,一旦设定值便保持不变,用`CONSTANT`声明,提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性,可以更高效地管理和控制变量与常量,提升代码质量。善用两者,能优化PL/SQL程序的结构和性能。
|
6月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL隐式游标:数据的“自动导游”与“轻松之旅”
【4月更文挑战第19天】Oracle PL/SQL中的隐式游标是自动管理的数据导航工具,简化编程工作,尤其适用于简单查询和DML操作。它自动处理数据访问,提供高效、简洁的代码,但不适用于复杂场景。显式游标在需要精细控制时更有优势。了解并适时使用隐式游标,能提升数据处理效率,让开发更加轻松。
|
6月前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(五):PL/SQL编程
【Oracle】玩转Oracle数据库(五):PL/SQL编程
133 8
|
1天前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
10 2
|
3月前
|
SQL Oracle 关系型数据库
SQL与PL/SQL:数据库编程语言的比较
【8月更文挑战第31天】
52 0
|
5月前
|
SQL 存储 数据库
技术好文:SQL查询语句基本练习
技术好文:SQL查询语句基本练习
|
5月前
|
SQL 数据库 Perl
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
441 0
|
6月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
6月前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句:数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句(LOOP、EXIT WHEN、FOR、WHILE)是处理数据的关键工具,用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环,可通过EXIT WHEN设定退出条件;FOR循环适用于固定次数迭代,WHILE循环基于条件判断执行。有效使用循环能提高效率,但需注意避免无限循环和优化大数据处理性能。掌握循环语句,将使数据处理更加高效和便捷。
|
6月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式:数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式,体验数据的魔法公式。表达式结合常量、变量、运算符和函数,用于数据运算与转换。算术运算符处理数值计算,比较运算符执行数据比较,内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序,助你驾驭数据,揭示其背后的规律与秘密,成为数据魔法师。