MySQL 기준

🎱 SP 실행 흐름

⚽️ SP 내부 처리 순서

• 클라이언트 요청 (CALL 프로시저)
• DB 서버에서 SP 실행
• 매개변수 처리 ( IN / OUT / INOUT )
• BEGIN ~ END 내부 SQL 로직 순차 실행
• 필요 시 트랜잭션, 조건문, 반복문, 예외처리 수행
• OUT / INOUT 결과 반환
• 클라이언트에 결과 응답

⚽️ 컴파일 vs 런타임

• 컴파일
  • CREATE PROCEDURE 할 때 문법 검사 및 내부 구조 생성
  • DB 내부에 저장
• 런타임
  • CALL 프로시저명() 으로 실제 실행
  • 실행마다 파라미터/조건 처리
  • 이미 컴파일 되어 있어 파싱/플랜 생성 없이 호출됨
  • 내부 로직의 조건 분기/실행 순서는 런타임에 결정

⚽️ BEGIN ~ END 블록 내부 실행 흐름

• BEGIN
  • 변수 선언

    DECLARE 변수명 데이터타입 [DEFAULT 기본값];

    • BEGIN 블록의 가장 앞부분에서만 사용 가능
  • 조건 분기
    • IF

      IF 조건 THEN
      	-- 실행 내용
      ELSEIF 조건2 THEN
      	-- 실행 내용
      ELSE
      	-- 실행 내용
      END IF;

    • CASE

      CASE 변수
      	WHEN 값1 THEN -- 실행 내용
      	WHEN 값2 THEN -- 실행 내용
      	ELSE -- 실행 내용
      END CASE;

  • 반복 처리
    • LOOP

      loop_label: LOOP
      	-- 실행 내용
      	LEAVE loop_label; -- 루프 종료
      END LOOP;

    • WHILE

      WHILE 조건 DO
      	-- 실행 내용
      END WHILE;

      • 조건 확인 → 실행
    • REPEAT (DO - WHILE)

      REPEAT
      	-- 실행 내용
      UNTIL 조건
      END REPEAT;

      • 실행 → 조건 확인 (최소 1회 실행 보장)
  • 트랜잭션

    START TRANSACTION;
    -- 실행 내용
    COMMIT;

    • START TRANSCTION / COMMIT / ROLLBACK
    • SP 내부에서 START TRANSACTION을 통해 명시적 트랜잭션 사용 가능
    • 단 DDL이 포함되면 암묵적 COMMIT 발생
  • 예외 처리 (HANDLER)

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
    	-- 에러 발생 시 실행 로직
    END;

    • CONTINUE - 에러 이후에도 계속 실행
    • EXIT - 에러 발생시 SP 종료
• END

⚽️ SP 호출 방식

CALL 프로시저명(매개변수);

• IN 매개변수: 값 전달
• OUT 매개변수: SP 내부에서 값을 설정하여 외부로 전달
• INOUT: 양방향

🎱 트랜잭션과 SP

⚽️ SP 내부 트랜잭션의 장단점

• 장점
  • 정합성 보장 가능
  • 롤백 가능 → 실패 시 안전한 복구 가능
  • 일관된 제어 가능
• 단점
  • 트랜잭션 범위 불명확
  • 트랜잭션 중첩 문제 발생
  • 오류 처리 복잡도 증가
  • 유지보수 어려움
  • 모듈화된 설계 어려움

➡️ SP 외부에서 트랜잭션으로 묶기

➡️ 정말 필요한 SP만 트랜잭션 포함

➡️ 트랜잭션 여부를 매개변수로 제어

⚽️ 주의사항

• DDL 포함 금지 → 암묵적 COMMIT 발생 (DROP, TRUNCATE 포함)
• 오토커밋 설정 → 트랜잭션 자동 종료 발생
• 핸들러에 롤백 지정 필수
  • 실패 시 트랜잭션이 커밋되거나 중간에 멈출 수 있음

⚽️ 예시

CREATE PROCEDURE TransferPoinsts(
	IN p_from_user INT,
	IN p_to_user INT,
	IN p_point INT
)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END;
	
	START TRANSACTION;
	UPDATE users SET point = point - p_point
		WHERE id = from_user;
	UPDATE users SET point = point + p_point
		WHERE id = to_user;
	COMMIT;
END;

• 트랜잭션 시작
• 업데이트 1 (point 감소)
• 업데이트 2 (point 추가)
• 둘다 성공 → COMMIT
• 중간에 에러 발생 → 핸들러에서 ROLLBACK

🎱 에러 핸들링

⚽️ 구조

DECLARE 핸들러종류 HANDLER FOR 핸들러조건
	실행 로직;

⚽️ 핸들러 종류

• CONTINUE
  • 에러가 발생해도 계속 다음 줄로 진행
  • 로그 기록
• EXIT
  • 에러 발생 시 SP 탈출
  • 즉시 종료

⚽️ 핸들러 조건 종류

• SQLEXCEPTION
  • 모든 SQL 에러
  • ex) 제약조건 위반, NULL 오류, 외래키 에러 …
• SQLWARNING
  • 경고 수준 에러
  • ex) 성능 경고, 문자열 잘림, 자동 형변환, 나눗셈 경고 …
• NOT FOUND
  • 조회/커서에서 결과 없음
  • ex) 조회 결과 없음, 커서 FETCH 실패

⚽️ 예제

CREATE PROCEDURE InsertWithWarning()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
        -- 경고만 기록하고 계속 진행
        SET @warning_msg = '경고 발생: 문자열이 잘렸을 수 있음';
    END;

    INSERT INTO users(name) VALUES ('이름이너무길어요'); -- name 컬럼이 VARCHAR(5)라고 가정
END;

• 트랜잭션 시작
• 두번째 INSERT에서 에러 발생 → 핸들러 작동
• 핸들러에서 ROLLBACK 수행 후 메시지 반환
• SP 종료

🎱 커서

⚽️ 커서란?

• 조회 결과를 한 행씩 순차적으로 처리하기 위한 컨트롤 구조

⚽️ 구조

DECLARE 커서명 CURSOR FOR SELECT문;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN 커서명;

FETCH 커서명 INTO 변수1, 변수2, ...;

WHILE NOT done DO
	-- 처리 로직
	FETCH 커서명 INTO 변수1, 변수2, ...;
END WHILE;

CLOSE 커서명;

• 사용할 SELECT문, 커서 선언
• 커서에 더이상 FETCH할 행이 없을 경우를 감지
• 커서 실행 시작
• 한 행을 변수에 불러오기
• 반복문에서 FETCH 및 로직 처리
• 커서 종료

⚽️ 주의사항

• 핸들러 필수 → FETCH 실패 시 루프 종료 조건을 NOT FOUND 핸들러로 처리
• 커서는 DECLARE한 순서대로 OPEN, FETCH, CLOSE 해야 함
• CLOSE 필수 → 자원 누수 방지
• 반복에서 FETCH 필수 → 무한루프 방지

⚽️ 예제

CREATE PROCEDURE RewardAllUsers()
BEGIN
	DECLARE done INT DEFAULT FALSE;
  DECLARE user_id INT;
  DECLARE user_name VARCHAR(50);

  -- 커서 선언
  DECLARE user_cursor CURSOR FOR
    SELECT id, name FROM users;
  -- FETCH 실패 시 종료 조건
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 커서 오픈
  OPEN user_cursor;

  read_loop: LOOP
    FETCH user_cursor INTO user_id, user_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- 처리 로직: 포인트 100씩 추가
    UPDATE users SET point = point + 100 WHERE id = user_id;
  END LOOP;

  -- 커서 종료
  CLOSE user_cursor;
END;