K
ks_reny
Unregistered / Unconfirmed
GUEST, unregistred user!
CREATE OR REPLACE Procedure Get_TBLAPSCF (itemcodes in varchar2,months in varchar2)<br>As<br>Begin<br> Insert into TBLAPSCF(item_code,kind,month)<br> Values(itemcodes,'客戶交期',months);<br> Insert into TBLAPSCF(item_code,kind,month)<br> Values(itemcodes,'制ㄧ課',months);<br> Insert into TBLAPSCF(item_code,kind,month)<br> Values(itemcodes,'制二課',months);<br> Insert into TBLAPSCF(item_code,kind,month)<br> Values(itemcodes,'制三課',months);<br> Insert into TBLAPSCF(item_code,kind,month)<br> Values(itemcodes,'生管課',months);<br> Insert into TBLAPSCF(item_code,kind,month)<br> Values(itemcodes,'品管課',months);<br> Update TBLAPSCF<br> Set S1=(Select Area From CUSTOMERDATE_AREA Where itemcode=itemcodes and to_char(ops_date,'mm')=months and to_char(ops_date,'dd')=1)<br> Where item_code=itemcodes and kind='客戶交期' and month=months;<br> Update TBLAPSCF<br> Set S1=(Select Area From work_item_area Where item_code=itemcodes and Work_code='制ㄧ課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='制ㄧ課' and month=months;<br> Update TBLAPSCF<br> Set S1=(Select Area From work_item_area Where item_code=itemcodes and Work_code='制二課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='制二課' and month=months;<br> Update TBLAPSCF<br> Set S1=(Select Area From work_item_area Where item_code=itemcodes and Work_code='制三課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='制三課' and month=months;<br> Update TBLAPSCF<br> Set S1=(Select Area From work_item_area Where item_code=itemcodes and Work_code='生管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='生管課' and month=months;<br> Update TBLAPSCF<br> Set S1=(Select Area From work_item_area Where item_code=itemcodes and Work_code='品管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='品管課' and month=months;<br> Update TBLAPSCF<br> Set N1=(Select OPS_QNTY From CUSTOMERDATE_AREA Where itemcode=itemcodes and to_char(ops_date,'mm')=months and to_char(ops_date,'dd')=1)<br> Where item_code=itemcodes and kind='客戶交期' and month=months;<br> Update TBLAPSCF<br> Set N1=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='制ㄧ課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='制ㄧ課' and month=months;<br> Update TBLAPSCF<br> Set N1=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='制二課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='制二課' and month=months;<br> Update TBLAPSCF<br> Set N1=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='制三課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='制三課' and month=months;<br> Update TBLAPSCF<br> Set N1=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='生管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='生管課' and month=months;<br> Update TBLAPSCF<br> Set N1=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='品管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=1)<br> Where item_code=itemcodes and kind='品管課' and month=months;<br>......<br>......<br> Update TBLAPSCF<br> Set S31=(Select Area From CUSTOMERDATE_AREA Where itemcode=itemcodes and to_char(ops_date,'mm')=months and to_char(ops_date,'dd')=31)<br> Where item_code=itemcodes and kind='客戶交期' and month=months;<br> Update TBLAPSCF<br> Set S31=(Select Area From work_item_area Where item_code=itemcodes and Work_code='制ㄧ課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='制ㄧ課' and month=months;<br> Update TBLAPSCF<br> Set S31=(Select Area From work_item_area Where item_code=itemcodes and Work_code='制二課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='制二課' and month=months;<br> Update TBLAPSCF<br> Set S31=(Select Area From work_item_area Where item_code=itemcodes and Work_code='制三課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='制三課' and month=months;<br> Update TBLAPSCF<br> Set S31=(Select Area From work_item_area Where item_code=itemcodes and Work_code='生管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='生管課' and month=months;<br> Update TBLAPSCF<br> Set S31=(Select Area From work_item_area Where item_code=itemcodes and Work_code='品管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='品管課' and month=months;<br> Update TBLAPSCF<br> Set N31=(Select OPS_QNTY From CUSTOMERDATE_AREA Where itemcode=itemcodes and to_char(ops_date,'mm')=months and to_char(ops_date,'dd')=31)<br> Where item_code=itemcodes and kind='客戶交期' and month=months;<br> Update TBLAPSCF<br> Set N31=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='制ㄧ課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='制ㄧ課' and month=months;<br> Update TBLAPSCF<br> Set N31=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='制二課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='制二課' and month=months;<br> Update TBLAPSCF<br> Set N31=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='制三課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='制三課' and month=months;<br> Update TBLAPSCF<br> Set N31=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='生管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='生管課' and month=months;<br> Update TBLAPSCF<br> Set N31=(Select PCS From work_item_area Where item_code=itemcodes and Work_code='品管課'and to_char(sysdate,'mm')=months and to_char(sysdate,'dd')=31)<br> Where item_code=itemcodes and kind='品管課' and month=months;<br>Commit;<br>End;<br>編譯時,報下面的錯誤.<br>PLS-00123: 程式太大<br>如何處理呀.我的存儲過程有1100行左右.