스터디그룹 > ORACLE,MYSQL DB관련 > 동적 SQL(Dynamic SQL)의 사용방법

동적 SQL(Dynamic SQL)의 사용방법 - EXECUTE IMMEDIATE - | PL/SQL 2006.03.22 16:22

kj_choi 카페매니저   http://cafe.naver.com/pdsinc/74

관련 링크
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_pl09.htm *(일본 : 추천)

http://blog.naver.com/swinter8/130000770771 (한국 : 강추, 자세한 설명과 샘플소스 )

http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/appdev.920/a96624/11_dynam.htm#13131
(영어 : 추천, 사용설명)

위의 링크만 봐도 사용이 가능합니다만 저 같은 초보자를 위하여 부연 설명드립니다.

제가 만들고 싶었던것은 패키지 바디에 정의한 프로시져가 INPUT파라메타로 받은 정보로
테이블의 특정값을 갖는 데이타들을 갱신하되, 갱신 대상데이타가 없으면
에러로 처리하는 것이었습니다.
select 문에 대한 예는 많았는데 update할때 갱신결과를 어떻게 받아야 하는지 몰라서 정말 많이
고민했는데 --; SQL%ROWCOUNT 로 기냥 받아지더군요

<Sample 1>--------------------------------------------------------------------------
PROCEDURE UPD_PRG
(
i_up_time   IN DATE
,o_rtn OUT VARCHAR2
)

-- 상수선언
cn_pkg_name CONSTANT VARCHAR2(20) := 'UPD_PKG';

-- 변수선언
w_sql_data VARCHAR2(4096);
w_update_flag NUMBER := 0;
w_out VARCHAR2(3);

w_up_time DATE;
w_up_name VARCHAR2(60);

-- SQL문작성
w_sql_data := 'UPDATE TEST_CSV_TABLE'
|| ' SET '
||    'OUTPUT_STATUS = ''1'''
||    ' ,OUTPUT_YMD = ' || '''' || i_up_time || ''''
||    ' ,UPDATED_BY = ' || '''' || cn_pkg_name || ''''
|| ' WHERE '
||    'OUTPUT_STATUS = ''0''';

-- log
DBMS_OUTPUT.PUT_LINE('【L140】w_sql_data=[' || w_sql_data || ']');

-- Dynamic SQL 실행
EXECUTE IMMEDIATE w_sql_data;

/* 실행결과 검증*/

-- 갱신결과가 0건이면 대상 데이타가 없음으로 에러
IF SQL%ROWCOUNT = 0 THEN

-- log
DBMS_OUTPUT.PUT_LINE('SQL ROWCOUNT :' || SQL%ROWCOUNT);

-- out 파라메터에 해당 에러코드 설정
w_out := 'XXX';

-- 에러 처리
RAISE e_not_exist_Recode;

ELSE

DBMS_OUTPUT.PUT_LINE('SQL ROWCOUNT :' || SQL%ROWCOUNT);

-- out 파라메터에 정상처리 종료 설정
w_out := 'OOO';

END IF;
-------------------------------------------------------------------------------------

만약 어떤 특정 컬럼의 갱신여부를 확인하고 싶다면

<Sample 2>--------------------------------------------------------------------------

-- SQL문작성
w_sql_data := 'UPDATE TEST_CSV_TABLE'
|| ' SET '
||    'OUTPUT_STATUS = ''1'''
||    ' ,OUTPUT_YMD = ' || '''' || i_up_time || ''''
||    ' ,UPDATED_BY = ' || '''' || cn_pkg_name || ''''
|| ' WHERE '
||    'OUTPUT_STATUS = ''0'''
   || ' RETURNING OUTPUT_STATUS INTO :w_update_flag';

-- log
DBMS_OUTPUT.PUT_LINE('【L140】w_sql_data=[' || w_sql_data || ']');

-- Dynamic SQL 실행
EXECUTE IMMEDIATE w_sql_data
   RETURNING INTO w_update_flag;

여기서 "OUTPUT_STATUS" 라는 컬럼이 갱신되었다면 "w_update_flag"에는 "1"이라는 숫자가
반환되어 집니다. 갱신대상 데타가 없으면(갱신이 않되었기때문에) "0"이 반환됩니다.
-------------------------------------------------------------------------------------

만약 동적으로 갱신할 값이 변경된다고 한다면

<Sample 3>--------------------------------------------------------------------------

-- 루프를 돌아가면서 처리가 되겠지요..
w_up_time := 어디선가 얻어온 값을 셋팅;
w_up_name :=어디선가 얻어온 값을 셋팅;

-- SQL문작성
w_sql_data := 'UPDATE TEST_CSV_TABLE'
|| ' SET '
||    'OUTPUT_STATUS = ''1'''
||    ' ,OUTPUT_YMD = :val1'
||    ' ,UPDATED_BY = :val2'
|| ' WHERE '
||    'OUTPUT_STATUS = ''0''';

-- log
DBMS_OUTPUT.PUT_LINE('【L140】w_sql_data=[' || w_sql_data || ']');

-- Dynamic SQL 실행
EXECUTE IMMEDIATE w_sql_data
   USING   w_up_time, w_up_name;

-------------------------------------------------------------------------------------

프로시져 실행시키기
<Sample 4>--------------------------------------------------------------------------
EXECUTE IMMEDIATE 'BEGIN UPD2_PKG.UPD2_PRG(:a, :b, :c, :d, :e, :f, :g, :h, :i); END;'
USING IN w_data_a,
w_data_b,
w_data_c,
w_data_d,
w_data_e,
w_data_f,
w_data_g,
w_data_h,
   OUT    w_rtn_cd_i;

IF w_rtn_cd_i = success THEN
   - 성공했을때의 처리

ELSIF w_rtn_cd_i = not_found THEN
   - 리턴값이 특정 에러의 경우의 처리

ELSE
-- 기타 시스템에러 등
RAISE e_exception;
END IF;