Oracle PL/SQL 常见的存储过程异常处理DBMS_UTILITY、SQLCODE、SQLERRM

创建测试存储过程

CREATE OR REPLACE PROCEDURE example_proc (
  p_empno IN emp.empno%TYPE,
  p_ename OUT emp.ename%TYPE,
  p_error_code OUT NUMBER,
  p_error_message OUT VARCHAR2,
  p_error_stack OUT VARCHAR2,
  p_error_backtrace OUT VARCHAR2
) IS
BEGIN
  SELECT ename
  INTO p_ename
  FROM emp
  WHERE empno = p_empno;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    p_error_code := SQLCODE;
    p_error_message := SQLERRM;
    p_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
    p_error_backtrace := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  WHEN TOO_MANY_ROWS THEN
    ROLLBACK;
    p_error_code := SQLCODE;
    p_error_message := SQLERRM;
    p_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
    p_error_backtrace := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  WHEN OTHERS THEN
    ROLLBACK;
    p_error_code := SQLCODE;
    p_error_message := SQLERRM;
    p_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
    p_error_backtrace := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
END example_proc;
/
复制

调用

set serveroutput on 
DECLARE
  v_empno emp.empno%TYPE := 7788;
  v_ename emp.ename%TYPE;
  v_error_code NUMBER;
  v_error_message VARCHAR2(200);
  v_error_stack VARCHAR2(2000);
  v_error_backtrace VARCHAR2(2000);
BEGIN
  example_proc(v_empno, v_ename, v_error_code, v_error_message, v_error_stack, v_error_backtrace);
  IF v_error_code IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || v_error_code);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || v_error_message);
    DBMS_OUTPUT.PUT_LINE('Error Stack: ' || v_error_stack);
    DBMS_OUTPUT.PUT_LINE('Error Backtrace: ' || v_error_backtrace);
  END IF;
END;
/
复制

在这个例子中，我们创建了一个名为 example_proc 的存储过程。当查询结果不唯一或没有数据时，程序将回滚并设置相应的错误代码和错误消息。您可以使用此存储过程，根据员工编号查询员工的名字，并了解查询过程中的任何错误。

SQL> set serveroutput on 
SQL> DECLARE
  2    v_empno emp.empno%TYPE := 7788;
  3    v_ename emp.ename%TYPE;
  4    v_error_code NUMBER;
  5    v_error_message VARCHAR2(200);
  6    v_error_stack VARCHAR2(2000);
  7    v_error_backtrace VARCHAR2(2000);
  8  BEGIN
  9    example_proc(v_empno, v_ename, v_error_code, v_error_message, v_error_stack, v_error_backtrace);
 10    IF v_error_code IS NULL THEN
 11      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
 12    ELSE
 13      DBMS_OUTPUT.PUT_LINE('Error Code: ' || v_error_code);
 14      DBMS_OUTPUT.PUT_LINE('Error Message: ' || v_error_message);
 15      DBMS_OUTPUT.PUT_LINE('Error Stack: ' || v_error_stack);
 16      DBMS_OUTPUT.PUT_LINE('Error Backtrace: ' || v_error_backtrace);
 17    END IF;
 18  END;
 19  /
Error Code: -1422
Error Message: ORA-01422: exact fetch returns more than requested number of rows
Error Stack: ORA-01422: exact fetch returns more than requested number of rows


Error Backtrace: ORA-06512: at "SCOTT.EXAMPLE_PROC", line 10



PL/SQL procedure successfully completed.

SQL> 
SQL> set serveroutput on 
SQL> DECLARE
  2    v_empno emp.empno%TYPE := 7789;
  3    v_ename emp.ename%TYPE;
  4    v_error_code NUMBER;
  5    v_error_message VARCHAR2(200);
  6    v_error_stack VARCHAR2(2000);
  7    v_error_backtrace VARCHAR2(2000);
  8  BEGIN
  9    example_proc(v_empno, v_ename, v_error_code, v_error_message, v_error_stack, v_error_backtrace);
 10    IF v_error_code IS NULL THEN
 11      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
 12    ELSE
 13      DBMS_OUTPUT.PUT_LINE('Error Code: ' || v_error_code);
 14      DBMS_OUTPUT.PUT_LINE('Error Message: ' || v_error_message);
 15      DBMS_OUTPUT.PUT_LINE('Error Stack: ' || v_error_stack);
 16      DBMS_OUTPUT.PUT_LINE('Error Backtrace: ' || v_error_backtrace);
 17    END IF;
 18  END;
 19  /
Error Code: 100
Error Message: ORA-01403: no data found
Error Stack: ORA-01403: no data found


Error Backtrace: ORA-06512: at "SCOTT.EXAMPLE_PROC", line 10



PL/SQL procedure successfully completed.
复制

Error Code为这个100是Oracle内部定义好的错误代码。
当您在PL/SQL块中遇到特定的异常时，Oracle会将相应的错误代码分配给SQLCODE变量。在我们的示例中，错误代码100对应于NO_DATA_FOUND异常。

Oracle为各种可能的异常和错误预定义了一组错误代码。这些错误代码及其对应的描述可以在Oracle文档中找到。这些预定义的错误代码有助于识别问题并找到可能的解决方案。

在我们的示例中，当SELECT INTO语句找不到与指定条件匹配的任何数据时，会触发NO_DATA_FOUND异常。此时，Oracle会将错误代码100分配给SQLCODE变量，并将相应的错误消息分配给SQLERRM函数。

类似地，对于其他异常，例如TOO_MANY_ROWS，Oracle会分配不同的错误代码（例如，-1422）。这些错误代码可以帮助您了解异常的具体原因，从而更好地处理异常情况。

参考：

https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/SQLERRM-function.html
https://sqlines.com/oracle-to-postgresql/sqlerrm