Skip to Content
Author's profile photo Yuan Fang

SAP HANA SQLScript Procedure中的异常处理

     对于使用SQlScript 的存储过程,SAP HANA提供了相应的异常处理的方法。分别包含几三个相关的工具:EXIT HANDLER, CONDITION, SIGNAL RESIGNAL


     使用EXIT HANLDER的例子如下所示:


CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC1
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
END;


    

     调用这个存储过程时,因为主键的限制,插入同样的内容,会因违反唯一性约束而引发异常。使得这个存储过程因为异常而中止,后续操作不会执行。在存储过程调用中止后,再执行EXIT HANDLER中的操作。

注:可以使用::SQL_ERROR_CODE”,”::SQL_ERROR_MESSAGE”来获取当前异常的SQL ERROR CODE及相应的错误信息。


EXIT HANDLER

     在SQLScript Procedure中声明EXIT HANDLER,相应语法为:


DECLARE EXIT HANDLER FOR <proc_condition_value_list> <proc_stmt>;

<proc_condition_value_list> ::= <condition_value> [ { <comma> <condition_value> }… ]

<condition_value> ::= <sql_error_code> | <condition_name> | SQLEXCEPTION

<sql_error_code> ::= SQL_ERROR_CODE <numeric_literal>

     可以在存储过程中,声明针对特定的SQL ERROR CODE或者用户自定义的条件的EXIT HANDLER。并指明当抛出异常中止后,要执行的语句。

注:

SAP HANA数据库提供常用的SQL error code

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a78d3275191014b41bae7c4a46d835/content.htm

或查看“SAP HANA SQL and System Views Reference”-> SQL Error Codes

CONDITION

     在SQLScript Procedure中可以声明CONDITION,来定义包含一个SAP HANA错误代码或者用户自定义的错误代码的条件,并为其命名。则后续可以声明针对处理这个条件的EXIT HANDLER,或者可以使用SIGNAL RESIGNAL 显示抛出只包含用户自定义错误代码的条件的异常。


     声明CONDITION 的语法为:

DECLARE <condition name> CONDITION [ FOR <sql_error_code> ];

<sql_error_code> ::= SQL_ERROR_CODE <numeric_literal>

SIGNAL/RESIGNAL

     使用SINGAL或者RESINGAL语句来显示地抛出异常。SINGALRESINGAL只能抛出用户自定义的错误类型(即指错误代码 SQL ERROR CODE10000~19999之间)


     SIGNALRESIGNAL的区别,SIGNAL使用时必须指明SQL ERROR CODE或者CONDITION的名字,RESIGANL可以单独在EXIT HANDLERaction语句中使用,即可以使用RESIGNALEXIT HANDLER捕获的异常再抛出给上一级调用者。同SAP HANA的错误代码所对应的异常一样,一个用户自定义的异常可以被它所在的存储过程中相关的exit handler捕获处理,也可以被上层调用的存储过程所捕获处理。

     使用SINGALRESIGNAL的语法为:


SIGNAL <signal_value> [<set_signal_information>] ;

RESIGNAL [<signal_value>] [<set_signal_information>];

<signal_value> ::= <condition_name> | <sql _error_code>

<set_signal_information> ::= SET <signal_information_item>

<signal_information_item> ::= MESSAGE_TEXT  <equals operator> <simple_value_specification>

  <simple_value_specification> ::= <procedure_expression>


使用举例


1.普通异常处理



CREATETABLE 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;


/wp-content/uploads/2014/04/1_422703.png

2.指定特定SQL ERROR CODE的异常处理


CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
CREATE PROCEDURE MYPROC
AS BEGIN
    DECLARE myVar INT;
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
    SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception
END;
CALL MYPROC;


/wp-content/uploads/2014/04/2_422704.png

SQL ERROR CODE 1299对应的错误为”No data found”.

/wp-content/uploads/2014/04/3_422711.png

3.指定特定CONDITION的异常处理



CREATETABLE MYTAB (I INTEGERPRIMARYKEY);
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;


/wp-content/uploads/2014/04/4_422712.png

4.使用SIGNAL抛出异常



CREATEPROCEDURE 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;
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;
CALL MYPROC;


/wp-content/uploads/2014/04/5_422713.png

 

5.使用RESIGNAL 抛出异常



CREATEPROCEDURE MYPROC
AS BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;
CALL MYPROC;


/wp-content/uploads/2014/04/6_422714.png

6.对于EXIT HANDLER的使用,可用使用先关表格来保存异常信息。


CREATE TABLE ERROR_LOG

(ERROR_CODE VARCHAR(5),

ERROR_MSG VARCHAR(200),

TIME_STAMP TIMESTAMP);


CREATEPROCEDURE MYPROC
AS BEGIN
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE EXIT HANDLER FOR MYCOND signal mycond set message_text = 'error';
 SIGNAL MYCOND;
-- will not be reached
END;
CREATE PROCEDURE MYPROC1
AS BEGIN
DECLARE EXIT HANDLER FOR sqlexception
INSERT INTO ERROR_LOG VALUES(::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE,CURRENT_TIMESTAMP);
CALL MYPROC;
END;   
CALL MYPROC1;


      查询ERROR_LOG表中的信息:

/wp-content/uploads/2014/04/7_422715.png

想获取更多SAP HANA学习资料或有任何疑问,请关注新浪微博@HANAGeek!我们欢迎你的加入!

转载本文章请注明作者和出处<http://scn.sap.com/community/chinese/hana/blog/2014/04/01/sap-hana-sqlscript-procedure%E4%B8%AD%E7%9A%84%E5%BC%82%E5%B8%B8%E5%A4%84%E7%90%86>,请勿用于任何商业用途。

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo zongjie zhang
      zongjie zhang

      你好,我有一个需求,就是LOOP 里面循环select 一个表变量:var_sub,这样,但是其中一个可能会没有数据,然后就会有异常,我如果使用EXIT HANDLE的话,不会继续往下执行的,是吧?我这样有什么好的解决办法吗?

      Author's profile photo Yuan Fang
      Yuan Fang
      Blog Post Author

      你好,可以在循环的开始处declare一个exit handler,它的作用范围就是每次循环。如果有异常的话,可以被这个exit handler捕获,然后再进入下一次循环。

      Author's profile photo zongjie zhang
      zongjie zhang

      好的,我试试,谢谢!

      Author's profile photo Minjie Lao
      Minjie Lao

      hi 以下是来自这片文章,英文版的一个comment, 意思就是说,如果用和不用 exception handler, rolleback 的结果不一样吗? 还是可以避免这样的问题?

       

      Hi,

      I have noticed a strange behavior with ROLLBACK when using exit handler and was wondering if you have faced the same.

      It looks like EXIT HANDLER does an implicit commit when applied in a stored procedure that has multiple DML operations. This makes exit handler a problem as it can implicitly commit despite the fact that the stored proc hits an exception.

      Here is an example:

      Without exit handler:

      CREATE TABLE MYZTAB0 (I INTEGER PRIMARY KEY);

      CREATE PROCEDURE MYZPROC0 AS BEGIN

      INSERT INTO MYZTAB0 VALUES (1);

      INSERT INTO MYZTAB0 VALUES (1); — expected unique violation error: 301

      END;

      CALL MYZPROC0;

      Could not execute ‘CALL MYZPROC0’ in 84 ms 865 µs .

      [301]: unique constraint violated:  [301] “E0228574”.”MYZPROC0″: line 4 col 2 (at pos 70): [301] (range 3) unique constraint violated exception: unique constraint violated: Table(MYZTAB0), Index(_SYS_TREE_RS_#52275459_#0_#P0)

      Neither the first, nor the second insert got committed. This is exactly the desired behavior.

      With exit handler:

      CREATE PROCEDURE MYZPROC AS BEGIN

      DECLARE EXIT HANDLER FOR SQLEXCEPTION

      SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

      INSERT INTO MYZTAB VALUES (1);

      INSERT INTO MYZTAB VALUES (1); — expected unique violation error: 301

      END;

      CALL MYZPROC;

      The output of the call statement shows error code 301 as well as error text “unique…”

      However a SELECT on MYZTAB tables shows that a record has been inserted.

      Why? The rollback should apply to all DML operations of the stored procedure not just the second insert.