MySQL 스토어드 프로시저

스토어드 프로시저(stored procedure) 개념과 형식

MySQL에서 제공하는 프로그래밍 기능이다.

쿼리문의 집합으로 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로 사용한다.

자주 사용하는 쿼리문을 스토어드 프로시저로 묶어두고 필요할 때마다 간단히 호출하면 편리하게 MySQL을 운영할 수 있다.

 

작성 방식

delimiter $$

create porcedure 스토어드_프로시저_이름(in 또는 out 매개변수)

begin

    sql 프로그래밍 코드 작성

end $$

delimiter ;

 

delimiter : 구분자

MySQL에서 구분자는 기본적으로 세미콜론(;)을 사용하는데, 스토어드 프로시저 안의 sql문에서도 세미콜론을 사용하기 때문에 sql문의 끝인지, 스토어드 프로시저의 끝인지 모호할 수 있다. 따라서 구분자를 $$로 변경한 이후 $$가 나올때까지는 스토어드 프로시저가 끝난 것이 아니라는 것을 표시하기 위해서 사용한다.

 

스토어드 프로시저 호출 방식

call 스토어드_프로시저_이름();

 

스토어드 프로시저의 생성

drop procedure if exists user_proc;
delimiter $$
create procedure user_proc()
begin
	select * from member;
end $$
delimiter ;

call user_proc();

call user_proc(); 으로 호출하면 안에 미리 작성해둔 select * from member; 문장이 실행된다.

 

스토어드 프로시저의 삭제

drop procedure user_proc;

create procedure 에서는 스토어드 프로시저 이름 뒤에 괄호를 사용했지만 drop procedure 에서는 괄호를 사용하지 않는다.

 

스토어드 프로시저 매개변수의 사용

스토어드 프로시저는 실행시 입력 매개변수(parameter)를 지정할 수 있다.

 

작성 방법

in 입력_매개변수_이름 데이터_형식

 

스토어드 프로시저에 입력 매개변수를 생성했다면 스토어드 프로시저를 실행하기 위해서는 괄호안에 값을 전달해야 사용할 수 있다.

 

사용 방법

call 스토어드_프로시저_이름(전달_값);

 

입력 매개변수의 활용

drop procedure if exists user_proc1;
delimiter $$
create procedure user_proc1(in userName varchar(10)) -- 입력 매개변수 userName 지정
begin
	select * from member where mem_name = userName; -- 입력 매개변수에 대한 데이터 조회
end $$
delimiter ;
call user_proc1("에이핑크"); -- '에이핑크'를 입력 매개변수로 전달

이 스토어드 프로시저를 실행시키기 위해서 '에이핑크' 를 입력 매개변수에 전달하고 전달된 이후에는 스토어드 프로시저가 실행되어 select * from member where mem_name = '에이핑크'; 라는 sql을 실행한 결과를 보여주게 된다.

 

2개 이상의 입력 매개변수가 있는 스토어드 프로시저

drop procedure if exists user_porc2;
delimiter $$
create procedure user_proc2(in userNumber int, in userHeight int)
begin
	select * from member where mem_number > userNumber AND height > userHeight;
end $$
delimiter ;

call user_proc2(6, 165); -- userNumber에는 6, userHeigh에는 165가 대입

이 스토어드 프로시저를 실행시키기 위해서 userNumber 에는 6을, userHeight 에는 165를 입력 매개변수로 전달하고 전달된 이후에는 스토어드 프로시저가 실행되어 위의 sql문장을 실행한 결과를 보여주게 된다.

 

 

스토어드 프로시저는 처리 결과를 출력 매개변수를 통해 얻을 수도 있다.

 

작성 방법

out 출력_매개변수_이름 데이터_형식

 

사용 방법

call 스토어드 프로시저_이름(@변수명);

select @변수명

 

출력 배개변수의 활용

drop procedure if exists user_proc3;
delimiter $$
create procedure user_proc3(in txtValue char(10), out outValue int) -- 출력 매개변수 outValue 지정
begin
	insert into noTable values(null, txtValue);
	select max(id) into outValue from noTable; -- outValue 변수에 id열의 최댓값 저장
end $$
delimiter ;

사실 위에서 사용하는 noTable은 만든적이 없다.

하지만 스토어드 프로시저를 만드는 시점에는 존재하지 않는 테이블을 작성하여 미리 만들어두어도 오류가 발생하지 않는다.

단 call로 호출하여 사용하는 시점에는 noTable 이라는 테이블이 존재하지 않으면 오류를 발생시키므로 사용하기 전에 미리 테이블을 만들어 두는것이 좋다.

 

테이블 생성하기

create table if not exists noTable(
	id int auto_increment primary key,
	txt char(10)
);

 

출력 매개변수가 있는 스토어드 프로시저 호출하기

출력 매개변수의 위치에 @변수명 형태로 변수를 전달하면 그 변수에 결과가 저장된다.

이후 사용하고 싶을 때 @변수명으로 호출하여 사용할 수 있다.

call user_proc3("테스트1", @myValue);
select concat("입력된 ID 값 ==>", @myValue);