Oracle 存储过程中自定义异常

参考:

1. ORACLE 用户自定义异常小例子
2. 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;

1. 并列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;