DB2 SQLSTATE=42601

最新推荐文章于 2024-04-22 23:35:42 发布

原创最新推荐文章于 2024-04-22 23:35:42 发布 · 2.4w 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#db2 #integer #sql #null #input

本文介绍了一个具体的SQL存储过程案例，该案例展示了如何通过合理的错误处理机制来改善存储过程的健壮性和可用性。文中详细解释了如何设置错误处理、使用游标遍历数据以及如何在遇到异常情况时进行恰当的响应。

部署运行你感兴趣的模型镜像

CREATE PROCEDURE DOLOWN.BUSINESS_TOTALREPORT ()
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

BEGIN

DECLARE SQLCODE      INTEGER DEFAULT 0; -- 异常代码
DECLARE R_CODE       INTEGER DEFAULT 0; -- 存放异常码                                                                                    DECLARE SQLCMD       VARCHAR(6000);          --动态SQL
DECLARE CARS         VARCHAR(500);                                                                                                                             DECLARE V_SERIES_ID INTEGER;
DECLARE V_DEALER_ID                 INTEGER;
DECLARE V_DEALERSHORT_NAME          VARCHA(150);                                                                                          DECLARE COMPANYID     INTEGER;
DECLARE CURSORSERIES CURSOR WITH HOLD FOR     -- 在游标中使用COMMIT 时声明 WITH HOLD
          SELECT SERIES_ID FROM TM_SERIES WHERE COMPANY_ID = COMPANYID;

SET COMPANYID = 10000002; SET CARS = '';
SET SQLCMD = '';

--声明出错处理
--如果出现SQL异常将结果记录并继续执行后面结果
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       SET R_CODE=2;
    END;
    --当SELECT 语句没有找到任何记录时回滚刚才操作
    DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
        SET R_CODE=1;
END;
OPEN CURSORSERIES;

   FETCH CURSORSERIES INTO V_SERIES_ID;

   WHILE R_CODE = 0 DO                                                                                                                                               SET CARS = CARS||CHAR(V_SERIES_ID)||',';
  FETCH CURSORSERIES INTO V_SERIES_ID;
   END WHILE;

IF CARS IS NOT NULL AND R_CODE = 1 THEN
    SET R_CODE = 0;
END IF;
CLOSE CURSORSERIES;

END;

执行时报错 SQLSTATE=42601

把出错处理那部分放到另一块中顺利执行

CREATE PROCEDURE DOLOWN.BUSINESS_TOTALREPORT ()
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

P1: BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0; -- 异常代码
DECLARE R_CODE INTEGER DEFAULT 0; -- 存放异码

P2: BEGIN

DECLARE SQLCMD       VARCHAR(6000);          --动态SQL
DECLARE CARS         VARCHAR(500);                                                                                                                             DECLARE V_SERIES_ID INTEGER;
DECLARE V_DEALER_ID                 INTEGER;
DECLARE V_DEALERSHORT_NAME          VARCHA(150);                                                                                          DECLARE COMPANYID     INTEGER;
DECLARE CURSORSERIES CURSOR WITH HOLD FOR     -- 在游标中使用COMMIT 时声明 WITH HOLD
          SELECT SERIES_ID FROM TM_SERIES WHERE COMPANY_ID = COMPANYID;

SET COMPANYID = 10000002; SET CARS = '';
SET SQLCMD = '';

OPEN CURSORSERIES;

   FETCH CURSORSERIES INTO V_SERIES_ID;

   WHILE R_CODE = 0 DO                                                                                                                                               SET CARS = CARS||CHAR(V_SERIES_ID)||',';
  FETCH CURSORSERIES INTO V_SERIES_ID;
   END WHILE;

IF CARS IS NOT NULL AND R_CODE = 1 THEN
    SET R_CODE = 0;
END IF;
CLOSE CURSORSERIES;

END P2;

END P1;

您可能感兴趣的与本文相关的镜像

LobeChat

AI应用

LobeChat 是一个开源、高性能的聊天机器人框架。支持语音合成、多模态和可扩展插件系统。支持一键式免费部署私人ChatGPT/LLM 网络应用程序。

DB2 SQLSTATE=42601

1 条评论