[MySQL] 스토어드 프로시저 사용법 정리

스토어드 프로시저란 무엇인가요?

MySQL의 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가하여 일반 프로그래밍 언어와 같은 동작을 이끌어낼 수 있습니다.

 

스토어드 프로시저는 쿼리 문의 집합(단위 작업, 2개 이상의 SQL문)이라 할 수 있습니다.

스토어드 프로시저로 묶은 뒤 필요할 때마다 호출하면 편리하게 MySQL을 운영할 수 있습니다.

 

또한, 스토어드 프로시저도 데이터베이스의 개체 중 한가지입니다.

스토어드 프로시저를 익히는 제일 좋은 방법

스토어드 프로시저의 형식이 복잡해보일 수 있지만 일단 기본 형식을 먼저 익히시고, 그 뒤 추가로 완전한 형식을 학습하시면 됩니다.

 

스토어드 프로시저의 생성, 실행, 삭제 방법

스토어드 프로시저의 생성 코드 형식은 아래와 같습니다.

DELIMITER $$
CREATE PROCEDURE 스-프_이름(IN 또는 OUT 파라미터)
BEGIN
	실행할 SQL문
END $$
DELIMITER ;

 

스토어드 프로시저의 실행 형식은 아래와 같습니다.

CALL 스-프_이름();

 

스토어드 프로시저의 삭제 형식은 아래와 같습니다.

DROP PROCEDURE 스-프_이름;

 

스토어드 프로시저의 입력과 출력 파라미터

스토어드 프로시저의 입력 파라미터를 지정하는 형식은 아래와 같습니다.

IN 입력_파라미터 데이터_타입;

스토어드 프로시저의 출력 파라미터를 지정하는 형식은 아래와 같습니다.

OUT 출력_파라미터 데이터_타입;

출력 파라미터가 있는 스토어드 프로시저의 실행 형식은 아래와 같습니다.

CALL 프로시저_이름(@변수_이름);
SELECT @변수_이름;

 

2개의 입력 파라미터가 있는 스토어드 프로시저 예제입니다.

-- 스토어드 프로시저 생성
DELIMITER $$
CREATE PROCEDURE proc_test(
	IN userNumber INT,
    IN userAge INT
    )
    BEGIN
    SELECT * FROM member
    	WHERE mem_number > userNumber AND mem_age > userAge;
    END $$
DELIMITER ;

CALL proc_test(5, 18); -- 스토어드 프로시저 실행

 

출력 파라미터를 활용하는 스토어드 프로시저입니다.

DELIMITER $$
CREATE PROCEDURE countNumTable(
	IN txt CHAR(12),
    OUT countNum INT)
BEGIN
	INSERT INTO countTable VALUES(NULL, countNum);
    SELECT MAX(id) INTO countNum FROM countNumTable;
END $$
DELIMITER ;

 

스토어드 프로시저를 활용한 동적 SQL

BEGIN과 END 사이에

BEGIN
SET @변수이름 = 대입할_값 혹은 함수; -- "변수 선언" 후 "변수 혹은 함수" 대입
PREPARE SQL_Query FROM @변수이름; -- 실행할 SQL문 작성
EXECUTE SQL_Query; -- SQL문 실행
DEALLOCATE PREPARE SQL_QUERY; -- SQL 연결 해제

 

으로 동적 SQL을 구성할 수 있습니다.