DBMS_SQL

개요

DBMS_SQL은 PL/pgSQL로 데이터 조작어(DML: Data Manipulation Language, 이하 DML), 데이터 정의어(DDL: Data Definition Language) 등 Dynamic SQL 문장을 사용하기 위한 패키지입니다.

Dynamic SQL 문장은 PL/pgSQL의 소스 안에 SQL 문장을 직접 사용하지 않고 나중에 PL/pgSQL 구문을 실행할 때, 문자열로 전달하여 파싱되는 SQL 문장을 말합니다. 따라서 PL/pgSQL 프로시저/함수를 생성할 때 존재하지 않았던 테이블에 대해 SELECT를 실행할 수도 있고, CREATE TABLE, DROP TABLE 등의 DDL 문장도 수행할 수 있습니다.

DBMS_SQL 패키지로 Dynamic SQL 문장을 실행하는 과정은 다음과 같습니다.

1. OPEN_CURSOR 함수를 이용하여 커서를 열고, 커서 ID를 얻습니다.

2. 커서를 구분하기 위해 앞에서 얻은 커서 ID를 다른 함수의 파라미터로 전달합니다.

3. Dynamic SQL 문장을 파싱하기 위해 PARSE 함수를 호출합니다. 만약 실행할 Dynamic SQL 문장 안에 바인드해야 할 변수가 있는 경우 BIND_VARIABLE 함수를 호출해 변수를 바인드합니다.

4. 실행 결과를 가져오기 전에 결과 컬럼의 타입을 정의하기 위해 DEFINE_COLUMN 함수를 호출합니다.

5. EXECUTE 함수를 이용하여 파싱된 SQL 문장을 실행합니다.

6. 실행한 결과를 fetch하기 위해 FETCH_ROWS 함수를 실행합니다. fetch한 결과를 원하는 변수로 가져오기 위해서는 COLUMN_VALUE 함수를 호출합니다.

7. 모든 수행을 끝낸 후 커서를 종료하기 위해 CLOSE_CURSOR 함수를 실행합니다.

함수 / 프로시저	반환 타입	설명
BIND_VARIABLE	n/a	주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값을 바인딩 해주는 프로시저입니다.
BIND_ARRAY	n/a	주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값들의 집합을 바인딩 해주는 프로시저입니다.
CLOSE_CURSOR	n/a	주어진 커서를 닫는 프로시저입니다.
COLUMN_VALUE	n/a	Fetch한 컬럼의 값을 가져오는 프로시저입니다.
DEFINE_COLUMN	n/a	Fetch될 컬럼의 타입을 정의하는 프로시저입니다.
DEFINE_ARRAY	n/a	Fetch될 컬럼 집합의 타입을 정의하는 프로시저입니다.
EXECUTE	BIGINT	주어진 커서를 실행하는 함수입니다.
EXECUTE_AND_FETCH	INTEGER	주어진 커서에 대하여 EXECUTE와 FETCH_ROWS를 수행하는 함수입니다.
FETCH_ROWS	INTEGER	주어진 커서 쿼리의 결과 row를 fetch하는 함수입니다.
IS_OPEN	BOOL	열려 있는 커서인지 여부를 체크하는 함수입니다.
LAST_ROW_COUNT	INTEGER	현재 수행중인 문장의 fetch된 row 수의 총합을 구하는 함수입니다.
OPEN_CURSOR	INTEGER	커서를 새로이 열고 매칭된 커서 번호를 리턴하는 함수입니다.
PARSE	n/a	주어진 SQL 문장을 파싱하는 프로시저입니다.

---

권한

설치는 super user에 이루어져야 하고 public으로 공개되어 있습니다.

---

유의사항

프로시저/함수나 anonymous block 내에서 DBMS_SQL 패키지를 이용하여 DML문 수행과 COMMIT/ROLLBACK을 같이 사용할 경우 EXCEPTION절을 사용하면 postgreSQL 특징에 의하여 오류가 발생한다. 따라서 COMMIT/ROLLBACK을 동일 프로시저/함수나 anonymous block 내에 삽입하고 싶을 경우 EXCEPTION절을 사용하지 않도록 합니다.

create table src(id numeric, name text, birthdate date);                         
create table dest as select * from src;                                          
                                                                                 
insert into src select i, 'x', current_date from generate_series(1,1000) g(i);   
                                                                                 
create or replace procedure copy (source in text, dest in text)                  
as $$                                                                            
declare                                                                          
  id_var numeric;                                                                
  name_var text;                                                                 
  birthdate_var date;                                                            
  source_cursor integer;                                                         
  destination_cursor integer;                                                    
  ignore integer;                                                                
begin                                                                            
  source_cursor := dbms_sql.open_cursor();                                       
  call dbms_sql.parse(source_cursor,                                             
    'select id, name, birthdate from ' || source);                                                                
                                                                                 
  call dbms_sql.define_column(source_cursor, 1, id_var);                         
  call dbms_sql.define_column(source_cursor, 2, name_var, 30);                   
  call dbms_sql.define_column(source_cursor, 3, birthdate_var);                  
                                                                                   
  perform dbms_sql.execute(source_cursor);                                       
                                                                                 
  destination_cursor := dbms_sql.open_cursor();                                  
  call dbms_sql.parse(destination_cursor,                                        
    'insert into ' || dest || ' values (:id_bind, :name_bind, :birthdate_bind)');
                                                                                                     
  loop                                                                           
    if dbms_sql.fetch_rows(source_cursor) > 0 then                               
      call dbms_sql.column_value(source_cursor, 1, id_var);                      
      call dbms_sql.column_value(source_cursor, 2, name_var);                    
      call dbms_sql.column_value(source_cursor, 3, birthdate_var);               
                                                                                 
      call dbms_sql.bind_variable(destination_cursor, ':id_bind', id_var);       
      call dbms_sql.bind_variable(destination_cursor, ':name_bind', name_var);   
      call dbms_sql.bind_variable(destination_cursor, ':birthdate_bind', birthdate_var);
                                                                                 
      ignore := dbms_sql.execute(destination_cursor);                            
    else                                                                         
      exit;                                                                      
    end if;                                                                      
  end loop;                                                                      
     
  -- Exception절 있을 경우 
  -- ERROR:  cannot commit while a subtransaction is active 발생                                                                                                                                                      
  commit; 
                                                                                 
  call dbms_sql.close_cursor(source_cursor);                                     
  call dbms_sql.close_cursor(destination_cursor);                                

-- 사용하지 말 것                                                                                  
/*exception                                                                       
  when others then                                                               
    raise notice 'exception';                                                    
                                                                                 
    if dbms_sql.is_open(source_cursor) then                                      
      call dbms_sql.close_cursor(source_cursor);                                 
    end if;                                                                      
                                                                                 
    if dbms_sql.is_open(destination_cursor) then                                 
      call dbms_sql.close_cursor(destination_cursor);                            
    end if;                                                                      
    raise;                                                                      
*/
end;                                                                             
$$ language plpgsql; 

---

구성

본 절에서는 DBMS_SQL 패키지에서 제공하는 프로시저와 함수를 알파벳 순으로 설명합니다.

BIND_ARRAY

주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값들의 집합을 설정해주는 프로시저입니다. 변수의 값이 모두 설정되지 않은 상태로 EXECUTE 함수를 호출하면 예외가 발생합니다. 변수는 이름으로 찾을 수 있으므로, SQL 문장 내의 변수이름과 파라미터로 주어진 이름을 동일하게 호출해야 합니다.

BIND_ARRAY 프로시저는 테이블에 동시에 여러 로우를 삽입, 삭제, 변경하는 용도로 활용됩니다.

프로토타입

BIND_ARRAY(c INTEGER, name TEXT, value ANYARRAY) 

BIND_ARRAY(c INTEGER, name TEXT, value ANYARRAY, index1 INTEGER, index2 INTEGER) 

파라미터

파라미터	설명
c	대상 커서입니다.
name	SQL 문장 내의 변수의 이름입니다. 이름에서 첫 문자 콜론(:)은 생략해도 됩니다.
value	SQL 문장 내의 변수에 설정할 값들의 집합 변수입니다.
index1	이 파라미터를 사용할 경우 집합 변수 내 바인딩할 영역의 시작점을 지정합니다. ( ≥ 1)
index2	이 파라미터를 사용할 경우 집합 변수 내 바인딩할 영역의 끝점을 지정합니다. ( ≥ index1)

예제

DO
$$
DECLARE
    c int;
    a varchar[];
    b int[];    
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'insert into foo values(:a, :b)');
        
    a := ARRAY['ahoj1', 'ahoj2', 'ahoj3', 'ahoj4', 'ahoj5'];
    b := ARRAY[1, 2, 3, 4, 5];
        
    CALL dbms_sql.bind_array(c, 'a', a, 2, 3);
    CALL dbms_sql.bind_array(c, 'b', b, 3, 4);
END;
$$;

BIND_VARIABLE

주어진 커서의 SQL 문장에서 콜론(:)으로 시작하는 변수에 값을 설정해주는 프로시저입니다. 변수의 값이 모두 설정되지 않은 상태로 EXECUTE 함수를 호출하면 예외가 발생합니다. 변수는 이름으로 찾을 수 있으므로, SQL 문장 내의 변수이름과 파라미터로 주어진 이름을 동일하게 호출해야 합니다.

프로토타입

BIND_VARIABLE(c INTEGER, name TEXT, value "any") 

파라미터

파라미터	설명
c	대상 커서입니다.
name	SQL 문장 내의 변수의 이름입니다. 이름에서 첫 문자 콜론(:)은 생략해도 됩니다.
value	SQL 문장 내의 변수에 설정할 값입다.

예제

DO
$$
DECLARE
    c int;
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'insert into test values(:a)');
    CALL dbms_sql.bind_variable(c, 'a', 'ahoj');
END;
$$;

CLOSE_CURSOR

주어진 커서를 닫고, NULL로 설정하는 프로시저이다.

프로토타입

CLOSE_CURSOR(c INTEGER)

파라미터

파라미터	설명
c	대상 커서입니다.

예제

DO
$$
DECLARE
    c int;
BEGIN
    c := dbms_sql.open_cursor();
    call dbms_sql.close_cursor();
END;
$$;

COLUMN_VALUE

주어진 커서에서 fetch한 컬럼의 값을 원하는 변수로 가져오는 프로시저입니다. 실제 fetch는 FETCH_ROWS 함수에서 일어나고, COLUMN_VALUE 프로시저는 fetch한 후 데이터를 가져오는 데 사용됩니다.

프로토타입

DEFINE_COLUMN(c INTEGER, pos INTEGER, INOUT value anyelement) 

파라미터

파라미터	설명
c	대상 커서입니다.
pos	fetch할 컬럼 목록에서 상대적 위치입니다. (1 이상)
value	가져올 컬럼을 저장할 변수입니다.

예제

DO
$$
DECLARE
    c int;
    strval varchar;
    intval int;
    stack text := '';
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, 5) g(i)');
    CALL dbms_sql.define_column(c, 1, strval);
    CALL dbms_sql.define_column(c, 2, intval);
    PERFORM dbms_sql.execute(c);
        
    WHILE dbms_sql.fetch_rows(c) > 0
    LOOP
        CALL dbms_sql.column_value(c, 1, strval);
        CALL dbms_sql.column_value(c, 2, intval);
    END LOOP;

    CALL dbms_sql.close_cursor(c);
END;
$$;

DEFINE_COLUMN

주어진 커서에서 fetch될 컬럼의 타입을 정의하는 프로시저입니다. 이 프로시저는 SELECT 문장에만 사용할 수 있습니다.

프로토타입

DEFINE_COLUMN(c INTEGER, col INTEGER, value "any", column_size INTEGER) 

파라미터

파라미터	설명
c	대상 커서입니다.
col	SELECT를 실행할 컬럼 목록에서의 상대적 위치입니다. 문장의 컬럼에서 첫 번째 위치를 1로 하며, 1 이상의 값을 입력합니다.
value	정의할 컬럼의 변수로, 타입만 참조하며 값은 관계없습니다.
column_size	SELECT를 실행할 컬럼 값의 최대 길이입니다. 단, 최대 길이를 초과한 결과 컬럼은 절삭하여 가져옵니다.

예제

DO
$$
DECLARE
    c int;
    strval varchar;
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'select ''test'', i from generate_series(1, 5) g(i)');
    CALL dbms_sql.define_column(c, 1, strval);
END;
$$;

DEFINE_ARRAY

주어진 커서에서 fetch될 컬럼 집합의 타입을 정의하는 프로시저입니다. 이 프로시저는 SELECT 문장에만 사용할 수 있습니다.

DEFINE_ARRAY 프로시저는 한 번의 FETCH로 여러 로우를 가져올 경우 사용합니다.

프로토타입

DEFINE_ARRAY(c INTEGER, col INTEGER, value "anyarray", cnt INTEGER, lower_bnd INTEGER) 

파라미터

파라미터	설명
c	대상 커서입니다.
col	SELECT를 실행할 컬럼 목록에서의 상대적 위치입니다. 문장의 컬럼에서 첫 번째 위치를 1로 하며, 1 이상의 값을 입력합니다.
value	정의할 컬럼의 집합 변수로, 타입만 참조하며 값은 관계없습니다.
cnt	한번에 FETCH되어야 하는 row의 갯수입니다.
lower_bnd	이 파라미터를 사용할 경우 FETCH되어 저장될 집합변수의 시작점을 지정합니다. ( ≥ 1)

예제

DO
$$
DECLARE
    c int;
    strval varchar[];
    intval int[];
    stack text := '';
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, 5) g(i)');
    CALL dbms_sql.define_array(c, 1, strval, 5, 1);
    CALL dbms_sql.define_array(c, 2, intval, 5, 1);
END;
$$;

EXECUTE

주어진 커서를 실행하는 함수이다. INSERT, UPDATE, DELETE 문장인 경우 처리된 로우의 수가 반환됩니다.

프로토타입

EXECUTE(c IN INTEGER)
RETURNS BIGINT

파라미터

파라미터	설명
c	대상 커서입니다.

예제

DO
$$
DECLARE
    c int;
    a varchar[];
    b int[];
    result int;
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'insert into foo values(:a, :b)');
      
    a := ARRAY['ahoj1', 'ahoj2', 'ahoj3', 'ahoj4', 'ahoj5'];
    b := ARRAY[1, 2, 3, 4, 5];
       
    CALL dbms_sql.bind_array(c, 'a', a, 2, 3);
    CALL dbms_sql.bind_array(c, 'b', b, 3, 4);
        
    result := dbms_sql.execute(c);

    CALL dbms_sql.close_cursor(c);
END;
$$;

EXECUTE_AND_FETCH

주어진 커서에 EXECUTE 와 FETCH_ROWS 함수를 연속해서 호출하는 것과 동일한 함수입니다. 반환되는 값은 FETCH_ROWS와 동일합니다. 즉, 실제 fetch한 로우의 수를 반환합니다.

프로토타입

EXECUTE_AND_FETCH(c IN INTEGER, exact IN BOOL)
RETURNS INT

파라미터

파라미터	설명
c	대상 커서입니다.
exact	TRUE : 정확히 하나의 로우가 fetch되지 않으면 예외가 발생합니다. FALSE : 여러 개의 ROW가 fetch되어도 예외가 발생하지 않습니다.

예제

DO
$$
DECLARE
    c int;
    strval varchar;
    intval int;
    stack text := '';
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, 5) g(i)');
    CALL dbms_sql.define_column(c, 1, strval);
    CALL dbms_sql.define_column(c, 2, intval);
    PERFORM dbms_sql.execute_and_fetch(c, false);
        
    WHILE dbms_sql.fetch_rows(c) > 0
    LOOP
        CALL dbms_sql.column_value(c, 1, strval);
        CALL dbms_sql.column_value(c, 2, intval);
    END LOOP;

    CALL dbms_sql.close_cursor(c);
END;
$$;

FETCH_ROWS

주어진 커서에서 로우를 fetch하는 함수입니다. 이때 실제 fetch된 로우의 수가 반환됩니다. 더 이상 fetch할 로우가 없는데 이 함수를 계속 호출하게 되면 예외가 발생합니다.

프로토타입

FETCH_ROWS(c IN INTEGER)
RETURNS INTEGER

파라미터

파라미터	설명
c	대상 커서입니다.

예제

DO
$$
DECLARE
    c int;
    strval varchar;
    intval int;
    stack text := '';
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, 5) g(i)');
    CALL dbms_sql.define_column(c, 1, strval);
    CALL dbms_sql.define_column(c, 2, intval);
    PERFORM dbms_sql.execute(c);
        
    WHILE dbms_sql.fetch_rows(c) > 0
    LOOP
        CALL dbms_sql.column_value(c, 1, strval);
        CALL dbms_sql.column_value(c, 2, intval);
    END LOOP;

    CALL dbms_sql.close_cursor(c);
END;
$$;

IS_OPEN

주어진 커서가 DBMS_SQL.open_cursor 함수로 열릴 수 있는 커서인지 검사하는 함수입니다.

프로토타입

IS_OPEN(c IN INTEGER)
RETURNS BOOL

파라미터

파라미터	설명
c	대상 커서입니다.

예제

DO
$$
DECLARE
    c int;
    x bool;
BEGIN
    c := dbms_sql.open_cursor();
    x := dbms_sql.is_open(c);
END;
$$;

LAST_ROW_COUNT

현재 수행 중인 문장의 fetch된 로우 수의 총합을 반환하는 함수입니다.

프로토타입

LAST_ROW_COUNT()
RETURNS INT

예제

DO
$$
DECLARE
    c int;
    strval varchar;
    intval int;
    n int;
BEGIN
    c := dbms_sql.open_cursor();
    CALL dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, 5) g(i)');
    CALL dbms_sql.define_column(c, 1, strval);
    CALL dbms_sql.define_column(c, 2, intval);
    PERFORM dbms_sql.execute(c);
        
    WHILE dbms_sql.fetch_rows(c) > 0
    LOOP
        CALL dbms_sql.column_value(c, 1, strval);
        CALL dbms_sql.column_value(c, 2, intval);

        n := dbms_sql.last_row_count();
    END LOOP;

    CALL dbms_sql.close_cursor(c);
END;
$$;

OPEN_CURSOR

SQL 문장을 수행하기 위한 커서를 새로 하나 여는 함수입니다. 열린 커서는 사용 후에 반드시 close_cursor를 호출해야 리소스가 서버에 반납됩니다.

한번 열린 커서는 동일한 SQL 문장을 반복해서 수행할 수도 있고, 다른 SQL 문장을 실행하는 데 사용할 수도 있습니다. 커서를 여는 데 성공하면, 커서 ID를 INTEGER 타입으로 반환합니다. 이 값을 이후에 DBMS_SQL 패키지의 커서 파라미터로 사용하면 됩니다.

프로토타입

OPEN_CURSOR()
RETURNS INTEGER

예제

DO
$$
DECLARE
    c int;
BEGIN
    c := dbms_sql.open_cursor();
END;
$$;

PARSE

주어진 SQL 문장을 파싱하는 프로시저입니다. 기존에 열려 있는 SQL 문장이 있다면 닫고 새로 입력된 SQL 문장을 파싱합니다.

프로토타입

PARSE(c INTEGER, stmt TEXT)

파라미터

파라미터	설명
c	대상 커서입니다.
stmt	파싱할 대상이 되는 SQL 문장입니다.

예제

DO
$$
DECLARE
    c int;
BEGIN
    c := dbms_sql.open_cursor();
    call dbms_sql.parse(c, 'insert into test values(''test1'',1)');
END;
$$;