[PostgreSQL] 동적쿼리 : Dynamic SQL

Dynamic SQL

애플리케이션이 실행해야 하는 SQL문은 애플리케이션이 작성되는 시점에 정해지는 것이 일반적입니다. 그러나 어떤 경우에는 SQL문이 런타임 시 구성되거나 외부 요청에 의해 제공되는 경우도 있습니다. 이러한 경우 SQL문을 문자열 변수에 할당하여 임의의 SQL문을 동적으로 만들어 호출할 수 있는 기능이 동적쿼리(Dynamic SQL)입니다.

 

기본 문법

Result값이 없고, 매개변수를 갖지 않는 SQL문

- DDL, INSERT, UPDATE, DELETE 등

- EXECUTE IMMEDIATE 는 result값이 없는 동적쿼리를 호출할 때 쓰이는 명령어이다.

-- 선언
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test1 (...);";
EXEC SQL END DECLARE SECTION;

-- 호출 
EXEC SQL EXECUTE IMMEDIATE :stmt;

 

Result값이 없고, 매개변수를 갖는 SQL문 

- DDL, INSERT, UPDATE, DELETE 등

- PREPARE {name} FROM : 동적쿼리를 사용하겠다고 선언하는 명령어. 동적쿼리 변수를  PREPARE 변수에 할당해주는 방식.

- EXECUTE {name} USING : PREPARE 한 쿼리를 매개변수와 함께 사용하는 명령어

- DEALLOCATE PREPARE {name}  : 사용 완료시 PREPARE 변수를 지워주는 명령어

-- 선언
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
EXEC SQL END DECLARE SECTION;

-- 사용 ON
EXEC SQL PREPARE mystmt FROM :stmt;
 ...

-- 호출
EXEC SQL EXECUTE mystmt USING 42, 'foobar';

-- 사용 OFF
EXEC SQL DEALLOCATE PREPARE name;

 

단일 Record Result를 갖는 SQL문 

- EXECUTE{name} USING 절에 INTO를 추가하여 결과 값을 컬럼별로 변수에 담을 수 있다.

- 매개변수가 없으면 USING 절 생략 가능

- 단일 Result와 매개변수 전부 있으면 INTO , USING 절 같이 사용 가능

- 혹은 둘다 생략 가능

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";                                                               "
int v1, v2;
VARCHAR v3[50];
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE mystmt FROM :stmt;
 ...
EXEC SQL EXECUTE mystmt INTO :v1, :v2, :v3 USING 37;

 

여러 Record Result를 갖는 SQL문

EXEC SQL BEGIN DECLARE SECTION;
char dbaname[128];
char datname[128];
char *stmt = "SELECT u.usename as dbaname, d.datname """
             "  FROM pg_database d, pg_user u "
             "  WHERE d.datdba = u.usesysid"";
EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO testdb AS con1 USER testuser;
EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

EXEC SQL PREPARE stmt1 FROM :stmt;

EXEC SQL DECLARE cursor1 CURSOR FOR stmt1;
EXEC SQL OPEN cursor1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    EXEC SQL FETCH cursor1 INTO :dbaname,:datname;
    printf("dbaname=%s, datname=%s\n", dbaname, datname);
}

EXEC SQL CLOSE cursor1;

EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL;

 

공식 문서 : https://www.postgresql.org/docs/9.5/ecpg-dynamic.html