스토어드 프로시저 (1)

prefer·2025년 1월 22일

SQL 기초

목록 보기

13/15

스토어드 프로시저 사용 방법

MySQL의 스토어드 프로시저(stored procedure, 저장 프로시저)는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 보인다. 스토어드 프로시저는 쿼리문의 집합으로, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용된다. 이러한 스토어드 프로시저도 데이터베이스 개체 중 하나로 테이블처럼 각 데이터베이스 내부에 저장된다.

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

위는 스토어드 프로시저를 사용한 예시이다. ‘$$’는 필수 항목으로 스토어드 프로시저를 묶어주는 역할을 한다. ‘$’ 하나만 사용해도 무방하나 명확한 표시를 위해 2개를 사용하는게 일반적이다. 구분자는 ‘$’가 아닌 다른 문자를 사용해도 무방하다. DELIMITER는 ‘구분자’라는 의미로 MySQL에서 구분자는 기본적으로 세미콜론이나, 스토어드 프로시저 내부에 많은 쿼리문 끝에도 세미콜론 사용한다. 따라서 스토어드 프로시저 내부에 세미콜론이 나왔을 때 SQL의 끝인지, 스토어드 프로시저의 끝인지 모호해지므로 구분자를 ‘ $’로 바꿔 ‘$ ’가 나오기 전까지는 스토어드 프로시저 종료하지 않도록 하는것이다.

이렇게 정의한 구분자는 마지막 행에서 DELIMITER를 세미콜론으로 변경하여 MySQL의 구분자가 다시 세미콜론이 되도록 한다.

CREATE PROCEDURE는 스토어드 프로시저를 만드는 구문이다. 이때 IN 또는 OUT 매개변수는 입력 또는 출력 매개변수에 해당한다. 자세한 사용법은 뒤에서 확인해보자.

CALL 프로시저명();

생성한 스토어드 프로시저를 호출하려면 CALL 프로시저명()과 같이 호출하면 된다. 필요하다면 괄호 내부에 매개변수를 넣어서 사용할 수 있다.

<실행>

USE market_db;
DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
	SELECT * FROM member;
END $$
DELIMITER ;

CALL user_proc()

<결과>

회원 테이블을 조회하는 함수 user_proc()을 생성하고, CALL을 통해 호출했다.

DROP PROCEDURE user_proc;

프로시저를 제거하려면 DROP PROCEDURE (프로시저명)을 사용하면 된다. CREATE PROCEDURE는 함수명 옆에 괄호를 붙이지만 DROP PROCEDURE는 괄호를 붙이지 않음에 주의해야한다.

입력 매개변수와 출력 매개변수 지정하기

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

스토어드 프로시저는 IN을 사용하여 입력 매개변수를 지정할 수 있다.

CALL 프로시저명(전달값);

입력 매개변수가 존재하는 스토어드 프로시저를 실행하기 위해서는 괄호 내부에 매개변수에 맞는 값을 전달해야 한다.

OUT 출력_매개변수명 데이터형식

떠힌 스토어드 프로시저는 OUT을 사용하여 출력 매개변수를 지정할 수 있다.

CALL 프로시저명(@변수명);
SELECT @변수명;

출력 매개변수에 값을 대입하기 위해 주로 SELECT ~ INTO문을 사용한다. 출력 매개변수가 존재하는 스토어드 프로시저의 실행을 위해서는 변수를 사용해야 한다.

먼저 입력 매개변수부터 사용해보도록 하자.

<실행>

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

CALL user_proc1('에이핑크');

<결과>

스토어드 프로시저를 정의할 때 VARCHAR 자료형의 입력 매개변수 userName을 정의하였다. 따라서 실제로 user_proc1 스토어드 프로시저를 호출할 때 ‘에이핑크’를 입력 매개변수로 전달하여 userName 매개변수에 할당되었고, 결과가 정상적으로 출력된 것을 확인할 수 있다.

<실행>

DROP PROCEDURE IF EXISTS user_proc2;
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);

<결과>

이번에는 입력 매개변수를 INT 타입으로 userNumber, userHeight 2개를 지정했다. 띠리사 userNumber에 6, userHeight에 165가 할당되어 조건이 제어됐고 결과가 조회되었다.

이번에는 출력 매개변수를 사용해보도록 하자.

<실행>

DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(
	IN txtValue CHAR(10),
	OUT outValue INT)
BEGIN
	INSERT INTO noTable VALUES (NULL, txtValue);
	SELECT MAX(id) INTO outValue FROM noTable;
END $$
DELIMITER ;

CREATE TABLE IF NOT EXISTS noTable
(
	id INT AUTO_INCREMENT PRIMARY KEY,
	txt CHAR(10)
);

CALL user_proc3('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

<결과>

출력 매개변수로 outValue를 지정하고 INTO outValue를 사용하여 noTable의 id 열 최대값을 저장하는 스토어드 프로시저를 생성하였다. 스토어드 프로시저 생성 시점에서 사용한 테이블이 없어도 생성 가능하므로 noTable이 지정 가능하나, CALL로 실행하는 시점에는 사용하는 테이블이 존재해야 한다. 따라서 CALL 이전에 noTable 생성하는 것이다.

출력 매개변수 위치에 @변수명 형태로 변수를 전달할 시 변수에 스토어드 프로시저의 결과가 저장된다.

<실행>

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
	IN memName VARCHAR(10)
)
BEGIN
	DECLARE debutYear INT;
	SELECT YEAR(debut_date) INTO debutYear FROM member
	WHERE mem_name = memName;
	IF (debutYear >= 2015) THEN
		SELECT '신인 가수네요. 화이팅 하세요.' AS '메시지';
	ELSE
		SELECT '고참 가수네요. 그동안 수고하셨어요.' AS '메시지';
	END IF;
END $$
DELIMITER ;

CALL ifelse_proc('오마이걸');

<결과>

IF ~ ELSE 문을 사용한 스토어드 프로시저로 데뷔 연도가 2015년 이전이면 ‘고참 가수’, 2015년 이후이면 ‘신인 가수’를 출력하는 스토어드 프로시저이다. MySQL에는 날짜와 관련된 함수 여러 개 제공한다.

YEAR(날짜)는 연도 반환
MONTH(날짜)는 월 반환
DAY(날짜)는 일 반환
CURDATE()는 현재 날짜 반환

<실행>

DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
	DECLARE hap INT;
	DECLARE num INT;
	SET hap = 0;
	SET num = 1;
	
	WHILE (NUM <= 100) DO
		SET hap = hap + num;
		SET num = num + 1;
	END WHILE;
	SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

<결과>

while 문을 사용한 1부터 100까지의 합계를 구하는 스토어드 프로시저를 생성하고 호출했다.

이번에는 스토어드 프로시저에 동적 쿼리를 사용해보자.

<실행>

DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
	IN tableName VARCHAR(20)
)
BEGIN
	SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
	PREPARE myQuery FROM @sqlQuery;
	EXECUTE myQuery;
	DEALLOCATE PREPARE myQuery;	
END $$
DELIMITER ;

CALL dynamic_proc('member');

<결과>

동적 SQL을 활용한 예시로 동적 SQL이란 다이나믹하게 SQL이 변경되는 것을 말한다. 사용자의 입력에 맞게 출력되는 테이블을 달리하기 위해 동적 SQL을 사용했으며, 테이블 이름을 매개변수로 전달받아 해당 테이블을 조회하는 스토어드 프로시저를 생성했다.

출처

혼자 공부하는 SQL(우재남 저, 한빛미디어)

prefer

기술적 의사결정에 객관성을 가지는 Back-End 개발자 이선호입니다.

스토어드 프로시저 (1)

SQL 기초

스토어드 프로시저 사용 방법

입력 매개변수와 출력 매개변수 지정하기

출처

인덱스(3)

스토어드 프로시저 (2)

0개의 댓글