여러 SQL 문을 하나의 SQL 문처럼 정리하여 CALL ✕ ✕
라는 명령으로 실행할 수 있게 만든 것을 저장 프로시저(Stored Procedure)라고 합니다.
Stored는 '저장하다', Procedur는 '절차'라는 의미입니다. 즉, 저장 프로시저는 일련의 절차를 정리해서 저장한 것입니다.
사전에 준비 한 많은 명령을 자동으로 실행할 수 있기 때문에, 작업의 효율성도 높일 수 있습니다. 단, 중요한 데이터가 축적된 데이터베이스에서 제대로 검증되지 않은 저장 프로시저를 실행하는 것은 매우 위험합니다.
DELIMITER $$
CREATE PROCEDURE GetCustomers()
BEGIN
SELECT customerName, city, state, postalCode, country
FROM customers
ORDER BY customerName;
END $$
DELIMITER ;
저장 프로시저 내부에 사용하는 SQL문은 일반 SQL문이기때문에 세미콜론(;)으로 문장을 끝맺어야 한다.
이 때, 저장 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 위험을 막기 위해 구분자(;)를 다른 구분자로 바꿔주어야하는데 이 때 사용하는 명령어가 DELIMITER
이다.
따라서 저장 프로시저 생성 전에 구분자(DELIMITER)를 $$ 으로 바꿔주고 프로시저 작성이 끝났을 때 END $$ 로 저장 프로시저의 끝을 알려준다.
마지막으로 구분자를 원래대로 되돌리기 위해 구분자(DELIMITER)를 세미콜론(;)으로 바꿔준다.
CALL GetCustomers();
이처럼, 저장 프로시저를 활용하면 쿼리문을 일일히 작성하지 않아도 함수처럼 사용하여 손쉽게 쿼리문과 동일한 결과를 조회 할 수 있다.
저장 프로시저를 호출하면, MySQL 은 데이터베이스 카달로그에서 프로시저이름을 찾아 명령코드(SQL문)를 컴파일하고 메모리 공간(cache)에 저장하고, 프로시저를 실행시킨다.
그리고 같은 세션에서 동일한 저장 프로시저를 한번 더 호출하면, MySQL은 컴파일과정을 다시 거치지 않고 기존의 저장 프로시저를 캐시(cache)에서 불러온다.
저장 프로시저는 위 예시처럼 단순 select문으로 작성할 수도 있지만, 매개변수(파라미터)에 개별 value값을 넣어 원하는 결과를 조회할 수도 있다.
또한, IF
, CASE
그리고 LOOP
같은 제어 흐름 문장을 사용하여 보다 향상된 SQL 코드문 작성도 가능하며 프로시저 내에서 다른 저장프로시저를 호출하여 사용할 수도 있다.
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 ;
CALL deleteReboard(4, 0, 4);
SHOW CREATE PROCEDURE 저장_프로시저_이름;
DROP PROCEDURE 저장_프로시저_이름;