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

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

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

저장 프로시저 생성/ 호출

예시)

select customerName, city, state, postalCode, country from customers order by customerName;

위 SQL문을 GetCustomers()라는 이름의 Stored Procedure문으로 작성하고 실행해보자.

저장 프로시저 생성

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값을 넣어 원하는 결과를 조회할 수도 있다.

활용

저장 프로시저 내용 확인

show create procedure GetCustomers;

저장 프로시저 삭제

drop procedure GetCustmers;