파트 8-1. 스토어드 프로그램 - 스토어드 프로시저

1. 스토어드 프로시저


스토어드 프로시저는 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다. 자주 사용되는 일반적인 쿼리를 사용하는 것보다는 이것을 모듈화 시켜서 필요할 때마다 호출만 하면 편리하게 운영할 수 있다.

-- 형식
delimiter $$
create procedure 스토어드프로시저이름()
begin
    sql 코딩
end $$
delimiter ;
call 스토어드프로시저이름();

-- 삭제
drop procedure 프로시저이름;


2. 매개 변수의 사용


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

-- 입력,출력 매개변수의 값을 대입하기 위해서는 주로 select...into문을 사용
in 입력_매개변수_이름 데이터_형식

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

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

자세한 사용법은 실습에서 확인하자.

3. 실습


USE sqlDB;
DROP PROCEDURE IF EXISTS userProc1;
DELIMITER $$
-- 입력 매개 변수 사용
CREATE PROCEDURE userProc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM userTbl WHERE name = userName; 
END $$
DELIMITER ;

CALL userProc1('조관우');


DROP PROCEDURE IF EXISTS userProc2;
DELIMITER $$
-- 입력 매개 변수가 2개
CREATE PROCEDURE userProc2(IN userBirth INT, IN userHeight INT)
BEGIN
  SELECT * FROM userTbl 
    WHERE birthYear > userBirth AND height > userHeight;
END $$
DELIMITER ;

CALL userProc2(1970, 178);


DROP PROCEDURE IF EXISTS userProc3;
DELIMITER $$
-- 입력 매개 변수와 출력 매개 변수
CREATE PROCEDURE userProc3(IN txtValue CHAR(10),OUT outValue INT)
BEGIN
	-- 현재 testtbl이 없는데도 프로시저는 만들 수 있음
    -- 만드는 시점에서는 상관없으나 호출시점에는 상관이 있음
  INSERT INTO testTBL VALUES(NULL,txtValue);
  SELECT MAX(id) INTO outValue FROM testTBL; 
END $$
DELIMITER ;
-- 프로시저에 있는 테이블 만들기
CREATE TABLE IF NOT EXISTS testTBL(
    id INT AUTO_INCREMENT PRIMARY KEY, 
    txt CHAR(10)
);
-- 출력 매개 인자를 받아서 사용
CALL userProc3 ('테스트값', @myValue);
SELECT CONCAT('현재 입력된 ID 값 ==>', @myValue);


DROP PROCEDURE IF EXISTS ifelseProc;
DELIMITER $$
-- if else문을 사용한 프로시저
CREATE PROCEDURE ifelseProc(IN userName VARCHAR(10))
BEGIN
    DECLARE bYear INT; -- 변수 선언
    SELECT birthYear into bYear FROM userTbl
        WHERE name = userName;
    IF (bYear >= 1980) THEN
            SELECT '아직 젊군요..';
    ELSE
            SELECT '나이가 지긋하시네요.';
    END IF;
END $$
DELIMITER ;

CALL ifelseProc ('조용필');


DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
-- case문을 사용한 프로시저
CREATE PROCEDURE caseProc(IN userName VARCHAR(10))
BEGIN
    DECLARE bYear INT; 
    DECLARE tti  CHAR(3);-- 띠
    SELECT birthYear INTO bYear FROM userTbl
        WHERE name = userName;
    CASE 
        WHEN ( bYear%12 = 0) THEN    SET tti = '원숭이';
        WHEN ( bYear%12 = 1) THEN    SET tti = '닭';
        WHEN ( bYear%12 = 2) THEN    SET tti = '개';
        WHEN ( bYear%12 = 3) THEN    SET tti = '돼지';
        WHEN ( bYear%12 = 4) THEN    SET tti = '쥐';
        WHEN ( bYear%12 = 5) THEN    SET tti = '소';
        WHEN ( bYear%12 = 6) THEN    SET tti = '호랑이';
        WHEN ( bYear%12 = 7) THEN    SET tti = '토끼';
        WHEN ( bYear%12 = 8) THEN    SET tti = '용';
        WHEN ( bYear%12 = 9) THEN    SET tti = '뱀';
        WHEN ( bYear%12 = 10) THEN    SET tti = '말';
        ELSE SET tti = '양';
    END CASE;
    SELECT CONCAT(userName, '의 띠 ==>', tti);
END $$
DELIMITER ;

CALL caseProc ('김범수');


DROP TABLE IF EXISTS guguTBL;
CREATE TABLE guguTBL (txt VARCHAR(100)); -- 구구단 저장용 테이블
-- while문 사용한 프로시저
DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE str VARCHAR(100); -- 각 단을 문자열로 저장
    DECLARE i INT; -- 구구단 앞자리
    DECLARE k INT; -- 구구단 뒷자리
    SET i = 2; -- 2단부터 계산
    
    WHILE (i < 10) DO  -- 바깥 반복문. 2단~9단까지.
        SET str = ''; -- 각 단의 결과를 저장할 문자열 초기화
        SET k = 1; -- 구구단 뒷자리는 항상 1부터 9까지.
        WHILE (k < 10) DO
            SET str = CONCAT(str, '  ', i, 'x', k, '=', i*k); -- 문자열 만들기
            SET k = k + 1; -- 뒷자리 증가
        END WHILE;
        SET i = i + 1; -- 앞자리 증가
        INSERT INTO guguTBL VALUES(str); -- 각 단의 결과를 테이블에 입력.
    END WHILE;
END $$
DELIMITER ;

CALL whileProc();
SELECT * FROM guguTBL;


DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
-- error 프로시저
CREATE PROCEDURE errorProc()
BEGIN
    DECLARE i INT; -- 1씩 증가하는 값
    DECLARE hap INT; -- 합계 (정수형). 오버플로 발생시킬 예정.
    DECLARE saveHap INT; -- 합계 (정수형). 오버플로 직전의 값을 저장.

    DECLARE EXIT HANDLER FOR 1264 -- INT형 오버플로가 발생하면 이 부분 수행
    BEGIN
        SELECT CONCAT('INT 오버플로 직전의 합계 --> ', saveHap);
        SELECT CONCAT('1+2+3+4+...+',i ,'=오버플로');
    END;
    
    SET i = 1; -- 1부터 증가
    SET hap = 0; -- 합계를 누적
    -- 오버플로우가 발생하면 위쪽에 선언된게 실행된다.
    WHILE (TRUE) DO  -- 무한 루프.
        SET saveHap = hap; -- 오버플로 직전의 합계를 저장
        SET hap = hap + i;  -- 오버플로가 나면 위쪽 exit으로 가서 해당 문 실행
        SET i = i + 1; 
    END WHILE;
END $$
DELIMITER ;

CALL errorProc();

-- 현재 저장 프로시저가 어떤 것이 있는지 확인
SELECT routine_name, routine_definition FROM INFORMATION_SCHEMA.ROUTINES
    WHERE routine_schema = 'sqldb' AND routine_type = 'PROCEDURE';

-- 프로시저의 파라미터 확인법
SELECT parameter_mode, parameter_name, dtd_identifier
	FROM INFORMATION_SCHEMA.PARAMETERS
	WHERE specific_name = 'userProc3';

-- 프로시저 내용 확인법
SHOW CREATE PROCEDURE sqldb.userProc3;


DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
-- 테이블 이름을 파라미터로 넣어서 테이블처럼 사용하기 -> 불가능
-- 파라미터로 들어온 인자는 그저 문자열에 불과함
CREATE PROCEDURE nameProc(IN tblName VARCHAR(20))
BEGIN
 SELECT * FROM tblName;
END $$
DELIMITER ;

CALL nameProc ('userTBL');

-- 테이블 이름을 파라미터로 넣고 사용하고 싶은 경우 -> 동적 sql 사용
-- 프로시저 안에서 동적쿼리 사용할 경우 
-- 쿼리문을 담을 변수는 declare이 아닌 set @으로 변수 선언
DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
CREATE PROCEDURE nameProc(IN tblName VARCHAR(20))
BEGIN
	-- 변수에 쿼리문 저장
  SET @sqlQuery = CONCAT('SELECT * FROM ', tblName);
  -- 동적 sql에 넣고 사용하면 사용 가능
  PREPARE myQuery FROM @sqlQuery;
  EXECUTE myQuery;
  DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL nameProc ('userTBL');

-- 프로시저 삭제
drop procedure nameproc;



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony