创建存储过程的代码:
delimiter $$
drop procedure if exists orderstockamount$$
create procedure orderstockamount()
begin
declare _check_tag int; /*审核标志*/
declare _product_id varchar(50); /*产品id*/
declare _amount int ; /*表示未审核数量*/
declare _amount_check int; /*表示已审核数量*/
declare done int default 0; /*表示是否是结束标志*/
/*定义游标*/
declare rs_cursor cursor for select check_tag,product_id,amount,amount_check from crm_order_details where order_id in (select order_id from crm_order where customer_name not like '%华东乐购%' and check_tag <> 9 and pay_tag <> 3 );
declare continue handler for not found set done=1;
open rs_cursor;
cursor_loop:loop
fetch rs_cursor into _check_tag,_product_id,_amount,_amount_check; /*取数据*/
if done = 1 then
leave cursor_loop;
end if;
if _check_tag = 0 then
insert into order_data_temp values(_product_id,_amount);
end if;
if _check_tag = 1 then
insert into order_data_temp values(_product_id,_amount_check);
end if;
end loop cursor_loop;
close rs_cursor;
end$$
delimiter ;