我的SQL的ＳＰ没有输出的,给您个ＯＲＡＣＬＥ的例子吧.
CREATE PROCEDURE SP_PTPERMIT(
COMPIDI IN TPRODUCTTRANSFER.COMPID%TYPE,
PTNOI IN TPRODUCTTRANSFER.PTNO%TYPE,
USER IN TPRODUCTTRANSFER.USERNAME%TYPE,
RET OUT CHAR
) IS
VSTOREOUTNO TPRODUCTTRANSFER.STOREOUTNO%TYPE;
VSTOREINNO TPRODUCTTRANSFER.STOREINNO%TYPE;
VPERMIT TPTRANSFERDETAILS.WPERMIT%TYPE;
VLOCK TPTRANSFERDETAILS.WPERMIT%TYPE;
cursor C_PTD IS
SELECT PRODUCTNO,ISNORMAL,WEIGHT,WPERMIT
FROM TPTRANSFERDETAILS
WHERE COMPID=COMPIDI AND PTNO=PTNOI AND WEIGHT>0;
BEGIN
RET:='Y';
SELECT STOREOUTNO,STOREINNO INTO VSTOREOUTNO,VSTOREINNO
FROM TPRODUCTTRANSFER
WHERE COMPID=COMPIDI AND PTNO=PTNOI;
IF VSTOREINNO<>' ' AND VSTOREINNO IS NOT NULL THEN
FOR R IN C_PTD LOOP
IF R.WEIGHT>R.WPERMIT THEN
RET:='N';
EXIT;
END IF;
END LOOP ;
ELSE
FOR R IN C_PTD LOOP
SELECT WPERMIT INTO VPERMIT
FROM TORDERDETAILS OD,TDELIVERIES D,TPRODUCTTRANSFER P
WHERE P.PTNO=PTNOI AND P.COMPID=COMPIDI
AND D.DELIVERYNO=P.DELIVERYNO AND D.COMPID=P.COMPID
AND OD.ORDERNO=D.ORDERNO AND OD.COMPID=D.COMPID
AND OD.PRODUCTNO=R.PRODUCTNO AND OD.ISNORMAL=R.ISNORMAL;
SELECT WLOCK INTO VLOCK
FROM VNOTICELOCK OD,TDELIVERIES D,TPRODUCTTRANSFER P
WHERE P.PTNO=PTNOI AND P.COMPID=COMPIDI
AND D.DELIVERYNO=P.DELIVERYNO AND D.COMPID=P.COMPID
AND OD.ORDERNO=D.ORDERNO AND OD.COMPID=D.COMPID
AND OD.PRODUCTNO=R.PRODUCTNO AND OD.ISNORMAL=R.ISNORMAL;
IF VLOCK IS NULL THEN
VLOCK:=0;
END IF;
IF R.WEIGHT>(VPERMIT-VLOCK) THEN
RET:='N';
EXIT;
END IF;
END LOOP ;
END IF;
IF RET='Y' THEN
UPDATE TPRODUCTTRANSFER
SET STATUS='F',USERNAME=USER
WHERE PTNO=PTNOI AND COMPID=COMPIDI ;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
WHEN OTHERS THEN
ROLLBACK;
END SP_PTPERMIT;
cdswbook.close;
cdswbook.params.parambyname('compidi').value:=CDSpt['compid'];
cdswbook.params.parambyname('ptnoi').value:=CDSpt['ptno'];
cdswbook.Execute;
cdswbook.FetchParams;
//showmessage(cdswbook.params.parambyname('compidi').asstring+' '+cdswbook.params.parambyname('ptnoi').asstring+' '+cdswbook.params.parambyname('RET').asstring);
// showmessage(cdswbook.params.parambyname('RET').asstring+' A '+COPY(cdswbook.params.parambyname('RET').asstring,1,1)+' A ');
if COPY(cdswbook.params.parambyname('RET').asstring,1,1)='N' then
BEGIN
if MessageDlg('已订量不足，您确认要生效该单吗?',
mtConfirmation, [mbYes, mbNo], 0) = mrNo then
abort;
END;
