[MySQL] 프로시저 만들기(DECLARE, SET, IN, IF, ELSEIF 등)

사실 프로시저는 처리성능과 재사용면에서 좋지 않다고 합니다.

• 제한된 유연성: 프로시저는 특정 작업을 수행하기 위해 작성된 코드 블록입니다. 이러한 프로시저는 보통 한 가지 목적을 위해 설계되어 있으며, 다른 작업에 대한 재사용성이 제한될 수 있습니다.
• 코드 중복과 일관성 문제: 프로시저를 재사용하기 위해 여러 곳에서 복사 및 붙여넣기를 하면 코드 중복이 발생할 수 있습니다. 이는 유지보수와 일관성에 문제를 일으킬 수 있습니다. 한 프로시저를 수정하면 해당 프로시저를 사용하는 모든 곳에서 수정해야 하므로 코드의 일관성을 유지하기 어려울 수 있습니다.

 

반대로 프로시저를 사용해야 하는 이유가 여러가지 존재한다.

• 코드의 재사용성: 프로시저는 한 번 작성하고 필요할 때마다 호출하여 사용할 수 있습니다.
• 코드의 추상화와 단순화: 프로시저는 복잡한 로직을 단순하고 추상화된 형태로 표현할 수 있습니다.
• 보안과 접근 제어: 프로시저는 데이터베이스 시스템에서도 사용될 수 있습니다.

 

함수와 프로시저의 차이는

함수는 쿼리를 수행한 후에 값을 가져오는게 중점이지만

프로시저는 여러 쿼리를 한번에 수행하는 것이 중점이라고 합니다.

 

DROP PROCEDURE IF EXISTS TEST_PROC; /* 기존에 프로시저가 존재하면 DROP */

DELIMITER $$ /* 프로시저 작성이 완료되지 않았음에도 SQL문이 실행되는 것을 막기 위해 사용된다. */
CREATE PROCEDURE 'TEST_PROC' (
    -- 파라미터 선언
    PARAM_NAME VARCHAR(20),
    PARAM_AGE INT
)
BEGIN
    -- 변수 선언
    DECLARE PARAM_NUM INTEGER;
    
    -- 쿼리문1
    SELECT COUNT(*) + 1
    	INTO PARAM_NUM
        FROM table1;
        
    -- 쿼리문2
    INSERT INTO table1(total_count, user_name, user_age) VALUES(PARAM_NUM, PARAM_NAME, PARAM_AGE);
END $$
DELIMITER ;

 

프로시저 실행

CALL TEST_PROC('테스트이름', 21);

 

DB카탈로그 -> SQL컴파일 -> 메모리저장 -> 실행

또한 If, case, loop 같은 제어,반복문을 사용해 보다 향상된 SQL코드를 작성할 수 있고 프로시저 내에서 다른 프로시저를 호출할 수도 있다.

 

DELIMITER $$
CREATE PROCEDURE 'TEST_PROC2'(
    IN loopCount1 INT,     -- input : 10
    IN loopCount2 INT,     -- input : 20
    OUT rst1 INT,
    OUT rst2 INT,
    INOUT rst3 INT
)
BEGIN
    DECLARE NUM1 INTEGER DEFAULT 0;    -- DEFAULT : 초기값 설정
    DECLARE NUM2 INTEGER DEFAULT 0;
    DECLARE NUM3 INTEGER DEFAULT 0;
    
    WHILE NUM1 < loopCount1 DO           -- NUM1은 0~9까지 10번반복
        WHILE NUM2 < loopCount2 DO       -- NUM2는 0~19까지 20번반복
            SET NUM3 = NUM3 + 1;
            SET NUM2 = NUM2 + 1;
        END WHILE;                     -- NUM2가 19가 되면 나옴
        
        SET NUM1 = NUM1 + 1;
        SET NUM2 = 0;
    END WHILE;
    
    SET rst1 = NUM1;
    SET rst2 = NUM3;
    SET rst3 = rst1 + rst2 + rst3;
END $$
DELIMITER ;

IN

프로시저에 값을 전달하며,

프로시저 내부에서 값을 수정할 수는 있지만 프로시저가 반환되고 나서 호출자가 수정은 불가능하다.

즉 원본 값은 프로시저가 끝난 후에도 유지된다.

 

OUT

프로시저의 값을 호출자에게 다시 Return한다.

초기값은 프로시저 내에서 NULL이며 프로시저가 반환될 때 새로운 값이 호출자에게 Return되고 프로그램이 시작될 때,

OUT 파라미터의 초기값에 접근할 수 없다.

 

INOUT

호출자에 의해 하나의 변수가 초기화되고 프로시저에 의해 수정된다.

간단하게 IN+OUT이라고 생각하면 될 것 같다.

프로시저가 Return될 때 프로시저가 변경한 사항은 호출자에게 Return된다.

 

프로시저 삭제

DROP PROCEDURE IF EXISTS procedure_name;

(이미 프로시저가 정의되어 있다면 삭제합니다.)

 

프로시저 목록 확인

SHOW CREATE PROCEDURE 프로시저이름;

 

조건문(IF) 예시

IF total >= 95 AND total <= 100 THEN
    SET grade = 'A+';
ELSE
    IF total >= 90 THEN
        SET grade = 'A';
    END IF;
END IF;

 

조건문(CASE) 예시

CASE NUM
    WHEN 'Apple' THEN
        SET 'result_Str' = 'apple_cookie';
    WHEN 'Banana' THEN
        SET 'result_Str' = 'banana_cookie';
    ELSE
        SET 'result_Str' = 'orange_cookie';
END CASE;