MySQL 스토어드 프로그램 (1) 프로시저

스토어드 프로그램

스토어드 프로시저

스토어드 프로시저(이하 프로시저)는 여러개의 명령을 묶어서 실행할 수 있는 기능이다.

복잡한 비즈니스 로직을 구현하고, 트랜잭션을 처리하는 목적을 가진다.
그렇기 때문에 스토어드 함수와는 달리 비결정성을 허용한다.

프로시저 목적

1. 복잡한 비즈니스 로직 구현
2. 데이터 변경
3. 자동화된 작업 수행

CREATE PROCEDURE 프로시저 생성

DELIMITER //
CREATE PROCEDURE 프로시저명()
BEGIN
  DECLARE var1 INT;
  SET var1 = 100;

  SELECT * FROM 테이블명1;
  SELECT * FROM 테이블명2;
END //

DELIMITER ;

프로시저명 뒤에 오는 괄호 내부에 파라미터를 줄 수도 있다.

파라미터 종류

1. 입력 파라미터

   IN 파라미터명 데이터형식

2. 출력 파라미터

   OUT 파라미터명 데이터형식

CALL 프로시저 호출

CALL myProc();

프로시저에 파라미터가 존재해 인자를 넘겨주는 경우에는 다음과 같이 호출

CALL myProc(인자1, 인자2, ...);

만약 출력 파라미터가 존재하는 경우에는, 인자로 변수명을 준다.

출력 인자에 값 넣기

DELIMITER //
CREATE PROCEDURE userProc3(
    IN txtValue CHAR(10),
    OUT outValue INT
)
    BEGIN
        INSERT INTO testTBL VALUES(NULL, txtValue);
        SELECT MAX(id) INTO outValue FROM testTBL;
    END //
DELIMITER ;

SELECT ~ INTO 변수명 명령으로 출력 인자에 값을 넣는다.

변수 선언

프로시저 내부에서 변수 선언 시 DECLARE 변수명 데이터타입으로 선언
기본적으로 사용자 정의 변수에 대해서 @을 붙여서 선언하지만, 프로시저 내부에서는 @을 붙이지 않음

할당 시 SET 사용은 똑같음

IF문 사용

IF 조건 THEN
    실행문;
ELSEIF 조건2 THEN
    실행문;
ELSE 
    실행문;
END IF;

일반 프로그래밍 언어와 비슷하지만, IF(조건) 뒤에 THEN을 붙여야하고 프로시저 내부에서 사용하는 경우 END IF;로 구문을 끝내야 한다.

CASE문 사용

CASE 
    WHEN(조건) THEN
        실행문;
    WHEN(조건2) THEN
        실행문;
    ELSE
        실행문;
END CASE;

역시 프로시저 내부에서 사용하는 경우 WHEN(조건) 뒤에 THEN을 붙여야하고, END CASE;로 구문을 끝내야 한다.

프로그래밍 언어의 switch와 유사한 기능을 하지만, 처음으로 참인 조건에서 THEN 이하의 실행문을 실행하고 멈추게 되므로 break는 사용하지 않는다.

delimiter //
create procedure userGrade() 
    begin 
        select u.userid, u.name, sum(price * amount) '총구매액',
    case 
        when (sum(price*amount) >= 10000) then '최우수고객'
        when (sum(price*amount) >= 5000) then '우수고객'
        when (sum(price*amount) >= 1000) then '일반고객'
        else '유령고객'
    end as '고객등급'
        from buytbl b
        right outer join usertbl u
        on b.userid = u.userid
        group by u.userid
        order by sum(price * amount) desc;
    end //
delimiter ;

call userGrade();

만약 SELECT에서 CASE문을 사용한다면, END AS 컬럼명으로 컬럼을 생성한다.

WHILE문 사용

WHILE(조건) DO
    실행문
END WHILE;

WHILE(조건) 뒤에 DO가 붙고, END WHILE로 구문을 끝낸다.

label 지정

delimiter //
create procedure whileProc()
    begin
        declare i int;
        declare sum_of int;
        set i = 1;
        set sum_of = 0;

        myWhile :
        while (i <= 100) do -- myWhile: => while문에 label을 지정
            if(i % 7 = 0) then 
                set i = i + 1;
                iterate myWhile; -- iterate => 지정한 label문으로 가서 계속 진행
            end if;
            set sum_of = sum_of + i;
            if(sum_of > 1000) then
                leave myWhile;
            end if;
            set i = i + 1;
        end while;

        select sum_of;
    end //
    delimiter ;

call whileProc(); -- 호출

while문에 대한 label 지정 시 :을 사용하여 while문 label을 지정할 수 있다.

• ITERATE label명 : 프로그래밍 언어의 while문에서 continue와 같은 기능
• LEAVE label명 : 프로그래밍 언어의 while문에서 break와 같은 기능

중첩 WHILE문

DELIMITER //
CREATE PROCEDURE GUGUPROC()
    BEGIN
        DECLARE i INT;
        DECLARE k INT;
        DECLARE str VARCHAR(100);

        SET k = 2;
        SET str = '';

        WHILE(k < 10) DO
            SET i = 1;
            WHILE (i < 10) DO
                SET str = CONCAT(k, ' * ', i, ' = ', k * i);
                INSERT INTO guguTBL VALUES(str);
                SET i = i + 1;
            END WHILE;
            SET k = k + 1;
        END WHILE;    
    END //
DELIMITER ;

일반 프로그래밍 언어와 똑같이 중첩 WHILE문을 만들 수 있다.
각각의 WHILE문마다 END WHILE;로 구문을 끝내야 한다.

오류 핸들링

DECLARE 액션 HANDLER FOR 오류조건 처리할문장;

• 액션 : 오류 발생 시의 행동.
  CONTINUE -> 처리할문장을 실행
  EXIT -> 종료
• 오류조건 : 어떤 오류를 처리할 것인지.
  오류코드 숫자, SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 오게 됨
• 처리할문장 : 처리할 문장이 하나라면 한 문장이 나오면 되고, 여러 문장이라면 BEGIN END로 묶음

커서

프로시저 내부에서 커서를 사용할 수 있다.
커서는 일반 프로그래밍 언어의 파일 처리와 방법이 비슷하기 때문에 행의 집합을 다루기에 편리한 기능을 많이 제공해준다.

일반 파일 처리에서 파일을 연 후에 한 행씩 읽거나 쓰는 작업을 처리한다. 파일 포인터가 한 행을 읽을 때마다 자동으로 다음 행을 가리킨다.
커서도 이와 비슷한 동작을 한다.

1. 커서의 선언 (DECLARE CURSOR)
2. 반복 조건 선언 (DECLARE CONTINUE HANDLER)
   더 이상 읽을 행이 없을 경우에 실행할 내용 설정
3. 커서 열기 (OPEN)
4. 커서에서 데이터 가져오기 (FETCH)
5. 데이터 처리
   (4 ~ 5번은 LOOP ~ END LOOP 문으로 반복 구간을 지정)
6. 커서 닫기 (CLOSE)

고객 키 평균 프로시저

DELIMITER //
CREATE PROCEDURE cursorProc()
BEGIN
    DECLARE userHeight INT; -- 고객의 키
    DECLARE cnt INT DEFAULT 0; -- 고객의 인원 수 (= 읽은 행의 수)
    DECLARE totalHeight INT DEFAULT 0; -- 키의 합계

    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부

    DECLARE userCursor CURSOR FOR
        SELECT height FROM userTbl;

    DECLARE CONTINUE HANDLER FOR -- 행의 끝이면 TRUE 대입
        NOT FOUND SET endOfRow = TRUE;

    OPEN userCursor;

    cursor_loop : LOOP
        FETCH userCursor INTO userHeight; -- 고객의 키 1개를 대입

        IF endOfRow THEN
            LEAVE cursor_loop;
        END IF;

        IF userHeight IS NOT NULL THEN
            set cnt = cnt + 1;
            set totalHeight = totalHeight + userHeight;
        END IF;
    END LOOP;

    -- 고객 키의 평균을 출력
    SELECT CONCAT('고객 키의 평균 ===>', (totalHeight / cnt));
    CLOSE userCursor;
END //
DELIMITER ;

고객 등급 추가 프로시저

ALTER TABLE userTbl ADD grade varchar(5);

DELIMITER //
CREATE PROCEDURE gradeProc()
BEGIN
    DECLARE id VARCHAR(10); -- 현재 고객 아이디
    DECLARE hap BIGINT; -- 총 구매액
    DECLARE userGrade VARCHAR(5); -- 현재 고객 등급

    DECLARE endOfRow BOOLEAN DEFAULT FALSE;
    DECLARE userCursor CURSOR FOR
        SELECT U.userId, sum(price * amount)
            FROM buyTbl B
                RIGHT OUTER JOIN userTbl U
                ON U.userId = B.userId
            GROUP BY U.userId, U.name;

    DECLARE CONTINUE HANDLER FOR
        NOT FOUND SET endOfRow = TRUE;

    OPEN userCursor;

    grade_loop: LOOP
        FETCH userCursor INTO id, hap; -- 행에서 읽은 값 대입

        IF endOfRow    THEN
            LEAVE grade_loop;
        END IF;    

        CASE
            WHEN (hap >= 10000) THEN
                SET userGrade = '최우수고객';
            WHEN (hap >= 5000) THEN
                SET userGrade = '우수고객';
            WHEN (hap >= 1000) THEN
                SET userGrade = '일반고객';
            ELSE SET userGrade = '유령고객';
        END CASE;

        UPDATE userTbl
            SET grade = userGrade
            WHERE userId = id;

    END LOOP;

    CLOSE userCursor; -- 커서 닫기
END //
DELIMITER ;

PREPARE문

동적 쿼리는 SQL의 일부 값이 런타임에 결정되는 경우 사용된다.
이 때 SQL문을 CONCAT으로 이어서 실행하는 경우에는 SQL Injection 공격에 취약할 수 있다.
이를 피하기 위해 PREPARED STATEMENT를 사용한다.

SET @bYear = 1998;
SET @height = 176;

set @myQuery = 'SELECT * FROM userTbl WHERE birthYear >= ? AND height >= ?';
PREPARE stmt FROM @myQuery;
-- prepared statement 생성

EXECUTE stmt USING @bYear, @height;
-- using으로 ?에 값 넣기. 순서를 지켜야 한다.

DEALLOCATE PREPARE stmt;

PREPARED STATEMENT 생성 시에는 FROM SQL_템플릿_문자열를 사용해 생성한다.
SQL 템플릿 문자열에는 값 자리에만 플레이스홀더(?)를 포함할 수 있다.
또한 변수에 SQL 템플릿 문자열을 담아서 FROM 뒤에 변수명을 써서 PREPARED STATEMENT를 만들 수도 있다.

주의점

MySQL에서 동적 쿼리를 위한 PREPARED STATEMENT를 사용할 때의 주의할 점은 다음과 같다.

1. 테이블명이나 컬럼명과 같은 구조적인 부분은 플레이스홀더(?)로 사용할 수 없다.
   즉, 테이블이나 컬럼명을 동적으로 결정하는 경우에는 CONCAT을 사용하여 만든 SQL 템플릿 문자열로 PREPARED STATEMENT를 만들어야 한다.
2. 공백에 주의
3. 실행 후 할당 해제하기

프로시저의 테이블명 파라미터와 동적 쿼리

다음과 같이 프로시저에서 테이블명을 파라미터로 넘겨서 조회를 하는 경우 조회가 되지 않는다.

DELIMITER //
CREATE PROCEDURE nameProc(
    IN tableName VARCHAR(20)
)
    BEGIN
        SELECT * FROM tableName;
    END //
DELIMITER ;

tableName이라는 인자를 받았지만, 넘겨받은 인자가 아닌 tableName이라는 이름 자체로 테이블 조회를 시도한다.

그래서 이러한 경우에는 동적 쿼리를 사용해야 한다.

DELIMITER //
CREATE PROCEDURE nameProc(
    IN tableName VARCHAR(20)
)
    BEGIN
        SET @myQuery = CONCAT('SELECT * FROM ', tableName);
        PREPARE stmt FROM @myQuery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT * FROM tableName;
    END //
DELIMITER ;

테이블명은 플레이스홀더(?)를 사용할 수 없으므로, CONCAT을 사용해 동적으로 SQL 템플릿 문자열을 만들고 이를 사용해 PREPARED STATEMENT를 생성 및 실행했다.

파라미터 조회

SELECT parameter_mode, parameter_name, dtd_identifier
    FROM INFORMATION_SCHEMA.PARAMETERS
    WHERE specific_name = '프로시저명';

위 명령어를 사용해 해당 프로시저의 파라미터들을 조회할 수 있다.

모든 프로시저 조회

SELECT routine_name, routine_definition
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE routine_schema = 'DB명' and routine_type = 'PROCEDURE';

위 명령어를 사용해 해당 DB에 존재하는 모든 프로시저를 조회할 수 있다.

프로시저의 장점

1. 성능 향상
   긴 코드로 구현된 쿼리 실행 시 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송되어야 한다. 하지만 미리 정의된 프로시저를 사용하면 프로시저명과 파라미터 등의 몇 글자만 전송하면 되므로 네트워크 부하를 어느 정도 줄일 수 있으며 결과적으로 성능을 향상시킨다.

   +) 다른 DBMS에서는 프로시저 초기 호출 1회에만 컴파일된 후 메모리에 로딩. MySQL에서는 매 호출마다 컴파일 되기 때문에 컴파일과 관련해 성능 향상은 불가능 하지만, 네트워크 부하를 줄인다는 점에서 성능 향상을 가져올 수 있음.

2. 유지 관리가 간편하다
   응용 프로그램에서 직접 SQL문을 작성하지 않고 프로시저 이름만 호출하게 설정함으로써 DB에서 관련된 프로시저 내용을 일관되게 수정/유지보수 할 수 있다.

3. 모듈식 프로그래밍 가능

4. 보안을 강화할 수 있다
   사용자별로 테이블에 접근 권한을 주지 않고 스토어드 프로시저에만 접근 권한을 줄 수 있다. 이로써 보안을 강화할 수 있다.
   (뷰와 비슷하게 제한된 접근 권한을 주어 보안을 강화시킴)