[PL/SQL] FOR LOOP 구현하기

CREATE OR REPLACE PROCEDURE SP_WORK (
      IN_OPERATION   IN VARCHAR2
    , IN_USER_JOB    IN VARCHAR2
    , RETURN_CODE    IN OUT VARCHAR2
    , ERROR_MESSAGE  IN OUT VARCHAR2
) IS

BEGIN

RETURN_CODE   := 0;
ERROR_MESSAGE := '';

IF IN_OPERATION = 'CREATE' THEN
	
    FOR USERS IN (
    	SELECT 
               USER_ID
             , USER_NAME
             , USER_GENDER
             , USER_JOB
          FROM USER_TABLE
         WHERE USER_JOB = IN_USER_JOB
    ) LOOP
    	
        DBMS_OUTPUT.PUT_LINE(USERS.USER_NAME);
        
        MERGE INTO USER_TABLE U
        USING DUAL
           ON (U.USER_NAME = USERS.NAME AND U.USER_GENDER = USERS.GENDER)
         
         WHEN MATCHED THEN
              -- 중복데이터 처리
              UPDATE SET
                     U.USER_JOB = IN_USER_JOB
         
         WHEN NOT MATCHED THEN
              -- 신규데이터 처리
              INSERT (
                   U.USER_NAME
                  ,U.USER_JOB
                  ,U.USER_GENDER
              ) VALUES (
                   USERS.USER_NAME
                  ,USERS.USER_JOB
                  ,USERS.USER_GENDER
              );
    END LOOP;
    
END IF;

EXCEPTION WHEN OTHERS THEN
    RETURN_CODE := -1;
    ERROR_MESSAGE := 'SOME ERROR';
END;

 

DBMS_OUT 출력을 위해서는

SET SERVEROUTPUT ON;

명령을 입력하고 

SQL DEVELOPER 기준 보기 > DBMS 출력에서 확인할 수 있습니다.