# 第一个用 LOOP方式进行循环的游标测试代码
DROP PROCEDURE IF EXISTS cursor_test;
CREATE PROCEDURE cursor_test()
BEGIN
DECLARE a INT;
DECLARE v_done TINYINT DEFAULT FALSE;
-- 声明游标,并表示游标从t1表中id字段获取数据
DECLARE cur CURSOR FOR SELECT ID FROM t;
-- 在此处后不能定义任何变量,否则sql会报错
-- 捕捉查询不到的异常
DECLARE CONTINUE handler FOR NOT found SET v_done=TRUE;
DROP TABLE IF EXISTS t;
CREATE TABLE t (ID INT,NAME VARCHAR(20));
INSERT INTO t (ID) VALUES (1),(2),(3),(4),(5);
OPEN cur;
read_loop:LOOP
FETCH cur INTO a;
IF v_done THEN
LEAVE read_loop;
ELSE
UPDATE t SET t.NAME=CONCAT('aa',a) WHERE t.ID = a;
END IF;
END LOOP;
END;
# 第二个用wile方式循环的游标测试代码
CREATE PROCEDURE myProException()
BEGIN
DECLARE v_done INT DEFAULT 0;
DECLARE v_index INT ;
-- 声明游标,并表示游标从t1表中id字段获取数据
DECLARE cur CURSOR FOR SELECT id FROM t1;
-- 在此处后不能定义任何变量,否则sql会报错
-- 捕捉查询不到的异常
DECLARE CONTINUE HANDLER FOR NOT found SET v_done = 1;
-- 创建表 12
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int);
INSERT INTO t1 VALUES(1),(2),(3);
OPEN cur;
FETCH cur INTO v_index;
WHILE v_done != 1
DO
SELECT v_index;
FETCH cur INTO v_index;
END WHILE;
CLOSE cur;
END;
#第三个例子,用游标获取一行数据然后存入到新的表中
-- 游标取出多条数据进行更新
DROP PROCEDURE IF EXISTS cursores_test;
CREATE PROCEDURE cursores_test()
BEGIN
-- 定义变量id
DECLARE v_id INT;
-- 定义变量用户名
DECLARE v_name VARCHAR(20);
-- 定义循环的标志
DECLARE v_tag TINYINT DEFAULT FALSE;
-- 定义循环的次数,此次数动态的从t_user表中获取数据长度
DECLARE t_user_size INT;
-- 定义游标
DECLARE v_cursor CURSOR FOR SELECT id,name FROM t_user;
-- 定义错误捕捉
DECLARE CONTINUE HANDLER FOR NOT found SET t_user_size=0;
-- 定义用户表
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(id INT,name VARCHAR(20));
INSERT INTO t_user (id,name) VALUES (5,'张三丰'),(61,'赵虎'),(72,'隔壁老王'),(28,'钱三'),(19,'李斯');
-- 定义需要将用户表的信息转移到临时的表
DROP TABLE IF EXISTS t_user_temp;
CREATE TABLE t_user_temp(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,u_id INT,u_name VARCHAR(20));
-- 给循环的长度赋值
SELECT COUNT(id) INTO t_user_size FROM t_user;
-- 打开游标
OPEN v_cursor; /*接着使用OPEN打开游标*/
-- 把第一行数据写入变量中,游标也随之指向了记录的第一行
FETCH v_cursor INTO v_id, v_name;
WHILE(t_user_size>0)
DO
-- 将游标读到的数据插入到新表t_user_temp
insert INTO t_user_temp (u_id,u_name) values (v_id,v_name);
-- 设置长度-1
SET t_user_size = t_user_size-1;
-- 获取游标的下一条指引的数据
FETCH v_cursor INTO v_id,v_name;
END WHILE;
-- 关闭游标
CLOSE v_cursor;
END;
- 注意事项:
-
- 在游标定义后,如果再定义属性则SQL报错。譬如:在DECLARE cur CURSOR FOR SELECT id FROM t1 后定义 DECLARE v_i INT ; 则SQL通不过
- 游标必须在定义处理程序之前被定义,但变量必须在定义游标之前被定义,顺序就是变量定义-游标定义-处理程序。