[MySQL] 스토어드 프로시저 (Stored Procedure) 에 대해 알아보자

스토어드 프로시저 (Stored Procedure)

• MySQL에서 제공하는 프로그래밍 기능
• 쿼리문의 집합, 한 동작을 일괄 처리하는 용도로 사용

 

- 스토어드 프로시저 생성

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN

     스토어드_프로시저_내용

END $$
DELIMITER ;

 

 

- 스토어드 프로시저 호출 (실행)

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

 

 

 

매개 변수로 유연하고 강력한 스토어드 프로시저 만들기

 

입력 매개 변수

 

- 입력 매개 변수 지정

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

 

- 입력 매개 변수가 있는 스토어드 프로시저 실행

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

 

 

 

- 1개의 입력 매개 변수가 있는 스토어드 프로시저 생성 / 호출

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('에이핑크');

 

 

- 2개의 입력 매개 변수가 있는 스토어드 프로시저 생성 / 호출

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);

 

 

 

출력 매개 변수

 

- 출력 매개 변수 지정

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

 

- 출력 매개 변수가 있는 스토어드 프로시저 실행

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

 

 

- 존재하지 않는 테이블을 사용해 스토어드 프로시저 생성

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;

DESC noTable; ## noTable의 구조 확인해보기

→ noTable이라는 테이블은 market_db에 애초에 존재하지 않는 테이블이었으므로 구조를 확인할 수 없다.

→ 스토어드 프로시저를 만드는 시점에서는 아직 존재하지 않는 테이블을 사용할 수 있다.

→ 단, 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);

 

 

 

 

SQL 프로그래밍의 활용

스토어드 프로시저 안에 SQL 프로그래밍 활용하기

조건문인 IF ~ ELSE 구문을 사용해보자.

 

 

- 데뷔 연도가 2015년 이전이면 ‘고참’, 이후이면 ‘신인’을 출력하는 스토어드 프로시저

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
	IN memeName 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('오마이걸');

 

 

 

- WHILE 문을 활용해 1부터 100까지의 합을 계산하는 스토어드 프로시저

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();

 

 

 

 

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

테이블을 조회하는 기능을 가진 스토어드 프로시저를 생선한다

-> 이 때 테이블은 고정된 것이 아니며, 이름을 매개변수로 전달 받아 테이블을 조회한다.

 

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');

 

CONCAT 문 내에서 ‘SELECT * FROM ’을 쓸 때 FROM 뒤에 반드시 공백이 있어야 한다

→ 공백을 안줘서 실행 시 계속 syntax error가 발생했음