1、(屬尀錀耀$坝(漬帀言$坟(珜怀謀$坡礶(瘴戀錀鐀$坣礙(趤攀鴀$坤(钬昀$坧(栀渀$坩(樀$坫(氀帀$坭(渀$坯(瀀稀$坱礢(爀$坳(琀$坵(瘀$坷礹(砀瘀$坹示(稀洀$坻礢(簀挀$坽(縀渀$坿礻(耀蠀$垁(舀$垃礽(萀$垅礿(括蘀瀀$垇(晰蠀錀$垉礴(欠言猀砀$型?(盘谀唀$垍(耸踀謀$垏(贜退$垑(鱘鈀堀$垓(鱘鐀堀$垕祂(鶄阀萀$垗(鶄頀$垙祄(騀謀嘀$垛(鰀吀$垝(鸀嘀頀$垟祆(吀$垡祈(挀$垣(一/帀琀$垥(昀$垧祋(踀昀$垩祌(猀$垫(瘀尀$垭(笀蠀$垯祎(伀簀$垱祐(蜀脀$垳(儀蜀$垵祒(踀蜀氀$垷祓(蠀氀$垹祔(謀$垻祕(嘀踀$垽(鐀退栀$垿(需$埁(鸀瀀$埃祗(需鰀$埅祘(
2、踀$埇祙(灴娀琀$埉(睼P堀$埋(羰$埍(羰渀$埏祛($埑寬(尀$埓?(戀%$埕($埗(崀$埙神(釨$埛(魈$埝(欀$域(开$埡祠(愀琀$埣($埥(戀$埧祣(踀$埩常(隴$埫(怀$埭(蘀蠀$埯(砀$埱祤(藄攀$埳(昀退$埵尷(渀$埶(伀$培(最倀瀀$埻(瀤洀开$埽(氀搀琀$埿(栀瘀鐀$堀祩(瘀鐀$堃(蔀$堅(蠀$堇(谀$堉(圀鴀$堋(樀$堍m(斴欀$堏祬(躸鴀砀$堑(洀$堓m(洀$堕($堗(洀$堙(蔀尀$堛m(渀$堝祯(瀀$堟(create or replace package PL_YUAN_101102_CXCPZB isTYPE my_Cursor IS REF CURSOR;proce
3、dure GetYieldInfo( p_KSNY varchar, p_JSNY varchar, p_num float, p_ReCursor0 out my_Cursor -p_ReCursor1 out my_Cursor, -p_ReCursor2 out my_Cursor);end PL_YUAN_101102_CXCPZB;create or replace package body PL_YUAN_101102_CXCPZB isprocedure GetYieldInfo( p_KSNY varchar, p_JSNY varchar, p_num float, p_Re
4、Cursor0 out my_Cursor -p_ReCursor1 out my_Cursor, -p_ReCursor2 out my_Cursor)is i_SqlText varchar(4000);beginDELETE FROM TEMP_YUAN_101102_CXCPZB ;INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY,F_YSL)(select 1,b.prodny,count(*) from PL_Bill a,PL_Bill_Ferment b where a.BillState=03and a.Wo
5、rkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.stationid in(c2e479fe-fb49-4a33-8437-3896230d0b91,e142f4b3-9594-47f2-8b2a-4068137f2a35,d37a8b0a-15ed-4701-bf7a-bfe4b02e2eed) and b.prodny between p_KSNY and p_JSNY and a.billid=b.billid GROUP BY b.prodny);UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=(
6、SELECT COUNT(*) FROM PL_Bill a,PL_Bill_Ferment b where a.BillState=03and a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.stationid in(c2e479fe-fb49-4a33-8437-3896230d0b91,e142f4b3-9594-47f2-8b2a-4068137f2a35,d37a8b0a-15ed-4701-bf7a-bfe4b02e2eed) and a.billid=b.billidAND b.prodny BETWEEN SUB
7、STRING(F_NY,1,4)|01 AND F_NY)WHERE F_CPZB=;-INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY)(select 2,F_NY FROM TEMP_YUAN_101102_CXCPZB WHERE F_CPZB=);UPDATE TEMP_YUAN_101102_CXCPZB SET F_YSL=(select count(*) from PL_Bill a,PL_Bill_Ferment b where a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e
8、09fbba101 and a.stationid in(c2e479fe-fb49-4a33-8437-3896230d0b91,e142f4b3-9594-47f2-8b2a-4068137f2a35,d37a8b0a-15ed-4701-bf7a-bfe4b02e2eed)and b.prodny=F_NY and a.BillState=03and a.billid=b.billid and b.OutKettleUnit=p_num)WHERE F_CPZB=;UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=(SELECT COUNT(*) FROM
9、 PL_Bill a,PL_Bill_Ferment b where a.BillState=03and a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.stationid in(c2e479fe-fb49-4a33-8437-3896230d0b91,e142f4b3-9594-47f2-8b2a-4068137f2a35,d37a8b0a-15ed-4701-bf7a-bfe4b02e2eed) and a.billid=b.billid AND b.prodny BETWEEN SUBSTRING(F_NY,1,4)|01
10、 AND F_NY and b.OutKettleUnitp_num)WHERE F_CPZB=;UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=(SELECT COUNT(*) FROM PL_Bill a,PL_Bill_Ferment b where a.BillState=03and a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.billid=b.billidAND b.prodny BETWEEN SUBSTRING(F_NY,1,4)|01 AND F_NY and b.OutKe
11、ttleUnitp_num)WHERE F_CPZB=;-INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY)(select 4,F_NY FROM TEMP_YUAN_101102_CXCPZB WHERE F_CPZB=);UPDATE TEMP_YUAN_101102_CXCPZB SET F_YSL=(SELECT count(*) from PL_Bill a,PL_Bill_Ferment b wherea.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and b.
12、FungusChoose =1 AND b.prodny=F_NYand a.BillState=03 and a.billid=b.billid ) WHERE F_CPZB= ;UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=(SELECT COUNT(*) FROM PL_Bill a,PL_Bill_Ferment b where a.BillState=03 and b.FungusChoose =1 and a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.billid=b.billi
13、dAND b.prodny BETWEEN SUBSTRING(F_NY,1,4)|01 AND F_NY )WHERE F_CPZB=;-INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY,F_YSL,F_NLJ,F_JS1,F_JS2)(SELECT 5,A.F_NY,A.F_YSL,A.F_NLJ,B.F_YSL,B.F_NLJ FROM TEMP_YUAN_101102_CXCPZB A, TEMP_YUAN_101102_CXCPZB B WHERE A.F_CPZB= AND B.F_CPZB= AND A.F_NY
14、=B.F_NY);UPDATE TEMP_YUAN_101102_CXCPZB SET F_YSL=F_JS1/F_YSL*100 WHERE F_CPZB=AND F_YSL0;UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=F_JS2/F_NLJ*100 WHERE F_CPZB=AND F_NLJ0;-INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY,F_YSL)(select 6,ka,b.prodny,sum(b.mainquantity) from PL_Bill a,PL_Bil
15、l_Ferment b where a.BillState=03and a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and b.prodny between p_KSNY and p_JSNY and a.billid=b.billidGROUP BY b.prodny);UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=(SELECT sum(b.mainquantity) FROM PL_Bill a,PL_Bill_Ferment b where a.BillState=03and a.Worksh
16、opID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.billid=b.billid AND b.prodny BETWEEN SUBSTRING(F_NY,1,4)|01 AND F_NY)WHERE F_CPZB=;-INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY,F_YSL)(select 7,ka,b.prodny,sum(b.mainquantity) from PL_Bill a,PL_Bill_Ferment b where a.BillState=03 and a.
17、WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.billid=b.billid and b.prodny between p_KSNY and p_JSNYGROUP BY b.prodny);UPDATE TEMP_YUAN_101102_CXCPZB SET F_NLJ=(SELECT sum(b.mainquantity) FROM PL_Bill a,PL_Bill_Ferment b where a.BillState=03and a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbb
18、a101 and a.billid=b.billidAND b.prodny BETWEEN SUBSTRING(F_NY,1,4)|01 AND F_NY)WHERE F_CPZB=;-INSERT INTO TEMP_YUAN_101102_CXCPZB (F_XH,F_CPZB,F_DW,F_NY,F_YSL)(select 8,160T,ka,b.prodny,sum(b.mainquantity) from PL_Bill a,PL_Bill_Ferment b,PL_Kettle c where a.WorkshopID =ddc8e52a-1c9d-471d-8968-30e09fbba101 and a.BillS
