Oracle cursor

오라클에서 커서 사용하는 SP 샘플

create or replace PROCEDURE TEST IS o_ZIP_CD VARCHAR2(7) := ''; o_ZIP_SEQ VARCHAR2(3) := ''; o_JIBUN VARCHAR2(500) := ''; o_JI_SIDO VARCHAR2(50) := ''; o_JI_SIGUNGU VARCHAR2(50) := ''; o_JI_DONG VARCHAR2(50) := ''; o_JI_RI VARCHAR2(50) := ''; o_JI_OTHER VARCHAR2(300) := ''; ERR_USER_EXCEPTION EXCEPTION; BEGIN FOR C1 IN ( SELECT B.BRAN_CD , A.C09 AS ADDR FROM TEMP a , TEMP1 b WHERE a.c06 = b.bran_id_kg ) LOOP UPRO_ADDRESS(c1.ADDR, o_BRAN_CD, --담당지점 o_ZIP_CD, --우편번호 o_ZIP_SEQ, --우편번호SEQ o_JIBUN, --지번 주소 전체 o_JI_SIDO, --시도 o_JI_SIGUNGU, --시군구 o_JI_DONG, --읍,면,동 o_JI_RI, --리 ); UPDATE TM_ADDRESS SET DOWN_ZIP_CD =o_ZIP_CD , DOWN_ZIP_SEQ =o_ZIP_SEQ , DOWN_ADDR1 =NVL(o_JI_SIDO, '') || ' ' || NVL(o_JI_SIGUNGU, '') || ' ' || NVL(o_JI_DONG, '') || '' || NVL(o_JI_RI, '') , DOWN_ADDR2 =o_JI_OTHER WHERE BRAN_CD = C1.bran_cd; DBMS_OUTPUT.PUT_LINE(o_ZIP_CD || '-' || o_ZIP_SEQ || '-' || o_JIBUN || '-' || o_JI_SIDO || '-' || o_JI_SIGUNGU); END LOOP; EXCEPTION WHEN ERR_USER_EXCEPTION THEN ROLLBACK; END TEST ;

DECLARE vcINPUT_AMT NUMBER; vcBALANCE NUMBER; vcAMT NUMBER; BEGIN vcINPUT_AMT := 100000; vcBALANCE := vcINPUT_AMT; vcAMT := 0; FOR CUR IN ( SELECT CUST_CD , ACCT_DT , ACCT_YM , ACCT_AMT , IN_AMT , ACCT_AMT - IN_AMT AS BALANCE FROM TA_ACCT WHERE CUST_CD = '125487' AND ACCT_AMT > IN_AMT ) LOOP IF vcBALANCE > CUR.BALANCE THEN BEGIN vcAMT := CUR.BALANCE; vcBALANCE := vcBALANCE - vcAMT; END; ELSE BEGIN vcAMT := vcBALANCE; vcBALANCE :=0; END; END IF; UPDATE TA_ACCT SET IN_AMT = vcAMT WHERE CUST_CD = CUR.CUST_CD AND ACCT_DT = CUR.ACCT_DT; EXIT WHEN vcBALANCE = 0; END LOOP; END;