oracle 游标格式,oracle 标准游标格式

CREATE OR REPLACE PROCEDURE JL_GSYYJC_CBS_INS_SP(P_CBS_CBRQ IN DATE,P_IOPOINT_ID IN NUMBER) IS

V_B_ID NUMBER;

V_CNT NUMBER;

CURSOR V_RS_B IS

SELECT B_ID FROM JC_B_TB WHERE B_WZBM = P_IOPOINT_ID;

BEGIN

SAVEPOINT V_SP_00000;

OPEN V_RS_B;

LOOP

FETCH V_RS_B

INTO V_B_ID;

EXIT WHEN V_RS_B%NOTFOUND;

SELECT COUNT(*)

INTO V_CNT

FROM JL_CBS_TB

WHERE B_ID = V_B_ID

AND CBS_CBRQ = P_CBS_CBRQ;

IF V_CNT = 0 THEN

INSERT INTO JL_CBS_TB

(CBS_BM, B_ID, CBS_YSRQ, CBS_CBRQ)

VALUES

(SEQJL_CBS.NEXTVAL, V_B_ID, P_CBS_CBRQ, P_CBS_CBRQ);

END IF;

END LOOP;

CLOSE V_RS_B;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK TO SAVEPOINT V_SP_00000;

RAISE_APPLICATION_ERROR(-20001,

'STS_ERROR: JL_GSYYJC_CBS_INS_SP ERROR HAPENED' ||

SQLERRM);

END JL_GSYYJC_CBS_INS_SP;