mysql中collect_set函数 mysql declare cursor

游标的概念

游标 cursor
1.cursor 游标 (不同于select的一次性全部取出)
比如一条where语句，对应了N条结果，而对应N条结果集组成的资源，取出资源的接口/句柄，就是游标
我们沿着游标，可以一次只取一行

2.基本知识

#declare 声明游标  	
 declare 游标名 cursor for select_statement
	
	#open 打开游标资源
	open 游标名
 
			#fetch 取值							
				fetch 游标名 into var1, var2[...]
 
#close 关闭
close 游标名

3.使用游标每次只能取出一行数据

4.游标越界标志

游标取值越界时候，利用标识来结束
在MySQL cursor中，可以declare continue handler 来操作1个越界标识
格式：declare continue handler for not found statement;  
格式：declare exit handler for not found statement;  

continue 会在游标最后一次执行没有获取到数据--触发handler；游标后面的statement会执行一次
exit 会在游标最后一次执行没有获取到数据-触发handler；游标后面的statement执行会立刻退出执行

处理continue,exit外，还有一种undo handler;
Continue 触发后,后面的语句会继续执行
Exit 触发后，后面的语句不再执行
Undo 是触发后，前面的语句撤销（目前MySQL不支持undo）

创建数据表

CREATE TABLE `goods` (
  `gid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

#自行插入数据

demo1【一次获取一条数据】:

#创建存储过程
create procedure pcursor()
BEGIN
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
		
	#声明游标
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods;
	
	#打开游标
	open getgoodsrow;
	
			#取值
			fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;

			#查询变量
			select row_gid,row_goods_name,row_goods_num;
	
	#关闭游标
	close getgoodsrow;	

end;

#执行游标
call pcursor()

执行结果（截图）：

demo2【一次获取多条数据（手动）】:

#创建存储过程
create procedure pcursor_much()
BEGIN
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
		
	#声明游标
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods;
	
	#打开游标
	open getgoodsrow;
	
			#1.取值
			fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
			#查询变量
			select row_gid,row_goods_name,row_goods_num;
	

			#2.取值
			fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
			#查询变量
			select row_gid,row_goods_name,row_goods_num;

			#3.取值
			fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
			#查询变量
			select row_gid,row_goods_name,row_goods_num;
			

	#关闭游标
	close getgoodsrow;	

end;

#执行游标
call pcursor_much()

执行结果（截图）：

demo3【一次获取多条数据（循环）】:

#创建存储过程
create procedure pcursor_repeat()
BEGIN
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
	declare sun int default 0;
	declare i int default 0;
		
	#声明游标
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods;
								   #注意此句sql只能在声明游标之后	
								   select count(*) into sun from goods;
	
	#打开游标
	open getgoodsrow;
			repeat
	
					#1.取值 【注意：fetch取值是可以重复的多次去取值的】
					fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
					#查询变量
					select row_gid,row_goods_name,row_goods_num;
					set i=i+1;
				until i>=sun
			end repeat;	

	#关闭游标
	close getgoodsrow;	

end;

#执行游标
call pcursor_repeat()

执行结果（截图）：

demo4【使用越界标识】:

#创建存储过程【此存储过程是验证越界标识的问题】
create procedure pcursor_error()
begin
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
	declare you int default 1;
		
	#声明游标
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods;
	
	#定义越界标识符
	declare continue handler for not found set you=0;
	
	#打开游标
	open getgoodsrow;
			repeat
	
					#1.取值
					fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
					#查询变量
					select row_gid,row_goods_name,row_goods_num;
				
				until you=0
			end repeat;	

	#关闭游标
	close getgoodsrow;	

end;

#执行存储过程
call pcursor_error()

执行结果（截图）：

【注意到4,5获取出来的数据都是一样的，游标多执行了一次获取最后数据】

以下是纠正的存储过程越界标识

#创建存储过程
create procedure pcursor_two()
begin
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
	declare you int default 1;
		
	#声明游标
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods;
	
	#定义越界标识符
	declare exit handler for not found set you=0;
	
	#打开游标
	open getgoodsrow;
			repeat
	
					#1.取值
					fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
					#查询变量
					select row_gid,row_goods_name,row_goods_num;
				
				until you=0
			end repeat;	

	#关闭游标
	close getgoodsrow;	

end;

#执行存储过程
call pcursor_two()

以上的触发器逻辑是不够严谨的，----毕竟如果一条数据都没有那游标也是取不到值
正确的逻辑—应该是先手动游标取值，然后先执行sql,是否有值，再去取游标的值

#创建存储过程
create procedure pcursor_three()
begin
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
	declare you int default 1;
		
	#声明游标[goods数据表只有4数据 where gid>6 模式大于6的没有数据情况下]
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods where gid>6;
	
	#定义越界标识符
	declare continue handler for not found set you=0;
	
	#打开游标
	open getgoodsrow;
			#应该先手动游标取值
			fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;

			repeat
		
					#查询变量
					select row_gid,row_goods_name,row_goods_num;
					#取值
					fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;
				
				until you=0
			end repeat;	

	#关闭游标
	close getgoodsrow;	

end;

#说明：以上的触发器逻辑是不够严谨的，----毕竟如果一条数据都没有那游标也是取不到值

#执行存储过程
call pcursor_three()

执行结果（截图）：

【注意：为什么一条数据都没有的时候—repeat还会取出空，原因是repeat条件无论是否成立会先执行一次】

》》正确的存储过程【使用while】

#创建存储过程
create procedure pcursor_true()
begin
	declare row_gid int;
	declare row_goods_num int;
	declare row_goods_name varchar(30);
	declare you int default 1;
		
	#声明游标[goods数据表只有4数据 where gid>6 模式大于6的没有数据情况下]
	declare getgoodsrow cursor for select gid,goods_name,goods_num from goods where gid>6;
	
	#定义越界标识符
	declare continue handler for not found set you=0;
	
	#打开游标
	open getgoodsrow;
			#应该先手动游标取值
			fetch getgoodsrow into row_gid,row_goods_name,row_goods_num;

			while you=1 do
					#查询变量
					select row_gid,row_goods_name,row_goods_num;
					#取值
					fetch getgoodsrow into row_gid,row_goods_name,row_goods_num; 
			end while;	

	#关闭游标
	close getgoodsrow;	

end;


#执行存储过程
call pcursor_true()

执行结果（截图）:

【为什么一条数据都没有的时候—repeat还会取出空-------而while不会
原因：while是先判断是条件是否为真】