游标的使用

游标：

　　类似于数组的下标。

declare cur_test CURSOR for select id ,username from erp_manages; 定义游标；

declare continue handler FOR SQLSTATE '02000' set done=1;   当数据扫描到低端标志结束；

DROP PROCEDURE if exists test_cursor;  Mysql存储过程无法更改内容，只能删除重建。

 

delimiter |

create procedure test_cursor(in param int(10),out result varchar(90))

begin
        declare cid int(20);
        declare cusername varchar(20);
        declare done int;
        declare cur_test CURSOR for select id ,username from erp_manages;
        declare continue handler FOR SQLSTATE '02000' set done=1;
        if param then
                select concat_ws(',',id,username)into result from erp_manages where id=param;
        else
                open cur_test;
                repeat
                        fetch cur_test into cid,cusername;
                        select concat_ws(',',result,cid,cusername)into result;
                until done end repeat;
                close cur_test;
        end if;
end; |