如下是SQL Server中的存储过程,请教在Oracle中如何实现: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 ;
@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 ;
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
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
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
exit;
end if;
end if;
begin
Delete From Zy_Order_Details Where Quantity = 0 and BatchNo = @BatchNo;
exception
when others then
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
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
o_err_no := -1 ;
o_err_msg := '更新 [Zy_Base_BatchNo]时出错!' ;
end;
o_err_no := 0;
o_err_msg := '执行成功';
return o_err_no; end Zy_Make_SortDeliverLineID;不好意思,
有点不负责没测试...
格式差不多就是这样,
如有错误请各位指出来.
谢谢!
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;
declare
begin
exception
end;一共就这么几个块,声明,执行,例外
你对SQL那么熟,相信改成pl/sql也很容易的.