059 - [Oracle PL/SQL] Dynamic SQL

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

 

 

 

 

 

 

 

 

 

 

 

 

• 테스트 환경 준비

# 새로운 테이블을 생성

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

 

 

 

• 다이나믹 쿼리가 적용된 프로시져 생성

create or replace procedure delete_any_table
    ( p_table_name varchar2)
is
    v_no_rec number;
begin
--execute immediate 명령어를 이용한 다이나믹 쿼리 실행
    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 );
    
    EXCEPTION
        when others THEN
        dbms_output.put_line('There is no table name: '||p_table_name);
end;
-----------------------------------------------
Procedure DELETE_ANY_TABLE compiled

 

 

 

• 프로시져 실행 및 확인

execute delete_any_table('emp1');
-----------------------------------------------
3 record(s) deleted form emp1


select * from emp1;
-----------------------------------------------
no rows selected

 

 

 

# 존재하지 않는 테이블을 삭제할때 예외 처리함

execute delete_any_table('dfd');
-----------------------------------------------------------
There is no table name: dfd

 

 

 

• DDL/DCL 을 plsql에서 실행하면 오류가 발생한다

--DDL and DCL not allowed in PLSQL block
--we also use execute immediate
drop table emp3;

begin
    create table emp3 ( emp_id number );
end;
-------------------------------------------------------------
Error report -
ORA-06550: line 2, column 5:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge
   json_exists json_value json_query json_object json_array
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

 

 

 

• 아래처럼 실행 가능하다

begin
--no need for ; at end of the statement
    execute immediate 'create table emp3 ( emp_id number )' ;
end;
-------------------------------------------------------------
PL/SQL procedure successfully completed.



select * from emp3;
-------------------------------------------------------------
no rows selected

 

 

 

• 다이나믹 sql을 이용하여 DDL/DCL 이 포함된 프로시져를 만들어보자.

create or replace procedure create_any_table
    (p_table_name varchar2,p_details varchar2)
is
    v_details varchar2(30000);

begin
    v_details:='create table '||p_table_name||' ('||p_details||')' ;
    dbms_output.put_line(v_details);
    execute immediate v_details;
end;



execute create_any_table ('emp4','emp_id number, name varchar2(100)' );
-------------------------------------------------------------
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "HR.CREATE_ANY_TABLE", line 9
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges



# 권한이 부족하여 오류가 발생하니, 아래처럼 권한을 부여해야 한다
/*
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> grant create table to hr;

Grant succeeded.

SQL>
*/


# 권한 부여이후 프로시져가 정상적으로 실행된다.
execute create_any_table ('emp4','emp_id number, name varchar2(100)' );
-------------------------------------------------------------
PL/SQL procedure successfully completed.



select * from emp4;
-------------------------------------------------------------
no rows selected