如何理解某人编写的存储过程 [英] How to understand the stored procedure written by someone
本文介绍了如何理解某人编写的存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
你好专家，
我有一个由某人写的存储过程。我对存储过程不太满意，因为我错过了对它的基本理解。
我没有得到任何东西。既然我必须处理类似的要求，我需要了解存储过程。如果有人有兴趣向我解释SP，我将不胜感激。我知道这有点奇怪，但如果我说出来而不是用它咕噜咕噜我感觉好多了。
Hello experts,
I am having a stored procedure that was written by someone. I am not quite positive about the stored procedure because I missed the basic understanding of it.
I didn't get anything from it. Now that I have to work on similar kind of requirement I need to understand the Stored procedure. I would appreciate if anyone is interested in explaining me the SP. I know this is kind of weird but I feel much better if I tell it out rather than rumbling with it.
DELIMITER $$
DROP PROCEDURE IF EXISTS `spm_pushittothemax` $$
CREATE DEFINER=`mysqluser`@`` PROCEDURE `spm_pushittothemax`(
tCust char(7)
)
begin_label: begin
declare lysales, tysales, promosales, myperc decimal(10, 2) default 0;
if year(current_date) <> 2014 then
leave begin_label;
end if;
select
rmname, rmadd1, rmadd2, rmadd3, rmcity, rmstat, rmzip,
(case when wccust is null then 'F' else 'T' end) pittm,
coalesce(sum(lyamount), 0) lyamount,
coalesce(sum(tyamount), 0) tyamount,
coalesce(sum(promosales), 0) promosales,
coalesce(sum(promosales), 0) * 0.5 promo
from TESTDATA.VARCUST
left outer join TESTDATA.VXWWCU on wctype = 'PITTM' and wccust = rmcust
left outer join (select
sum(case when year(oaordt) = 2013 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as lyamount,
sum(case when year(oaordt) = 2014 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as tyamount,
sum(case when year(oaordt) = 2014 and oaordt between 20141101 and 20141231 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as promosales
from TESTDATA.VCOHEAD
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obdel = 'A'
# join TESTDATA.VINITEM on icitem = obitem and icdiv = 1
# join TESTDATA.VININDEX on i001trln = ictrln #and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer')
where oacmp = 1
and oadel = 'A'
and oaprnt <> 'A'
and oaordt between 20130101 and 20141231
and oabocd in (0, 50)
and (oacust = tCust or (tCust = '0599298' /* new */ and oacust = '2001172' /* old */))
UNION ALL
select
sum(case when year(saordt) = 2013 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as lyamount,
sum(case when year(saordt) = 2014 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as tyamount,
sum(case when year(saordt) = 2014 and saordt between 20141101 and 20141231 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as promosales
from TESTDATA.VSAHEAD
join TESTDATA.VSADETL on sbcmp = 1 and sbord = saord and sbbocd = sabocd and sbindt = saindt
# join TESTDATA.VINITEM on icitem = sbitem and icdiv = 1
# join TESTDATA.VININDEX on i001trln = ictrln and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer')
where sacmp = 1
and saordt between 20130101 and 20141231
and sabocd in (0, 50)
and (sacust = tCust or (tCust = '0599298' /* new */ and sacust = '2001172' /* old */))
) x on 1=1
where rmcmp = 1 and rmcust = tCust
group by rmname, rmadd1;
set lysales = COALESCE((select scdltd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
set tysales = COALESCE((select
sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
from TESTDATA.VCODLYO
join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
where o17cmp = 1 and oacust = tCust), 0)
+ COALESCE((select scdytd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
set promosales = COALESCE((select
sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
from TESTDATA.VCODLYO
join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
where o17cmp = 1 and oacust = tCust and current_date + 0 between 20141101 and 20141231), 0)
+COALESCE((select scd10 + scd11 + scd12 from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
set myperc = (case when tysales = 0 then 0 when lysales = 0 then '9999999' else round((tysales + promosales * .5) / lysales * 100, 2) end);
select
myperc, count(*) + 1 rank
from TESTDATA.VSAANAL
where
sccmp = 1
and scrtyp = '02'
and sccust <> tCust
and
(case when scdytd = 0 then 0 when scdltd = 0 then '9999999' else round(
(scdytd + COALESCE((select sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
from TESTDATA.VCODLYO
join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
where o17cmp = 1 and oacust = sccust), 0)
) / scdltd * 100, 2) end)
> myperc;
end $$
DELIMITER ;
推荐答案
DROP PROCEDURE IF EXISTS spm_pushittothemax`
DROP PROCEDURE IF EXISTS `spm_pushittothemax`
CREATE DEFINER =`mysqluser` @``PROCEDURE`spm_pushittothemax`（
tCust char（7）
）
begin_label：begin
声明lysales，tysales，promosales，myperc decimal（10,2）默认为0;
如果年份（current_date）<> 2014年然后
离开begin_label;
结束如果;
选择
rmname，rmadd1，rmadd2，rmadd3，rmcity，rmstat，rmzip，
（当wccust为null然后'F'，否则'T'结束时的情况）pittm，
coalesce（sum（lyamount），0）lyamount，
coalesce（sum（tyamount），0）tyamount，
coalesce（sum（promosales），0）promosales，
coalesce（ sum（promosales），0）* 0.5促销
来自TESTDATA.VARCUST
左外连接TESTDATA.VXWWCU on wctype ='PITTM'和wccust = rmcust
left outer join（选择
sum（当年（oaordt）= 2013时的情况（当oatype ='O'然后obqord else -obqord结束时的情况）* obpric else 0 end）作为lyamount，
sum（年时的情况（ oaordt）= 2014年（当oatype ='O'然后obqord else -obqord结束时的情况）* obpric else 0 end）作为tyamount，
sum（年度（oaordt）= 2014年的情况以及20141101和20141231之间的oaordt然后（情况oatype ='O'然后obqord else -obqord结束）* obpric else 0 end）作为promosales
来自TESTDATA.VCOHEAD
加入TESTDAT在obcmp = 1上的A.VCODETL和obord = oaord和obbocd = oabocd和obdel ='A'
＃在icitem = obitem上加入TESTDATA.VINITEM并在i001trln上加入icdiv = 1
＃join TESTDATA.VININDEX = ictrln＃和i001002 in（'Auto Tires'，'Commercial Over the Road Truck Tyres'，'Farm Tyres'，'Goodride Trailer'，'Light Truck / Recreational Vehicle Tyres'，'Medium&重型Off-The-Road'，'预告片'）
其中oacmp = 1
和oadel ='A'
和oaprnt<> 'A'
和oaordt在20130101和20141231之间
和oabocd在（0,50）
和（oacust = tCust或（tCust ='0599298'/ * new * / and oacust =' 2001172'/ *旧* /））
UNION ALL
选择
总和（当年（saordt）= 2013时的情况（当satype ='O时的情况） '然后sboqty else -sboqty结束）* sbpric else 0结束）作为lyamount，
sum（当年（saordt）= 2014时的情况（当satype ='O'然后sboqty else -sboqty结束时的情况）* sbpric else 0结束）作为tyamount，
sum（年份（saordt）= 2014年的情况和2014-1101和20141231之间的saordt然后（satype ='O'然后sboqty else -sboqty结束时的情况）* sbpric else 0 end）作为promosales
来自TESTDATA.VSAHEAD
加入TESTDATA.VSADETL on sbcmp = 1 and sbord = saord and sbbocd = sabocd and sbindt = saindt
＃join TESTDATA.VINITEM on icitem = sbitem and icdiv = 1
＃在i001trln = ictrln和i001002 in（'Auto Tires'，'Commercia）中加入TESTDATA.VININDEX l Over the Road Truck Tires'，'Farm Tyres'，'Goodride Trailer'，'Light Truck / Recreational Vehicle Tyres'，'Medium&重型Off-The-Road'，'预告片'）
其中sacmp = 1
和20130101和20141231之间的saordt
和sabocd in（0,50）
和（sacust = tCust或（tCust ='0599298'/ * new * / and sacust ='2001172'/ * old * /））
）x on 1 = 1
其中rmcmp = 1且rmcust = tCust
group by rmname，rmadd1;
set lysales = COALESCE（（从TESTDATA.VSAANAL选择scdltd，其中sccmp = 1，scrtyp ='02'，sccust = tCust），0）;
set tysales = COALESCE（（选择
sum（（'O'的情况oatype然后obqshp else -obqshp结束）* obpric）来自TESTDATA.VCODLYO的
加入TESTDATA oCOmp = 1上的.VCOHEAD和oaord = o17ord和oabocd = o17bocd
在obcmp = 1上加入TESTDATA.VCODETL并且obord = oaord和obbocd = oabocd和obpcod<>'D'
其中o17cmp = 1和oacust = tCust），0）
+ COALESCE（（从TESTDATA.VSAANAL中选择scdytd，其中sccmp = 1，scrtyp ='02'，sccust = tCust），0）;
set promosales = COALESCE（（选择
sum（（'O'的情况oatype然后obqshp else -obqshp结束）* obpric）来自TESTDATA.VCODLYO的
加入TESTDATA oCOmp = 1上的.VCOHEAD和oaord = o17ord和oabocd = o17bocd
在obcmp = 1上加入TESTDATA.VCODETL并且obord = oaord和obbocd = oabocd和obpcod<>'D'
其中o17cmp = 1和oacust = tCust和current_date + 0在20141101和20141231之间），0）
+ COALESCE（（从TESTDATA.VSAANAL选择scd10 + scd11 + scd12，其中sccmp = 1，scrtyp ='02'和sccust = tCust） ，0）;
设置myperc =（tysales = 0时的情况，然后当lysales = 0时为0然后'9999999'否则为圆形（（tysales + promosales * .5）/ lysales * 100,2）结束）;
选择
myperc，count（*）+ 1等级
来自TESTDATA.VSAANAL
其中
sccmp = 1
和scrtyp =' 02'
和sccust<> tCust
和
（scdytd = 0时的情况，然后当scdltd = 0时为0然后'9999999'否则为圆形（
（scdytd + COALESCE）（选择sum（（'o'时的情况oatype然后obqshp else -obqshp end）* obpric）作为美元
来自TESTDATA.VCODLYO
加入TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
加入TESTDATA.VCODETL on obcmp = 1和obord = oaord和obbocd = oabocd和obpcod<>'D'
其中o17cmp = 1和oacust = sccust），0）
）/ scdltd * 100,2）end）
> myperc;
end
CREATE DEFINER=`mysqluser`@`` PROCEDURE `spm_pushittothemax`( tCust char(7) ) begin_label: begin declare lysales, tysales, promosales, myperc decimal(10, 2) default 0; if year(current_date) <> 2014 then leave begin_label; end if; select rmname, rmadd1, rmadd2, rmadd3, rmcity, rmstat, rmzip, (case when wccust is null then 'F' else 'T' end) pittm, coalesce(sum(lyamount), 0) lyamount, coalesce(sum(tyamount), 0) tyamount, coalesce(sum(promosales), 0) promosales, coalesce(sum(promosales), 0) * 0.5 promo from TESTDATA.VARCUST left outer join TESTDATA.VXWWCU on wctype = 'PITTM' and wccust = rmcust left outer join (select sum(case when year(oaordt) = 2013 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as lyamount, sum(case when year(oaordt) = 2014 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as tyamount, sum(case when year(oaordt) = 2014 and oaordt between 20141101 and 20141231 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as promosales from TESTDATA.VCOHEAD join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obdel = 'A' # join TESTDATA.VINITEM on icitem = obitem and icdiv = 1 # join TESTDATA.VININDEX on i001trln = ictrln #and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer') where oacmp = 1 and oadel = 'A' and oaprnt <> 'A' and oaordt between 20130101 and 20141231 and oabocd in (0, 50) and (oacust = tCust or (tCust = '0599298' /* new */ and oacust = '2001172' /* old */)) UNION ALL select sum(case when year(saordt) = 2013 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as lyamount, sum(case when year(saordt) = 2014 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as tyamount, sum(case when year(saordt) = 2014 and saordt between 20141101 and 20141231 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as promosales from TESTDATA.VSAHEAD join TESTDATA.VSADETL on sbcmp = 1 and sbord = saord and sbbocd = sabocd and sbindt = saindt # join TESTDATA.VINITEM on icitem = sbitem and icdiv = 1 # join TESTDATA.VININDEX on i001trln = ictrln and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer') where sacmp = 1 and saordt between 20130101 and 20141231 and sabocd in (0, 50) and (sacust = tCust or (tCust = '0599298' /* new */ and sacust = '2001172' /* old */)) ) x on 1=1 where rmcmp = 1 and rmcust = tCust group by rmname, rmadd1; set lysales = COALESCE((select scdltd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0); set tysales = COALESCE((select sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars from TESTDATA.VCODLYO join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D' where o17cmp = 1 and oacust = tCust), 0) + COALESCE((select scdytd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0); set promosales = COALESCE((select sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars from TESTDATA.VCODLYO join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D' where o17cmp = 1 and oacust = tCust and current_date + 0 between 20141101 and 20141231), 0) +COALESCE((select scd10 + scd11 + scd12 from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0); set myperc = (case when tysales = 0 then 0 when lysales = 0 then '9999999' else round((tysales + promosales * .5) / lysales * 100, 2) end); select myperc, count(*) + 1 rank from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust <> tCust and (case when scdytd = 0 then 0 when scdltd = 0 then '9999999' else round( (scdytd + COALESCE((select sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars from TESTDATA.VCODLYO join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D' where o17cmp = 1 and oacust = sccust), 0) ) / scdltd * 100, 2) end) > myperc; end
DELIMITER;
DELIMITER ;
这篇关于如何理解某人编写的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
