ORACLE截断字符串

文章作者 100test 发表时间 2008:04:19 12:50:08
来源 100Test.Com百考试题网

create or replace procedure ModifyLadingItemPack
(
ASoditemguid varchar2, --合同电子仓单明细GUID
ALadingitemGUID varchar2, --提单子项GUID
ACDResID varchar2, --电子仓单号
APackageIDList varchar2, --仓单捆包详细GUID列表
Aladingnum float,
Aladingpieces float,
AReturnValue out integer --返回值:
)
is
ALadingItem_PackagesGUID varchar(32).
ATempIDList varchar2(4000).
AID varchar2(32).
APackageWeight float.
APackagePiece float.
ATotalPackWeight float.
begin
AReturnValue := 1.
ATempIDList := APackageIDList.
APackageWeight :=0.
APackagePiece :=0.

---------------------------------更新合同电子仓单明细---------------------------

while ( INSTR(ATempIDList,: ) > 0 ) or ( LENGTH(ATempIDList ) > 0 ) loop
if INSTR(ATempIDList,: ) > 0 then

0select sys_guid() into ALadingItem_PackagesGUID from dual.
AID := SUBSTR(ATempIDList ,1,INSTR( ATempIDList,:) - 1 ).
ATempIDList := SUBSTR(ATempIDList,INSTR( ATempIDList,:) 1 , LENGTH(ATempIDList) - INSTR(ATempIDList,:)).

---------------------------------提单捆包明细----------------------------------
insert into HT_LadingItem_packages(sGUID, sLadingItemGUID, sCDResID, sPackageID)
values(ALadingItem_PackagesGUID, ALadingitemGUID, ACDResID,AID).

---------------------------------更新仓单捆包明细状态---------------------------
UPDATE ZY_Packages SET state = 5 WHERE SGUID = AID.

0select fWeight into ATotalPackWeight from ZY_Packages where sGUID=AID.

APackageWeight:=APackageWeight ATotalPackWeight.
APackagePiece:=APackagePiece 1.

else
AID := ATempIDList.
ATempIDList := .
end if.
end loop.

0update HT_SODItem set fLadingNum=fLadingNum APackageWeight,fLadingPieces=fLadingPieces APackagePiece
where sGUID=ASoditemguid.

COMMIT.
EXCEPTION
WHEN OTHERS THEN BEGIN
AReturnValue := 99.
ROLLBACK.
END.
end.