Oracle存储过程实例

之前写的一个存储过程，用到定义变量，赋值，while循环。好久前写的了，今天重新翻出来看着都有点陌生了，哎，技术还是常用常新，隔一段时间不用，就开始遗忘了。。。。

BEGIN
	#Routine body goes here...
	declare sjxs double;
	declare st0 double ;
	declare st02 double ;
	declare st03 double ;
	declare st1 double ;
	declare st2 double ;
	declare st3 double ;
	declare xt double ;
	declare xt0 double;
	declare tm VARCHAR(200) ;
	declare f double ;
	declare f0 double ;
	declare f1 double;
	declare f2 DOUBLE;
	declare a double;
	declare b double;
	declare c double;
	declare m int;
	declare xf DOUBLE;
	declare shi datetime DEFAULT '2015-01-01';
-- select方法赋值
select SUBSTR(shi FROM 1 FOR 7) into tm;
select CONCAT(tm,'%') into tm;

select SUM(XSJE_BHS+WRZXSJE_BHS+XSJE_PF_BHS+YHJE_BHS) into sjxs from rbb_fresh where rq LIKE tm;
-- 直接赋值
set f0 = 0;
set m = 1;
set st0 = sjxs;
set st02 = sjxs;
set st03 = sjxs;
set xt0 = sjxs;

set f1 = 5927404973277.7500;			-- 手动执行一次，输出起始日期的预测平方值作为基础值；
	WHILE(shi < '2016-01-01')					-- 控制时间循环，按外循环所给的阿尔法值统计预测误差值；
	DO
	select shi,tm,sjxs,st0,st02,st03,t;		-- 取出时间，数据库统计的销售金额值，前一次一次、二次、三次平滑值，阿尔法值；
	set st1= alpha*sjxs+(1-alpha)*st0;
	set st2 = alpha*st1+(1-alpha)*st02;
	set st3 = t*st2+(1-alpha)*st03;

	set a = 3*st1-3*st2+st3;
	set b = (alpha/(2*(1-alpha)*(1-alpha)))*((6-5*alpha)*st1-((10-8*alpha)*st2)+(4-3*alpha)*st3);
	set c = (t*t/((1-alpha)*(1-alpha)))*(st1-2*st2+st3);
	set xt = a*m + b*m +c*m*m*1/2;
	set f2 = (xt0-sjxs)*(xt0-sjxs);
	set f = (xt-sjxs)*(xt-sjxs)+f0;
/*
命令是首先给时间按月加1，之后根据时间进行模糊查询计算数据库内sum值
*/
	select ADDDATE(shi,INTERVAL 1 MONTH) into shi;
	select SUBSTR(shi FROM 1 FOR 7) into tm;
	select CONCAT(tm,'%') into tm;
	select SUM(XSJE_BHS+WRZXSJE_BHS+XSJE_PF_BHS+YHJE_BHS) into sjxs from rbb_fresh where rq LIKE tm;
	set st0 = st1;
	set st02 = st2;
	set st03 = st3;
	set f0 = f;		-- 将所求时间的预测误差累计值
	set xt0 = xt;		-- 将值赋给上个月；
	select st1,st2,st3,a,b,c,xt,xt0,f,f2,f0;			-- 取出当月一次、二次、三次平滑值，a、b、c的值，预测值，预测误差；
end WHILE;
/*
改变fut值大小，根据最后一个月预测第fut个月的值
*/
	set xf = a*fut+b*fut+c*fut*fut*1/2;
	select xf;

END