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 문장을 실행하는 과정은 다음과 같다.

OPEN_CURSOR 함수를 이용하여 커서를 열고, 커서 ID를 얻는다.
커서를 구분하기 위해 앞에서 얻은 커서 ID를 다른 함수의 파라미터로 전달한다.
Dynamic SQL 문장을 파싱하기 위해 PARSE 함수를 호출한다. 만약 실행할 Dynamic SQL 문장 안에 바인드해야 할 변수가 있는 경우 BIND_VARIABLE 함수를 호출해 변수를 바인드한다.
실행 결과를 가져오기 전에 결과 컬럼의 타입을 정의하기 위해 DEFINE_COLUMN 함수를 호출한다.
EXECUTE 함수를 이용하여 파싱된 SQL 문장을 실행한다.
실행한 결과를 fetch하기 위해 FETCH_ROWS 함수를 실행한다. fetch한 결과를 원하는 변수로 가져오기 위해서는 COLUMN_VALUE 함수를 호출한다.
모든 수행을 끝낸 후 커서를 종료하기 위해 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)

파라미터

설명

대상 커서이다.

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")

파라미터

설명

대상 커서이다.

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)

파라미터

설명

대상 커서이다.

예제

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)

파라미터

설명

대상 커서이다.

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)

파라미터

설명

대상 커서이다.

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)

파라미터

설명

대상 커서이다.

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

파라미터

설명

대상 커서이다.

예제

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

파라미터

설명

대상 커서이다.

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

파라미터

설명

대상 커서이다.

예제

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

파라미터

설명

대상 커서이다.

예제

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)

파라미터

설명

대상 커서이다.

stmt

파싱할 대상이 되는 SQL 문장이다.

예제

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

PreviousDBMS_RANDOM NextUTL_FILE

Last updated 2 months ago