mysql 存储过程 例子

CREATE DEFINER=`root`@`%` PROCEDURE `sp_GetWangingsCount`(IN `PCode` VARCHAR(50), IN `FromDay` DATE, IN `Cycle` INT, IN `DayCount` INT, OUT `Rlt` INT, OUT `DayStr` VARCHAR(520))

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ‘‘

BEGIN

  if Date_Add(FromDay, interval DayCount-1 day) < date(now()) then

     set Rlt = 0;            

  else   

set @n = floor((datediff(date(now()), FromDay)  + 1) / Cycle); 

set Rlt = 0;

set DayStr = ‘‘;

set @i=1;  

while @i <= @n do 

    set @_FromDay = Date_Add(FromDay, interval DayCount/Cycle*(@i-1) - 1 day) ;

       if @_FromDay <= date(now()) then

      if @i < @n then

              set @_ToDay = Date_add(date(FromDay), interval DayCount/Cycle*@i day);

      else

           set @_ToDay = Date_add(date(Fromday), interval DayCount-1 day);

      end if;      

          if datediff(@_ToDay, now()) > 0 then

              set @_ToDay = date(now());

          end if;       

      select @k:=if(sum(sa_money)/datediff(@_ToDay, @_FromDay) < 1000, 1 ,0) 

   from `t_sale`

   where sa_code = PCode and sa_date between @_FromDay and @_ToDay;

          set Rlt = Rlt + @k; 

          select @i, @_FromDay, @_ToDay, @k, Rlt;

   if @k = 1 then

      if DayStr = ‘‘ then

         set DayStr = concat(DayStr,  @_FromDay, ‘-‘,  @_ToDay); 

      else

         set DayStr = concat(DayStr, ‘,‘,  @_FromDay, ‘-‘,  @_ToDay); 

   end if;   

   end if;

end if;  

set @i = @i + 1;

    end while;       

  end if; 

END

CREATE DEFINER=`root`@`%` PROCEDURE `showWarnings`()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ‘ShowWarnigsDay‘

BEGIN

 -- 遍历数据结束标志

DECLARE done INT DEFAULT FALSE;

declare a_code varchar(50);

declare cur cursor for select * from `t_temp`; 

 -- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

create table t_tmp 

select 

  b.ps_name ps_name,

  a.p_code p_code,

  a.p_name p_name,

  a.p_scale p_scale,

  (select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code) as sa_money,

  p_scale-(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code) as sa_left,

  datediff(p_EndDate,p_BeginDate) as dayLeft, 

  floor(datediff(a.p_EndDate, a.p_BeginDate)/7) as LeftWeek,

  a.p_period p_period,

  a.p_BeginDate p_eginDate,

 (select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code)/a.p_scale*100 As Percent,

 1 as`预警次数` ,

 1 as`破线次数` ,

 ‘2014-08-10‘ as`破线日期` ,

 (select w_color from t_warning d where d.w_code=a.p_code) as W_color,

 1 as `当日破线` 

from t_product a 

  left join t_product_sort b on a.p_sort=b.ps_id and a.p_sort in (‘1‘,‘2‘);  

  -- 打开游标

  OPEN cur;

   -- 开始循环

  read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH cur INTO a_code;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    call `spGetWangingsCount`(a_code, ‘‘, ‘‘, ‘‘);

    update t_temp set `预警次数`=@;

  END LOOP;

  -- 关闭游标

  CLOSE cur;

END