[MySQL] 스토어드 프로시저(Stored Procedure)

개요

• 평소 SQL문을 사용할 때 쿼리문을 자주 사용하는데 특정한 경우 프로시저를 사용하면 편리성 면에서 효과가 있음
• MySQL 프로시저(Procedure)는 저장된 프로시저로도 알려져 있으며, 서버에 저장된 하나 이상의 SQL 문장의 집합
• 프로시저는 여러 SQL문을 모아서 재사용할 수 있는 방법을 제공하며 데이터베이스 작업의 자동화, 코드 재사용성 향상, 그리고 복잡한 로직을 캡슐화하는데 유용함

프로시저의 주요 특징

1.입력 및 출력 매개변수 지원

프로시저는 'IN', 'OUT', 'INOUT' 매개변수를 사용할 수 있음

• IN: 호출할 때 전달되는 입력 매개변수
• OUT: 프로시저가 반환하는 출려 ㄱ매개변수
• INOUT: 입력과 출력을 모두 담당하는 매개변수

2. SQL 문장의 집합

• 여러 SQL 문을 포함할 수 있으며, 복잡한 로직을 구현할 수 있음

3. 제어 구조

• IF, CASE, LOOP, WHILE, REPEAT, LEAVE, ITERATE 와 같은 제어 구조를 사용할 수 있음

4. 트랜잭션 제어

• 프로시저 내에서 트랜잭션을 관리할 수 있음 (START TRANSACTION, COMMIT, ROLLBACK 등)

5. 보안

• 프로시저는 데이터베이스에서 일련의 작업을 수행할 수 있는 권한을 캡슐화함. 이를 통해 보안 및 접근 제어를 쉽게 관리할 수 있음

프로시저의 기본 구조

DDELIMETER $$

CREATE PROCEDURE procedure_name (IN parameter1 datatype, OUT parameter2 datatype)
BEGIN
    -- SQL statement go here
    SELECT colum1 INTO parameter2
    FROM table
    WHERE column2 = paramter1;

    -- You can add more SQL statements or control structures like IF, WHILE, etc.

END $$

DELIMETER ;

프로시저 예시

다음은 두 개의 숫자를 더하는 간단한 프로시저이다.

DELIMETER $$

CREATE PROCEDURE AddNumber(IN num1 INT, IN num2 INT, OUT sum INT)
BEGIN
    SET sum = num1 + num2
END $$

DELIMITER ;

프로시저 호출

CALL AddNumber(5, 10, @result);
SELECT @resultl -- 15

프로시저의 활용

1. 복잡한 비즈니스 로직 구현: 여러 테이블 간의 복잡한 데이터 처리 작업을 프로시저로 캡슐화하여 관리할 수 있음
2. 반복 작업 자동화: 일일 보고서 생성, 데이터 백업, 대량의 데이터 처리 등 반복적인 작업을 자동화할 수 있음
3. 보안 관리: 특정 데이터베이스 작업에 대한 접근을 제어하고 관리할 수 있음

활용 예시 1. 오래된 구매 이력을 삭제

create
    definer = 계정명@`%` procedure delete_old_purchase2(IN start_date varchar(19), IN keep_years int, IN period int)
BEGIN

    /***
      :param_start_date: 기준 날짜를 입력 받는다. 프로시저 실행 날짜
      :param keep_years: 유지할 데이터의 기간을 입력받는다. (년 단위)
      :param period: 삭제할 기간을 일 단위로 입력 받는다.
     */
    DECLARE delete_start_time BIGINT;    -- 삭제할 데이터의 시작 시간
    DECLARE delete_end_time BIGINT;      -- 삭제할 데이터의 종료 시간
    DECLARE five_years_ago_limit BIGINT; -- 5년 이내 데이터는 삭제하지 않는다.
    DECLARE cnt_rows BIGINT DEFAULT 0;   -- 반복문 내에서 삭제된 행 수를 저장하기 위한 변수
    DECLARE min_created_at BIGINT;       -- 테이블에서 가장 오래된 데이터의 생성 시간

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'SQL Error occurred, transaction rollback.';
    END;

    -- start_date를 DATETIME 형식으로 변환 (예: 2024-07-05 00:00:00)
    SET @start_datetime = STR_TO_DATE(start_date,  '%Y-%m-%d 00:00:00'); -- 2024-07-05 00:00:00

    -- 기준 날짜에서 keep_years 년 전의 시간을 UNIX 타임스탬프로 변환 (예: 2019-07-05 00:00:00)
    SET five_years_ago_limit = UNIX_TIMESTAMP(DATE_SUB(@start_datetime, INTERVAL keep_years YEAR)) * 1000; -- 2019-07-05 00:00:00

    -- 삭제할 데이터의 초기 시작 시간을 설정 (예: 테이블의 가장 오래된 데이터 시간)
    SET min_created_at = (SELECT MIN(created_at) FROM lezhincomics.purchase2);  -- 예: 2014-01-02 09:00:00
    SET delete_start_time = min_created_at;

    -- period 기간 동안의 종료 시간을 설정
    SET delete_end_time = delete_start_time + (period * 86400000); -- 2014-01-02 09:00:00 + period(3일)

    -- 가장 오래된 데이터가 유지할 기간 내에 있는지 확인
    IF min_created_at >= five_years_ago_limit THEN
        SELECT 'No data to delete, the oldest data is within the retention period.';
    ELSE
        -- 한 번의 트랜잭션으로 지정된 시간 범위 내의 데이터를 삭제
        REPEAT
            DELETE FROM lezhincomics.purchase2
            WHERE created_at >= delete_start_time -- 예: 2014-01-02 09:00:00
            AND created_at < delete_end_time -- 예: 2014-01-05 09:00:00
            ANd created_at < five_years_ago_limit -- 5년 전 데이터까지만 삭제
            LIMIT 1000;

            SET cnt_rows = ROW_COUNT();

            -- 0.1초 대기
            DO SLEEP(0.1);

        UNTIL cnt_rows = 0 END REPEAT;
    END IF;
END;

활용 예시 2. 구매 이력 데이터를 구매 이력 보관 테이블로 동기화

## UTC로 start_date 전달
# 2024-07-31 03:00 실행
# start_date: 2024-07-30 18:00:00 UTC
create
    definer = 계정명@`%` procedure recent_one_year_migrate_purchase3(IN start_date varchar(19), IN period int)
BEGIN
    DECLARE insert_start_time BIGINT;
    DECLARE insert_end_time BIGINT;
    DECLARE one_year_ago_limit BIGINT;
    DECLARE cnt_rows BIGINT DEFAULT 0;
    DECLARE cur_time BIGINT;
    DECLARE last_insert_time BIGINT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred, transaction rolled back.';
    END;

    -- @start_datetime: 2024-07-30 18:00:00 UTC
#     SET @start_datetime = STR_TO_DATE(start_date, '%Y-%m-%d %H:%i:%s');

    -- 현재 시간을 UNIX 타임스탬프로 변환 (UTC 2024-07-31 03:00:00)
    SET cur_time = UNIX_TIMESTAMP() * 1000;

    -- 기준 날짜에서 1년 전의 시간을 UNIX 타임스탬프로 변환 UTC 2023-07-30 18:00:00
#     SET one_year_ago_limit = UNIX_TIMESTAMP(DATE_SUB(@start_datetime, INTERVAL 1 YEAR)) * 1000;

    -- 이전에 저장된 마지막 이관 시간 조회
    SELECT MAX(created_at) INTO last_insert_time FROM lezhincomics.purchase3;

    -- 이전에 저장된 마지막 이관 시간이 없으면 start_date 사용
    IF last_insert_time IS NULL THEN
#         SET insert_start_time = one_year_ago_limit;
    ELSE
        SET insert_start_time = last_insert_time + 1; -- 마지막 삽입 시간 다음부터 시작
    END IF;

    -- 기준 날짜를 기준으로 종료 시간을 설정
    SET insert_end_time = insert_start_time + (period * 86400000);

    -- 현재 시간보다 미래의 시간을 참조하지 않도록 조정
    IF insert_end_time > cur_time THEN
        SET insert_end_time = cur_time;
    END IF;

    -- 트랜잭션 시작
    START TRANSACTION;

    -- 지정된 시간 범위 내의 데이터를 가져와서 삽입
    INSERT IGNORE INTO lezhincomics.purchase3
    (
        SELECT * FROM lezhincomics.purchase2
        WHERE created_at >= insert_start_time
        AND created_at < insert_end_time
    );

    SET cnt_rows = ROW_COUNT();

    -- 트랜잭션 커밋
    COMMIT;

    -- 결과 확인용 메시지
    IF cnt_rows > 0 THEN
        SELECT CONCAT(cnt_rows, ' rows migrated from purchase2 to purchase3.');
    ELSE
        SELECT 'No data migrated, no rows matched the criteria.';
    END IF;
END;




select max(created_at) from lezhincomics.purchase3;

활용 예시 3. 구매 이력 데이터를 구매 이력 보관 테이블로 아카이빙

uspa_archiving_insert_purchsae

create
    definer = 계정명@`%` procedure uspa_archiving_insert_purchase2(IN archiving_date varchar(10),
                                                                             IN year_ago int,
                                                                             OUT archive_purchase_max_created_at bigint,
                                                                             OUT archive_limit bigint)
BEGIN
    /****************************************************************
    DB : lezhincomics
    SP : uspa_purchase2_archiving

    해당 프로시저는 입력받은 날짜를 유닉크타입스탬프*밀리초 로 변환하여 아카이빙 테이블에 데이터를 복사하는 프로시저이다.

    :param archiving_date: 기준 날짜를 입력 받는다. 보통은 프로시저 실행일 이 될 것임.
    :param year_ago: 기준 날짜보다 몇 년 전의 날짜를 아카이빙 할 것인지 입력 받는다. 보통은 5년이 될 것임.

    호출 예시:
    call uspa_archiving_purchase2('2024-06-10', 5);

    *****************************************************************
    -- #01 | 2024-06-10 | yyun | 최초 생성
    *****************************************************************/
    DECLARE archive_limit_utc bigint;
    DECLARE archive_limit_kst bigint;
    DECLARE archive_limit_kst_midnight VARCHAR(19);
    #DECLARE archive_limit bigint;
    #DECLARE archive_purchase_max_created_at bigint;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
        ROLLBACK;
        SELECT 'Error occurred, transaction rolled back.';
    END;

    -- 트랜잭션 시작
    START TRANSACTION;

    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- 1. Rundeck에서 매일 새벽 4시에 주기적으로 해당 프로시저를 호출한다. (KST 2024.08.03 04:00:00)
    -- 2. archiving_date: 2024.08.02 19:00:00  (파라미터는 UTC로 넘어옴)
    -- 3. UTC로 받은 시간을 1년 전 날짜를 구한다.   (2023.08.02 19:00)
    SET archive_limit_utc = UNIX_TIMESTAMP(DATE_SUB(archiving_date, INTERVAL  year_ago YEAR)) * 1000;

    -- 4. 9시간을 더해 KST로 변환한다. (KST 2023.08.03 04:00:00)
    SET archive_limit_kst = archive_limit_utc + (60 * 60 * 9 * 1000);

    -- 5. KST 로 날짜를 맞춘 뒤 00분 00초로 변환 (2023.08.03 00:00:00)
    set archive_limit_kst_midnight = DATE_FORMAT(FROM_UNIXTIME(archive_limit_kst / 1000), '%Y-%m-%d 00:00:00');

    -- 6. 5번을 unix_timestamp로 다시 UTC 변환 (2023.08.02 15:00:00)
    -- KST 00:00 -> UTC 15:00
    set archive_limit = UNIX_TIMESTAMP(archive_limit_kst_midnight) * 1000 - (60 * 60 * 9 * 1000); 

    -- 7. archive_purchase2에 저장된 가장 최근 max값을 조회한다.
    select max(created_at) into archive_purchase_max_created_at from archive_purchase2;


    -- 8. archive_purchase2에 저장된 데이터가 없다면 archive_limit에서 하루를 뺸다.
    if archive_purchase_max_created_at is null then
        set archive_purchase_max_created_at = archive_limit - (60 * 60 * 24 * 1 * 1000);
    end if;

    -- 9. 즉 아카이빙 되는 대상은 max_created < 아카이빙 대상 < archive_limit (2023.08.02 15:00:00 -> KST 2023.08.03 00:00:0

    -- 아카이빙 테이블에 데이터가 있다면 삭제 후 데이터 입력을 한다.
    #IF EXISTS(SELECT 1
    #          FROM archive_purchase2
    #          WHERE created_at > archive_limit - 86400000 AND created_at <= archive_limit) THEN
    #     CALL uspa_archiving_delete_purchase2(archiving_date, 5);
    #END IF;

    INSERT IGNORE INTO archive_purchase2 (
        balance_bonus_coin,
        balance_coin,
        balance_point,
        bonus_coin,
        bulk_purchase_id,
        canceled,
        coin,
        coin_product_id,
        company_event_id,
        country,
        created_at,
        currency,
        episode_coin,
        invitation_id,
        ip,
        is_head,
        issue_id,
        lezhin_object_id,
        lezhin_object_type,
        locale,
        meta,
        payment_id,
        platform,
        point,
        price,
        purchase_group_id,
        purchase_id,
        purchase_v1_id,
        ref_id,
        revenue_share,
        reward_type,
        store,
        title,
        type,
        updated_at,
        user_id,
        voucher_id
    )
    SELECT
        balance_bonus_coin,
        balance_coin,
        balance_point,
        bonus_coin,
        bulk_purchase_id,
        canceled,
        coin,
        coin_product_id,
        company_event_id,
        country,
        created_at,
        currency,
        episode_coin,
        invitation_id,
        ip,
        is_head,
        issue_id,
        lezhin_object_id,
        lezhin_object_type,
        locale,
        meta,
        payment_id,
        platform,
        point,
        price,
        purchase_group_id,
        purchase_id,
        purchase_v1_id,
        ref_id,
        revenue_share,
        reward_type,
        store,
        title,
        type,
        updated_at,
        user_id,
        voucher_id
    FROM purchase2
    WHERE created_at > archive_purchase_max_created_at
    AND created_at < archive_limit; 

    -- 트랜잭션 커밋
    COMMIT;

END;

uspa_archiving_delete_purchase

create
    definer = 계정명@`%` procedure uspa_archiving_delete_purchase2(IN archiving_date varchar(10),
                                                                             IN year_ago int,
                                                                             IN archive_purchase_max_created_at bigint,
                                                                             IN archive_limit bigint)
BEGIN
#    DECLARE archive_limit_utc bigint;
#    DECLARE archive_limit_kst bigint;
#    DECLARE archive_limit_kst_midnight VARCHAR(19);
#    DECLARE archive_limit bigint;
#    DECLARE archive_purchase_max_created_at bigint;
    DECLARE cnt_rows bigint DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
        ROLLBACK;
        SELECT 'Error occurred, transaction rolled back.';
    END;

    -- 1. Rundeck에서 매일 새벽 4시에 주기적으로 해당 프로시저를 호출한다. (KST 2024.08.01 04:00:00)
    -- 2. archiving_date: 2024.07.31 19:00:00 (파라미터는 UTC로 넘어옴)
    -- 3. UTC로 받은 시간을 1년전 날짜를 구한다. (2023.07.31 19:00)
    -- SET archive_limit_utc = UNIX_TIMESTAMP(DATE_SUB(archiving_date, INTERVAL year_ago YEAR)) * 1000;

    -- 4. 9시간을 더해 KST로 변환한다. (KST 2023.08.01 04:00:00)
    -- SET archive_limit_kst = archive_limit_utc + (60 * 60 * 9 * 1000);

    -- 5. KST 로 날짜를 맞춘 뒤 00분 00초로 변환 (2023.08.01 00:00:00)
    -- set archive_limit_kst_midnight = DATE_FORMAT(FROM_UNIXTIME(archive_limit_kst / 1000), '%Y-%m-%d 00:00:00');

    -- 6. 5번을 unix_timestamp로 다시 UTC 변환 (2023.07.31 15:00:00)
    -- KST 00:00 -> UTC 15:00
    -- set archive_limit = UNIX_TIMESTAMP(archive_limit_kst_midnight) * 1000 - (60 * 60 * 9 * 1000);

    -- 7. purchase2에 저장된 가장 최근 max값을 조회한다.
    -- select max(created_at) into archive_purchase_max_created_at from archive_purchase2;

#     if archive_purchase_max_created_at is null then
#         set archive_purchase_max_created_at = archive_limit - (60 * 60 * 24 * 2 * 1000);
#     end if;

    REPEAT
        -- 트랜잭션 시작
        START TRANSACTION;
        DELETE FROM purchase2 
        WHERE created_at > archive_purchase_max_created_at
        AND created_at < archive_limit
        LIMIT 1000;

        SET cnt_rows = ROW_COUNT();

        -- 트랜잭션 커밋
        COMMIT;

        -- 지연 시간 추가로 잠금 최소화
        DO SLEEP(0.1);
    UNTIL cnt_rows = 0 END REPEAT;

END;

RUNDECK을 이용한 배치 실행