示例: DELIMITER $$ CREATE PROCEDURE curdemo() BEGIN DECLARE done1,done2 INT DEFAULT 0; DECLARE name1,name2 VARCHAR(20); DECLARE id1,id2 INT; DECLARE cur1 CURSOR FOR SELECT id,NAME FROM test1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; OPEN cur1; REPEAT FETCH cur1 INTO id1, name1; IF NOT done1 THEN INSERT INTO test3(NAME) VALUES(name1); BEGIN DECLARE cur2 CURSOR FOR SELECT id,NAME FROM test2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1; OPEN cur2; REPEAT FETCH cur2 INTO id2,name2; IF NOT done2 THEN INSERT INTO test3(NAME) VALUES(name2); END IF; UNTIL done2 END REPEAT; CLOSE cur2; SET done2=0; END; END IF; UNTIL done1 END REPEAT; CLOSE cur1; COMMIT; END$$ DELIMITER ;
declare 要放在前面 你可以将select放到declare的后面再执行一下
DELIMITER $$
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done1,done2 INT DEFAULT 0;
DECLARE name1,name2 VARCHAR(20);
DECLARE id1,id2 INT; DECLARE cur1 CURSOR FOR SELECT id,NAME FROM test1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; OPEN cur1; REPEAT
FETCH cur1 INTO id1, name1;
IF NOT done1 THEN
INSERT INTO test3(NAME) VALUES(name1);
BEGIN
DECLARE cur2 CURSOR FOR SELECT id,NAME FROM test2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
OPEN cur2;
REPEAT
FETCH cur2 INTO id2,name2;
IF NOT done2 THEN
INSERT INTO test3(NAME) VALUES(name2);
END IF;
UNTIL done2 END REPEAT;
CLOSE cur2;
SET done2=0;
END;
END IF;
UNTIL done1 END REPEAT;
CLOSE cur1; COMMIT;
END$$
DELIMITER ;
DECLARE _doneUq BOOL DEFAULT FALSE;
DECLARE csUq CURSOR FOR
SELECT userId FROM UserQuery;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET _doneUq=TRUE;
OPEN csUq;
_csUq:LOOP
FETCH csUq INTO _userId;
IF(_doneUq)THEN
LEAVE _csUq;
END IF;
END LOOP _csUq;
CLOSE csUq;