데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

EXECUTE IMMEDIATE 나 OPEN FOR 를 사용하는 경우와

dbms_sql 패키지를 사용하는 경우가 있습니다.

EXECUTE IMMEDIATE의 경우 using 문을 사용하여 바인딩하며 순서 바인딩이 됩니다.

ex)

DECLARE

a_null CHAR(1); -- Set to NULL automatically at run time

BEGIN

EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'

USING a_null;

END;

OPEN FOR도 마찬가지입니다.

ex) --아래는 오라클에서 긁어온 것인데.... 에러 시 커서를 닫는 등의 세부 처리가 안되어 있네요...

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

v_emp_cursor EmpCurTyp;

emp_record employees%ROWTYPE;

v_stmt_str VARCHAR2(200);

v_e_job employees.job%TYPE;

BEGIN

-- Dynamic SQL statement with placeholder:

v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

-- Open cursor & specify bind variable in USING clause:

OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

-- Fetch rows from result set one at a time:

LOOP

FETCH v_emp_cursor INTO emp_record;

EXIT WHEN v_emp_cursor%NOTFOUND;

END LOOP;

-- Close cursor:

CLOSE v_emp_cursor;

END;

dbms_sql은 좀 복잡합니다.

그런데 그 대신 명칭 바인딩이 가능합니다.

DBMS_SQL.BIND_VARIABLE 이라는 것으로 합니다.

ex) --아래는 오라클에서 긁어온 것인데.... 에러 시 커서를 닫는 등의 세부 처리가 안되어 있네요...

--그리고 DBMS_SQL.TO_REFCURSOR 를 사용하여 커서 번호를 커서로 변환하여 Native Dynamic SQL과 섞어서 사용하네요

CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);

CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE do_query_1 (

placeholder vc_array,

bindvars vc_array,

sql_stmt VARCHAR2

)

TYPE curtype IS REF CURSOR;

src_cur curtype;

curid NUMBER;

bindnames vc_array;

empnos numlist;

depts numlist;

ret NUMBER;

isopen BOOLEAN;

BEGIN

-- Open SQL cursor number:

curid := DBMS_SQL.OPEN_CURSOR;

-- Parse SQL cursor number:

DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);

bindnames := placeholder;

-- Bind variables:

FOR i IN 1 .. bindnames.COUNT LOOP

DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));

END LOOP;

-- Run SQL cursor number:

ret := DBMS_SQL.EXECUTE(curid);

-- Switch from DBMS_SQL to native dynamic SQL:

src_cur := DBMS_SQL.TO_REFCURSOR(curid);

FETCH src_cur BULK COLLECT INTO empnos, depts;

-- This would cause an error because curid was converted to a REF CURSOR:

-- isopen := DBMS_SQL.IS_OPEN(curid);

CLOSE src_cur;

END;

김흥수(protokhs)님이 2015-08-17 16:53에 작성한 댓글입니다.