[MySQL] 저장 프로시저, 저장 함수

1. 저장 프로시저 (Stored Procedure)

1) 저장 프로시저

일련의 작업 절차를 정리해서 저장한 것이다. 여러 SQL문을 묶어서 미리 정의해 두고 하나의 요청으로 실행할 수 있다. 자주 사용되는 복잡한 작업들을 간단하게 실행할 수 있다. 애플리케이션에서 직접 모든 작업을 요청하지 않아도 되기 때문에 부하가 줄어들고 보안이 향상된다. 하지만 검증되지 않은 저장 프로시저를 실행하는 것은 위험하며 MySQL 5버전부터 사용이 가능하다.

2) 저장 프로시저 사용법

일반적으로 각 SQL문의 끝을 나타내기 위해 세미콜론을 입력해야한다. 하지만 그럴경우, 프로시저를 생성하는 동안에 명령이 끝나버리는 문제가 발생하기에 명령의 끝을 나타내는 구분문자를 임시로 변경하여 작성을 한다. 프로시저 작성이 끝난 뒤에는 반드시 구분문자를 다시 변경해주어야 한다. BEGIN으로 저장 프로시저의 범위의 시작을 알리고, END로 저장 프로시저 범위의 끝을 표현한다.

// 기본 사용법
mysql> DELIMITER //
mysql> CREATE PROCEDURE [PROCEDURE명] [ARGUMENT](인자값)
  BEGIN
    [실행문];
  END //
mysql> DELIMITER ;

// 저장 프로시저 확인
mysql> SHOW PROCEDURE STATUS;
mysql> SHOW CREATE PROCEDURE [PROCEDURE명];

// 저장 프로시저 실행
mysql> CALL [PROCEDURE명]();

// 저장 프로시저 삭제
mysql> DROP PROCEDURE [PROCEDURE명];

3) 저장 프로시저 변수 사용법

mysql> DELIMITER //
mysql> CREATE PROCEDURE [PROCEDURE명]()
  BEGIN
    DECLARE [변수명] [데이터타입] [DEFAULT값];
    SET [변수명]=[데이터];
    SELECT ...
  END //
mysql> DELIMITER ;

4) 저장 프로시저 조건문 사용법

mysql> DELIMITER //
mysql> CREATE PROCEDURE [PROCEDURE명] ()
  BEGIN
    IF [조건] THEN
      SELECT ...;
    ELSE IF [조건] THEN
      SELECT ...;
    ELSE
      SELECT ...;
    END IF;
  END //
mysql> DELIMITER ;

5) 저장 프로시저 선택문 사용법

mysql> DELIMITER //
mysql> CREATE PROCEDURE [PROCEDURE명] ()
  BEGIN
    CASE [조건대상]
    WHEN [조건값1] THEN
      SELECT ...;
    WHEN [조건값2] THEN
      SELECT ...;
    ELSE
      SELECT ...;
    END CASE;
  END //
mysql> DELIMITER ;

6) 저장 프로시저 반복문 사용법

mysql> DELIMITER //
mysql> CREATE PROCEDURE [PROCEDURE명] ()
  BEGIN
    WHILE [조건] DO
      SELECT ...;
      SET [조건] = [조건]+1;
    END WHILE;
  END //
mysql> DELIMITER ;

 

 

 

2. 저장 함수

1) 저장 함수

저장 프로시저와 거의 비슷하나 실행 후 결과 값을 반환 한다는 점이 다르다. 사용자 정의 함수라고도 불리며 MySQL 5버전부터 사용이 가능하다. 저장 프로시저에 비해 문법상 제약 사항이 많기에 실제로는 프로시저를 더 많이 사용힌다. 또한 저장 프로시저는 CALL로 호출할 때, 저장 함수는 SELECT로 호출한다.

2) 저장 함수 사용법

// 기본 사용법
mysql> DELIMITER //
mysql> CREATE FUNCTION [FUNCTION명] () RETURNS [반환데이터타입]
  BEGIN
    [실행문];
    RETURN [반환값];
  END //
mysql> DELIMITER ;

// 저장 함수 사용
mysql> SELECT [FUNCTION명] ();

// 저장 함수 확인
mysql> SHOW FUNCTION STATUS;
mysql> SHOW CREATE FUNCTION [FUNCTION명];

// 저장 함수 삭제
mysql> DROP FUNCTION [FUNCTION명];

 

 

 

3. 참고 사항

1) 세션 변수, 지역 변수

세션 변수는 흔히 사용하는 세션에서 사용 가능한 변수이며, 지역변수는 저장 프로시저 내에서 사용 가능한 변수이다.

// 세션 변수
mysql> SET @A = "Session Variables";
mysql> SELECT @A;

// 지역 변수
mysql> DROP PROCEDURE IF EXISTS var_test;
mysql> DELIMITER //
mysql> CREATE PROCEDURE var_test ()
  BEGIN
    DECLARE VAR1 CHAR(10) DEFAULT "Var 1";
    DECLARE VAR2 INT;
    SET VAR2 = 100;

    SELECT VAR1;
    SELECT VAR2;
  END //
mysql> DELIMITER ;
mysql> CALL var_test();

2) 파라미터 (IN, OUT, INOUT, INTO)

저장 프로시저에는 IN, OUT, INOUT을 이용하여 파라미터의 입출력을 표현할 수 있다. OUT은 변수를 통해서 전달 받으며, INOUT은 변수를 통해서 전달하고 전달받는다. INTO는 프로시저내에서 선언된 SELECT로 조회된 필드를 변수에 넣어주는 지시어이다. 반면 저장 함수는 파라미터 키워드를 사용을 하지 않는다.

// 프로시저 내 INTO
mysql> DROP PROCEDURE IF EXISTS ...
mysql> DELIMITER //
mysql> CREATE PROCEDURE ...
  BEGIN
  DECLARE VAR1 INT;
  DECLARE VAR2 INT;
      
  SELECT [COLUMN명], [COLUMN명] INTO VAR1, VAR2 FROM ...
      
// 프로시저 밖 INTO
mysql> SELECT [COLUMN명], [COLUMN명] INTO @VAR1, @VAR2 FROM ...

3) 트랜잭션, 예외 핸들러

무결성 제약 조건에 위배되면 SQL 쿼리에 에러가(SQLEXCEPTION) 발생하도록 프로시저 내에 선언할 수 있다.

mysql> DROP PROCEDURE IF EXISTS ...
mysql> DELIMITER //
mysql> CREATE PROCEDURE ...
  BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    ...
  END;
  
  START TRANSACTION;
    ...
  COMMIT;
  ...
      

---

 [참고] Database - Practice - 저장 프로시저, 저장 함수