오라클 커서 사용

CREATE OR REPLACE PROCEDURE UPDATE_RV_INFO

IS

CURSOR CUR1 IS

 

SELECT * FROM (

 SELECT LEVEL AS LVL, RV_LVL, RV_CODE 

        FROM RV_INFO 

        START WITH PRTS_RV_CODE = 'ROOT' 

        CONNECT BY PRIOR RV_CODE = PRTS_RV_CODE

        ) SUB

        WHERE SUB.RV_LVL != SUB.LVL; 

V_LVL NUMBER;

V_RV_LVL NUMBER;

V_RV_CODE VARCHAR2(12);

BEGIN

FOR CUR IN CUR1 LOOP

DBMS_OUTPUT.PUT_LINE('CUR.LVL:'||CUR.LVL);

DBMS_OUTPUT.PUT_LINE('CUR.RV_LVL:'||CUR.RV_LVL);

DBMS_OUTPUT.PUT_LINE('CUR.RV_CODE:'||CUR.RV_CODE);

UPDATE RV_INFO

SET RV_LVL = CUR.LVL

WHERE RV_CODE = CUR.RV_CODE;

END LOOP;

END;