064 - [Oracle PL/SQL] Dynamic SQL - DBMS_SQL Package VS Execute immediate

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

다이나믹 SQL을 구현하는 방법은 다양하다, 아래 방법중에 편한 방법을 사용하면 된다.

 

• 테스트용 테이블을 아래와 같이 준비

drop table emp1;
create table emp1 ( emp_id number );

drop table emp2;
create table emp2 ( emp_id number );

begin
    insert into emp1 values (1);
    insert into emp1 values (2);
    insert into emp1 values (3);
    insert into emp2 values (1);
    insert into emp2 values (2);
    insert into emp2 values (3);
    commit;
end;

select * from emp1;
select * from emp2;
---------------------------------
    EMP_ID
----------
         1
         2
         3


    EMP_ID
----------
         1
         2
         3

 

 

 

• execute immediate 를 이용한 다이나믹 SQL 샘플 구문

create or replace procedure delete_any_table
    ( p_table_name varchar2 )
is
    v_no_rec number;
begin
    execute immediate 'delete from '||p_table_name;
    v_no_rec:=sql%rowcount;
    commit; --same rules for commit and rollback
    dbms_output.put_line(v_no_rec ||' record(s) deleted form '||p_table_name );
end;

execute delete_any_table('emp1');
select * from emp1;
---------------------------------------------

Procedure DELETE_ANY_TABLE compiled

PL/SQL procedure successfully completed.

no rows selected

 

 

 

• DBMS_SQL 을 이용하여 다이나믹 SQL 샘플 코드 - execute immediate 와 동일한 동작을 한다

create or replace procedure delete_any_table2
    ( p_table_name varchar2 )
is
    v_no_rec number;
    v_cur_id number;
begin
    v_cur_id:=dbms_sql.open_cursor;
    dbms_sql.parse(v_cur_id,'delete from '||p_table_name, dbms_sql.native);
    v_no_rec:=dbms_sql.execute(v_cur_id);
    dbms_output.put_line(v_no_rec||' record(s) deleted form '||p_table_name );
    commit;
end;

select * from emp2;

execute delete_any_table2('emp2');

select * from emp2;
---------------------------------------------
Procedure DELETE_ANY_TABLE2 compiled

    EMP_ID
----------
         1
         2
         3

PL/SQL procedure successfully completed.

no rows selected

 

 

 

delete emp1;

create or replace procedure add_rows 
    ( p_table_name varchar2,p_value number )
is
begin
    EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values(:1) ' using p_value;
end;
/
EXECUTE add_rows('emp1',10);
/
SELECT * FROM emp1;
------------------------------------------------
Procedure ADD_ROWS compiled

PL/SQL procedure successfully completed.

    EMP_ID
----------
        10

 

 

 

• DBMS_SQL - into 구현 샘플 코드

create or replace procedure add_rows2
    ( p_table_name varchar2,p_value number )
is
    v_no_rec number;
    v_cur_id number;
    V_INSERT VARCHAR2(1000):='INSERT INTO '||p_table_name||' VALUES (:ID)';
begin
    v_cur_id:=dbms_sql.open_cursor;
    dbms_sql.parse(v_cur_id,V_INSERT ,dbms_sql.native);
    dbms_sql.BIND_VARIABLE (v_cur_id,':ID',p_value);
    v_no_rec:=dbms_sql.execute(v_cur_id);
    dbms_output.put_line(v_no_rec ||' record(s) INSERTED TO '||p_table_name );
    commit;
end;
/
EXECUTE add_rows2('emp1',90);
/
SELECT * FROM emp1;
------------------------------------------------
Procedure ADD_ROWS2 compiled

PL/SQL procedure successfully completed.

    EMP_ID
----------
        10
        90