MySQL - 스토어드 프로시저

스토어드 프로시저란?

스토어드 프로시저(Stored Procedure, 저장 프로시저)는 MySQL이 제공하는 프로그래밍 기능입니다. 테이블처럼 데이터베이스 개체 중의 한 가지이므로, 데이터베이스 내부에 저장됩니다. 주로 어떠한 동작들을 모아서 모듈화 시켜서, 필요할 때마다 호출만 하는 것으로 편리하게 MySQL을 운영할 수 있도록 합니다.

 

• MySQL의 성능을 향상시킬 수 있음 - 복잡하게 구현된 긴 코드를 전부 통신하지 않고, 프로시저를 한번 호출하는 SQL문만 통신하면 되기 때문에 네트워크에 부하를 줄일 수 있음
• 유지관리가 용이함 - SQL문을 직접 작성하지 않고, 프로시저명만 호출하면 되므로 일관된 유지보수를 할 수 있고, 직관적임.
• 보안이 강화됨 - 사용자에게 테이블의 접근 권한을 주지 않고, 프로시저에만 접근 권한을 주면 사용자가 악의적으로 또는 실수로, 내부 정보에 접근하거나 하는 보안상의 문제를 예방할 수 있음 (뷰와 같은 원리)

 

스토어드 프로시저 형식

DELIMITER $$
CREATE PROCEDURE 스토어드프로시저명(IN|OUT 파라미터)
BEGIN
	-- SQL 프로그래밍 코딩 부분..
END $$
DELIMITER ;

-- CALL 스토어드프로시저명();로 호출

기본적인 스토어드 프로시저의 형식입니다. DELIMITER $$ ~ END $$는 스토어드 프로시저의 범위를 명시해주는 부분이고, BEGIN 뒤에는 코드 부분을 넣고, DELIMITER ;로 종료 문자를 다시 세미콜론(;)으로 변경해 놓으면 됩니다. 그 후에는 CALL 스토어드프로시저명();으로 호출하면, 정의한 BEGIN 부분의 내용들이 한 번 실행됩니다.

 

수정은 ALTER PROCEDURE, 삭제는 DROP PROCEDURE를 사용합니다.

 

CREATE PROCEDURE 프로시저명(IN userName VARCHAR(10))
-- CREATE PROCEDURE 프로시저명(IN userName VARCHAR(10), IN userAge INT)

스토어드 프로시저는 실행 시, 입력 매개 변수를 지정할 수 있습니다. [IN|OUT 파라미터명 자료형] 형식 입니다. 2개 이상의 입력 파라미터가 있는 경우는 , 로 나열하면 됩니다.

CALL 프로시저명('LEE');
-- CALL 프로시저명('LEE', 32);

입력 파라미터가 있는 스토어드 프로시저를 호출할 때는 위 형식을 사용합니다. 'LEE'는 임의의 변수명입니다.

CALL 프로시저명(@out_var);
SELECT @out_var;

출력 파라미터가 있는 스토어드 프로시저를 호출할 때는 위 형식을 사용합니다. 'out_var'는 임의의 변수명입니다.

DROP PROCEDURE IF EXISTS proc1;
DELIMITER $$
CREATE PROCEDURE proc1(
    IN char_var CHAR(10),
    OUT int_var INT
)
BEGIN
    INSERT INTO testTable Values(NULL, char_var);
    SELECT MAX(idx) INTO outValue FROM testTable;
END $$
DELIMITER ;

CREATE TABLE IF NOT EXISTS testTable(
    idx INT AUTO_INCREMENT PRIMARY KEY,
    name CHAR(10)
);

--

CALL proc1('LEE', @value1);
SELECT @value1;

입력 파라미터 1개, 출력 파라미터 1개를 사용하는 프로시저의 예시입니다. 호출 시, INSERT를 이용해서 새로운 행을 추가하고 입력파라미터를 데이터로 넣습니다. 그리고 Max(idx) 즉, 행의 개수를 출력파라미터로 던져줍니다. @value1을 SELECT하면 testTable의 마지막 idx를 출력할 수 있습니다.

 

프로시저의 BEGIN 안에 IF문, CASE문, WHILE문 등을 활용할 수도 있습니다.

DECLARE EXIT HANDLER FOR 오류코드
BEGIN
	...
END;

[DECLARE EXIT HANDLER FOR 오류코드]를 사용하면, 자바의 try-catch문 처럼 오류가 발생했을 시 실행할 코드를 명시할 수 있습니다.

 

스토어드 프로시저 확인

SELECT routin_name, routine,definition FROM INFORMATION_SCHEMA.ROUTINES
    WHERE routin_schema = '데이터베이스명' AND routine_type = 'PROCEDURE';

INFORMATION_SCHEMA의 ROUTINE 테이블을 조회하면 존재하는 스토어드 프로시저의 목록과 각각의 내용(주석 포함)을 확인할 수 있습니다. // 파라미터는 아래의 문법으로 확인합니다.

SELECT param_list, body FROM MYSQL.PROC
    WHERE db='데이터베이스명' AND type='PROCEDURE' AND name='프로시저명';

MYSQL.PROC의 params_list열에 우클릭 후 [Open Value in Viewer]를 선택하면 해당 프로시저의 파라미터들을 확인할 수 있습니다.

SHOW CREATE PROCEDURE 데이터베이스명.프로시저명;

위 SQL문을 실행한 후, Create Procedure라는 열에 우 클릭 후, Open Value in Viewer를 사용하면, 파라미터를 포함한 프로시저의 전체 내용을 볼 수도 있습니다.

 

커서 사용

DROP PROCEDURE IF EXISTS proc1;
DELIMITER $$
CREATE PROCEDURE proc1()
BEGIN
    DECLARE userAge INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totalAge INT Default 0;
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;
    
    DECLARE userCursor CURSOR FOR -- 커서 선언
	SELECT user_age FROM userTable;
	
    DECLARE CONTINUE HANDLER -- 행이 끝이면 endOfRow 변수에 TRUE를 대입
	FOR NOT FOUND SET endOfRow = TRUE;
        
    OPEN userCursor; -- 커서 열기
    
    cursor_loop: LOOP
        FETCH userCursor INTO user_age; -- 고객의 나이 1개를 대입
        
        IF endOfRow THEN -- 더이상 읽을 행이 없으면 Loop 종료
	        LEAVE cursor_loop;
	END IF;
        
        SET cnt = cnt + 1;
        SET totalAge = totalAge + user_Age;
    END LOOP cursor_loop;
    
    -- 고객 나이의 평균을 출력
    SELECT (totalAge/cnt) AS '평균 나이';
    CLOSE userCursor; -- 커서 닫기
END $$
DELIMITER ;

커서를 사용해서 고객의 평균 나이를 구하는 스토어드 프로시저입니다. 물론 AVG를 쓰면 훨씬 효율적이지만, 커서의 사용법을 익히기 위한 간단한 예시입니다. 아래는 스토어드 프로시저의 프로세스입니다. 변수 대입이나, 선언에 관련된 기본적인 설명은 코드만 보면 MySQL을 공부하실 정도의 수준이시라면 알 수 있으리라 생각해서 생략했습니다.

 

커서를 선언하고 user_age 열을 가지도록 정의-> [ 커서의 값을 변수 userAge에 대입하고, cnt를 1씩 증가시킴 -> totalAge에 userAge를 더함 -> 커서를 다음행으로 넘김 ] 반복-> 더이상 행이 없으면 루프를 종료함

 

CALL proc1();로 호출한다면, 고객들의 평균 나이가 출력될 것입니다.