请问存储过程关闭游标后面的sql语句是滞还会继续执行? 见代码:DECLARE f1 as varchar(10);
DECLARE cur1 CURSOR FOR select f1 from t1;
OPEN cur1;
FETCH cur1 INTO f1;
WHILE (f1 is not null ) DO select f1; FETCH cur1 INTO s_DlrCode;
END WHILE;
CLOSE cur1;update table2 set v1=1 where a=1;
我的问题是,当 close cur1 时,后面的 update 语句不会被执行了,请问这有什么问题吗?
DECLARE cur1 CURSOR FOR select f1 from t1;
OPEN cur1;
FETCH cur1 INTO f1;
WHILE (f1 is not null ) DO select f1; FETCH cur1 INTO s_DlrCode;
END WHILE;
CLOSE cur1;update table2 set v1=1 where a=1;
我的问题是,当 close cur1 时,后面的 update 语句不会被执行了,请问这有什么问题吗?
你的代码应该是出错了。FETCH cur1 INTO s_DlrCode; 当最后一条记录后,应该是错误。
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare s_DlrCode varchar(10);
DECLARE cur1 CURSOR FOR select Distinct DlrCode from dms_tmp_tbl_dmsusage_dlr;
OPEN cur1;
FETCH cur1 INTO s_DlrCode;
WHILE (s_DlrCode is not null ) DO
select s_DlrCode ;
FETCH cur1 INTO s_DlrCode;
END WHILE;
CLOSE cur1; select 'ggggggggggggggggggggggaoming';END;这里的 select 'ggggggggggggggggggggggaoming'; 没有被执行。
+-----------+
| s_DlrCode |
+-----------+
| a1 |
+-----------+
1 row in set (0.00 sec)+-----------+
| s_DlrCode |
+-----------+
| a2 |
+-----------+
1 row in set (0.00 sec)ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
mysql>
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
CLOSE cur2;
END