[Database] STORED PROCEDURE

⚙️ 1. Stored Procedure (저장 프로시저)

Stored Procedure는 미리 컴파일된 SQL 블록을 데이터베이스에 저장해두고, 필요할 때 호출하여 실행할 수 있도록 만든 객체이다.
재사용성, 보안성, 성능 최적화, 네트워크 트래픽 절감 등 다양한 이점을 제공한다.

---

📌 1-1. 개념 및 특징

✅ 정의

• 미리 정의한 SQL 문을 저장하고 실행할 수 있는 프로시저 블록
• 복잡한 로직을 하나의 명령어(CALL) 로 실행

✅ 장점

1. 반복되는 SQL 로직 재사용
2. 미리 컴파일되어 속도 향상
3. 보안성 강화 (캡슐화)
4. 네트워크 트래픽 감소

⚠️ 단점

• 디버깅 어려움
• 특정 DBMS에 종속적
• 과도한 로직 포함 시 유지보수 어려움

---

🆚 1-2. Procedure vs Function

항목	Stored Procedure	Function
반환 값	여러 개 가능	하나만 반환
호출 방식	CALL 문	SELECT 등 식(Expression) 내 사용 가능
주요 용도	DML 수행	계산, 변환 처리

---

🛠️ 1-3. 생성 & 호출

DELIMITER $$

DROP PROCEDURE IF EXISTS get_menu_list$$

CREATE PROCEDURE get_menu_list()
BEGIN
    SELECT * FROM tbl_menu;
END$$

DELIMITER ;

CALL get_menu_list();

---

🎯 1-4. 파라미터 타입

파라미터	설명
IN	외부에서 값을 전달받아 사용
OUT	내부에서 계산한 값을 반환
INOUT	전달도 받고 반환도 함

📍 IN 예시

CREATE PROCEDURE get_menu_by_category(IN p_category_code INT)
BEGIN
    SELECT * FROM tbl_menu WHERE category_code = p_category_code;
END;

CALL get_menu_by_category(5);

📍 OUT 예시

CREATE PROCEDURE get_menu_count_by_category(IN p_category_code INT, OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count FROM tbl_menu WHERE category_code = p_category_code;
END;

SET @menu_count = 0;
CALL get_menu_count_by_category(4, @menu_count);
SELECT @menu_count AS '메뉴 개수';

📍 INOUT 예시

CREATE PROCEDURE calculate_price(INOUT p_price DECIMAL(10,2), IN p_tax_rate DECIMAL(4,2))
BEGIN
    SET p_price = p_price + (p_price * p_tax_rate);
END;

SET @price = 10000;
CALL calculate_price(@price, 0.1);
SELECT @price AS '세금 포함 가격';

---

🔄 1-5. 제어 흐름 (IF/조건 처리)

CREATE PROCEDURE insert_menu(IN p_name VARCHAR(100), IN p_price DECIMAL(10,2), IN p_category_code INT)
BEGIN
    DECLARE v_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO v_exists FROM tbl_menu WHERE menu_name = p_name;

    IF v_exists = 0 THEN
        INSERT INTO tbl_menu (...) VALUES (...);
    ELSE
        UPDATE tbl_menu SET menu_price = p_price WHERE menu_name = p_name;
    END IF;
END;

---

🗑️ 1-6. 삭제 및 조회

• 삭제

  DROP PROCEDURE IF EXISTS insert_menu;

• 목록 조회

  SELECT ROUTINE_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'menudb';

---

🔐 1-7. 권한 관리

• 권한 부여

  GRANT EXECUTE ON PROCEDURE get_menu_list TO user@'%';
  GRANT EXECUTE ON *.* TO user@'%';

• 권한 회수

  REVOKE EXECUTE ON PROCEDURE get_menu_list FROM user@'%';
  REVOKE EXECUTE ON *.* FROM user@'%';

---

⚠️ 1-8. 에러 핸들링

CREATE PROCEDURE divide_numbers(IN p_num1 INT, IN p_num2 INT, OUT p_result DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result = NULL;
        SELECT '에러가 발생했습니다.' AS error_message;
    END;

    IF p_num2 = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '0으로 나눌 수 없습니다.';
    ELSE
        SET p_result = p_num1 / p_num2;
    END IF;
END;

CALL divide_numbers(10, 0, @output); -- 예외 발생
CALL divide_numbers(10, 2, @output); -- 정상 실행

---