虽然一直听说游标的效率很低,但是抱着存在即有理的心态,去了解MySQL的游标,首先说游标的格式
定义游标
declare 游标 CURSOR FOR select语句
从游标中获取数据前要打开游标
open 游标
从游标中获取数据
fetch 游标 into 变量列
从游标中获取数据后要关闭游标
close 游标
注意的地方:游标的结束符一定是以分号作为结束的,而sql语句的结束默认也是以分号作为结束,两者之中就会发生
冲突,如图:
mysql> create procedure cur_demo()
-> begin
-> declare done default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'defau
lt 0' at line 3
mysql>
怎么结束呢?因为我们无法重定义游标的结束符,所以我们只能重定义sql语句的结束符:
delimiter |
这样,sql语句的的结束就由默认的;变为|。这样我们就可以定义游标了。如图:
mysql> delimiter |
mysql> create procedure test_cursor (in param int(10),out result varchar(90))
-> begin
-> declare name varchar(20);
-> declare pass varchar(20);
-> declare done int;
-> declare cur_test CURSOR for select user_name,user_pass from test.users;
-> declare continue handler FOR SQLSTATE '02000' SET done = 1;
-> if param then
-> select concat_ws(',',user_name,user_pass) into result from test.users where id=param;
-> else
-> open cur_test;
-> repeat
-> fetch cur_test into name, pass;
-> select concat_ws(',',result,name,pass) into result;
-> until done end repeat;
-> close cur_test;
-> end if;
-> end;|
Query OK, 0 rows affected (0.00 sec)