PROCEDURE (프로시져) 동적쿼리

프로시져 연습을 위하여 간단한 프로시져를 만들어 보았다.

 

구현한 프로시져는 name을 파라미터로 받아 User라는 Table에서

 

해당 name값을 가진 레코드의 age를 25살로 바꾸는 프로시져이다.

 

해당 프로시져를 만들기 위해서는 input parameter 값에 따라 실행할 쿼리의 내용이 달라지기 때문에

 

동적쿼리에 대해서 알아야 했다.

 

• 동적쿼리 사용방법

SET @variable = "variable";
SET @sql = "SELECT * FROM table WHERE field = ?";

PREPARE dquery FROM @Sql;

EXECUTE dquery USING @variable;

- PREPARE a FROM @b : b문을 대신할 a라는 변수를 만드는 구문.

- EXECUTE a USING @b : b를 와일드카드('?' 부분)에 대입하여 a를 실행하는 구문.

 

이 때, 동적쿼리의 와일드카드에 프로시져 input param 값으로 받아온 값을 바로 대입하려 했으나

 

"variable" is not valid at this position, expecting: '@', AT_TEXT_SUFFIX"

 

라는 error message가 뜨며 실행이 불가하였다.

 

즉, 동적쿼리에서 USING을 쓰려면 Input Param을 바로 대입하는 것이 아닌 @를 이용하여 지역변수를 대입해 주어야한다.

 

 

• 전체 프로시져 코드

CREATE PROCEDURE `procedure_exercise`(IN `name` VARCHAR(45), OUT RESULT INT)
BEGIN
	DECLARE exit handler for SQLEXCEPTION
	  BEGIN
		ROLLBACK;
		SET RESULT = -1;
	END;
    
    SET @_age := 25;
    SET @_name := IFNULL(name, "");
    
	START TRANSACTION;
    SET @sql = "UPDATE user SET age = ? WHERE name = ?";

    PREPARE dquery FROM @Sql;
    EXECUTE dquery USING @_age, @_name;
    
    SET RESULT = 1;
    COMMIT;
END