[MySQL] 프로시저 및 스케줄러 등록

by


프로시저 등록

DROP PROCEDURE IF EXISTS [프로시져 명];
DELIMITER $$
CREATE PROCEDURE [프로시져 명](tabledate VARCHAR(30))
BEGIN
    SET @s = 쿼리 작성;");
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;

프로시저 실행

call [프로시저 명] ([파라미터]);

스케줄러 등록

DROP EVENT IF EXISTS [스케쥴 명];
DELIMITER $$
CREATE EVENT IF NOT EXISTS [스케쥴 명]
    ON SCHEDULE
        EVERY 1 DAY
	STARTS '2022-07-29 22:30:00'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO 
    BEGIN
    call [프로시저 명] ([파라미터]));
    ...
END
$$
DELIMITER ;

스케줄러 관련 쿼리

show variables like 'event%';
set global event_scheduler = on;

SELECT * FROM information_schema.EVENTS;
show global variables like "%slow_query_log%";


Leave a Comment