[MySQL] 스토어드 프로시저

1. 스토어드 프로시저란

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

쿼리 문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용한다.

* 자주 사용하는 일반적인 쿼리를 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출하여 사용할 수 있다.

 

DELIMITER $$        		--구분자
CREATE PROCEDURE 프로시저이름 (IN 또는 OUT 매개변수)
BEGIN
	...
END $$				--스토어드 프로시저 종료
DELIMITER ;			--종료문자를 다시 세미콜론으로 변경


CALL 프로시저이름 ();

* Delimiter : 구분자

 MySQL에서는 SQL과 세미콜론(;)을 포함한 여러 문장들로 구성된 복합문인 Each stored Program을 

서버로 보내기위해 delimiter를 일시적으로 재정의 해주어야 한다!

(Each stored Program : 트리거, Stored Procedure)

보통 $$를 많이 사용하고, \, @의 문자를 사용해도 되지만 다른 기호와 중복될 수 있으니 두 개를 연속해서 사용해야 한다.

 

 

2. 프로시저 삭제

DROP PROCEDURE 프로시저이름;

 

3. 스토어드 프로시저 실습

1) 매개변수의 사용

실행 시 입력 매개변수를 지정할 수 있다.

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

호출은 

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

 

 

처리된 결과를 출력 매개변수로 얻을 수 있다.

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

SELECT 컬럼명 INTO 변수명
FROM ...;

호출은 

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

SELECT @변수명;

프로시저를 실행한 결과를 @변수명에 받고 실제 쿼리에서 SELECT @변수명 으로 확인 가능!

 

* 실습 1

DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
	SELECT *
    FROM	member
    where mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1('블랙핑크');

실행 결과는

 

* 실습 2 (입력 매개변수와 출력 매개변수)

DELIMITER $$
CREATE PROCEDURE user_proc2(IN userName VARCHAR(10), OUT MEMBER_ID CHAR(8))
BEGIN
	SELECT mem_id into MEMBER_ID
    FROM	member
    where mem_name = userName;
END $$
DELIMITER ;

CALL user_proc2('블랙핑크', @MEMBER_ID);
SELECT @MEMBER_ID;

 

 

* 실습 3 (테이블이 없는데 프로시저 만들수 있나?!)

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

DESC noTable;  
-- Error Code: 1146. Table shop_db.notable doesnt exist	0.000 sec
-- 스토어드 프로시저를 만드는 시점에는 테이블이 존재하지 않아도 된다. 실행하는 시점에만 있으면됨

CREATE TABLE noTable(
id INT auto_increment PRIMARY KEY,
txt CHAR(10)
);

Call user_proc3('테스트1', @myValue);
SELECT @myValue;

스토어드 프로시저를 만드는 시점에 테이블이 존재하지 않아도 에러 x

단, 프로시저를 실행하는 시점에는 있어야함!

 

 

* 실습 4 ( 변수 선언)

DELIMITER $$
CREATE PROCEDURE user_proc4(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 ;

프로시저 외부에서는 변수선언 : @변수명

프로시저 내부에서는 변수선언 : DECLARE 변수명 타입

 

* 실습 5 (동적 SQL)

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

동적 SQL 을 활용한 예제로

PREPARE와 EXECUTE 예약어를 사용한다.

PREPARE는 SQL문을 실행하지 않고 미리준비해놓음.

EXECUTE는 준비한 SQL문을 실행함.

그리고 실행 후에는 DEALLOCATE PREPARE를 해주어야 한다!