mysql中有游标吗_mysql中的游标

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();