我有一Oracle Job 调用一存储过程, 存储过程里面有一个insert和一个Update语句
Inser Into TableA()
values(....);Update TableB
Set Flag = 'Y'
where Flag = 'N';每次系统自动执行完后,Inser语句没有执行, Update却成功了! 而手动执行此job时,又会一切正常!
估计是哪里的参数设置有问题,还请大家帮忙解决!
Inser Into TableA()
values(....);Update TableB
Set Flag = 'Y'
where Flag = 'N';每次系统自动执行完后,Inser语句没有执行, Update却成功了! 而手动执行此job时,又会一切正常!
估计是哪里的参数设置有问题,还请大家帮忙解决!
is
R_TickNumber nvarchar2(20);
R_WareHouse nvarchar2(4);
R_Location nvarchar2(4);
e_exception EXCEPTION;
R_ADJ_TEMP INVADJTemp@bachadd %ROWTYPE;
CURSOR C_Adj_Temp is select * from InvADJTemp@bachadd where Flag = 'N';
R_PostDate nvarchar2(8);
R_OrderDate nvarchar2(8);
R_StockUom nvarchar2(8);
R_PurUom nvarchar2(8);
R_Convert number;
R_Amount number;
begin
R_PostDate := to_char(sysdate,'YYYY') || to_char(sysdate,'MM')
|| to_char(sysdate,'DD'); select 'ADJ' || to_char(ADJID.NEXTVAL) into R_TickNumber from dual;
OPEN C_Adj_Temp;
Loop
FETCH C_Adj_Temp INTO R_ADJ_TEMP;
EXIT WHEN C_Adj_Temp%NOTFOUND; R_Location := Rtrim(GetLocationByDepartment(R_ADJ_TEMP.room_sn)); R_WareHouse := GetWareHouse(R_Location);
R_Amount := R_ADJ_TEMP.amount;
R_OrderDate := to_char(R_ADJ_TEMP.Get_Date,'YYYY') || to_char(R_ADJ_TEMP.Get_Date,'MM')
|| to_char(R_ADJ_TEMP.Get_Date,'DD'); ---把单位转换成库存单位
R_StockUom := Rtrim(GetSuom(R_ADJ_TEMP.itemnumber));
R_PurUom := Rtrim(GetPuom(R_ADJ_TEMP.itemnumber));
R_Convert := GetConvert(R_ADJ_TEMP.itemnumber); if Rtrim(R_ADJ_TEMP.unit) = R_StockUom then
R_PurUom := Rtrim(GetPuom(R_ADJ_TEMP.itemnumber));
else
if Rtrim(R_ADJ_TEMP.unit) = R_PurUom then --如果等于,转换数目
R_Amount := R_Amount * R_Convert; --转换数目
--R_Amount := 0;
else
R_Amount := 0; --把数目变为0
R_StockUom := R_ADJ_TEMP.unit; ---单位不存在,把原来的单位传过去
end if;
end if; Insert into invithi(TID, TPROD, TTYPE, TWHS,
TLOC,TQTY,Tums,Tump, Tumcn, TCOM, FLAG,
THCDT, TRES, ORDERID, HISID, oqty,
ouom, owhs, oloc, orderdepartment, executedepartment, oorderdepartment,
oexecutedepartment, OrderDate)
values('TM', R_ADJ_TEMP.ItemNumber, 'I', R_WareHouse,
R_Location, R_Amount, R_StockUom, R_PurUom,R_Convert, R_TickNumber, 'N',
R_PostDate, '00', R_ADJ_TEMP.order_id, R_ADJ_TEMP.HIS_ID, R_ADJ_TEMP.Amount,
R_ADJ_TEMP.unit, R_WareHouse, R_Location, R_ADJ_TEMP.orderdepartment,
R_ADJ_TEMP.executedepartment, R_ADJ_TEMP.orderdepartment,
R_ADJ_TEMP.executedepartment, R_OrderDate);
update InvADJTemp@bachadd set flag = 'Y', Post_Date = sysdate
where flag = 'N' and HIS_ID = R_ADJ_TEMP.HIS_ID;
End Loop;
还有,循环体里面,是否应该加commit! 想想,全是问题呀,真是写程序不难,写个好程序还真不容易
对于事务的处理,自己搜索一下...