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