各位帮忙看下一个存储过程问题出在哪
过程如下:
CREATE PROCEDURE AAA (IN V_SBTYPE VARCHAR(30))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE ZXDZS VARCHAR(400);
DECLARE V_SBSERIALNO INT(20);
DECLARE V_PTBGNTM TIMESTAMP;
DECLARE V_PTENDTM TIMESTAMP;
DECLARE CR_INVKNAME VARCHAR(10);
DECLARE CR_GRADE VARCHAR(10);
DECLARE CR_OPERATWAY VARCHAR(10);
DECLARE CR_ISRNAME VARCHAR(10);
DECLARE CR_PRMT VARCHAR(400);
DECLARE CR_PRMC VARCHAR(100);
DECLARE CR_ISVALID VARCHAR(1);
DECLARE C_PL_DATA CURSOR FOR
SELECT INVKNAME, GRADE, OPERATWAY, ISRNAME, PRMT, PRMC, ISVALID FROM ZRL_PLLIST WHERE ISVALID = '1';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CALL ZSP_SBSERIALNO_UPDT();
IF UPPER(V_SBTYPE) = 'ALL' THEN
SET V_SBSERIALNO=GET_SBSERIALNO();
OPEN C_PL_DATA;
read_loop: LOOP
FETCH C_PL_DATA INTO CR_INVKNAME, CR_GRADE, CR_OPERATWAY, CR_ISRNAME, CR_PRMT, CR_PRMC, CR_ISVALID;
IF done THEN
LEAVE read_loop;
END IF;
SET ZXDZS = concat('SELECT PL',CR_INVKNAME,'VLINK(',V_SBTYPE,',',CR_GRADE,',',CR_OPERATWAY,',',CR_ISRNAME,',',CR_PRMT,',',CR_PRMC,');');
SET @ZXDZS = ZXDZS;
SET @V_PTBGNTM=TIME(NOW());
SET @V_PTENDTM = TIME(NOW());
CALL LOGWRT_SBTRACE_LIST(V_SBSERIALNO, 'ZXD_SBSP_ZSB_BGN', 'ZSB_PL_BGN', 'EXECUTE', CR_OPERATWAY, V_PTBGNTM, V_PTENDTM, ROUND((V_PTENDTM -
V_PTBGNTM) * 24.0 * 60.0 * 60.0, 4));
PREPARE stmt FROM @ZXDZS;
EXECUTE stmt;
END LOOP;
ELSEIF UPPER(V_SBTYPE) = 'ONE' THEN
OPEN C_PL_DATA;
read_loop: LOOP
FETCH C_PL_DATA INTO CR_INVKNAME, CR_GRADE, CR_OPERATWAY, CR_ISRNAME, CR_PRMT, CR_PRMC, CR_ISVALID;
IF done THEN
LEAVE read_loop;
END IF;
SET ZXDZS = concat('SELECT PL',CR_INVKNAME,'VLINK(',V_SBTYPE,',',CR_GRADE,',',CR_OPERATWAY,',',CR_ISRNAME,',',CR_PRMT,',',CR_PRMC,');'); ---会调用函数PLPVLINK(),已经确定函数是没有问题的,用来返回一个插入语句。
SET @ZXDZS = ZXDZS;
SET @V_PTBGNTM=TIME(NOW());
PREPARE stmt FROM @ZXDZS;
EXECUTE stmt;
END LOOP;
END IF;
CLOSE C_PL_DATA;
END
调用的时候报错:
CALL AAA('ALL');
错误如下:
[Err] 1172 - Result consisted of more than one row
猜测是游标的问题,但是写之前我测试过游标的语法。第一次使用mysql游标,
实在不知道问题出在哪,麻烦帮忙看下