Lesson 26: Stored Procedures and Functions

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

-- Call
CALL procedure_name(1, @result);
SELECT @result;

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

-- Call
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
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;
$$;

-- Call
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;
$$;

-- Usage
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
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: Define cursor
    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 handler: set v_done = 1 when no more rows to FETCH
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 2. OPEN: Execute cursor
    OPEN cur;

    -- 3. FETCH: Process one row at a time
    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: Release cursor
    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;
$$;

-- Deactivate customers with no orders for 6+ months
SELECT fn_deactivate_dormant_customers(6);

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 ;

-- Call: December 2024 report
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;
$$;

-- Call: December 2024 report
SELECT * FROM fn_monthly_sales_report(2024, 12);

DELIMITER //
CREATE PROCEDURE sp_refresh_customer_grades()
BEGIN
    -- VIP: 5,000,000 or more
    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: 1,000,000 or more
    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: 300,000 or more
    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: the rest
    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;

    -- Customers with no order history
    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;

    -- Check current status
    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;

    -- Change order status
    UPDATE orders
    SET status = 'processing'
    WHERE id = p_order_id;

    -- Check and update payment status
    UPDATE payments
    SET status = 'completed',
        paid_at = NOW()
    WHERE order_id = p_order_id
      AND status = 'pending';

    -- Create shipping record
    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
    -- Check current status
    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;

    -- Change order status
    UPDATE orders
    SET status = 'processing'
    WHERE id = p_order_id;

    -- Check and update payment status
    UPDATE payments
    SET status = 'completed',
        paid_at = NOW()
    WHERE order_id = p_order_id
      AND status = 'pending';

    -- Create shipping record
    INSERT INTO shipping (order_id, status, shipped_at)
    VALUES (p_order_id, 'preparing', NOW());

    -- PostgreSQL auto-transaction within 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 ;

-- Use inside 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;
$$;

-- Use inside 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;

-- List procedures in current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- List functions
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- List user-defined procedures/functions
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;

-- Function/procedure source code
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 procedure execution privilege to a specific user
GRANT EXECUTE ON PROCEDURE sp_process_order TO 'app_user'@'%';

-- Grant function execution privilege to a specific user
GRANT EXECUTE ON FUNCTION fn_order_total(INT) TO app_user;