스토어드 프로시저

스토어드 프로시저를 사용하면 복잡한 SQL을 한 번에 실행 할 수 있습니다.
MySQL의 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍언어와 비슷한 효과를 낼 수 있습니다.

스트어드 프로시저의 개념과 형식

스트어드 프로시저란 MySQL에서 제공하는 프로그래밍 기능입니다.
C, 자바, 파이썬 등의 프로그래밍과는 조금 차이가 있지만, MySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공합니다.
퀴리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리 하기 위한 용도로도 사용합니다. 자주 사용하는 일반적인 쿼리를 반복하는 것 보다는
스토어드 프로시저를 묶어 놓고 필요할 때 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있습니다.

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

		이 부분에 SQL 프로그래밍을 코드를 작성
        
END $$
DELIMITER;

DELIMITER 의미

'구분자'라는 의미입니다. 세미콜론은 SQL의 끝으로만 표시하고, $$는 스토어드 프로시저의 끝으로 사용합니다. 그리고 마지막 행에서 DELIMITER를 세미콜론으로 바꿔주면 원래대로 MySQL의 구분자가 세미콜론으로 돌아옵니다.

DELIMITER $$ ~ END $$ 필수 항목으로 스토어드 프로시저를 묶어주는 기능을 합니다. $$는 $ 1개만 사용해도 되지만 명확하게 표시하기 위해 2개를 사용합니다.
(##, %%, &&, //) 등으로 바꿔도 됩니다.

CREATE PROCEDURE 스토어드프로시저이름( IN 또는 OUT 매개변수 ) 프로시저의 이름을 정해줍니다. 가능하면 이름만으로도 스토어드 프로시저라는 것을 알 수 있도록 표현하는 것이 좋습니다.

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

CALL~ 은 스토어드 프로시저를 실행(호출)합니다.

SQL 프로그래밍의 활용

member 테이블을 이용해서 IF ~ ELSE 조건문을 사용해보겠습니다.
debut_date(데뷔 연도)가 2015년 이전이면 '고참 가수', 2015년 이후 (2015년 포함)이면 '신인 가수'를 출력하는 스토어드 프로시저를 작성해보겠습니다.

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 $$
DELIMITER ;

CALL ifelse_proc('오마이걸');

IN memName VARCHAR(10)

--> 매개 변수로 가수 그룹의 이름을 넘겨 받습니다.

DECLARE debutYear INT;

--> 변수를 선언을 하고

SELECT YEAR(debut_date) into debutYear FROM member
    	WHERE mem_name = memName;

--> YEAR() 함수로 연도만 추출해서 변수 debutYear에 저장합니다.

IF (debutYear >= 2015) THEN
		SELECT '신인 가수네요. 화이팅 하세요,' AS '메시지';
ELSE
		SELECT '고참 가수네요. 그동안 수고하셨어요.' AS '메시지';

--> IF~ ELSE 문으로 데뷔 연도에 따라서 필요한 내용을 출력합니다.

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