Mysql的游标cursor
==
声明游标:declare 游标名 cursor for sql_statement;
打开游标: open 游标名;
取游标值: fetch 游标名 into value1,value2,[...];
关闭游标: close 游标名;
==
1、实例一
delimiter $
create procedure p1()
begin
-- 声明一些要显示的字段信息
declare recp varchar(20) ;
declare posp varchar(20) ;
declare contentp varchar(200) ;
-- 越界标志并赋初始值
declare fetchSeqOk boolean default false;
-- 声明游标
declare first_cursor cursor for select rec,pos,content from message;
-- 声明句柄直到 not found 然后触发赋值语句
declare continue handler for NOT FOUND set fetchSeqOk = true;
-- 打开游标
open first_cursor;
-- 循环读取游标
read_loop: LOOP
if fetchSeqOk then
leave read_loop;
else
fetch first_cursor into recp,posp,contentp;
select recp,posp,contentp ;
end if;
end Loop;
close first_cursor;
end$
【结果】
mysql> select rec,pos ,content from message$
+----------+----------+--------------+
| rec | pos | content |
+----------+----------+--------------+
| admin | zhangsan | This is test |
| zhangsan | admin | I Konw |
+----------+----------+--------------+
2 rows in set (0.00 sec)
【===显示的2条数据===】
mysql> call p1()$
+-------+----------+--------------+
| recp | posp | contentp |
+-------+----------+--------------+
| admin | zhangsan | This is test |
+-------+----------+--------------+
1 row in set (0.00 sec)
+----------+-------+----------+
| recp | posp | contentp |
+----------+-------+----------+
| zhangsan | admin | I Konw |
+----------+-------+----------+
1 row in set (0.04 sec)
+----------+-------+----------+
| recp | posp | contentp |
+----------+-------+----------+
| zhangsan | admin | I Konw |
+----------+-------+----------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.12 sec)
【===显示的3条数据===】
Note:
1、变量的定义不要和你的select的列的键同名!不然fetch into 会失败!
2、这里游标查询的结果集会对结果集的最后一条输出两次,
continue在被not found 触发之后仍会触发一次select语句,
exit在被not found 触发之后后面的语句不再执行
undo在被not found 触发之后前面的语句将被撤销[mysql不支持]
处理方法1:
declare continue handler for NOT FOUND set fetchSeqOk = true;
修改为
declare exit handler for NOT FOUND set fetchSeqOk = true;
显示的结果为[结果_1]
处理方法2:
如果想继续使用continue可以采用
-- 打开游标
open first_cursor;
-- 循环读取游标
-- 为了避免continue对结果集最后一条数据取2次的现象我们可以
-- 1、先取出一条数据显示,在进行循环
fetch fetch first_cursor into recp,posp,contentp;
read_loop: LOOP
if fetchSeqOk then
leave read_loop;
else
select recp,posp,contentp ;
fetch first_cursor into recp,posp,contentp;
end if;
end Loop;
close first_cursor;
【结果_1】
mysql> call p1()$
+-------+----------+--------------+
| recp | posp | contentp |
+-------+----------+--------------+
| admin | zhangsan | This is test |
+-------+----------+--------------+
1 row in set (0.00 sec)
+----------+-------+----------+
| recp | posp | contentp |
+----------+-------+----------+
| zhangsan | admin | I Konw |
+----------+-------+----------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.09 sec)
3、注意:这里有个问题即如果游标的结果集为空,如果使用是continue会继续执行
后面的语句显示情况为[结果1],使用的exit显示的情况为[结果2]
循环读取游标
[结果1]
mysql> call p1()$
+------+------+----------+
| recp | posp | contentp |
+------+------+----------+
| NULL | NULL | NULL |
+------+------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
[结果2]
mysql> call p1()$
Query OK, 0 rows affected (0.00 sec)
4、以上的这些显示结果是在使用Loop的循环的条件下,如果使用while,repeate
请自行研究。
==
声明游标:declare 游标名 cursor for sql_statement;
打开游标: open 游标名;
取游标值: fetch 游标名 into value1,value2,[...];
关闭游标: close 游标名;
==
1、实例一
delimiter $
create procedure p1()
begin
-- 声明一些要显示的字段信息
declare recp varchar(20) ;
declare posp varchar(20) ;
declare contentp varchar(200) ;
-- 越界标志并赋初始值
declare fetchSeqOk boolean default false;
-- 声明游标
declare first_cursor cursor for select rec,pos,content from message;
-- 声明句柄直到 not found 然后触发赋值语句
declare continue handler for NOT FOUND set fetchSeqOk = true;
-- 打开游标
open first_cursor;
-- 循环读取游标
read_loop: LOOP
if fetchSeqOk then
leave read_loop;
else
fetch first_cursor into recp,posp,contentp;
select recp,posp,contentp ;
end if;
end Loop;
close first_cursor;
end$
【结果】
mysql> select rec,pos ,content from message$
+----------+----------+--------------+
| rec | pos | content |
+----------+----------+--------------+
| admin | zhangsan | This is test |
| zhangsan | admin | I Konw |
+----------+----------+--------------+
2 rows in set (0.00 sec)
【===显示的2条数据===】
mysql> call p1()$
+-------+----------+--------------+
| recp | posp | contentp |
+-------+----------+--------------+
| admin | zhangsan | This is test |
+-------+----------+--------------+
1 row in set (0.00 sec)
+----------+-------+----------+
| recp | posp | contentp |
+----------+-------+----------+
| zhangsan | admin | I Konw |
+----------+-------+----------+
1 row in set (0.04 sec)
+----------+-------+----------+
| recp | posp | contentp |
+----------+-------+----------+
| zhangsan | admin | I Konw |
+----------+-------+----------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.12 sec)
【===显示的3条数据===】
Note:
1、变量的定义不要和你的select的列的键同名!不然fetch into 会失败!
2、这里游标查询的结果集会对结果集的最后一条输出两次,
continue在被not found 触发之后仍会触发一次select语句,
exit在被not found 触发之后后面的语句不再执行
undo在被not found 触发之后前面的语句将被撤销[mysql不支持]
处理方法1:
declare continue handler for NOT FOUND set fetchSeqOk = true;
修改为
declare exit handler for NOT FOUND set fetchSeqOk = true;
显示的结果为[结果_1]
处理方法2:
如果想继续使用continue可以采用
-- 打开游标
open first_cursor;
-- 循环读取游标
-- 为了避免continue对结果集最后一条数据取2次的现象我们可以
-- 1、先取出一条数据显示,在进行循环
fetch fetch first_cursor into recp,posp,contentp;
read_loop: LOOP
if fetchSeqOk then
leave read_loop;
else
select recp,posp,contentp ;
fetch first_cursor into recp,posp,contentp;
end if;
end Loop;
close first_cursor;
【结果_1】
mysql> call p1()$
+-------+----------+--------------+
| recp | posp | contentp |
+-------+----------+--------------+
| admin | zhangsan | This is test |
+-------+----------+--------------+
1 row in set (0.00 sec)
+----------+-------+----------+
| recp | posp | contentp |
+----------+-------+----------+
| zhangsan | admin | I Konw |
+----------+-------+----------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.09 sec)
3、注意:这里有个问题即如果游标的结果集为空,如果使用是continue会继续执行
后面的语句显示情况为[结果1],使用的exit显示的情况为[结果2]
循环读取游标
[结果1]
mysql> call p1()$
+------+------+----------+
| recp | posp | contentp |
+------+------+----------+
| NULL | NULL | NULL |
+------+------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
[结果2]
mysql> call p1()$
Query OK, 0 rows affected (0.00 sec)
4、以上的这些显示结果是在使用Loop的循环的条件下,如果使用while,repeate
请自行研究。