参考:
ORACLE 用户自定义异常小例子Oracle中RAISE异常深入分析
1.进入pl/sql测试窗口
2.执行语句
declareempname varchar2(255);customize_exp EXCEPTION; --自定义异常beginFOR c IN (select d.* from scott.dept d) LOOPbegindbms_output.put_line('dept: ' || c.deptno || '=' || c.dname);--当部门ID为40时抛出异常if (c.deptno = 40) thenRAISE customize_exp; -- 抛出自定义异常end if;--当部门ID为10、20、30时,会执行下面的查询,由于出现多行所以会报 Too many rows round!--当部门ID为40时，这里不再执行，控制转向select e.ename into empname from scott.emp ewhere e.deptno = c.deptno;exceptionwhen customize_exp thendbms_output.put_line('customize error!');when no_data_found thendbms_output.put_line('Data is not found!');when too_many_rows thendbms_output.put_line('Too many rows round!');when OTHERS thendbms_output.put_line('others error');end;END LOOP;end;
3.结果
4. IF嵌套
** 注意是 elsif 不是 else if
declareempname varchar2(255);beginFOR c IN (select d.* from scott.dept d) LOOPbegindbms_output.put_line('dept: ' || c.deptno || '=' || c.dname);if c.deptno = 10 thendbms_output.put_line('deptno= ' || c.deptno);select e.ename into empname from scott.emp e where e.empno = 7782;if empname = 'CLARK' thendbms_output.put_line('ename= ' || empname);elsif empname = 'KING' thendbms_output.put_line('ename= ' || empname);end if;end if;exceptionwhen no_data_found thendbms_output.put_line('Data is not found!');when too_many_rows thendbms_output.put_line('Too many rows round!');when OTHERS thendbms_output.put_line('others error');end;END LOOP;exceptionwhen OTHERS thendbms_output.put_line('others error');end;
并列for循环与局部变量
declareempname varchar2(255);beginFOR c IN (select d.* from scott.dept d) LOOPbegindbms_output.put_line('dept: ' || c.deptno || '=' || c.dname);exceptionwhen no_data_found thendbms_output.put_line('Data is not found!');when too_many_rows thendbms_output.put_line('Too many rows round!');when OTHERS thendbms_output.put_line('others error');end;END LOOP;FOR c IN (select e.* from scott.emp e) LOOP--局部变量declarev_ename varchar2(255);beginv_ename := c.ename;dbms_output.put_line('emp: ' || v_ename);exceptionwhen no_data_found thendbms_output.put_line('Data is not found!');when too_many_rows thendbms_output.put_line('Too many rows round!');when OTHERS thendbms_output.put_line('others error');end;END LOOP;exceptionwhen OTHERS thendbms_output.put_line('others error');end;
