26강: 저장 프로시저와 함수

DELIMITER //
CREATE PROCEDURE procedure_name(
    IN  param1 INT,
    OUT param2 VARCHAR(100)
)
BEGIN
    -- SQL statements;
END //
DELIMITER ;

-- 호출
CALL procedure_name(1, @result);
SELECT @result;

-- 프로시저 (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE procedure_name(
    IN  param1 INT,
    INOUT param2 VARCHAR(100)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- SQL statements;
END;
$$;

-- 호출
CALL procedure_name(1, NULL);

DELIMITER //
CREATE PROCEDURE sp_get_customer_stats(
    IN  p_customer_id INT,
    OUT p_order_count INT,
    OUT p_total_spent DECIMAL(12,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO p_order_count, p_total_spent
    FROM orders
    WHERE customer_id = p_customer_id
      AND status <> 'cancelled';
END //
DELIMITER ;

-- 호출
CALL sp_get_customer_stats(100, @cnt, @total);
SELECT @cnt AS order_count, @total AS total_spent;

CREATE OR REPLACE FUNCTION fn_get_customer_stats(
    p_customer_id INT,
    OUT p_order_count INT,
    OUT p_total_spent NUMERIC(12,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO p_order_count, p_total_spent
    FROM orders
    WHERE customer_id = p_customer_id
      AND status <> 'cancelled';
END;
$$;

-- 호출
SELECT * FROM fn_get_customer_stats(100);

DELIMITER //
CREATE PROCEDURE sp_classify_customer(
    IN  p_customer_id INT,
    OUT p_grade VARCHAR(20)
)
BEGIN
    DECLARE v_total DECIMAL(12,2);

    SELECT COALESCE(SUM(total_amount), 0)
    INTO v_total
    FROM orders
    WHERE customer_id = p_customer_id
      AND status <> 'cancelled';

    IF v_total >= 5000000 THEN
        SET p_grade = 'VIP';
    ELSEIF v_total >= 1000000 THEN
        SET p_grade = 'GOLD';
    ELSEIF v_total >= 300000 THEN
        SET p_grade = 'SILVER';
    ELSE
        SET p_grade = 'BRONZE';
    END IF;
END //
DELIMITER ;

CREATE OR REPLACE FUNCTION fn_classify_customer(
    p_customer_id INT
)
RETURNS VARCHAR(20)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC(12,2);
BEGIN
    SELECT COALESCE(SUM(total_amount), 0)
    INTO v_total
    FROM orders
    WHERE customer_id = p_customer_id
      AND status <> 'cancelled';

    IF v_total >= 5000000 THEN
        RETURN 'VIP';
    ELSIF v_total >= 1000000 THEN
        RETURN 'GOLD';
    ELSIF v_total >= 300000 THEN
        RETURN 'SILVER';
    ELSE
        RETURN 'BRONZE';
    END IF;
END;
$$;

-- 사용
SELECT fn_classify_customer(100);

DELIMITER //
CREATE PROCEDURE sp_batch_update_grades()
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_cust_id INT;
    DECLARE v_grade VARCHAR(20);

    DECLARE cur CURSOR FOR
        SELECT id FROM customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_cust_id;
        IF v_done THEN
            LEAVE read_loop;
        END IF;

        CALL sp_classify_customer(v_cust_id, @new_grade);

        UPDATE customers
        SET grade = @new_grade
        WHERE id = v_cust_id;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

CREATE OR REPLACE PROCEDURE sp_batch_update_grades()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
    v_grade VARCHAR(20);
BEGIN
    FOR rec IN SELECT id FROM customers LOOP
        v_grade := fn_classify_customer(rec.id);

        UPDATE customers
        SET grade = v_grade
        WHERE id = rec.id;
    END LOOP;
END;
$$;

-- 호출
CALL sp_batch_update_grades();

DELIMITER //
CREATE PROCEDURE sp_deactivate_dormant_customers(
    IN p_months INT
)
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_cust_id INT;
    DECLARE v_count INT DEFAULT 0;

    -- 1. DECLARE: 커서 정의
    DECLARE cur CURSOR FOR
        SELECT c.id
        FROM customers AS c
        WHERE c.is_active = 1
          AND NOT EXISTS (
              SELECT 1 FROM orders AS o
              WHERE o.customer_id = c.id
                AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL p_months MONTH)
          );

    -- NOT FOUND 핸들러: FETCH할 행이 없으면 v_done = 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 2. OPEN: 커서 실행
    OPEN cur;

    -- 3. FETCH: 한 행씩 처리
    deactivate_loop: LOOP
        FETCH cur INTO v_cust_id;
        IF v_done THEN
            LEAVE deactivate_loop;
        END IF;

        UPDATE customers
        SET is_active = 0
        WHERE id = v_cust_id;

        SET v_count = v_count + 1;
    END LOOP;

    -- 4. CLOSE: 커서 해제
    CLOSE cur;

    SELECT v_count AS deactivated_count;
END //
DELIMITER ;

CREATE OR REPLACE FUNCTION fn_deactivate_dormant_customers(
    p_months INT
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_cust_id INT;
    v_count INT := 0;
    cur CURSOR FOR
        SELECT c.id
        FROM customers AS c
        WHERE c.is_active = TRUE
          AND NOT EXISTS (
              SELECT 1 FROM orders AS o
              WHERE o.customer_id = c.id
                AND o.order_date >= CURRENT_DATE - (p_months || ' months')::INTERVAL
          );
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_cust_id;
        EXIT WHEN NOT FOUND;

        UPDATE customers
        SET is_active = FALSE
        WHERE id = v_cust_id;

        v_count := v_count + 1;
    END LOOP;
    CLOSE cur;

    RETURN v_count;
END;
$$;

-- 6개월 이상 미주문 고객 비활성화
SELECT fn_deactivate_dormant_customers(6);

CREATE OR REPLACE FUNCTION fn_deactivate_dormant_customers(
    p_months INT
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
    v_count INT := 0;
BEGIN
    -- FOR-IN은 내부적으로 커서를 자동 관리
    FOR rec IN
        SELECT c.id
        FROM customers AS c
        WHERE c.is_active = TRUE
          AND NOT EXISTS (
              SELECT 1 FROM orders AS o
              WHERE o.customer_id = c.id
                AND o.order_date >= CURRENT_DATE - (p_months || ' months')::INTERVAL
          )
    LOOP
        UPDATE customers
        SET is_active = FALSE
        WHERE id = rec.id;

        v_count := v_count + 1;
    END LOOP;

    RETURN v_count;
END;
$$;

DELIMITER //
CREATE PROCEDURE sp_monthly_sales_report(
    IN p_year INT,
    IN p_month INT
)
BEGIN
    SELECT
        DATE_FORMAT(o.order_date, '%Y-%m-%d') AS order_date,
        COUNT(DISTINCT o.id) AS order_count,
        SUM(oi.quantity) AS items_sold,
        SUM(oi.total_price) AS revenue
    FROM orders AS o
    INNER JOIN order_items AS oi ON oi.order_id = o.id
    WHERE YEAR(o.order_date) = p_year
      AND MONTH(o.order_date) = p_month
      AND o.status <> 'cancelled'
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m-%d')
    ORDER BY order_date;
END //
DELIMITER ;

-- 호출: 2024년 12월 보고서
CALL sp_monthly_sales_report(2024, 12);

CREATE OR REPLACE FUNCTION fn_monthly_sales_report(
    p_year INT,
    p_month INT
)
RETURNS TABLE (
    order_date DATE,
    order_count BIGINT,
    items_sold BIGINT,
    revenue NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        o.order_date::DATE,
        COUNT(DISTINCT o.id),
        SUM(oi.quantity)::BIGINT,
        SUM(oi.total_price)
    FROM orders AS o
    INNER JOIN order_items AS oi ON oi.order_id = o.id
    WHERE EXTRACT(YEAR FROM o.order_date) = p_year
      AND EXTRACT(MONTH FROM o.order_date) = p_month
      AND o.status <> 'cancelled'
    GROUP BY o.order_date::DATE
    ORDER BY o.order_date::DATE;
END;
$$;

-- 호출: 2024년 12월 보고서
SELECT * FROM fn_monthly_sales_report(2024, 12);

DELIMITER //
CREATE PROCEDURE sp_refresh_customer_grades()
BEGIN
    -- VIP: 500만원 이상
    UPDATE customers AS c
    SET grade = 'VIP'
    WHERE (
        SELECT COALESCE(SUM(o.total_amount), 0)
        FROM orders AS o
        WHERE o.customer_id = c.id AND o.status <> 'cancelled'
    ) >= 5000000;

    -- GOLD: 100만원 이상
    UPDATE customers AS c
    SET grade = 'GOLD'
    WHERE grade <> 'VIP'
      AND (
        SELECT COALESCE(SUM(o.total_amount), 0)
        FROM orders AS o
        WHERE o.customer_id = c.id AND o.status <> 'cancelled'
    ) >= 1000000;

    -- SILVER: 30만원 이상
    UPDATE customers AS c
    SET grade = 'SILVER'
    WHERE grade NOT IN ('VIP', 'GOLD')
      AND (
        SELECT COALESCE(SUM(o.total_amount), 0)
        FROM orders AS o
        WHERE o.customer_id = c.id AND o.status <> 'cancelled'
    ) >= 300000;

    -- BRONZE: 나머지
    UPDATE customers
    SET grade = 'BRONZE'
    WHERE grade NOT IN ('VIP', 'GOLD', 'SILVER');

    SELECT grade, COUNT(*) AS customer_count
    FROM customers
    GROUP BY grade
    ORDER BY FIELD(grade, 'VIP', 'GOLD', 'SILVER', 'BRONZE');
END //
DELIMITER ;

CALL sp_refresh_customer_grades();

CREATE OR REPLACE PROCEDURE sp_refresh_customer_grades()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE customers AS c
    SET grade = CASE
        WHEN t.total_spent >= 5000000 THEN 'VIP'
        WHEN t.total_spent >= 1000000 THEN 'GOLD'
        WHEN t.total_spent >=  300000 THEN 'SILVER'
        ELSE 'BRONZE'
    END
    FROM (
        SELECT customer_id, COALESCE(SUM(total_amount), 0) AS total_spent
        FROM orders
        WHERE status <> 'cancelled'
        GROUP BY customer_id
    ) AS t
    WHERE c.id = t.customer_id;

    -- 주문 이력이 없는 고객
    UPDATE customers
    SET grade = 'BRONZE'
    WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);
END;
$$;

CALL sp_refresh_customer_grades();

DELIMITER //
CREATE PROCEDURE sp_process_order(
    IN p_order_id INT
)
BEGIN
    DECLARE v_status VARCHAR(20);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Order processing failed. Transaction rolled back.';
    END;

    -- 현재 상태 확인
    SELECT status INTO v_status
    FROM orders
    WHERE id = p_order_id;

    IF v_status IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Order not found.';
    END IF;

    IF v_status <> 'pending' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Only pending orders can be processed.';
    END IF;

    START TRANSACTION;

    -- 주문 상태 변경
    UPDATE orders
    SET status = 'processing'
    WHERE id = p_order_id;

    -- 결제 상태 확인 및 갱신
    UPDATE payments
    SET status = 'completed',
        paid_at = NOW()
    WHERE order_id = p_order_id
      AND status = 'pending';

    -- 배송 레코드 생성
    INSERT INTO shipping (order_id, status, shipped_at)
    VALUES (p_order_id, 'preparing', NOW());

    COMMIT;

    SELECT 'Order processed successfully.' AS result;
END //
DELIMITER ;

CALL sp_process_order(1001);

CREATE OR REPLACE PROCEDURE sp_process_order(
    p_order_id INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR(20);
BEGIN
    -- 현재 상태 확인
    SELECT status INTO v_status
    FROM orders
    WHERE id = p_order_id;

    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Order not found: %', p_order_id;
    END IF;

    IF v_status <> 'pending' THEN
        RAISE EXCEPTION 'Only pending orders can be processed. Current: %', v_status;
    END IF;

    -- 주문 상태 변경
    UPDATE orders
    SET status = 'processing'
    WHERE id = p_order_id;

    -- 결제 상태 확인 및 갱신
    UPDATE payments
    SET status = 'completed',
        paid_at = NOW()
    WHERE order_id = p_order_id
      AND status = 'pending';

    -- 배송 레코드 생성
    INSERT INTO shipping (order_id, status, shipped_at)
    VALUES (p_order_id, 'preparing', NOW());

    -- PostgreSQL은 CALL 내에서 자동 트랜잭션
    RAISE NOTICE 'Order % processed successfully.', p_order_id;
END;
$$;

CALL sp_process_order(1001);

DELIMITER //
CREATE FUNCTION fn_order_total(
    p_order_id INT
)
RETURNS DECIMAL(12,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_total DECIMAL(12,2);

    SELECT COALESCE(SUM(total_price), 0)
    INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END //
DELIMITER ;

-- SELECT 안에서 사용
SELECT
    id,
    order_date,
    fn_order_total(id) AS calculated_total
FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC
LIMIT 5;

CREATE OR REPLACE FUNCTION fn_order_total(
    p_order_id INT
)
RETURNS NUMERIC(12,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC(12,2);
BEGIN
    SELECT COALESCE(SUM(total_price), 0)
    INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END;
$$;

-- SELECT 안에서 사용
SELECT
    id,
    order_date,
    fn_order_total(id) AS calculated_total
FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC
LIMIT 5;

-- 현재 데이터베이스의 프로시저 목록
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- 함수 목록
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- 사용자 정의 프로시저/함수 목록
SELECT routine_name, routine_type, data_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_type, routine_name;

SHOW CREATE PROCEDURE sp_monthly_sales_report;
SHOW CREATE FUNCTION fn_order_total;

-- 함수/프로시저 소스 코드
SELECT prosrc
FROM pg_proc
WHERE proname = 'fn_order_total';

DROP PROCEDURE IF EXISTS sp_monthly_sales_report;
DROP FUNCTION IF EXISTS fn_order_total;

DROP PROCEDURE IF EXISTS sp_process_order(INT);
DROP FUNCTION IF EXISTS fn_order_total(INT);

-- 특정 사용자에게 프로시저 실행 권한 부여
GRANT EXECUTE ON PROCEDURE sp_process_order TO 'app_user'@'%';

-- 특정 사용자에게 함수 실행 권한 부여
GRANT EXECUTE ON FUNCTION fn_order_total(INT) TO app_user;