异常处理示例

常规异常处理

可以使用在语句开头声明的异常处理程序来处理一般异常，该异常处理程序会产生显式或隐式信号异常。

CREATE TABLE MYTAB (I INTEGER PRIMARYKEY); 
CREATE PROCEDURE MYPROC AS BEGIN    
	DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);     
	INSERT INTO MYTAB VALUES (1);  -- expected unique violation error: 301     
	-- will not be reached 
END; 

CALL MYPROC;

错误代码异常处理

可以声明使用特定错误代码编号捕获异常的异常处理程序。

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301 SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);
    INSERT INTO MYTAB VALUES (1);  -- expected unique violation error: 301
    -- will not be reached
END;

CALL MYPROC;

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE myVar INT;
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299
    	BEGIN
           	SELECT 0 INTO myVar FROM DUMMY;
    	    	SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    	    	SELECT :myVar FROM DUMMY;
    	END;
    SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception
    SELECT 'NeverReached_noContinueOnErrorSemantics' FROM DUMMY;
END;

CALL MYPROC;

条件异常处理

可以使用 CONDITION 变量声明例外。可以选择使用错误代码编号指定 CONDITION。

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;
    DECLARE EXIT HANDLER FOR MYCOND SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);
    INSERT INTO MYTAB VALUES (1);  -- expected unique violation error: 301
    -- will not be reached
END;

CALL MYPROC;

发出异常信号

SIGNAL 语句可用于在过程中显式引发异常。

备注使用的错误代码必须在用户定义的 10000 到 19999 范围内。

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    INSERT INTO MYTAB VALUES (1);
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;

CALL MYPROC;

辞去异常

RESIGNAL 语句在异常处理程序中引发操作语句的异常。如果未指定错误代码，RESIGNAL 将抛出捕获到的异常。

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;
    INSERT INTO MYTAB VALUES (1);
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;

CALL MYPROC;

嵌套块异常

可以为嵌套块声明异常处理程序。

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 1';
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 2';
        INSERT INTO MYTAB VALUES (1);
        BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 3';
            INSERT INTO MYTAB VALUES (1);  -- expected unique violation error: 301
            -- will not be reached
        END;
    END;
END;

CALL MYPROC;