SAP HANA异常处理

最新推荐文章于 2024-11-30 11:05:00 发布

weixin_34206899

最新推荐文章于 2024-11-30 11:05:00 发布

阅读量735

点赞数

原文链接：https://yq.aliyun.com/articles/288040

版权

本文通过创建两个存储过程MYPROC和MYPROC2，演示了如何在SQL中使用DECLARE EXITHANDLER FOR来捕获并处理不同类型的错误，如除数为零错误和唯一索引冲突等。同时展示了如何在发生错误时回滚事务，并记录错误信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

drop TABLE MYTAB;
drop TABLE MYTAB2;
drop PROCEDURE MYPROC;
drop PROCEDURE MYPROC2;

CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);
CREATE TABLE MYTAB2 (I INTEGER, log varchar(100));

CREATE PROCEDURE MYPROC AS
BEGIN
DECLARE EXIT HANDLER FOR sql_error_code 301
select 201 a, '唯一索引冲突' b from dummy;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT ::SQL_ERROR_CODE a, substr(::SQL_ERROR_MESSAGE, 1, 30) b FROM DUMMY;

INSERT INTO MYTAB VALUES (1);
INSERT INTO MYTAB VALUES (1/0);
END;

CALL MYPROC;

CREATE PROCEDURE MYPROC2 AS
BEGIN
DECLARE EXIT HANDLER FOR sql_error_code 301
begin
execute immediate 'rollback';
insert into mytab2 values (301, 'error 301');
execute immediate 'commit';

--下面sql可以省略，为方便查看异常添加该步骤
select 301 a, '唯一索引冲突' b from dummy;
end;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
begin
execute immediate 'rollback';
insert into mytab2 values (::SQL_ERROR_CODE, substr(::SQL_ERROR_MESSAGE, 1, 30));
execute immediate 'commit';

--下面sql可以省略，为方便查看异常添加该步骤
SELECT ::SQL_ERROR_CODE a, substr(::SQL_ERROR_MESSAGE, 1, 30) b FROM DUMMY;
end;

insert into mytab2 values (1, 'ok');
execute immediate 'commit';

INSERT INTO MYTAB VALUES (1);
insert into mytab2 values (2, '1 inserted');
execute immediate 'commit';

INSERT INTO MYTAB VALUES (1/0);
insert into mytab2 values (2, 'devide 0');
execute immediate 'commit';
END;

CALL MYPROC2;

select * from mytab2;