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

728x90

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

일련의 SQL문장을 선언해서 MySQL에 저장하고, 해당 SQL문을 함수처럼 사용하는것으로 만들어 두기만 하면 함수처럼 호출하여 편하게 사용할 수 있다.

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 #SQL 프로그래밍 부분 시작 
DECLARE cnt INT; #답변 변수 설정 
SET cnt=0; #변수 초기화 
/*답변이 달린 원본 글인 경우에는 삭제하지 말고 delFlag를 Y 로 update하자*/ 
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; 
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 저장_프로시저_이름;

참고 :

recoveryman.tistory.com/186

www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx