본문 바로가기

프로그램/oracle

[ORACLE]DBMS_SQL을 활용한 동적쿼리 실행

반응형

DBMS_SQL을 활용한 동적쿼리 실행


테스트 환경 : oracle 10g


대략의 문법 설명

dbms_sql.open_cursor : 커서 오픈

dbms_sql.is_open(커서) : 해당 커서의 open 여부, if에서 사용하여 체크

dbms_sql.parse(커서, 쿼리, 언어)

언어 

- dbms_sql.v6 : 버젼 6 의 행동? 잘 모르겠음

- dbms_sql.v7 : 버젼 7 의 해동? 잘 모르겠음

- dbms_sql.native : 프로그램이 연결된 데이터베이스의 행동


dbms_sql.describe_columns(커서, 컬럼갯수, 컬럼정보저장테이블객체)

컬럼정보저장테이블객체 

- dbms_sql.desc_tab

- dbms_sql.desc_tab2

- dbms_sql.desc_tab3

dbms_sql.desc_tab의 record의 구조체정보

TYPE desc_rec IS RECORD (

 col_type            BINARY_INTEGER := 0,

 col_max_len         BINARY_INTEGER := 0,

 col_name            VARCHAR2(32)   := '',

 col_name_len        BINARY_INTEGER := 0,

 col_schema_name     VARCHAR2(32)   := '',

 col_schema_name_len BINARY_INTEGER := 0,

 col_precision       BINARY_INTEGER := 0,

 col_scale           BINARY_INTEGER := 0,

 col_charsetid       BINARY_INTEGER := 0,

 col_charsetform     BINARY_INTEGER := 0,

 col_null_ok         BOOLEAN        := TRUE);

TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

dbms_sql.define_column(커서, 컬럼순서, 컬럼유형, 크기)

- select 절에서만 사용가능

- 컬럼유형

BINARY_DOUBLE

BINARY_FLOAT

BFILE

BLOB

CLOB CHARACTER SET ANY_CS

DATE

DSINTERVAL_UNCONSTRAINED

NUMBER

TIME_UNCONSTRAINED

TIME_TZ_UNCONSTRAINED

TIMESTAMP_LTZ_UNCONSTRAINED

TIMESTAMP_TZ_UNCONSTRAINED

TIMESTAMP_UNCONSTRAINED

UROWID

VARCHAR2 CHARACTER SET ANY_CS

YMINTERVAL_UNCONSTRAINED

user-defined object types

collections (VARRAYs and nested tables)

REFs

Opaque types


변수 := dbms_sql.execute(커서)

- 커서를 리턴함

ln_cnt := dbms_sql.fetch_rows(커서) : fetch 되는 row수, 보통 1 일듯


dbms_sql.column_value(커서, 순번, 변수)


dbms_sql.close_cursor(커서)


DBMS_SQL.DEFINE_ARRAY(커서, 쿼리에서 매핑할 컬럼의 순서, 담을 변수 유형, 한번에 가져올 갯수, table유형 중 제일 작은 index 순서)


DBMS_SQL.BIND_VARIABLE(커서, 바인드변수이름, 값)

DBMS_SQL.BIND_ARRAY(커서, 바인드변수이름, 값담은 table형 변수)


SELECT 실행 샘플


declare

    ln_cur integer;

    lv_qry varchar2(500);

    ln_col_cnt number;

    ln_tmp integer;

    

    oDescTab DBMS_SQL.DESC_TAB; --record의 table

    

    lv_col_val varchar2(2000);

begin

    ln_cur := dbms_sql.open_cursor; --커서ID 반환

    

    if dbms_sql.is_open(ln_cur) then

        dbms_output.put_line('OPEN');

    else 

        dbms_output.put_line('CLOSE');

    end if;

    

    lv_qry := 'select ''이승규'' as user_nm from dual union all select ''lsk'' as user_nm from dual';


    --쿼리 파싱

    dbms_sql.parse(ln_cur, lv_qry, dbms_sql.native);

    

    --컬럼 추출

    DBMS_SQL.DESCRIBE_COLUMNS(ln_cur, ln_col_cnt, oDescTab);

    

    dbms_output.put_line('col count : ' || ln_col_cnt);

    

    for cn in 1..ln_col_cnt loop /*oDescTab.count or ln_col_cnt*/

        dbms_output.put_line('col : ' || oDescTab(cn).col_name);

        

        dbms_sql.define_column(ln_cur, cn, 'VARCHAR2', 2000);

    end loop;

    

    ln_tmp := dbms_sql.execute(ln_cur);

    dbms_output.put_line('ln_tmp : ' || ln_tmp);

    

    while( dbms_sql.fetch_rows(ln_cur) > 0 ) loop

        for cn in 1..ln_col_cnt loop

            dbms_sql.column_value(ln_Cur, cn, lv_col_val);

            dbms_output.put_line('val : ' || lv_col_val);

        end loop;

    

    end loop;

    

    dbms_sql.close_cursor(ln_cur);

    

exception 

    when others then

        dbms_output.put_line('예외 시작');

        

        if DBMS_SQL.IS_OPEN(ln_cur) then

            dbms_output.put_line('예외 커서 확인 - OPEN');

            dbms_sql.close_cursor(ln_cur);

        end if;

end;


DML 샘플


declare

    ln_cur integer;

    lv_qry varchar2(500);

    ln_col_cnt number;

    ln_tmp integer;

    

    oDescTab DBMS_SQL.DESC_TAB; --record의 table

    

    lv_col_val varchar2(2000);

begin

    ln_cur := dbms_sql.open_cursor; --커서ID 반환

    

    if dbms_sql.is_open(ln_cur) then

        dbms_output.put_line('OPEN');

        null;

    else 

        dbms_output.put_line('CLOSE');

        null;

    end if;

    

    lv_qry := 'insert into temi_label_lot(lot) values (''kkk'')';


    --쿼리 파싱

    dbms_sql.parse(ln_cur, lv_qry, dbms_sql.native);

    

    ln_tmp := dbms_sql.execute(ln_cur);

    

    dbms_sql.close_cursor(ln_cur);

    

exception 

    when others then

        dbms_output.put_line('예외 시작');

        

        if DBMS_SQL.IS_OPEN(ln_cur) then

            dbms_output.put_line('예외 커서 확인 - OPEN');

            dbms_sql.close_cursor(ln_cur);

        end if;

end;


SELECT를 테이블 활용


declare

    ln_cur integer;

    lv_qry varchar2(500);

    ln_col_cnt number;

    ln_tmp integer;

    

    oDescTab DBMS_SQL.DESC_TAB; --record의 table

    

    lt_col_val DBMS_SQL.VARCHAR2_TABLE;

    

    lv_col_val varchar2(2000);

    

    ln_cnt number;

    

    ln_idx number := -10;

begin

    ln_cur := dbms_sql.open_cursor; --커서ID 반환

    

    if dbms_sql.is_open(ln_cur) then

        dbms_output.put_line('OPEN');

    else 

        dbms_output.put_line('CLOSE');

    end if;

    

    lv_qry := 'select ''이승규'' as user_nm from dual union all select ''lsk'' as user_nm from dual';


    --쿼리 파싱

    dbms_sql.parse(ln_cur, lv_qry, dbms_sql.native);

    

    DBMS_SQL.DEFINE_ARRAY(ln_cur, 1, lt_col_val, 10, 1); --커서, 쿼리에서 매핑할 컬럼의 순서, 담을 변수 유형, 한번에 가져올 갯수, table유형 중 제일 작은 index 순서


--    ln_tmp := dbms_sql.execute(ln_cur);

--    dbms_output.put_line('ln_tmp : ' || ln_tmp);


    ln_cnt := dbms_sql.execute(ln_cur);    


    loop

        ln_cnt := dbms_sql.fetch_rows(ln_cur);

        

        dbms_output.put_line('ln_cnt : ' || ln_cnt);

        

        DBMS_SQL.COLUMN_VALUE(ln_cur, 1, lt_col_val);

        

        exit when ln_cnt != 10;

    end loop;

    

    for cn in 1..lt_col_val.count loop

        dbms_output.put_line('cn : ' || cn || ', value : ' || lt_col_val(cn));

        dbms_output.put_line('cn : ' || cn);

    end loop;


    dbms_sql.close_cursor(ln_cur);

    

exception 

    when others then

        dbms_output.put_line('예외 시작');

        

        if DBMS_SQL.IS_OPEN(ln_cur) then

            dbms_output.put_line('예외 커서 확인 - OPEN');

            dbms_sql.close_cursor(ln_cur);

        end if;

end;


DML 에 BIND


declare

/*

bind 예제

*/

    ln_cur integer;

    lv_qry varchar2(500);

    ln_col_cnt number;

    ln_tmp integer;

    

    oDescTab DBMS_SQL.DESC_TAB; --record의 table

    

    lt_col_val DBMS_SQL.VARCHAR2_TABLE;

    

    lv_col_val varchar2(2000);

    

    ln_cnt number;

    

    ln_idx number := -10;

begin

    ln_cur := dbms_sql.open_cursor; --커서ID 반환

    

    if dbms_sql.is_open(ln_cur) then

        dbms_output.put_line('OPEN');

    else 

        dbms_output.put_line('CLOSE');

    end if;

    

    lv_qry := 'insert into temi_label_lot(lot) values (:lot)';


    --쿼리 파싱

    dbms_sql.parse(ln_cur, lv_qry, dbms_sql.native);

    

    DBMS_SQL.BIND_VARIABLE(ln_cur, ':lot', 'jjj');    


    ln_cnt := dbms_sql.execute(ln_cur);


    dbms_sql.close_cursor(ln_cur);

    

    commit;

    

exception 

    when others then

        dbms_output.put_line('예외 시작');

        

        if DBMS_SQL.IS_OPEN(ln_cur) then

            dbms_output.put_line('예외 커서 확인 - OPEN');

            dbms_sql.close_cursor(ln_cur);

        end if;

end;


DML 에 TABEL형태로 BIND


declare

/*

bind 예제

*/

    ln_cur integer;

    lv_qry varchar2(500);

    ln_col_cnt number;

    ln_tmp integer;

    

    oDescTab DBMS_SQL.DESC_TAB; --record의 table

    

    lt_col_val DBMS_SQL.VARCHAR2_TABLE;

    

    lv_col_val varchar2(2000);

    

    ln_cnt number;

    

    ln_idx number := -10;

begin

    ln_cur := dbms_sql.open_cursor; --커서ID 반환

    

    if dbms_sql.is_open(ln_cur) then

        dbms_output.put_line('OPEN');

    else 

        dbms_output.put_line('CLOSE');

    end if;

    

    for cn in 1..10 loop

        lt_col_val(cn) := cn || 'k';

    end loop;

    

    lv_qry := 'insert into temi_label_lot(lot) values (:lot)';


    --쿼리 파싱C

    dbms_sql.parse(ln_cur, lv_qry, dbms_sql.native);

    

    DBMS_SQL.BIND_ARRAY(ln_cur, ':lot', lt_col_val);    


    ln_cnt := dbms_sql.execute(ln_cur);


    dbms_sql.close_cursor(ln_cur);

    

    commit;

    

exception 

    when others then

        dbms_output.put_line('예외 시작');

        

        if DBMS_SQL.IS_OPEN(ln_cur) then

            dbms_output.put_line('예외 커서 확인 - OPEN');

            dbms_sql.close_cursor(ln_cur);

        end if;

end;



참고url

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i996891

http://www.gurubee.net/lecture/1146