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