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 저장_프로시저_이름;