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')-- 控制时间循环，按外循环所给的阿尔法值统计预测误差值；DOselect 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