[SQL] - 스토어드 프로시저 사용 방법

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

• SQL에서 제공하는 프로그래밍 기능
• 쿼리 문의 집합

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

SQL 코드

END $$
DELIMITER ;

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

2. 스토어드 프로시저의 생성 / 삭제

• 생성

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

CALL user_proc();

• 삭제

DROP PROCEDURE user_proc;

3. 매개변수의 사용

• 입력 매개변수

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

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

• 입력 매개변수의 활용

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 ;

나는 noTable을 만든적이 없다. 하지만 프로시저는 만들어진다. 테이블이 없어도 만드는건 지장이 없지만 call로 실행하는 시점에는 존재해야 한다.

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

호출

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

SELECT * FROM noTable;

한개가 추가 되었기 때문에 위에 출력 프로시저에서 1이 나옴

4. SQL 프로그래밍의 활용

• IF ~ ELSE 활용

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

이름을 받아서 연도를 가져오고 그 연도를 이용해서 2015년 이전 이후로 구분하여 신인, 고참 가수를 출력

날짜 관련 함수

• YEAR(날짜) : 연
• MONTH(날짜) : 월
• DAY(날짜) : 일
• CURDATE() : 현재 날짜
  
  

• WHILE 문 활용

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