[혼공SQL] 스토어드 프로시저

  프로시저의 생성 

스토어드 프로시저는 데이터베이스의 개체 중 하나로써 SQL로 프로그래밍 기능(반복문, 조건문 등)을 제공해준다.

 

사용되는 완전한 형식은 복잡하므로, 자주 쓰이는 형태를 다뤄본다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
	-- SQL 프로그래밍 코드 작성
END $$
DELIMITER ;

 

여기서 눈여겨 보아야할 것은 DELIMTER이다. SQL의 구분자를 변경하는 키워드인데, 이를 사용하는 이유는 다음과 같다.

 

우리가 프로시저 생성에서 SQL 프로그래밍 코드를 작성할 때 사용하는 구분자인 세미콜론(;)은 내부 SQL 구문일 뿐이지, 실제 문장의 끝(프로시저의 끝)을 의미하지 않는다. 문장의 끝은 END 키워드와 구분자로 끝나야하기 때문이다.

 

하지만 만약 우리가 구분자를 세미콜론으로 하게된다면 내부 SQL 프로그래밍 코드와 헷갈릴 염려가 있다. 따라서, 스토어드 프로시저를 생성할 때 구분자를 바꾸고, SQL 프로그래밍 코드에서는 세미콜론으로 구분자를, 프로시저 종료 시에는 DELIMITER로 변경한 구분자를 사용하면 된다.

 

주의할 점은 구분자를 변경 후에는 다시 세미콜론으로 돌려놓아야 한다는 것이다.

 

 

  프로시저의 삭제

프로시저는 아래의 구문으로 삭제할 수 있다.

DROP PROCEDURE 스토어드_프로시저_이름;

 

다만, 생성할 때는 소괄호를 이용해서 IN과 OUT의 파라미터를 받을 수 있었다면, 삭제 시에는 그러한 것이 필요하지 않으므로 따로 소괄호를 표기하지 않는다.

 

 

  매개변수의 사용

스토어드 프로시저에서는 파라미터로 입력 파라미터와 출력 파라미터를 전달하여 원하는 값을 전달하고, 가져올 수 있다.

 

  입력 매개변수의 사용

입력 매개변수는 아래의 구문으로 지정할 수 있다.

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

 

또한 이러한 매개변수를 사용하기 위해서는 CALL로 스토어드 프로시저를 호출할 떄, 소괄호 안에 입력 파라미터의 정보를 전달해주면 된다.

CALL 프로시저_이름(전달_값);

 

 

실제 사용되는 예시는 아래와 같다.

DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc(IN userName VARCHAR(10))
BEGIN
	SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc('아이유');

 

CALL을 통해 member 테이블의 mem_name에 해당하는 데이터 중 하나인 '아이유'를 입력하였으므로, 출력 결과는 member 테이블에서 아이유 이름을 가진 데이터가 조회되었을 것이다.

 

 

입력 파라미터가 2개인 경우도 마찬가지이다. 이 때는 두 파라미터를 구분하기 위한 쉼표(,)만 작성하여 주면 동일하게 사용할 수 있다.

DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc(
	IN userName VARCHAR(10),
	IN userNumber INT	)
BEGIN
	SELECT * FROM member WHERE mem_name = userName AND mem_number = userNumber;
END $$
DELIMITER ;

CALL user_proc('아이유', 24);

 

  출력 매개변수의 사용

출력 매개변수를 통해서 스토어드 프로시저의 출력 결과를 가져올 수 있다. 이 때는 SELECT ~ INTO 구문을 사용해야 한다. 이 구문은 출력 결과를 INTO 뒤에 오는 테이블에 넣어주는 기능을 한다.

DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc(
	IN inValue CHAR(10),
    OUT outValue CHAR(10)	)
BEGIN
	INSERT INTO table1 VALUES(inValue);
    SELECT * INTO outValue FROM table1;
END $$
DELIMITER ;

CALL user_proc('test', @outValue);
SELECT @outValue;

 

프로시저 내의 SQL 구문은 단순히, 입력된 inValue를 테이블에 넣고, 해당 값을 뽑아와 outValue에 넣는 의미이다. 여기서 table1은 이미 만들어져있다고 가정한다.

 

스토어드 프로시저 내에 작성된 테이블은 현재 존재하지 않아도 선언은 가능하다. 따라서 table1이 존재하지 않아도 user_proc 프로시저는 선언이 가능하다. 하지만 실행 시 오류가 발생하며, 실행하기 이전에 table1이라는 테이블을 만들어주어야 한다.