[MySQL] 저장 프로시저 (Stored Procedure)

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

• 여러 SQL 문을 하나의 SQL 문처럼 정리하여 CALL ✕ ✕라는 명령으로 실행할 수 있게 만든 것을 저장 프로시저(Stored Procedure)라고 합니다.
• Stored는 '저장하다', Procedur는 '절차'라는 의미입니다. 즉, 저장 프로시저는 일련의 절차를 정리해서 저장한 것입니다.

사전에 준비 한 많은 명령을 자동으로 실행할 수 있기 때문에, 작업의 효율성도 높일 수 있습니다. 단, 중요한 데이터가 축적된 데이터베이스에서 제대로 검증되지 않은 저장 프로시저를 실행하는 것은 매우 위험합니다.

---

2. 저장 프로시저 생성 / 호출

저장 프로시저 생성

• 예) 고객 테이블에서 고객이름순으로 조회한 정보를 저장 프로시저로 생성

DELIMITER $$ 
CREATE PROCEDURE GetCustomers() 
BEGIN 
	 SELECT customerName, city, state, postalCode, country 
      FROM customers 
     ORDER BY customerName; 
  END $$ 
DELIMITER ;

DELIMITER 는 왜 사용할까?

• 저장 프로시저 내부에 사용하는 SQL문은 일반 SQL문이기때문에 세미콜론(;)으로 문장을 끝맺어야 한다.
• 이 때, 저장 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 위험을 막기 위해 구분자(;)를 다른 구분자로 바꿔주어야하는데 이 때 사용하는 명령어가 DELIMITER 이다.
• 따라서 저장 프로시저 생성 전에 구분자(DELIMITER)를 $$ 으로 바꿔주고 프로시저 작성이 끝났을 때 END $$ 로 저장 프로시저의 끝을 알려준다.
• 마지막으로 구분자를 원래대로 되돌리기 위해 구분자(DELIMITER)를 세미콜론(;)으로 바꿔준다.

저장 프로시저 호출

CALL GetCustomers();

• 이처럼, 저장 프로시저를 활용하면 쿼리문을 일일히 작성하지 않아도 함수처럼 사용하여 손쉽게 쿼리문과 동일한 결과를 조회 할 수 있다.
• 저장 프로시저를 호출하면, MySQL 은 데이터베이스 카달로그에서 프로시저이름을 찾아 명령코드(SQL문)를 컴파일하고 메모리 공간(cache)에 저장하고, 프로시저를 실행시킨다.
• 그리고 같은 세션에서 동일한 저장 프로시저를 한번 더 호출하면, MySQL은 컴파일과정을 다시 거치지 않고 기존의 저장 프로시저를 캐시(cache)에서 불러온다.
• 저장 프로시저는 위 예시처럼 단순 select문으로 작성할 수도 있지만, 매개변수(파라미터)에 개별 value값을 넣어 원하는 결과를 조회할 수도 있다.
• 또한, IF, CASE 그리고 LOOP 같은 제어 흐름 문장을 사용하여 보다 향상된 SQL 코드문 작성도 가능하며 프로시저 내에서 다른 저장프로시저를 호출하여 사용할 수도 있다.

---

3. 활용

저장 프로시저 생성

• 예) 답변 테이블에서 원본글인지 답변글인지를 판별하고, 답변여부에 따라 삭제여부 UPDATE 혹은 DELETE 처리

DELIMITER $$ 
DROP PROCEDURE IF EXISTS deleteReboard $$ #같은 이름이 있다면 지우기
CREATE PROCEDURE deleteReboard #저장 프로시저 생성 
( 
	#변수 선언 
    m_no INT, 
    m_step INT, 
    m_groupNo INT 
) 
BEGIN
      DECLARE cnt INT;
          SET cnt=0;
           IF m_step = 0 THEN
             SELECT COUNT(*) INTO cnt FROM reboard WHERE groupno=m_groupNo; 
           IF cnt > 1 THEN 
             UPDATE reboard SET delflag='Y' WHERE NO=m_no; 
           ELSE 
            DELETE FROM reboard WHERE NO=m_no; 
           END IF; 
END$$ 
DELIMITER ;

저장 프로시저 호출

• 변수는 반드시 프로시저에서 선언한 순서대로 입력해야한다.
• m_no, m_step, m_groupNo 순서

CALL deleteReboard(4, 0, 4);

---

4. 저장 프로시저 내용 확인

SHOW CREATE PROCEDURE 저장_프로시저_이름;

---

5. 저장 프로시저 삭제

DROP PROCEDURE 저장_프로시저_이름;

---

참고: https://recoveryman.tistory.com/186