02_main_demo.sql

SET SERVEROUTPUT ON;

DECLARE
    TYPE t_service_lookup IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
    service_names t_service_lookup;

    TYPE t_price_array IS VARRAY(5) OF NUMBER;
    service_prices t_price_array := t_price_array(5000,10000,15000,20000,30000);

    TYPE t_order_list IS TABLE OF NUMBER;
    orders_per_customer t_order_list := t_order_list();

    v_customer carwash_customers%ROWTYPE;

    TYPE t_summary IS RECORD(
        customer_id NUMBER,
        full_name   VARCHAR2(100),
        total_orders NUMBER,
        total_amount NUMBER
    );
    customer_summary t_summary;

    CURSOR c_orders IS
        SELECT * FROM carwash_orders ORDER BY customer_id;
    v_order c_orders%ROWTYPE;

BEGIN
    FOR s IN (SELECT service_id, service_name FROM carwash_services) LOOP
        service_names(s.service_id) := s.service_name;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('--- Car Wash Order Processing Start ---');

    OPEN c_orders;
    LOOP
        FETCH c_orders INTO v_order;
        EXIT WHEN c_orders%NOTFOUND;

        SELECT * INTO v_customer
        FROM carwash_customers
        WHERE customer_id = v_order.customer_id;

        IF v_order.service_id < 1 OR v_order.service_id > 5 THEN
            GOTO PROGRAM_ERROR;
        END IF;

        orders_per_customer.EXTEND;
        orders_per_customer(orders_per_customer.COUNT) := v_order.order_id;

        customer_summary.customer_id := v_customer.customer_id;
        customer_summary.full_name   := v_customer.full_name;
        customer_summary.total_orders := orders_per_customer.COUNT;
        customer_summary.total_amount := NVL(customer_summary.total_amount,0)
                                         + service_prices(v_order.service_id);

        DBMS_OUTPUT.PUT_LINE('Processed order '||v_order.order_id||
                             ' for '||v_customer.full_name||
                             ' ('||service_names(v_order.service_id)||')');
    END LOOP;
    CLOSE c_orders;

    GOTO CLEANUP_RESOURCES;

PROGRAM_ERROR:
    DBMS_OUTPUT.PUT_LINE('ERROR: Invalid service encountered.');
    GOTO CLEANUP_RESOURCES;

CLEANUP_RESOURCES:
    DBMS_OUTPUT.PUT_LINE('--- Final Summary ---');
    DBMS_OUTPUT.PUT_LINE('Customer: '||customer_summary.full_name);
    DBMS_OUTPUT.PUT_LINE('Total Orders: '||customer_summary.total_orders);
    DBMS_OUTPUT.PUT_LINE('Total Amount: '||customer_summary.total_amount);
    DBMS_OUTPUT.PUT_LINE('--- End of Program ---');
END;
/