mysql 에서 prepared statement 는 굉장히 유용합니다.

주로 stored procedure 내에서 dynamic query 를 만들어서 실행하고자할 때 종종사용됩니다.


mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;


문제는 prepared statement 에는 제약점이 많습니다.

여기에서는 모든 sql 구문을 사용할 수 있지 않습니다.

다음과 같은 구문만 사용할 수 있습니다. (mysql 5.5 기준)


ALTER TABLE
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} TABLE
{CREATE | RENAME | DROP} USER
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE


그런데 만약 IF ~ ELSE ~ END IF 구문을 사용하려고 한다면 오류가 발생합니다.

그리고 stored procedure 내에서 호출되는 prepared statement 의 경우에, procedure 내부에서 선언한 변수값은 사용할 수도 없습니다. 따라서 SELECT ~ INTO local_var 와 같은 구문을 사용할 수가 없습니다. 

또, prepared statement 에 넘겨지는 값은 EXECUTE ~ USING @var1, @var2... 형태로 입력으로만 제공될 뿐, 입출력 또는 출력으로는 사용할 수도 없습니다.

따라서 prepared statement 내에서 얻어진 출력 값을 외부에 제공할 때 난감합니다.

그렇다면 방법이 전혀 없는 것인가?


꼼수일 수도 있지만, MEMORY TABLE 을 사용하면 됩니다.

예제를 적어보면 다음과 같습니다.


DECLARE TB_NAME VARCHAR(100);
SET TB_NAME = CONCAT('MONTH_', DATE_FORMAT(NOW(), '%Y%m'));

CREATE TABLE TEMP_DATA(SEQ BIGINT) ENGINE=MEMORY;


SET @SQL = CONCAT('INSERT INTO TEMP_DATA(SEQ) SELECT SEQ FROM ', TB_NAME, ' WHERE USER_SEQ = ?');
PREPARE INS FROM @SQL;
SET @P1 = c_user_seq;
EXECUTE INS USING @P1;
DEALLOCATE PREPARE INS;


IF EXISTS (SELECT SEQ FROM TEMP_DATA) THEN
	SET @SQL = CONCAT('UPDATE ', TB_NAME, ' SET MONEY = ? WHERE USER_SEQ = ?');
	PREPARE UPD FROM @SQL;
	SET @P1 = c_money;
	SET @P2 = c_user_seq;
	EXECUTE UPD USING @P1, @P2;
	DEALLOCATE PREPARE UPD;
ELSE
	SET @SQL = CONCAT('INSERT INTO ', TB_NAME, ' (USER_SEQ, MONEY) VALUES (?, ?)');
	PREPARE INS FROM @SQL;
	SET @P1 = c_user_seq;
	SET @P2 = c_money;
	EXECUTE INS USING @P1, @P2;
	DEALLOCATE PREPARE INS;
END IF;


DROP TABLE IF EXISTS TEMP_DATA;


  1. TEMP_DATA 메모리 테이블을 미리 만들어 둔다.
  2. prepared statement 내에서 조건을 만족하면 데이터를 TEMP_DATA 에 넣어둔다.
  3. 상위 프로그램에서 TEMP_DATA 에 대해서 IF 조건을 검사한다.
  4. 이후에는 원하는 작업에 대해서 또 다른 prepared statement 를 실행한다.
  5. TEMP_DATA 메모리 테이블을 삭제한다.


참고:


Posted by 집시F