SOS,救命！请各位帮帮我，尽管发言！

Question

能否编一个程序来分析一个存储过程所要执行的操作。请各位指教！！！

Answer 1

其實不用另外編程來分析﹐在ORACLE的工具PL/SQL DEVELOPER中有這樣的功能﹐你測試某個procedure時先按F9再按ctrl+n中可以看到這個procedure是怎么執行的了。下面給你一個procedure看一看﹕CREATE OR REPLACE Procedure Pro_Import_Odr_Part
   (Vi_Fact_No        In    Odrsfc.Fact_No%Type,
    Vi_Odr_No         In    Odrsfc.Odr_No%Type,
    Vi_Dept_Mk        In    Char,
    Vo_Msg            Out   Varchar2)
Is
/*****************************************************************************************
程式類別 : Procedure
程式功能 : 轉入訂單所有的部位資料到odrsfc表中﹐准備開鎖
           1.從bomd中轉入所有部位﹐含追加部位
           2.從預計用量中轉入直接輸入用量的部位(內盒﹑外箱等)
程式名稱 : Pro_Import_Odr_Part
傳入參數 : Vi_Fact_No:廠別
           Vi_Odr_No:訂單號碼
           Vi_Dept_Mk:部門注記
           1.若vi_Dept_Mk = 'D'則轉底部部位
           2.若vi_Dept_Mk = 'M'則轉面部部位﹐含加工﹑裁斷﹑針車﹑成型
傳出參數 : Vo_Msg 
原設計者 : 
撰寫日期 : 2003/06/26
----------------------------------------異動記錄明細-------------------------------------
異 動 日 期   異  動  者      異   動   原   因
******************************************************************************************/  
   V_Dept_No             Odrsfc.Dept_No%Type;
   V_Error_Dept_No       Odrsfc.Dept_No%Type;--Add By 李燕青 At 2003/12/11
   V_Error_Unlock_Mk     Odrsfc.Unlock_Mk%Type;--Add By 李燕青 At 2003/12/11
   V_Old_Dept_No         Odrsfc.Dept_No%Type;   
   V_Unlock_Mk           Odrsfc.Unlock_Mk%Type;   
   V_Style_No            Odrm.Style_No%Type;
   V_Model_Name          Stylem.Model_Name%Type;
   V_Odr_Yymm            Odrm.Odr_Yymm%Type;
   V_Odr_Type            Odrm.Odr_Type%Type;
   V_Count               Integer; 
   V_Odr_Qty             Odrsfcms.Odr_No%Type;  
   V_Ret                 Char;
-----------------------------------------------------------------------------
   Procedure Process_Del_All
   (Vi_Fact_No_F        In    Odrsfcms.Fact_No%Type,
    Vi_Odr_No_F         In    Odrsfcms.Odr_No%Type ,
    Vi_Dept_No_F        In    Odrsfcms.Dept_No%Type,
    Vi_Part_No_F        In    Odrsfcms.Part_No%Type,
    Vo_Msg_F            Out   Char) 
   Is
      V_Sfced_Qty             Odrsfcms.Sfced_Qty%Type;  
      V_Sfcreq_Qty            Odrneeds.Sfcreq_Qty%Type; 
      V_Count_F               Integer;
   Begin
           Begin
          Select Nvl(Sum(Nvl(Sfced_Qty,0)),0)
            Into V_Sfced_Qty
            From Odrsfcms
           Where Fact_No = Vi_Fact_No_F And Odr_No = Vi_Odr_No_F 
             And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F;
       Exception
            When Others Then
                 V_Sfced_Qty:=0;
             End;
           Begin
          Select Nvl(Sum(Nvl(Sfcreq_Qty,0)),0)     
            Into V_Sfcreq_Qty      
            From Odrneeds
           Where Fact_No = Vi_Fact_No_F And Odr_No = Vi_Odr_No_F
             And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F;
       Exception
            When Others Then
                 V_Sfcreq_Qty:=0;
             End;
       Select Count(*)
            Into V_Count_F
            From Odrsfcd
           Where Fact_No = Vi_Fact_No_F And Odr_No = Vi_Odr_No_F 
             And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F
             And Sfcuse_Qty>0;       If V_Sfced_Qty+V_Sfcreq_Qty=0 Then
         
             Delete From Odrsfcms
              Where Fact_No = Vi_Fact_No_F  And Odr_No = Vi_Odr_No_F
                And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F ;
             Delete From Odrsfcm
              Where Fact_No = Vi_Fact_No_F  And Odr_No = Vi_Odr_No_F
                And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F ;            
             Delete From Odrneeds
              Where Fact_No = Vi_Fact_No_F  And Odr_No = Vi_Odr_No_F
                And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F ;
             Delete From Odrneed
              Where Fact_No = Vi_Fact_No_F  And Odr_No = Vi_Odr_No_F
                And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F ;
             Delete From Odrsfc
              Where Fact_No = Vi_Fact_No_F  And Odr_No  = Vi_Odr_No_F 
                And Dept_No = Vi_Dept_No_F And Part_No = Vi_Part_No_F ;
           Vo_Msg_F:='0';
       Elsif V_Count_F>0 Then
           Vo_Msg_F:='2';--原投入部門已有派工回饋資料
       Else
           Vo_Msg_F:='1';
       End If;
   End;
-----------------------------------------------------------------------------

Answer 2

接上面
Begin
   --按odrbomd部位轉入﹐若已轉入則不重復轉入,不轉入子部位(主料註記)
   --半成品歸屬類別semi_Mk為先期加工註記
   --投入部份
   --0加工,A裁斷b準備c針車d底加e成型f電繡
   --1==》裁斷  2==》准備  3==》針車  4==》底加  5==》成型
   --面:
   --製程投入別為加工input_Id = '0' Then
   --   半成品類別semi_Id加工后到裁斷,針車,成型('A','C','E'),其餘不需要
   --Else 非加工部位處理
   --   製程投入別為input_Id裁斷,針車,成型('A','C','E'),其餘不需要
   --底:
   --製程投入別為底加input_Id = '4',其餘不需要
   Begin
      Select Odrm.Style_No,Odrm.Odr_Type,Stylem.Model_Name,Odrm.Odr_Yymm
        Into V_Style_No,V_Odr_Type,V_Model_Name,V_Odr_Yymm
        From Odrm,Stylem
       Where Stylem.Fact_No = Odrm.Fact_No
         And Stylem.Style_No = Odrm.Style_No
         And Odrm.Fact_No = Vi_Fact_No
         And Odrm.Fact_Odr_No = Vi_Odr_No;
   Exception
        When Others Then
             Raise_Application_Error(-20002,'請檢查訂單檔中型號資料!');
         End;
    --整理部位,因bom變更刪除廢棄不用的部位
   For  Rec_Odrsfc In ( Select  Odrsfc.Fact_No,Odrsfc.Odr_No,Odrsfc.Part_No,Odrsfc.Dept_No
                           From Odrsfc,Odrbomd
                          Where Odrsfc.Fact_No = Vi_Fact_No  
                            And Odrsfc.Odr_No  = Vi_Odr_No 
                            And Odrsfc.Fact_No = Odrbomd.Fact_No(+)
                            And Odrsfc.Odr_No = Odrbomd.Odr_No(+)
                        --  And Odrbomd.Artic_No(+)=V_Artic_No ==>因為odrbomd中有些部位對應的artic_No存放的是目的地資料
                            And Odrsfc.Part_No = Odrbomd.Part_No(+)
                            And Rtrim(Odrbomd.Mat_No) Is Null )
   Loop
       Process_Del_All(Rec_Odrsfc.Fact_No,Rec_Odrsfc.Odr_No,
                            Rec_Odrsfc.Dept_No,Rec_Odrsfc.Part_No,V_Ret);      
   End Loop; 
          
   For Rec_Part In (  Select Odrbomd.Artic_No,Odrbomd.Part_No,
                             Odrbomd.Input_Id,Odrbomd.Semi_Id,
                             Nvl(Odrbomd.Semi_Mk,'0') Semi_Mk,Odrbomd.Bom_Seq,
                             Decode(Odrbomd.Input_Id,'1','1','2','2','3','3',
                                                         '4','4','5','5','Z') Input_Dept,
                             Decode(Odrbomd.Semi_Id,'1','1','2','2','3','3',
                                                         '4','4','5','5','Z') Semi_Dept,
                             --Decode(Odrbomd.Input_Id,'A','1','B','2','C','3',
                             --                            'D','4','E','5','Z') Input_Dept,
                             --Decode(Odrbomd.Semi_Id,'A','1','B','2','C','3',
                             --                            'D','4','E','5','Z') Semi_Dept,
                             Partm.Part_Class
                        From Odrbomd,Partm
                       Where Odrbomd.Fact_No =  Vi_Fact_No
                         And Odrbomd.Odr_No  =  Vi_Odr_No
                         And Odrbomd.Same_Mk Is Null
                         And Odrbomd.Part_No  =  Partm.Part_No
                    Order By Odrbomd.Bom_Seq   ) 
   Loop     
   
     -- Rec_Part.Semi_Mk 寶一二廠抓型體先期部位注記
      If Trim(Vi_Fact_No) In ('131','132','13A') And V_Odr_Yymm>='200406' Then
         Begin
         Select Nvl(Modelm_Partm.Advance,'0')
           Into Rec_Part.Semi_Mk
           From Modelm_Partm          
          Where Modelm_Partm.Fact_No = Vi_Fact_No
            And Modelm_Partm.Model_Name = V_Model_Name                                                
            And Modelm_Partm.Part_No = Rec_Part.Part_No;
         Exception
         When Others Then
         Rec_Part.Semi_Mk:='0';
         End;
      End If;   
   
          V_Dept_No:=Null;
          If Vi_Dept_Mk = 'M' Then
             --If (Rec_Part.Input_Id = '0'  And Rec_Part.Semi_Id In ('A','C','E'))  Then
             If (Rec_Part.Input_Id = '0'  And Rec_Part.Semi_Id In ('1','3','5'))  Then
                 V_Dept_No:=Substr(Rec_Part.Semi_Dept,1,1);
             --Elsif (Rec_Part.Input_Id <> '0' And Rec_Part.Input_Id In ('A','C','E')) Then
             Elsif (Rec_Part.Input_Id <> '0' And Rec_Part.Input_Id In ('1','3','5')) Then
                 V_Dept_No:=Substr(Rec_Part.Input_Dept,1,1); 
             End If;   
          End If;   
          If Vi_Dept_Mk = 'D' Then
             --If (Rec_Part.Input_Id = '0'  And Rec_Part.Semi_Id ='D') Then
             If (Rec_Part.Input_Id = '0'  And Rec_Part.Semi_Id ='4') Then
                 V_Dept_No:=Substr(Rec_Part.Semi_Dept,1,1);
             --Elsif (Rec_Part.Input_Id ='D') Then    
             Elsif (Rec_Part.Input_Id ='4') Then
                 V_Dept_No:=Substr(Rec_Part.Input_Dept,1,1); 
             End If;
          End If ;

Answer 3

接上面
          --B品訂單,只能轉入包裝材料
          --If V_Odr_Type='11' And Rec_Part.Part_Class<>'B' Then
          If V_Odr_Type='B' And Rec_Part.Part_Class<>'B' Then
             V_Dept_No:=Null;
          End If;
          --取得合法的部門,說明:如果不符所需的制程投入
          --V_Dept_No為null
          --更新制程投入別(適用于制程投入別投錯的部位)
          --先期加工:底部部位設為'1'﹐全部設為先期加工
          If Rtrim(V_Dept_No) Is Not Null  Then           
             Select Count(*)
               Into V_Count
               From Odrsfc
              Where Fact_No = Vi_Fact_No
                And Odr_No  = Vi_Odr_No
                And Part_No = Rec_Part.Part_No ;
          -- 處理舊資料問題(同一部位因制程變更,已先后在兩個部門都有訂單轉入資料) Begin 李燕青 2003/12/11       
            /* If V_Count > 1 Then
                Select Unlock_Mk,Dept_No
                  Into V_Error_Unlock_Mk,V_Error_Dept_No
                  From Odrsfc
                 Where Fact_No = Vi_Fact_No
                   And Odr_No  = Vi_Odr_No
                   And Part_No = Rec_Part.Part_No 
                   And Dept_No <> V_Dept_No;
                   
                If V_Error_Unlock_Mk In ('0','1') Then  --未開,需開鎖             
                   Delete From Odrsfc
                    Where Fact_No = Vi_Fact_No
                      And Odr_No  = Vi_Odr_No
                      And Part_No = Rec_Part.Part_No 
                      And Dept_No = V_Error_Dept_No;
                Elsif V_Error_Unlock_Mk='2' Then
                     Process_Del_All(Vi_Fact_No,Vi_Odr_No,V_Error_Dept_No,Rec_Part.Part_No,V_Ret);
                     If V_Ret= '1'  Then
                        Raise_Application_Error(-20002,'制程變更:訂單號碼:'||Vi_Odr_No||
                                                       '原投入部門:'||V_Error_Dept_No||
                                                       '  新投入部門:'||V_Dept_No||
                                                       '  部位:'||Rec_Part.Part_No||
                                                       '  請先刪除原有的派工資料!');
                     Elsif V_Ret= '2'  Then                                 
                        Vo_Msg:='制程變更:訂單號碼:'||Vi_Odr_No||
                                                       '原投入部門:'||V_Error_Dept_No||
                                                       '  新投入部門:'||V_Dept_No||
                                                       '  部位:'||Rec_Part.Part_No||
                                                       '  原投入部門已有派工回饋資料,無需再按新部門來派!';
                     End If;                
                End If;
             End If; */
         -- 處理舊資料問題(同一部位因制程變更,已先后在兩個部門都有訂單轉入資料) End 李燕青 2003/12/11
             If V_Count <= 1 Then
              Begin
             Select Unlock_Mk,Dept_No
               Into V_Unlock_Mk,V_Old_Dept_No
               From Odrsfc
              Where Fact_No = Vi_Fact_No
                And Odr_No  = Vi_Odr_No
                And Part_No = Rec_Part.Part_No ;
          Exception
               When Others Then
                    V_Unlock_Mk:=Null;
                End;
            End If;
             --未有此部位
            If     V_Unlock_Mk Is Null Then
                   Insert Into Odrsfc(Fact_No,Odr_No,Dept_No,
                                      Part_No,Unlock_Mk,Adv_Mk)
                               Values(Vi_Fact_No,Vi_Odr_No,V_Dept_No,
                                      Rec_Part.Part_No,'0',Rec_Part.Semi_Mk);  
                                      
            Elsif  V_Unlock_Mk In ('0','1') Then  --未開,需開鎖             
                   Update Odrsfc
                      Set Dept_No = V_Dept_No
                    Where Fact_No = Vi_Fact_No
                      And Odr_No  = Vi_Odr_No
                      And Part_No = Rec_Part.Part_No ;
            Elsif  (V_Unlock_Mk='2') And (V_Old_Dept_No<>V_Dept_No) Then --已派改部門,(已派不改部門,不處理)
                   Process_Del_All(Vi_Fact_No,Vi_Odr_No,V_Old_Dept_No,Rec_Part.Part_No,V_Ret);
                   If V_Ret= '0'  Then
                      Insert Into Odrsfc(Fact_No,Odr_No,Dept_No,
                                         Part_No,Unlock_Mk,Adv_Mk)
                                  Values(Vi_Fact_No,Vi_Odr_No,V_Dept_No,
                                         Rec_Part.Part_No,'0',Rec_Part.Semi_Mk);
                                         
                   Elsif V_Ret= '1'  Then
                      Raise_Application_Error(-20002,'制程變更:訂單號碼:'||Vi_Odr_No||
                                                     '原投入部門:'||V_Old_Dept_No||
                                                     '  新投入部門:'||V_Dept_No||
                                                     '  部位:'||Rec_Part.Part_No||
                                                     '  請先刪除原有的派工資料!');
                   Else                                  
                      Vo_Msg:='制程變更:訂單號碼:'||Vi_Odr_No||
                                                     '原投入部門:'||V_Old_Dept_No||
                                                     '  新投入部門:'||V_Dept_No||
                                                     '  部位:'||Rec_Part.Part_No||
                                                     '  原投入部門已有派工回饋資料,無需再按新部門來派!';
                   End If;
            End If;          
       End If;      
   End Loop;   
   Vo_Msg :=Vo_Msg||'訂單部位轉入成功!!' ;    --整理因訂單size變更;
   --個別size指令數增加時,修改開鎖注記為'需開鎖'
   --把已開鎖且寫入到訂單派工總檔的資料時行更新注記;
      V_Count := 0;
      Select Count(*)
        Into V_Count
        From Odrsfcms
       Where Fact_No =Vi_Fact_No
         And Odr_No = Vi_Odr_No;
      If V_Count>0 Then
           Begin
          Select Nvl(Sum(Nvl(Odrd.Qty,0)-Nvl(Odrsfcms.Odr_Qty,0)),0) Qty
            Into V_Odr_Qty
            From Odrd,(Select Odr_No,Sizerun,Odr_Qty
                         From Odrsfcms
                        Where Fact_No =Vi_Fact_No
                          And Odr_No = Vi_Odr_No
                     Group By Odr_No,Sizerun,Odr_Qty ) Odrsfcms
           Where Odrd.Fact_No =Vi_Fact_No
             And Odrd.Fact_Odr_No = Vi_Odr_No
             And Odrd.Fact_Odr_No=Odrsfcms.Odr_No(+)
             And Odrd.Sizerun=Odrsfcms.Sizerun(+);
       Exception
            When Others Then
                 V_Odr_Qty:=0;
            End;
       If V_Odr_Qty> 0 Then
          Update Odrsfc
             Set Unlock_Mk = '1'
           Where Fact_No = Vi_Fact_No 
             And Odr_No  = Vi_Odr_No ;
         Vo_Msg := '訂單部位轉入成功!!個別size指令數有增加﹐請重新開鎖!';       
       End If;  
     End If;     Commit; 
  
End;