개요

MySQL 저장 프로시저의 기본 구조와 변수, 제어문, 트랜잭션을 활용한 실습 예제를 정리했음. DELIMITER를 사용해 복잡한 로직을 저장하고 재사용함.

1. 저장 프로시저 기본 구조

DELIMITER를 변경해서 세미콜론 충돌을 방지하고 프로시저를 생성함. 한글 프로시저명도 가능.

DELIMITER //
CREATE PROCEDURE 프로시저이름()
BEGIN
    -- 프로시저 내용
END //
DELIMITER ;

2. 간단한 조회 프로시저

회원 목록 조회와 상세 조회를 매개변수로 구현함.

-- 회원목록조회
DELIMITER //
CREATE PROCEDURE 회원목록조회()
BEGIN
    SELECT * FROM author;
END //
DELIMITER ;

-- 회원상세조회 (매개변수 사용)
DELIMITER //
CREATE PROCEDURE 회원상세조회(IN idInput BIGINT)
BEGIN
    SELECT * FROM author WHERE id = idInput;
END //
DELIMITER ;

3. 변수 사용 프로시저

DECLARE는 BEGIN 바로 아래에서 선언하고 INTO로 값 할당함.

DELIMITER //
CREATE PROCEDURE 전체회원수조회()
BEGIN
    DECLARE authorCount BIGINT;
    SELECT COUNT(*) INTO authorCount FROM author;
    SELECT authorCount;
END //
DELIMITER ;

4. 글쓰기 프로시저 (트랜잭션 + 예외처리)

여러 테이블 작업을 트랜잭션으로 묶고 SQLEXCEPTION 핸들러로 롤백 처리함.

DELIMITER //
CREATE PROCEDURE 글쓰기(IN titleInput VARCHAR(255), contentsInput VARCHAR(255), emailInput VARCHAR(255))
BEGIN
    DECLARE authorIdValue BIGINT;
    DECLARE postIdValue BIGINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            ROLLBACK;
        END;
    START TRANSACTION;
        SELECT id INTO authorIdValue FROM author WHERE email = emailInput;
        INSERT INTO post(title, contents) VALUES (titleInput, contentsInput);
        SELECT id INTO postIdValue FROM post ORDER BY id DESC LIMIT 1;
        INSERT INTO author_post_list(author_id, post_id) VALUES (authorIdValue, postIdValue);
    COMMIT;
END //
DELIMITER ;

5. 글삭제 프로시저 (IF-ELSE)

참여자 수에 따라 다른 삭제 로직을 분기 처리함.

DELIMITER //
CREATE PROCEDURE 글삭제(IN postIdInput BIGINT, IN authorIdInput BIGINT)
BEGIN
    DECLARE authorCountValue BIGINT;
    SELECT COUNT(*) INTO authorCountValue FROM author_post_list WHERE post_id = postIdInput;
    IF authorCountValue = 1 THEN
        DELETE FROM author_post_list WHERE post_id = postIdInput AND author_id = authorIdInput;
        DELETE FROM post WHERE id = postIdInput;
    ELSE
        DELETE FROM author_post_list WHERE post_id = postIdInput AND author_id = authorIdInput;
    END IF;
END //
DELIMITER ;

6. 대량 글쓰기 프로시저 (WHILE 반복문)

반복문을 사용해 대량 데이터를 삽입함.

DELIMITER //
CREATE PROCEDURE 글도배(IN countInput BIGINT, IN emailInput VARCHAR(255))
BEGIN
    DECLARE authorIdValue BIGINT;
    DECLARE postIdValue BIGINT;
    DECLARE countValue BIGINT DEFAULT 0;
    SELECT id INTO authorIdValue FROM author WHERE email = emailInput;
    INSERT INTO post(title) VALUES ('안녕하세요');
    WHILE countValue < countInput DO
        SELECT id INTO postIdValue FROM post ORDER BY id DESC LIMIT 1;
        INSERT INTO author_post_list(author_id, post_id) VALUES (authorIdValue, postIdValue);
        SET countValue = countValue + 1;
    END WHILE;
END //
DELIMITER ;

마치며

저장 프로시저는 복잡한 비즈니스 로직을 DB에 저장해서 재사용하기 좋음. 트랜잭션과 제어문을 조합하면 실무에서 유용하게 쓸 수 있을듯!