mysql存储过程创建实例

########################################################################
#下面的存储过程为更新金币产品价格的存储过程
#请在执行之前，先使用 “delimiter .”语句将分隔符设为.号
#或者在phpmyadmin中将Delimiter文本框中的值设置为.号
########################################################################
drop procedure if exists update_gold_product_price.
create procedure update_gold_product_price(in var_game_id integer)  
begin 
   #声明一些相关变量    
   declare var_price_rate        decimal(18,4);
   declare var_category_id         int;        
   declare var_base_parameter     decimal(18,2);
   declare var_products_id        int;
   declare var_server_id        int;
   declare var_fraction_id        int;
   declare var_gold_count        int;
   declare stopFlag             int; 
   #定义游标
   declare cur_products        cursor for select products_id,server_id,fraction_id,gold_count 
           from product_gold_extra where products_id in(select products_id from products_to_categories where categories_id=var_category_id);
   DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; 
   #获取价格比例
   select cast(configuration_value as decimal(18,4)) into var_price_rate 
           from configuration where configuration_key='DHISYS_PRICE_RATE' limit 1;
   #若价格比例小于或等于0,则将价格比例设置回1
   if var_price_rate<=0 then            
           set var_price_rate = 1;    
   end if;
   #获取类别id
   select category_id_for_gold into var_category_id 
           from game 
           where game_id=var_game_id limit 1;
   #获取base price   
   select parameter into var_base_parameter 
           from game_gold_amount 
        where game_id=var_game_id and is_base_amount=1 limit 1;   
   #打开游标   
   open cur_products;
   #循环游标
   repeat
         fetch cur_products into var_products_id,var_server_id,var_fraction_id,var_gold_count;
         begin
           #获取gold price相关参数
         declare var_service_price     decimal(18,2);
         declare var_price             decimal(18,2);
         declare var_parameter        decimal(18,2);
         declare var_delivery_price    decimal(18,2);
         declare var_give_gold_rate    int;
         declare var_gold_amount_give    int;
         declare var_products_price    decimal(18,2);
         
         select service_price,price into var_service_price, var_price
             from     game_gold_price 
             where     game_id        = var_game_id
            and     server_id    = var_server_id
             and     fraction_id    = var_fraction_id
            limit 1;
         #获取gold amount相关参数
         select parameter,delivery_price,give_gold_rate into var_parameter,var_delivery_price,var_give_gold_rate
             from    game_gold_amount
            where    game_id=var_game_id
            and        amount=var_gold_count
            limit 1;
         #计算价格
         set var_gold_amount_give     = var_give_gold_rate*var_gold_count/100;
         set var_products_price        = var_price*(var_parameter/var_base_parameter)+var_delivery_price+var_service_price*(var_gold_count+var_gold_amount_give);    
         set var_products_price        = var_products_price*var_price_rate;
         #更新价格
         update products set products_price=var_products_price where products_id=var_products_id;
      end;
      UNTIL stopFlag = 1
   end repeat;
   #关闭游标
   close cur_products;
end.

call update_gold_product_price(1).