Oracle学习笔记（四）

• 例外：用来增强程序的健壮性和容错性。
• 系统例外：No_data_found（没有找到数据）、Too_many_rows（select...into语句匹配多个行）、Zero_Divide（被零除）、Value_error（算术或（类型）转换错误，算数错误例如：负数开平方）、Timeout_on_resource（在等待资源时发生超时，主要用在分布式数据库中）

-- 系统例外：no_data_found
SET SERVEROUTPUT ON;
DECLARE
    pename emp.ename%type;
BEGIN
    -- 查询员工号是1234的员工姓名
    SELECT ename INTO pename FROM emp WHERE empno=1234;
EXCEPTION
    WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('没有找到该员工！');
    -- 关键字others表示接收其他例外
    WHEN OTHERS THEN dbms_output.put_line('其他例外！'); 
END;
/

-- 系统例外：too_many_rows
SET SERVEROUTPUT ON;
DECLARE
    pename emp.ename%type;
BEGIN
    -- 查询所有10号部门的员工姓名
    SELECT ename INTO pename FROM emp WHERE deptno=10;
EXCEPTION
    WHEN too_many_rows THEN DBMS_OUTPUT.PUT_LINE('select...into 匹配了多行');
    -- 关键字others表示接收其他例外
    WHEN OTHERS THEN dbms_output.put_line('其他例外！'); 
END;
/

-- 系统例外：被0除 zero_divide
SET SERVEROUTPUT ON;
DECLARE
    -- 定义一个基本变量
    pnum number;
BEGIN
    pnum := 1 / 0;
EXCEPTION
    -- then 相当于一个花括号，所以其后面可以有多个处理语句
    WHEN zero_divide THEN DBMS_OUTPUT.PUT_LINE('1:0不能作除数');
                          DBMS_OUTPUT.PUT_LINE('2:0不能作除数');
    -- 关键字others表示接收其他例外
    WHEN OTHERS THEN dbms_output.put_line('其他例外！'); 
END;
/

-- 系统例外：value_error
SET SERVEROUTPUT ON;
DECLARE
    -- 定义一个基本变量
    pnum number;
BEGIN
    pnum := 'abc';
EXCEPTION
    WHEN value_error THEN DBMS_OUTPUT.PUT_LINE('算术或者转换错误！');
    WHEN OTHERS THEN dbms_output.put_line('其他例外！'); 
END;
/

• 自定义例外：定义变量，类型是exception，使用关键字raise抛出自定义例外

-- 自定义例外：查询50号部门的员工姓名
SET SERVEROUTPUT ON;
DECLARE
    -- 定义游标，代表50号部门的员工姓名
    CURSOR cemp IS SELECT ename FROM emp WHERE deptno=50;
    pename emp.ename%type;
    -- 自定义例外
    no_emp_found EXCEPTION;
BEGIN
    -- 打开游标
    OPEN cemp;
    FETCH cemp INTO pename;
    if cemp%notfound THEN 
    -- 抛出例外
        raise no_emp_found;
    END IF;
    -- 关闭游标
    -- Oracle自动启动pmon(process monitor)（进程监视器，将程序中遗留的系统垃圾和资源一个个pmon释放掉）
    -- 因此，游标最终会被关闭，而不会因为例外的发生造成资源的浪费
    CLOSE cemp;

EXCEPTION
    WHEN no_emp_found THEN DBMS_OUTPUT.PUT_LINE('没有找到员工！');
    WHEN OTHERS THEN dbms_output.put_line('其他例外！');
END;
/

• 瀑布模型（Waterfall Model）是将软件生存周期中的各个活动规定为依线性顺序连接的若干阶段的模型，包括需求分析、（概要/详细）设计、编码、测试、运行和维护。它规定了由前至后、相互衔接的固定次序，如同瀑布流水逐级下落：

瀑布模型

-- 统计每年入职的员工人数和入职总人数
SET SERVEROUTPUT ON;
DECLARE 
    -- 定义游标；to_char()函数将数值型或者日期型转化为字符型
    CURSOR cemp IS SELECT to_char(hiredate,'yyyy') FROM emp;
    -- 定义一个记录型变量
    phiredate VARCHAR2(4);
    -- 定义每年入职的员工人数变量
    count80 number := 0;
    count81 number := 0;
    count82 number := 0;
    count87 number := 0;
BEGIN
    -- 打开游标
    OPEN cemp;
    LOOP
    FETCH cemp INTO phiredate; 
    EXIT WHEN cemp%notfound;
    
    -- 判断入职年份
    if phiredate = '1980' THEN count80 := count80 + 1;
    elsif phiredate = '1981' THEN count81 := count81 + 1;
    elsif phiredate = '1982' THEN count82 := count82 + 1;
    else count87 := count87 + 1;
    END IF;

    END LOOP;
    -- 关闭游标
    CLOSE cemp;
    -- 输出结果
    DBMS_OUTPUT.PUT_LINE('Total: ' || (count80 + count81 + count82 + count87));
    DBMS_OUTPUT.PUT_LINE('1980: ' || count80);
    DBMS_OUTPUT.PUT_LINE('1981: ' || count81);
    DBMS_OUTPUT.PUT_LINE('1982: ' || count82);
    DBMS_OUTPUT.PUT_LINE('1987: ' || count87);
END;
/ 

-- 为员工涨工资：从最低工资起每人涨10%，但工资总额不能超过5w，求涨工资的人数和涨后的工资总额
SET SERVEROUTPUT ON;
DECLARE 
    CURSOR cemp IS SELECT empno, sal FROM emp ORDER BY sal;
    pempno emp.empno%type;
    psal emp.sal%type;
    -- 涨工资的人数
    countEmp number := 0;
    -- 涨后的工资总额，number可以存储浮点数，也可以存储整数；
    salTotal number;
BEGIN
    SELECT SUM(sal) INTO salTotal FROM emp;
    -- 打开游标
    OPEN cemp;
    LOOP
    -- 1、工资总额 > 5w时退出循环
    EXIT WHEN salTotal > 50000;
    -- 取出当前一个员工进行涨工资
    FETCH cemp INTO pempno, psal;
    -- 2、%notfound为true时退出循环
    EXIT WHEN cemp%notfound;
    -- 3、超过总工资5w时退出循环 
    EXIT WHEN saltotal + psal * 0.1 > 50000;
    -- 4、涨工资
    UPDATE emp SET sal = sal * 1.1 WHERE empno = pempno;
    -- 5、人数加1
    countEmp := countEmp + 1;
    -- 6、涨后的工资总额=涨前工资总额+sal*0.1
    saltotal := saltotal + psal * 0.1;
    END LOOP;
    -- 关闭游标
    CLOSE cemp;

    -- 记得提交更新操作
    COMMIT;
    
    -- 打印结果
    dbms_output.put_line('人数：' || countemp || '，涨后的工资总额：' || saltotal);
END;
/

为员工涨工资的小测试

-- 涉及两张表的员工涨工资问题：用PL/SQL语言实现按部门分段（6000以上、（6000，3000），3000元以下）统计各工资段的职工人数、以及各部门的工资总额（工资总额中不包括奖金）
-- 首先创建一张待存储结果的新表
CREATE TABLE msg
(
  deptno number,
  count1 number,
  count2 number,
  count3 number,
  saltotal number
);
-- 编写PL/SQL语言
SET SERVEROUTPUT ON;
DECLARE 
    -- 定义部门的游标
    CURSOR cdept IS SELECT deptno FROM dept;
    -- 定义存放部门号的变量
    pdeptno dept.deptno%type;
    -- 遍历部门中员工薪水的游标，其中部门号作为形参
    CURSOR cemp(dno number) IS SELECT sal FROM emp WHERE deptno = dno;
    -- 定义一个员工收入的变量
    psal emp.sal%type;
    -- 定义每个工资段的员工人数变量
    count1 number;
    count2 number;
    count3 number;
    
    -- 每个部门的工资总额
    saltotal number;
BEGIN
    -- 打开部门的游标
    OPEN cdept;
    LOOP
        -- 取出一个部门号
        FETCH cdept INTO pdeptno;
        EXIT WHEN cdept%notfound;
        -- 初始化工作
        count1 := 0;
        count2 := 0;
        count3 := 0;
        -- 得到同一个部门的工资总额
      -- SELECT SUM(sal) INTO saltotal FROM emp WHERE deptno = pdeptno;
        saltotal := 0;
        -- 打开游标，遍历每个员工
        OPEN cemp(pdeptno);
            LOOP
                -- 取出一个员工的薪水
                FETCH cemp INTO psal;
                EXIT WHEN cemp%notfound;
                saltotal := saltotal + psal;
                -- 判断薪水的范围
                if psal < 3000 THEN count1 := count1 + 1;
                elsif psal < 6000 THEN count2 := count2 + 1;
                else count3 := count3 + 1;
                END IF;
            END LOOP;
        CLOSE cemp;
        -- 保存当前部门的结果，NVL(expr1,expr2)函数是一个空值转换函数，若expr1不为空值则输出本身，否则返回指定值expr2。
      -- INSERT INTO msg VALUES(pdeptno,count1, count2, count3, nvl(saltotal, 0)); 
        INSERT INTO msg VALUES(pdeptno,count1, count2, count3, saltotal); 
    END LOOP;
    
    -- 关闭部门的游标
    CLOSE cdept;
    
    -- 带插入操作，需提交事务
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('统计完成！');
END;
/

涉及操作2张表的测试结果