[MariaDB] 동적쿼리 (2. 개념)

이번 장에선 동적쿼리의 각 구문별 간단한 내용을 정리하도록 하겠습니다.

PREPARE 구문

PREPARE 구문은 명령문을 준비하고 이후에 명령문 대신 사용될 이름을 할당합니다.
명령문 이름은 대소문자 구분하지 않습니다.
이름의 경우 하나의 SQL 구문만 할당해서 사용하여야 합니다.
SQL 구문 내부에 "?"와 같은 기호를 사용한다면 이후에 쿼리가 실행될 때 파라미터 값과 바운딩되도록 가리키는 파라미터 마커로 사용합니다.
prepared statement의 범위는 작성된 세션까지 입니다.
만약에 동일한 이름의 prepared statement가 존재하면 새로운 SQL구문이 준비되기 전에 할당해제됩니다. 즉, 새로운 SQL 구문이 에러가 포함되어 있고 실행 준비되지 못하면 에러는 반환되고 주어진 이름의 SQL 구문이 존재하지 않게 됩니다.

EXECUTE 구문

PREPARE 구문을 통해 SQL 명령문을 준비한 후 prepared statement의 이름을 참조하는 EXECUTE 구문을 통해 실행합니다.
만약 prepared statement가 파라미터 마커를 포함하고 있다면 USING 절을 사용하여 파라미터에 값이 바운딩되도록 선언한 사용자 변수 리스트를 작성해야 합니다.
파라미터 값의 경우 오직 사용자 변수에 의해서만 제공될 수 있으며, USING 절은 선언된 SQL에 있는 파라미터 마커의 수 많큼 정확하게 변수이름을 지정해야 합니다.
만약 SQL구문이 PREPARE되지 못했다면 아래와 비슷한 에러가 발생되게 됩니다.

ERROR 1243 (HY000): Unknown prepared statement handler (stmt_name) given to EXECUTE

DEALLOCATE/DROP PREPARE

PREPARE로 생성되어 prepared statement을 할당 해제하려면 SQL 명령문 이름을 참조하는 DEALLOCATE PREPARE를 사용하시면 됩니다.
prepared statement는 새 PREPARE 명령이 발생될 때 내재적으로 할당 해제됩니다. 이런 경우에는 DEALLOCATE를 사용할 필요가 없습니다.

prepared statement가 자원 해제(deallocate)되었을 때 실행을 시도하면 아래와 같은 에러가 발생하게 됩니다.

ERROR 1243 (HY000): Unknown prepared statement handler (stmt_name) given to EXECUTE

지정된 명령문이 PREPARE되지 않은 경우 아래와 같은 에러가 발생할 수 있습니다.

ERROR 1243 (HY000): Unknown prepared statement handler (stmt_name) given to DEALLOCATE PREPARE

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE는 동적 쿼리문을 실행하여 성능 오버헤드를 줄일 수 있습니다.

예를들어 아래와 같습니다.

1. EXECUTE IMMEDIATE 'SELECT 1'

또는

2. prepare stmt from 'select 1";
   execute stmt;
   deallocate prepare stmt;

위의 1과 2는 서로 동일한 의미입니다.

EXECUTE IMMEDIATE는 준비된 소스와 파라미터를 가진 복잡한 표현식도 지원합니다.

EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', 't1', ' WHERE a=?') USING 5+5;

하지만 subselect문이거나 저장된 함수 호출은 지원하지 않습니다.

아래 예제는 오류를 반환 합니다.

CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(64) RETURN 'SELECT * FROM t1';
EXECUTE IMMEDIATE f1();

ERROR 1970 (42000): EXECUTE IMMEDIATE does not support subqueries or stored functions

EXECUTE IMMEDIATE (SELECT 'SELECT * FROM t1');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
  corresponds to your MariaDB server version for the right syntax to use near 
  'SELECT 'SELECT * FROM t1')' at line 1
  
CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 10;
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING f1();

ERROR 1970 (42000): EXECUTE..USING does not support subqueries or stored functions

EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING (SELECT 10);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
  corresponds to your MariaDB server version for the right syntax to use near 
  'SELECT 10)' at line 1

해결 방법으로는 사용자 또는 SP 변수를 사용하시면 됩니다.

CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(64) RETURN 'SELECT * FROM t1';
SET @stmt=f1();
EXECUTE IMMEDIATE @stmt;

SET @stmt=(SELECT 'SELECT 1');
EXECUTE IMMEDIATE @stmt;

CREATE OR REPLACE FUNCTION f1() RETURNS INT RETURN 10;
SET @param=f1();
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING @param;

SET @param=(SELECT 10);
EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING @param;

참고

• https://mariadb.com/kb/en/prepare-statement/