SQL 저장 프로시저와 고급 기능

Stored Procedure ✨

SQL 쿼리의 집합으로, 특정 동작을 일괄 처리하기 위한 용도로 사용됨.

✅ 특징

• 쿼리 모듈화: 복잡한 쿼리를 프로시저 내부에 저장 후 호출하여 사용 (CALL 프로시저_이름();)
• 이점
  • 절차적 기능 구현 가능
  • 유지보수 용이
  • 네트워크 트래픽 감소
  • 보안 강화
• 주의사항
  • ALTER 문으로 저장 프로시저의 매개변수나 바디 수정 불가 → 삭제 후 재생성 필요 ❌
  • 기본 반환값이 없음 (반환값이 필요하면 Function 사용) ➡ RETURN 사용 불가
  • 매개변수 종류
    • IN: 입력 전용
    • OUT: 출력 전용
    • INOUT: 입력 및 출력 가능

⚙ 제어문

IF 문

• 특정 조건이 참일 경우 실행

IF 조건식 THEN 실행문;
ELSE 실행문;
END IF;

CASE 문

• 여러 조건 중 하나를 선택하여 실행 (다중 분기)

CASE
    WHEN 조건1 THEN 실행문;
    WHEN 조건2 THEN 실행문;
    ELSE 실행문;
END;

WHILE 문

• 특정 조건이 만족할 때까지 반복 수행 (프로시저 내부에서만 사용 가능)

WHILE 조건식 DO 실행문; END WHILE;

---

Stored Function 🌟

사용자가 직접 만들어 사용하는 함수로, 저장 프로시저와 유사하지만 반환값이 필수

✅ 특징

• SELECT 문 안에서 호출 가능
• IN, OUT 매개변수 사용 불가 (모든 매개변수는 입력 전용)
• RETURN 문을 통해 단일 값 반환
• SELECT INTO를 제외한 SELECT 사용 불가

⚡ 스토어드 프로시저와 차이점

구분	Stored Function	Stored Procedure
매개변수	IN만 사용 가능	IN, OUT, INOUT 사용 가능
반환값	RETURN 필수	RETURN 없음 (필요 시 OUT 사용)
호출 방식	SELECT 문에서 호출	CALL 사용
SELECT 사용	제한적 (SELECT INTO 가능)	자유롭게 사용 가능

🌟 예제

drop function if exists getAgeFunc;
delimiter $$
create function getAgeFunc(bYear int)
    returns int deterministic no sql
begin
    declare age int;
    set age = year(curdate()) - bYear;
    return age;
end $$
delimiter ;

SELECT getAgeFunc(1999) AS '나이';

---

Cursor 🔄

저장 프로시저 내부에서 사용되며, 테이블에서 여러 개의 행을 조회 후 한 행씩 처리할 수 있도록 지원

⚡ 커서 처리 순서

1. 커서 선언: DECLARE CURSOR
2. 반복 조건 선언: DECLARE CONTINUE HANDLER (더 이상 읽을 행이 없을 경우 대비)
3. 커서 열기: OPEN
4. 데이터 가져오기: FETCH
5. 데이터 처리
6. 커서 닫기: CLOSE
   • 3~4단계를 LOOP ~ END LOOP로 반복

---

Trigger ⚡

테이블에 DML(INSERT, UPDATE, DELETE) 이벤트가 발생할 때 자동으로 실행되는 기능

✅ 특징

• 데이터 무결성을 유지하기 위해 사용됨
• 테이블에 부착되는 프로그램 코드
• 직접 실행 불가 (테이블 이벤트 발생 시 자동 실행)
• IN, OUT 매개변수 사용 불가
• MySQL에서는 VIEW에 트리거 사용 불가

⚡ 트리거 종류

1. AFTER 트리거
   • 이벤트 발생 후 실행
2. BEFORE 트리거
   • 이벤트 발생 전 실행

🔍 임시 테이블 (NEW, OLD)

• INSERT, UPDATE, DELETE 작업 시 자동 생성됨
• NEW: 새롭게 삽입되거나 변경된 값
• OLD: 기존 값

💡 트리거 활용 예제

CREATE TRIGGER before_user_insert
BEFORE INSERT ON usertbl
FOR EACH ROW
SET NEW.created_at = NOW();