MySQL 프로시저 PROCEDURE 생성(CASE문, IF문)+ 추가 FUNCTION

 DB에서 작업을 수행하는 함수나 프로시저 생성을 하게 되었다.

 다뤘던 프로젝트에서 생성된 함수를 사용 한 것을 보았기 때문에 처음 생각한 것은 함수였으나 프로시저의 속도가 빠르고 구조와 기능에 차이가 없다는 글을 보고 프로시저로 선택했다.

 

작업후기:

사용법에서 큰 차이가 있다.  프로시저의 호출은 CALL 프로시저 만 되지만 함수의 경우 SQL문 내에서도 사용이 가능하다. 예를들어 처리된 ID를 불러오는 함수 A(id_before) 이 있다면 SELECT * FROM TABLE ID = A(id_before) 이런식으로 구문에서 바로 함수를 호출하여 사용이 가능 한 것이다. 내가 필요한 기능은 이 부분이었기 때문에 함수를 추가로 만들었다.

 

 

 

 함수와 프로시저에 대해 간단히 알아 본 결과 둘의 구조는 동일하나(프로시저는 리턴값이 없고 함수는 있다고도 하는데 둘 다 리턴이 가능하다) , 작업을 수행하는 위치에 따른 것이다. 함수의 처리는 클라이언트, 프로시저의 처리는 서버에서 이루어지고, 때문에 프로시저의 속도가 더 빠르다고 할 수 있다.(이부분에서도 예외나 case by case가 존재하지만 DB는 서버와 가까운 곳에서 작업을 수행하는 것으로 조언을 들었기에 깊게 들어가기 전 까진 이를 지키려 한다.)

 내가 사용하고 있는 SQL 툴인 SQLyog에선 프로시저 생성 기능이 있어 이를 통해 만들기를 해 보면

 

 DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `ERP`.`test`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
BEGIN

END$$

DELIMITER ;

 

이러한 구조를 만들어 준다.

 

내가 만든 프로시저의 형태는 다음과 같다.

 

이 곳에서 생성을 하든, 아니면 보통 작업 공간인 쿼리 편집기에서 생성하든 똑같다.

 

 DELIMITER $$ 

는 구분문자를 $$ 로 하여 선언된 곳까지 하나의 SQL문으로  수행되게 하는 것이다.

 

DROP PROCEDURE IF EXISTS p_sample$$  /*만들어 볼 p_sample 프로시저가 있다면 삭제한다.*/

 

CREATE PROCEDURE p_sample(IN p_seq VARCHAR(2), IN p_id VARCHAR(10)) 

                                                        /*파라미터는 IN 후 이름 타입순으로 정의한다*/

BEGIN                                /* 프로시저 로직 시작 */

   
 DECLARE result VARCHAR(54);  /*   프로시저 안에서 사용할 변수를 생성한다.  */

 DECLARE p_type VARCHAR(6);
 
SET p_type =                   /*   입력된 p_seq 가 에따라 p_type을 정의 //  프로시저 안에서 CASE문 사용*/ 
 CASE 
  WHEN (p_seq = 1) THEN 'C@'
  WHEN (p_seq_ = 2) THEN 'T@'
  WHEN (p_seq = 3) THEN 'P@'
  ELSE 'ERROR'
 END;    /* case문 종료를 선언해야한다 */

- 처음 프로시저를 만들면서 헤맸던 부분은 DECLARE를 SET f_prefix 뒤에 위치하여 에러가 발생하였는데 DECLARE부분인지 이후 구문의 문제인지를 알 수 없어 헤맸었다. JAVA나 다른 언어들 같은 경우 생성하는 위치는 사용하기 전이면 되었기 때문이다. BEGIN이후 DECLARE는 가장 먼저 해두고 다른 작업 이후에 하면 에러가 발생하는 것을 테스트를 통해 확인하였는데 이것은 "모든 DECLARE는 제일 위에 선언 및 SET은 DECLARE 하단에 선언한다"룰에 따른 것이라고 한다.

 

IF (p_type != 'ERROR')       /*   프로시저 안에서 IF문 사용  */
THEN
 
SELECT LAST_SEQ INTO  result FROM TABLE WHERE SEQ = p_seq AND CID = p_id ;

/*  에러가 아닐시 생성된 result 에  조회된 LAST_SEQ  값을 입력*/

ELSE

SELECT 'ERROR' INTO result;

/*  에러일시 error를 result에 입력*/

END IF;

/*   case와 마찬가지로 if문 종료시 end if를 선언해야한다  */
  
SELECT CONCAT(PREFIX,LAST_SEQ) INTO result FROM TB_SEQ WHERE SEQ_TYPE = f_seq_type AND CID = f_cid;
 

END$$            /*   프로시저 생성부터 여기까지 하나의 SQL문으로 실행된다 */


DELIMITER ;   /* 기존 설정대로 다시 ; 로 변경한다*/

 

 

 프로시저의 만드는데 있어서 큰 어려움은 없었지만 익숙하지 않은 구문들과 위치에 대한 올바른 사용법, 그리고 인자사용에 대한 의문이 나를 헤매게 했다. 그리고 본문에는 없지만 일반적인 SQL함수인 CONCAT, RAND, LPAD등이 정상적으로 사용된 것으로 보아 프로시저 내에서의 SQL 구문은 특별히 다를바 없기에 큰 무리 없이 복잡한 SQL구문이 들어가는 함수나 프로시저 생성도 문제 없어 보인다.

 

 

 

+

프로시저를 함수로 변경하는 것은 간단하다.

먼저 PROCEDURE를 FUNCTION으로 변경하고, 함수생성은 프로시저 생성에서 파라미터의 IN 을 제거. 그리고 RETRUN 타입을 정해준다.

CREATE FUNCTION p_sample(IN p_seq VARCHAR(2), IN p_id VARCHAR(10)) RETURN VARCHAR(10)

이 되는 것이다.

 

그리고 마지막 처리 결과가 자동으로 리턴되는게 아니므로 

SELECT result 를 

RETURN result로 변경하면 된다.