꿈꾸는 개발자, DBA 커뮤니티 구루비

다이나믹 쿼리에서

by 김홍관 [2006.10.24 16:33:16]

많은 관심과 도움 감사 합니다.

전체 프로시져를 올리겠습니다.

아래와 같이 하면 정상 컴파일은 되는데 '추출행 없음'' 이 바로 나와버립니다.

분명히 건수가 존재하는 쿼리 거든요.

v_item_code_all := '1010';

이렇게 하드코딩으로 테스트 하면 제대로 1건이 나옵니다

그런데..

v_item_code_all := i_1||','||i_2||','||i_3||','||i_4||','||i_5;

이렇게 아규먼트를 붙여서 할려고 하면 안됩니다.

고수님들의 도움을 기다립니다.

CREATE OR REPLACE PROCEDURE ina (i_1 varchar2,

i_2 varchar2,

i_3 varchar2,

i_4 varchar2,

i_5 varchar2) IS

vCURSOR_ID                INTEGER;
vDUMMY                    INTEGER;
v_DynamicSQL            VARCHAR2(10000) := '';

v_item_code_all    varchar2(100);
v_a      varchar2(1);
v_item_year    varchar2(4);
v_mgroup     varchar2(2);
v_cnt    number := 0;

BEGIN

v_DynamicSQL := v_DynamicSQL||' SELECT to_char(1), ';
v_DynamicSQL := v_DynamicSQL||' decode(item_year, ''2004'',''2004'',''X''), ';
v_DynamicSQL := v_DynamicSQL||' mgroup ';
v_DynamicSQL := v_DynamicSQL||' from TITEM where item_code in (';
v_DynamicSQL := v_DynamicSQL||' :v_item_code_all';
v_DynamicSQL := v_DynamicSQL||')';
v_DynamicSQL := v_DynamicSQL||' AND item_year = ' ;
v_DynamicSQL := v_DynamicSQL||' :v_item_year';

v_item_code_all := i_1||','||i_2||','||i_3||','||i_4||','||i_5;

v_item_year := '2004';

BEGIN

vCURSOR_ID := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(vCURSOR_ID, v_DynamicSQL, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(vCURSOR_ID, ':v_item_code_all', v_item_code_all);

DBMS_SQL.BIND_VARIABLE(vCURSOR_ID, ':v_item_year', v_item_year);

      DBMS_SQL.DEFINE_COLUMN(vCURSOR_ID, 1, v_a, 1 );
      DBMS_SQL.DEFINE_COLUMN(vCURSOR_ID, 2, v_item_year,4 );
      DBMS_SQL.DEFINE_COLUMN(vCURSOR_ID, 3, v_mgroup,2 );

vDUMMY := DBMS_SQL.EXECUTE(vCURSOR_ID);

      LOOP

            IF DBMS_SQL.FETCH_ROWS(vCURSOR_ID) = 0 THEN
                  DBMS_OUTPUT.PUT_LINE(' 추출행 없음');
                  EXIT;
            END IF;

            DBMS_SQL.COLUMN_VALUE(vCURSOR_ID, 1, v_a );
            DBMS_SQL.COLUMN_VALUE(vCURSOR_ID, 2, v_item_year );
            DBMS_SQL.COLUMN_VALUE(vCURSOR_ID, 3, v_mgroup );

   v_cnt := v_cnt + 1;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(vCURSOR_ID);

    EXCEPTION
           WHEN OTHERS THEN
           DBMS_SQL.CLOSE_CURSOR(vCURSOR_ID);
    END;

END ina;
/

by 강정식 [2006.10.24 00:00:00]

제가 폼즈에서 작업한건데 이 부분을 보시면 참고가
될 것 같습니다.

v_deptno_10 NUMBER := 10;
v_deptno_20 NUMBER := 20;
v_deptno_30 NUMBER := 30;
v_deptno_40 NUMBER := 40;

v_status_flag NUMBER := 0; -- IN() 함수 받기 위한 구분자

v_where VARCHAR2(1000) := NULL;
w_deptno VARCHAR2(1000) := NULL;
w_empno VARCHAR2(1000) := NULL;
w_sal VARCHAR2(1000) := NULL;
w_hiredate VARCHAR2(1000) := NULL;
BEGIN
-- 기본 조회조건 생성
v_where := '1 = 1';

-- 체크박스로 조회조건 받은 구문 처리
/***************************************************************************/
w_deptno := ' AND DEPTNO IN (';

IF(:CONTROL.DEPTNO_10 = 'Y') THEN

w_deptno := w_deptno || v_deptno_10;
v_status_flag := 1;

END IF;

IF(:CONTROL.DEPTNO_20 = 'Y') THEN

IF(v_status_flag = 0) THEN
w_deptno := w_deptno || v_deptno_20;
ELSIF(v_status_flag = 1) THEN
w_deptno := w_deptno || ', ' || v_deptno_20;
END IF;

v_status_flag := 1;

END IF;

IF(:CONTROL.DEPTNO_30 = 'Y') THEN

IF(v_status_flag = 0) THEN
w_deptno := w_deptno || v_deptno_30;
ELSIF(v_status_flag = 1) THEN
w_deptno := w_deptno || ', ' || v_deptno_30;
END IF;

v_status_flag := 1;

END IF;

IF(:CONTROL.DEPTNO_40 = 'Y') THEN

IF(v_status_flag = 0) THEN
w_deptno := w_deptno || v_deptno_40;
ELSIF(v_status_flag = 1) THEN
w_deptno := w_deptno || ', ' || v_deptno_40;
END IF;

END IF;

w_deptno := w_deptno || ')';

IF(:CONTROL.DEPTNO_10 = 'Y' OR
:CONTROL.DEPTNO_20 = 'Y' OR
:CONTROL.DEPTNO_30 = 'Y' OR
:CONTROL.DEPTNO_40 = 'Y') THEN

v_where := v_where || w_deptno;

END IF;

/***************************************************************************/

-- 나머지 조회조건 처리
w_empno := ' AND EMPNO BETWEEN :CONTROL.EMPNO_FROM AND :CONTROL.EMPNO_TO';
w_sal := ' AND SAL BETWEEN :CONTROL.SAL_FROM AND :CONTROL.SAL_TO';
w_hiredate := ' AND HIREDATE BETWEEN TO_DATE(' || '''' || :CONTROL.HIREDATE_FROM || '''' || ', ''YYYY-MM-DD'')
AND TO_DATE(' || '''' || :CONTROL.HIREDATE_TO || '''' || ', ''YYYY-MM-DD'')';

--v_where := v_where || w_deptno;

IF(:CONTROL.EMPNO_FROM IS NOT NULL AND :CONTROL.EMPNO_TO IS NOT NULL) THEN
v_where := v_where || w_empno;
END IF;

IF(:CONTROL.SAL_FROM IS NOT NULL AND :CONTROL.SAL_TO IS NOT NULL) THEN
v_where := v_where || w_sal;
END IF;

IF(:CONTROL.HIREDATE_FROM IS NOT NULL AND :CONTROL.HIREDATE_TO IS NOT NULL) THEN
v_where := v_where || w_hiredate;
END IF;

MESSAGE(v_where);
SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, v_where);
END;

이 내용은 DEPT 테이블에서 DEPTNO를 체크조건으로
만든담에 체크된 값을 받아서 EMP테이블을 조회하는 로직입니다.
즉 동적으로 쿼리를 생성하는건데요.

중요하게 보실부분은 별표로 주석을 쳐논 부분인데
제 경우는 DEPTNO가 '10, 20, 30, 40' 4개이므로
이를 먼저 DECLARE에 설정해놓고
주석친부분 안에서 이를 IF ELSE로 처리해서
where조건을 변수에 저장했습니다.

혹시 보시고 궁금한점 있으시면 리플 달아주세요.
도움이 되셨길 바랍니다.

by 백해현 [2006.10.24 00:00:00]

v_item_code_all := i_1||','||i_2||','||i_3||','||i_4||','||i_5;
=>
v_item_code_all := ''''||i_1||''','''||i_2||''','''||i_3||''','''||i_4||''','''||i_5||'''' ;

by 백해현 [2006.10.24 00:00:00]

위와같이해도 안된다면 변수에다 담지 말고
Dynamic Query에 직접 붙이셔야 할것 같습니다

by 백해현 [2006.10.24 00:00:00]

v_item_code_all := ''''||i_1||''','''||i_2||''','''||i_3||''','''||i_4||''','''||i_5||'''' ;
을 먼저 기술하시고
v_DynamicSQL := v_DynamicSQL||v_item_code_all;

이렇게 해보시지요

by 김홍관 [2006.10.25 00:00:00]

감사합니다..
v_item_code_all := ''''||i_1||''','''||i_2||''','''||i_3||''','''||i_4||''','''||i_5||'''' ;
이렇게 붙이니까 되네요..

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변