[MySQL] 7장 스토어드 프로시저 | 매개변수, 동적 SQL 활용 예제

 

스토어드 프로시저

: SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

 

스토어드 프로시저 문법

DELIMITER $$
CREATE PROCEDURE 이름 ( IN 또는 OUT 매개변수 )
BEGIN
	프로그래밍 코드 작성
END $$
DELIMITER ;

 

예제 1) 테이블 전체 선택

USE market_db;

DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
	SELECT * FROM member; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

CALL user_proc() ; -- 프로시저 호출

 

 

예제 2) 입력 매개변수를 활용한 조건부 검색

DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10)) -- 입력변수 userName 지정
BEGIN
	SELECT * FROM member WHERE mem_name = userName; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

CALL user_proc1("마마무"); -- 호출 시 매개변수 설정

 

 

예제 3) 출력 매개변수에 값 담아서 호출하기

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; -- SELECT INTO 는 검색 걸과를 뒤에 나오는 변수에 담아줌
END $$
DELIMITER ; -- 존재하지 않는 테이블, 프로시저 생성은 상관 X, 실행만 안하면 됨

CALL user_proc3 ('테스트1', @myValue); -- @변수명(출력 매개변수)에 MAX(id) 담아줌
SELECT CONCAT('입력된 ID 값 ==> ', @myValue); -- 출력 매개변수 호출

 

 

예제 4) 스토어드 프로시저 내 SQL 프로그래밍으로 조건문 만들기

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

 

 

예제 5) 스토어드 프로시저 내 SQL 프로그래밍으로 반복문 만들기

DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc() -- 매개변수 X
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();

 

 

예제 6) 동적 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');