简单示例: CREATE PROCEDURE curdemo(IN V_GROUP INT) -> BEGIN -> DECLARE done INT DEFAULT 0; -> DECLARE a INT; -> DECLARE b CHAR(16); -> -> DECLARE cur1 CURSOR FOR SELECT id,name FROM student where classid=V_GROUP; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> OPEN cur1; -> -> REPEAT -> FETCH cur1 INTO a, b; -> select a,b; -> UNTIL done END REPEAT; -> -> CLOSE cur1; -> END
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
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 这句话是什么意思,还有。。我取的10条数据最后有11条,第11条和第10条是重复的
这个 done 需要用于下面的 UNTIL done END REPEAT; 判断。你的程序有问题。把 repeat 换成 while 以避免类似的问题。while 的用法,你可以先看一下手册中的例子。
while的时候怎么判断,有一种写法是先去第一条的数据判断不等于null就进循环体,但是到最后一次就一直执行了死循环。。晕 delimiter // drop procedure if exists curdemo;// CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a INT; DECLARE b varchar(50);
DECLARE cur1 CURSOR FOR SELECT id,optype FROM torderrelation LIMIT 0,10 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
FETCH cur1 INTO a, b; while ( a is not null ) do if b=0 then insert into table11 values(a,b); else insert into table22 values(a,b); end if; fetch cur1 into a,b; END while;
CREATE PROCEDURE curdemo(IN V_GROUP INT)
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE a INT;
-> DECLARE b CHAR(16);
->
-> DECLARE cur1 CURSOR FOR SELECT id,name FROM student where classid=V_GROUP;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
->
-> REPEAT
-> FETCH cur1 INTO a, b;
-> select a,b;
-> UNTIL done END REPEAT;
->
-> CLOSE cur1;
-> END
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
这句话是什么意思,还有。。我取的10条数据最后有11条,第11条和第10条是重复的
delimiter //
drop procedure if exists curdemo;//
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a INT;
DECLARE b varchar(50);
DECLARE cur1 CURSOR FOR SELECT id,optype FROM torderrelation LIMIT 0,10 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
FETCH cur1 INTO a, b;
while ( a is not null ) do
if b=0 then insert into table11 values(a,b);
else
insert into table22 values(a,b);
end if;
fetch cur1 into a,b;
END while;
CLOSE cur1;
END