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; -- 存放异码
--声明出错处理
--如果出现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;
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;