Oracle PL/SQL之EXCEPTION

Test Code:

DECLARE<br />BEGIN</p><p> <<test0>><br />-- most normal way to handle exception.<br /> DECLARE<br /> except_test0 EXCEPTION;<br /> BEGIN<br /> RAISE except_test0;<br /> EXCEPTION<br /> WHEN except_test0 THEN<br /> dbms_output.put_line('test0 except_test0: SQLCODE=' || SQLCODE ||<br /> ', SQLERRM=' || SQLERRM);<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('test0 OTHERS: SQLCODE=' || SQLCODE ||<br /> ', SQLERRM=' || SQLERRM);<br /> END;</p><p> <<test1>><br />-- custom exception error number.<br /> DECLARE<br /> except_test1 EXCEPTION;<br /> -- suggested error number range: -20,NNN.<br /> PRAGMA EXCEPTION_INIT(except_test1, -20001);<br /> BEGIN<br /> RAISE except_test1;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('test1: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);<br /> END;</p><p> <<test2>><br />-- custom exception error number and error message.<br /> BEGIN<br /> raise_application_error(-20002, 'except test 2');<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> IF SQLCODE = -20002<br /> THEN<br /> dbms_output.put_line('test2A: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);<br /> dbms_output.put_line('test2B: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);<br /> ELSE<br /> dbms_output.put_line('test2C: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);<br /> END IF;<br /> END;</p><p> -- SQLCODE and SQLERRM will be re evaluated after EXCEPTION handled.<br /> dbms_output.put_line('test2D: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);</p><p> <<test3>><br />-- custom exception error number and error message, more readable.<br /> DECLARE<br /> except_test3 EXCEPTION;<br /> PRAGMA EXCEPTION_INIT(except_test3, -20001);<br /> BEGIN<br /> raise_application_error(-20001, 'except test 3');<br /> EXCEPTION<br /> WHEN except_test3 THEN<br /> dbms_output.put_line('test3 except_test3: SQLCODE=' || SQLCODE ||<br /> ', SQLERRM=' || SQLERRM);<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('test3 OTHERS: SQLCODE=' || SQLCODE ||<br /> ', SQLERRM=' || SQLERRM);<br /> END;</p><p> <<test4>><br />-- exception can be re raised.<br /> BEGIN<br /> RAISE no_data_found;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('test4: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);<br /> RAISE;<br /> END;</p><p>EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('outer: SQLCODE=' || SQLCODE || ', SQLERRM=' ||<br /> SQLERRM);<br />END;<br />

 

Output:

test0 except_test0: SQLCODE=1, SQLERRM=User-Defined Exception<br />test1: SQLCODE=-20001, SQLERRM=ORA-20001:<br />test2A: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2<br />test2B: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2<br />test2D: SQLCODE=0, SQLERRM=ORA-0000: normal, successful completion<br />test3 except_test3: SQLCODE=-20001, SQLERRM=ORA-20001: except test 3<br />test4: SQLCODE=100, SQLERRM=ORA-01403: no data found<br />outer: SQLCODE=100, SQLERRM=ORA-01403: no data found<br />