分类: Mysql/postgreSQL
2011-07-20 11:00:56
游标主要是用来保存查询结果集,把多个结果保存到内存中,需要用数据时,从内存取出放到变量。游标中只能用查询语句,不能用其它语句
游标声明语法 delcare cursorname cursor for select * from tablene
打开游标 open cursorname//将查询的结果存放到内存中
取数据 fetch cursorname into 变量
利用循环取数据,必须要判断游标是否取完 declare continue handler for not found set var=1;
关闭游标 close cursorname
示例:
create procedure sp()
begin
declare done int default 0;
declare name1 varchar(20);
declare mycursor cursor for select name from tbl_emp;
declare continue handler for not found set done=1;
open mycursor;
myloop:loop
fetch mycursor into name1;
if done = 1 then
leave myloop;
end if ;
select name1;
end loop myloop ;
close mycursor;
end
示例:
create procedure pro9()
begin
declare done int default 0;
declare sname varchar(20);
declare p_cursor cursor for select name from person;
declare continue handler for not found set done=1;
open p_cursor;
p_loop:loop
fetch p_cursor into sname;
if done=1 then
leave p_loop;
end if;
select sname;
end loop p_loop;
close p_cursor;
end
错误处理
DECLARE type HANDLER FOR condition1, condition2, condition3,…command;
type可以为continue和exit两种
condition(条件) 可为 not found
实例:
create procedure sp()
begin
declare done int default 0;
declare name1 varchar(20);
declare continue handler for
not found set done=1;
select name from tbl_emp
where id=8 into name1;
select name1;
select done;
end
其他命令:
show create procedure proname //查看存储过程代码
show procedure status //查看所有存储过程名称