MySQL中的游标写法以及详细注释
DROP PROCEDURE IF EXISTS p;
DELIMITER ??
CREATE PROCEDURE p()
BEGIN
DECLARE sid INT DEFAULT 0;
DECLARE sname VARCHAR(100) DEFAULT '';
DECLARE done DEFAULT 0;
DECLARE c CURSOR FOR SELECT sid,sname FROM t_stu;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
FETCH c INTO sid,sname;
WHILE done <> 1 DO
INSERT INTO t_c (id,name) VALUES (sid,sname);
END WHILE;
CLOSE c;
END??
DELIMITER ;
CALL p();
DROP PROCEDURE IF EXISTS p;
注意
- BEGIN-END块中,必须先声明VARIABLE,然后再声明CURSOR,最后再声明HANDLER