Oracle PL/SQL之EXCEPTION
Oracle PL/SQL之EXCEPTION
Test Code:
- DECLARE
- BEGIN
- <<test0>>
- -- most normal way to handle exception.
- DECLARE
- except_test0 EXCEPTION;
- BEGIN
- RAISE except_test0;
- EXCEPTION
- WHEN except_test0 THEN
- dbms_output.put_line('test0 except_test0: SQLCODE=' || SQLCODE ||
- ', SQLERRM=' || SQLERRM);
- WHEN OTHERS THEN
- dbms_output.put_line('test0 OTHERS: SQLCODE=' || SQLCODE ||
- ', SQLERRM=' || SQLERRM);
- END;
- <<test1>>
- -- custom exception error number.
- DECLARE
- except_test1 EXCEPTION;
- -- suggested error number range: -20,NNN.
- PRAGMA EXCEPTION_INIT(except_test1, -20001);
- BEGIN
- RAISE except_test1;
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('test1: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- END;
- <<test2>>
- -- custom exception error number and error message.
- BEGIN
- raise_application_error(-20002, 'except test 2');
- EXCEPTION
- WHEN OTHERS THEN
- IF SQLCODE = -20002
- THEN
- dbms_output.put_line('test2A: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- dbms_output.put_line('test2B: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- ELSE
- dbms_output.put_line('test2C: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- END IF;
- END;
- -- SQLCODE and SQLERRM will be re evaluated after EXCEPTION handled.
- dbms_output.put_line('test2D: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- <<test3>>
- -- custom exception error number and error message, more readable.
- DECLARE
- except_test3 EXCEPTION;
- PRAGMA EXCEPTION_INIT(except_test3, -20001);
- BEGIN
- raise_application_error(-20001, 'except test 3');
- EXCEPTION
- WHEN except_test3 THEN
- dbms_output.put_line('test3 except_test3: SQLCODE=' || SQLCODE ||
- ', SQLERRM=' || SQLERRM);
- WHEN OTHERS THEN
- dbms_output.put_line('test3 OTHERS: SQLCODE=' || SQLCODE ||
- ', SQLERRM=' || SQLERRM);
- END;
- <<test4>>
- -- exception can be re raised.
- BEGIN
- RAISE no_data_found;
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('test4: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- RAISE;
- END;
- EXCEPTION
- WHEN OTHERS THEN
- dbms_output.put_line('outer: SQLCODE=' || SQLCODE || ', SQLERRM=' ||
- SQLERRM);
- END;
Output:
- test0 except_test0: SQLCODE=1, SQLERRM=User-Defined Exception
- test1: SQLCODE=-20001, SQLERRM=ORA-20001:
- test2A: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2
- test2B: SQLCODE=-20002, SQLERRM=ORA-20002: except test 2
- test2D: SQLCODE=0, SQLERRM=ORA-0000: normal, successful completion
- test3 except_test3: SQLCODE=-20001, SQLERRM=ORA-20001: except test 3
- test4: SQLCODE=100, SQLERRM=ORA-01403: no data found
- outer: SQLCODE=100, SQLERRM=ORA-01403: no data found
评论暂时关闭