mysql中的游标

最新推荐文章于 2024-06-21 11:56:49 发布

程宇寒

最新推荐文章于 2024-06-21 11:56:49 发布

阅读量1.4k

点赞数 1

文章标签： mysql 数据库游标

本文链接：https://blog.csdn.net/czh500/article/details/97036776

版权

mysql中的游标

#mysql中的游标

/*
CURSOR游标：简单通俗的说，游标就是游动的标识/标志
1条sql，对应N条结果集的资源，取出资源的接口/句柄，就是游标，
沿着游标，可以一次取出1行

什么是游标，说的简单直白点，游标的作用就是用来取多条数据，遍
历数据(一句话概括，其实就是处理多行数据)有点像java集合中的迭代器一样

/*
定义游标的语法

DECLARE 声明 DECLARE 游标名 CURSOR FOR SELECT查询语句;
OPEN 打开 OPEN 游标名;
FETCH 取值 FETCH 游标名 INTO 变量1,变量2,变量3;
CLOSE 关闭 CLOSE 游标名;

#
SELECT * FROM goods;

#使用游标
DELIMITER $
CREATE PROCEDURE p14()
BEGIN
DECLARE temp_gid INT;
DECLARE temp_gname VARCHAR(100);
DECLARE temp_num INT;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
OPEN getGoods;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
CLOSE getGoods;
END$

#
CALL p14();

#使用游标
DELIMITER $
CREATE PROCEDURE p15()
BEGIN
DECLARE temp_gid INT;
DECLARE temp_gname VARCHAR(100);
DECLARE temp_num INT;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
OPEN getGoods;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
CLOSE getGoods;
END$

#DROP PROCEDURE p15;
#
CALL p15();

#游标循环,使用WHILE
DELIMITER $
CREATE PROCEDURE p16()
BEGIN
DECLARE temp_gid INT;
DECLARE temp_gname VARCHAR(100);
DECLARE temp_num INT;

DECLARE rows INT DEFAULT 0;
DECLARE myIndex INT DEFAULT 0;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
SELECT COUNT(*) INTO rows FROM goods;
OPEN getGoods;

WHILE myIndex < rows DO

SET myIndex := myIndex + 1;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;

END WHILE;
CLOSE getGoods;
END$

#
CALL p16();

#游标循环，使用REPEAT
DELIMITER $
CREATE PROCEDURE p162()
BEGIN
DECLARE temp_gid INT;
DECLARE temp_gname VARCHAR(100);
DECLARE temp_num INT;

DECLARE rows INT DEFAULT 0;
DECLARE myIndex INT DEFAULT 0;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
SELECT COUNT(*) INTO rows FROM goods;
OPEN getGoods;

REPEAT

SET myIndex := myIndex + 1;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;

UNTIL myIndex >= rows END REPEAT;
CLOSE getGoods;
END$
#

CALL p162();

/*
游标取值越界时，利用标识来结束
在mysql CURSOR中，可以使用DECLARE CONTINUE HANDLER来操作1个越界标识
DECLARE CONTINUE HANDLER FOR NOT FOUND 语句;
*/

DELIMITER $
#使用CONTINUE HANDLER会有一个问题，就是会多出一行记录
CREATE PROCEDURE p17()
BEGIN
DECLARE temp_gid INT DEFAULT 666;
DECLARE temp_gname VARCHAR(100) DEFAULT '空空如也';
DECLARE temp_num INT DEFAULT 888;

DECLARE flag INT DEFAULT 1;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag := 0;
OPEN getGoods;

REPEAT
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
UNTIL flag = 0 END REPEAT;
CLOSE getGoods;
END$

#
CALL p17();

#如果goods表中没有任何数据，使用游标会怎么样?
TRUNCATE goods;
CALL p16();
CALL p162();
CALL p17();

#插入数据
INSERT INTO goods VALUES(1, 'ipad', 600);
INSERT INTO goods VALUES(2, '笔记本电脑', 500);
INSERT INTO goods VALUES(3, '牙膏', 200);
INSERT INTO goods VALUES(4, '六神花露水', 800);

CALL p16();
CALL p162();
CALL p17();

#
#使用CONTINUE HANDLER会有一个问题，就是会多出一行记录，如何解决这个问题？
#EXIT与CONTINUE的区别是，exit触发后，后面的语句不再执行
DELIMITER $
CREATE PROCEDURE p18()
BEGIN
DECLARE temp_gid INT DEFAULT 666;
DECLARE temp_gname VARCHAR(100) DEFAULT '空空如也';
DECLARE temp_num INT DEFAULT 888;

DECLARE flag INT DEFAULT 1;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
#把CONTINUE改成EXIT即可解决多出一行记录的问题
DECLARE EXIT HANDLER FOR NOT FOUND SET flag := 0;
OPEN getGoods;

REPEAT
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
SELECT temp_gid,temp_gname,temp_num;
UNTIL flag = 0 END REPEAT;
CLOSE getGoods;
END$

#
CALL p18();

/*
除CONTINUE和EXIT外，还有一种UNDO HANDLER
CONTINUE是触发后，后面的语句继续执行
EXIT是触发后，后面的语句不再执行
UNDO触发后，前面的语句撤销(但是，目前mysql还不支持UNDO)

#
SELECT '江西省赣州市于都县' FROM DUAL;
SELECT '江西省赣州市于都县' FROM DUAL WHERE 0;
SELECT '江西省赣州市于都县' FROM DUAL WHERE 1;
SELECT '江西省赣州市于都县' FROM DUAL WHERE 666 = 888;

#
#使用CONTINUE HANDLER会有一个问题，就是会多出一行记录，如何解决这个问题？
#EXIT与CONTINUE的区别是，exit触发后，后面的语句不再执行
#这里仍然使用CONTINUE也可以解决多出一行记录的问题
DELIMITER $
CREATE PROCEDURE p19()
BEGIN
DECLARE temp_gid INT DEFAULT 666;
DECLARE temp_gname VARCHAR(100) DEFAULT '空空如也';
DECLARE temp_num INT DEFAULT 888;

DECLARE flag INT DEFAULT 1;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
#这里仍然使用CONTINUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag := 0;

OPEN getGoods; #打开游标
#先FETCH一下
FETCH getGoods INTO temp_gid,temp_gname,temp_num;

REPEAT
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
UNTIL flag = 0 END REPEAT;
CLOSE getGoods;
END$

#
CALL p19();

#
DELIMITER $
CREATE PROCEDURE p20()
BEGIN
DECLARE temp_gid INT DEFAULT 666;
DECLARE temp_gname VARCHAR(100) DEFAULT '空空如也';
DECLARE temp_num INT DEFAULT 888;

DECLARE flag INT DEFAULT 1;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods;
#这里仍然使用CONTINUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag := 0;

OPEN getGoods; #打开游标
#先FETCH一下
FETCH getGoods INTO temp_gid,temp_gname,temp_num;

WHILE flag = 1 DO
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
END WHILE;
CLOSE getGoods;
END$
#
CALL p20();

#
DELIMITER $
CREATE PROCEDURE p21()
BEGIN
DECLARE temp_gid INT DEFAULT 666;
DECLARE temp_gname VARCHAR(100) DEFAULT '空空如也';
DECLARE temp_num INT DEFAULT 888;

DECLARE flag INT DEFAULT 1;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods WHERE 666 = 888;
#这里仍然使用CONTINUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag := 0;

OPEN getGoods; #打开游标
#先FETCH一下
FETCH getGoods INTO temp_gid,temp_gname,temp_num;

REPEAT
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
UNTIL flag = 0 END REPEAT;
CLOSE getGoods;
END$

#
CALL p21();

#
DELIMITER $
CREATE PROCEDURE p22()
BEGIN
DECLARE temp_gid INT DEFAULT 666;
DECLARE temp_gname VARCHAR(100) DEFAULT '空空如也';
DECLARE temp_num INT DEFAULT 888;

DECLARE flag INT DEFAULT 1;
DECLARE getGoods CURSOR FOR SELECT gid, gname, num FROM goods WHERE 28 > 89;
#这里仍然使用CONTINUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag := 0;

OPEN getGoods; #打开游标
#先FETCH一下
FETCH getGoods INTO temp_gid,temp_gname,temp_num;

WHILE flag = 1 DO
SELECT temp_gid,temp_gname,temp_num;
FETCH getGoods INTO temp_gid,temp_gname,temp_num;
END WHILE;
CLOSE getGoods;
END$
#
CALL p22();
#
CALL p21();
CALL p22();