mysql游标

来源:互联网 发布:vr家装软件 编辑:程序博客网 时间:2024/05/16 18:30

说明:mysql游标只能用于存储过程中。

创建存储过程:

create procedure processorders()
begin
     declare done boolean default 0;    
     declare o int;    
     declare t decimal(8,2);    

--定义游标

     declare ordernumbers cursor    for   select order_num from orders;    

--declare continue handler

     declare continue handler for sqlstate '02000'  set done = 1;    
         
     create table if not exists ordertotals    
            (order_num int,total decimal(8,2));        

--打开游标   

      open ordernumbers;     

--循环所有行

      REPEAT      
            FETCH ordernumbers into o;           
            call ordertotal(o,1,t);           
            insert into ordertotals(order_num,total) values(o,t);      

--结束循环      
      until done end REPEAT; 

--关闭游标     
      close ordernumbers;     
end;

调用存储过程:

call processorders();
select * from ordertotals