7. 스토어드 프로시저-1

0. 학습 목표

• 스토어드 프로시저의 작성 방법을 이해
• 스토어드 함수와 커서에 대해서 이해하고 활용방법을 학습
• 트리거의 적용 분야와 실제 사용방법을 학습

0. 요약

• 스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능
• 스토어드 프로시저는 BEGIN ~ END 사이에 코드를 구현
• 스토어드 프로시저 작성 후 CALL 문을 통해 스토어드 프로시저 호출
• 입력 매개변수는 스토어드 프로시저에 값을 전달(IN)
• 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려 받음(OUT)
• 동적 SQL은 다이나믹하게 SQL을 생성한 후 실행, PREPARE, EXECUTE 문을 사용

---

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

※핵심 키워드:  스토어드 프로시저, BEGIN - END, CALL, 입력 매개변수, 출력 매개변수, 동적 SQL

스토어드 프로시저를 사용하면 MySQL 안에서도 다른 프로그래밍 로직의 코딩이 가능하다.

---

1-1. 스토어드 프로시저 기본

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

스토어드 프로시저란 MySQL에서 제공하는 프로그램 기능이다.

스토어드 프로시저로 자주 사용하는 일반적인 쿼리를 묶어 놓고 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다

스토어드 프로시저를 만드는 완전한 형식은 복잡하므로 가장 많이 사용되는 필수적인 형식만 알아보자.

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

-------이 부분에 SQL 프로그래밍-------------
END $$
DELIMITRER;

스토어드 프로시저를 호출하는 형식은 다음과 같이 간단하다. 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();

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'
'ITZ', '잇지', '5', '경남', NULL, NULL, '167', '2019-02-12'
'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'
'OMY', '오마이걸', '7', '서울', NULL, NULL, '160', '2015-04-21'
'RED', '레드벨벳', '4', '경북', '054', '55555555', '161', '2014-08-01'
'SPC', '우주소녀', '13', '서울', '02', '88888888', '162', '2016-02-25'
'TWC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'
'WMN', '여자친구', '6', '경기', '031', '33333333', '166', '2015-01-15'

---

스토어드 프로시저의 삭제

삭제는 DROP를 사용해서 하면 된다. 다만 괄호를 붙이지 않아야 한다.

DROP PROCEDURE user_proc;

---

1-2. 스토어드 프로시저 실습

매개변수의 사용

스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다.

매개변수는 다른 말로 파라미터라고도 부른다.

간단히 생각하면 자판기에 동전을 넣고 버튼을 누르는 동작이라고 생각하면 된다. 입력 매개변수를 지정하고 값을 전달하는 것은 다음과 같다.

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

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

스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수도 있다. 출력 매개변수는 커피 자판기에서 미리 준비하고 있는 컵이라고 보면 된다. 비어 있던 컵에는 커피가 담겨저 돌아온다.

출력 매개변수의 형식은 다음과 같다.

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

출력 매개변수에 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용한다. 

출력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

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

---

입력 매개변수의 활용

입력 매개변수가 있는 스토어드 프로시저를 생성하고 실행해 보자.

• '에이핑크'를 입력 매개변수로 전달
• userName 매개변수에 대입
• '에이핑크'에 대한 조회를 수행

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

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'

이번에는 2개의 입력 매개변수가 있는 스토어드 프로시저를 만들어 보자.

• 인원을 6, 평균 키를 165로 전달(userNumber, userHeight)
• 인원이 6을 초과하고 키가 165를 초과하는 가수 그룹을 조회

USE market_db;

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

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'
'TWC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'

---

출력 매개변수의 활용

이번에는 출력 매개변수가 있는 스토어드 프로시저를 생성하자.

• noTable 테이블에 넘겨 받은 값을 입력하고 id 열의 최대 값을 알아내는 기능
• 입력 매개변수는 txtValue, 출력 매개변수는 outValue

USE market_db;

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 ;

noTable은 현재 없는데 user_proc3은 오류없이 잘 만들어 졌다. 아직 CALL을 하지 않았기 때문이다.

noTable 테이블을 만들자.

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

이제 스토어드 프로시저를 호출해 보자. 출력 매개변수의 위치에 @변수명 형태로 변수를 전달해 주면 그 변수에 결과가 저장된다. 그리고 SELECT로 출력하면 된다.

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

# CONCAT('입력된 ID 값 ==>', @myValue)
'입력된 ID 값 ==>1'

---

SQL 프로그래밍의 활용

이번에는 스토어드 프로시저 안에 SQL 프로그래밍을 활용해보자.

• IF ~ ELSE 문을 사용하여 가수 그룹의 데뷔 연도가 2015년 이전이면 '고참가수' 2015년 이후이면 '신인가수'를 출력

use market_db;

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 ('오마이걸');

# 메시지
'신인 가수네요. 화이팅 하세요'

이번에는 WHILE 문을 활용하여 1부터 100까지의 합계를 계산해 보자.

use market_db;

DROP PROCEDURE IF EXISTS while_proc;

DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
	DECLARE hap INT; -- 합계
    DECLARE num INT; -- 1부터 100까지 증가
    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();

# 1~100까지 합계
'5050'

이번에는 동적 SQL을 활용해 보자. 테이블을 조회할 때 고정된 것이 아니라 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회한다.

use market_db;

DROP PROCEDURE IF EXISTS dynamic_proc;

DELIMITER $$
CREATE PROCEDURE dynamic_proc(
	IN tableName VARCHAR(20)
)
BEGIN 
	SET @sqlQuery = CONCAT('SELECT * FROM ', tableName); -- SELECT * FROM member가 됨
    PREPARE myQuery FROM @sqlQuery;                      -- SELECT 문자열을 준비하고 실행
    EXECUTE myQuery;
    DEALLOCATE PREPARE myQuery;                          -- 사용한 myQuery를 해제
END $$
DELIMITER ;

CALL dynamic_proc ('member');

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'
'ITZ', '잇지', '5', '경남', NULL, NULL, '167', '2019-02-12'
'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'
'OMY', '오마이걸', '7', '서울', NULL, NULL, '160', '2015-04-21'
'RED', '레드벨벳', '4', '경북', '054', '55555555', '161', '2014-08-01'
'SPC', '우주소녀', '13', '서울', '02', '88888888', '162', '2016-02-25'
'TWC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'
'WMN', '여자친구', '6', '경기', '031', '33333333', '166', '2015-01-15'