Oracle 存储过程中自定义异常

来源：互联网发布：做淘宝需要哪些软件编辑：程序博客网时间：2024/05/01 00:41

参考:

ORACLE 用户自定义异常小例子
Oracle中RAISE异常深入分析

1.进入pl/sql测试窗口

这里写图片描述

2.执行语句

declare  empname varchar2(255);  customize_exp EXCEPTION; --自定义异常begin  FOR c IN (select d.* from scott.dept d) LOOP    begin      dbms_output.put_line('dept: ' || c.deptno || '=' || c.dname);      --当部门ID为40时抛出异常      if (c.deptno = 40) then        RAISE customize_exp; -- 抛出自定义异常            end if;      --当部门ID为10、20、30时,会执行下面的查询,由于出现多行所以会报 Too many rows round!      --当部门ID为40时，这里不再执行，控制转向      select e.ename into empname from scott.emp e       where e.deptno = c.deptno;    exception      when customize_exp then        dbms_output.put_line('customize error!');      when no_data_found then        dbms_output.put_line('Data is not found!');      when too_many_rows then        dbms_output.put_line('Too many rows round!');      when OTHERS then        dbms_output.put_line('others error');    end;  END LOOP;end;

3.结果

这里写图片描述

4. IF嵌套

** 注意是 elsif 不是 else if

declare  empname varchar2(255);begin  FOR c IN (select d.* from scott.dept d) LOOP    begin      dbms_output.put_line('dept: ' || c.deptno || '=' || c.dname);      if c.deptno = 10 then        dbms_output.put_line('deptno= ' || c.deptno);        select e.ename into empname from scott.emp e where e.empno = 7782;        if empname = 'CLARK' then          dbms_output.put_line('ename= ' || empname);        elsif empname = 'KING' then          dbms_output.put_line('ename= ' || empname);        end if;      end if;    exception      when no_data_found then        dbms_output.put_line('Data is not found!');      when too_many_rows then        dbms_output.put_line('Too many rows round!');      when OTHERS then        dbms_output.put_line('others error');    end;  END LOOP;exception  when OTHERS then    dbms_output.put_line('others error');end;

并列for循环与局部变量

declare  empname varchar2(255);begin  FOR c IN (select d.* from scott.dept d) LOOP    begin      dbms_output.put_line('dept: ' || c.deptno || '=' || c.dname);    exception      when no_data_found then        dbms_output.put_line('Data is not found!');      when too_many_rows then        dbms_output.put_line('Too many rows round!');      when OTHERS then        dbms_output.put_line('others error');    end;  END LOOP;  FOR c IN (select e.* from scott.emp e) LOOP    --局部变量    declare      v_ename varchar2(255);    begin      v_ename := c.ename;      dbms_output.put_line('emp: ' || v_ename);    exception      when no_data_found then        dbms_output.put_line('Data is not found!');      when too_many_rows then        dbms_output.put_line('Too many rows round!');      when OTHERS then        dbms_output.put_line('others error');    end;  END LOOP;exception  when OTHERS then    dbms_output.put_line('others error');end;

0 0