MySQLストアドプロシージャのサンプル
1.PROCEDUREを作成
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
2.– トラバーサルデータ終了マーク
DECLARE done INT DEFAULT FALSE;
declare a_code varchar(50);
declare cur cursor for select * from `t_temp`;
3.– カーソルに終了マークをバインド
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;
4.– カーソルを閉じる
CLOSE cur;
END
****************
CREATE DEFINER=`root`@`%` PROCEDURE `sp_showWarnings`(IN `sBeginDate` VARCHAR(50), IN `sEndDate` VARCHAR(50), IN `sProductSort` VARCHAR(200), IN `sOrderby` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '显示产品预警报表’
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a_code varchar(50);
DECLARE cur cursor for select `p_code` from `t_tmp`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
if (sBeginDate = ") and (sEndDate = ") then
set sBeginDate = '2000-01-31 00:00:00’;
set sEndDate = '2099-12-31 00:00:00’;
end if;
drop table if exists `t_tmp`;
if sProductSort = " then
create table `t_tmp`
select
b.ps_name ps_name,
a.p_code p_code,
a.p_name p_name,
(select sum(aa.pu_money) from t_publish aa where aa.pu_code=a.p_code) p_scale,
(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code and c.sa_time between sBeginDate and sEndDate ) as sa_money,
p_scale-(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code and c.sa_time between sBeginDate and sEndDate ) 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 and c.sa_time between sBeginDate and sEndDate )/a.p_scale*100 as Percent,
0 as`warningnum`,
0 as`outlinenum`,
' ' as`outlinedate`,
(select w_color from t_warning d where d.w_code=a.p_code) as W_color,
0 as `curdateoutline`
from t_product a left join t_product_sort b
on a.p_sort=b.ps_id and a.p_enddate>=curdate();
else
— set sProductSort = concat('\", sProductSort, '\");
— set sProductSort = replace(sProductSort, ',’, '\’,\");
create table `t_tmp`
select
b.ps_name ps_name,
a.p_code p_code,
a.p_name p_name,
(select sum(aa.pu_money) from t_publish aa where aa.pu_code=a.p_code) p_scale,
(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code and c.sa_time between sBeginDate and sEndDate ) as sa_money,
p_scale-(select sum(c.sa_money) from t_sale c where c.sa_code=a.p_code and c.sa_time between sBeginDate and sEndDate ) 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 and c.sa_time between sBeginDate and sEndDate )/a.p_scale*100 as Percent,
0 as`warningnum`,
0 as`outlinenum`,
" as`outlinedate`,
(select w_color from t_warning d where d.w_code=a.p_code) as W_color,
0 as `curdateoutline`
— from t_product a , t_product_sort b
— where a.p_sort=b.ps_id and a.p_enddate>=curdate();
from t_product a left join t_product_sort b
on a.p_sort=b.ps_id and a.p_enddate>=curdate()
and LOCATE(a.`p_sort`, sProductSort)>0;
end if;
ALTER TABLE `t_tmp`
CHANGE COLUMN `outlinedate` `outlinedate` VARCHAR(300) NOT NULL DEFAULT " AFTER `outlinenum`;
OPEN cur;
read_loop: LOOP
FETCH cur INTO a_code;
IF done THEN
LEAVE read_loop;
END IF;
set @rlt=0;
set @days=";
select @wnum:=w_num from t_warning where `w_code`= a_Code;
select @cycle:=p_Cycle from t_product where `p_code`=a_code;
CALL `sp_GetWangingsCount`(a_Code, sBeginDate, @wnum, @cycle, @rlt, @days);
/*select @rlt;
–select @days;
*/
update `t_tmp` set `warningnum`= @rlt, `outlinedate`= @days where `p_code`= a_Code;
END LOOP;
CLOSE cur;
select * from t_tmp;
end