oracle存儲過程與sqlserver存儲過程互換

---------------------------------------------sqlserver存儲過程---------------------------------------------

Create Procedure Zy_Make_SortDeliverLineID( @BatchNo integer ,
                                            @ProcessType integer ,
                                            @o_err_no integer  Output,
                                            @o_err_msg varchar(1024) Output )
as
Begin   --先刪除表中線路數據
  Delete From  Zy_Sort_LineExitDeliverID Where BatchNo = @BatchNo ;
  IF @@ERROR <> 0
  Begin
      Set @o_err_no =  -1 ;
      Set @o_err_msg = '刪除 [Zy_Sort_LineExitDeliverID]時出錯！' ;
      GOTO  ExitProc ;
  End ; 
 
  ----初始化狀態
  Update Zy_Base_DeliverLine Set Surfix = ' ' , DistributeType = 0
        Where  substring(DeliverLineID , 1 , 4 ) <> '2000' ;
  IF @@ERROR <> 0
  Begin
      Set @o_err_no =  -1 ;
      Set @o_err_msg = '更新 [Zy_Base_DeliverLine]時出錯！' ;
      GOTO  ExitProc ;
  End ; 
         
  if @ProcessType = 1  --整件拆分
  begin
      Exec Zy_Insert_TrunkSplitData @BatchNo, @o_err_no, @o_err_msg ;
      IF @o_err_no <> 0
      Begin
        GOTO  ExitProc ;
      End ;
  End ;    ---刪除訂貨量為零的數據
  Delete From Zy_Order_Details Where Quantity = 0 and BatchNo = @BatchNo;
  IF @@ERROR <> 0
  Begin
      Set @o_err_no =  -1 ;
      Set @o_err_msg = '刪除 [Zy_Order_Details]時出錯！' ;
      GOTO  ExitProc ;
  End ; 
 
  Delete From Zy_Order_Main Where BatchNo = @BatchNo and OrderID not in
            ( Select Distinct OrderID From Zy_Order_Details Where BatchNo = @BatchNo ) ;
  IF @@ERROR <> 0
  Begin
      Set @o_err_no =  -1 ;
      Set @o_err_msg = '刪除 [Zy_Order_Main]時出錯！' ;
      GOTO  ExitProc ;
  End ;   
               
  --更改批次號中訂單記錄           
  Update Zy_Base_BatchNo Set OrderQuan = (Select Count(Distinct OrderID)
          From Zy_Order_Main  Where BatchNo = @BatchNo ) Where  BatchNo = @BatchNo ; 
  IF @@ERROR <> 0
  Begin
      Set @o_err_no =  -1 ;
      Set @o_err_msg = '更新 [Zy_Base_BatchNo]時出錯！' ;
      GOTO  ExitProc ;
  End ; 
 
           
  Set  @o_err_no = 0 ;
  Set  @o_err_msg =' 執行成功 ' ;
  --返回信息------------------------------------------------------------------------
ExitProc:
    IF @o_err_no <> 0 
        SET @o_err_msg = '【出錯對象】{Zy_Insert_TrunkSplitData}'+ @o_err_msg ;                           
End ;

----------------------------------------------Oracle函數-----------------------------------

create or replace function Zy_Make_SortDeliverLineID(BatchNo in integer,
                          ProcessType in integer,
                          o_err_no out integer,
                          o_err_msg out varchar) return varchar
is
begin
  begin
    delete from Zy_Sort_LineExitDeliverID Where BatchNo=BatchNo;
  exception
    when others then
      Rollback;
      o_err_no := -1;
      o_err_msg := '刪除 [Zy_Sort_LineExitDeliverID]時出錯！' ;
  end;   begin
    Update Zy_Base_DeliverLine Set Surfix = ' ' , DistributeType = 0 
      Where  substring(DeliverLineID , 1 , 4 )  <> '2000' ;
  exception
    when others then
      Rollback;
      o_err_no := -1;
      o_err_msg = '更新 [Zy_Base_DeliverLine]時出錯！' ;
  end;   if ProcessType = 1 then
    Zy_Insert_TrunkSplitData(BatchNo,o_err_no,o_err_msg);
    if o_err_no  <> 0 then
      Rollback;
      return;
    end if;
  end if;
 
  begin
    Delete From Zy_Order_Details Where Quantity = 0 and BatchNo = @BatchNo;
  exception
    when others then
      Rollback;
      o_err_no :=  -1 ;
      o_err_msg := '刪除 [Zy_Order_Details]時出錯！' ; 
  end ;   begin
    Delete From Zy_Order_Main Where BatchNo = @BatchNo and OrderID not in 
            ( Select Distinct OrderID From Zy_Order_Details Where BatchNo = @BatchNo ) ;
  exception
    when others then
      Rollback;
      o_err_no :=  -1 ;
      o_err_msg := '刪除 [Zy_Order_Main]時出錯！' ;
  end;   begin
    Update Zy_Base_BatchNo Set OrderQuan = (Select Count(Distinct OrderID) 
          From Zy_Order_Main  Where BatchNo = @BatchNo ) Where  BatchNo = @BatchNo ; 
  exception
    when others then
      Rollback;
      o_err_no :=  -1 ;
      o_err_msg := '更新 [Zy_Base_BatchNo]時出錯！' ;
  end;
 
  commit;
  o_err_no := 0;
  o_err_msg := '執行成功';
 
  return o_err_no;  end Zy_Make_SortDeliverLineID;
--------------------------------------------2010-06-01今天寫Oracle函數時出現的問題，記錄下-------------------------------------------

CREATE OR REPLACE FUNCTION fn_getEmpName(p_str in varchar2,p_delimiter in varchar2)
        return VARCHAR2
is
  str varchar2(4000):='';
  str_name varchar2(4000);
   CURSOR c
    IS
        SELECT *
          FROM TABLE (CAST (fn_split (p_str,p_delimiter) AS ty_str_split
                           )
                     );
    r c%ROWTYPE;
begin
    OPEN c;
    LOOP
        FETCH c INTO r;
        EXIT WHEN c%NOTFOUND;
             begin
             select employee_name into str_name from mocha_osm_emp a,mocha_osm_accounts b where a.id=b.emp_id and b.user_id=r.column_value;
             str:=str||str_name||p_delimiter;
             exception when NO_DATA_FOUND then
                --dbms_output.put_line('無數據');
                str_name:='';
             end;
            
       --------------處理異常時需要有begin end 相當於();
    END LOOP;
    CLOSE c;
    dbms_output.put_line('長度:'||length(str_name));
    if length(str)>0 then
       str:=substr(str,0,length(str)-1);
    end if;
    return str;
END fn_getEmpName;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
出現的錯誤在於：處理函數異常時需要加入begin end,相當於()，因為Oracle需要知道對哪一塊的代碼進行異常處理。