Untitled

CREATE OR replace PROCEDURE cancel_order(
        orderid IN NUMBER,
        cancelstatus OUT NUMBER
    ) 
AS 
    orderstatus orders.status %TYPE;
BEGIN 
    -- Check if the order ID exists
    SELECT status INTO orderstatus
        FROM orders
        WHERE order_id = orderid;
IF orderstatus = 'Canceled' THEN 
    -- Order has already been canceled
    cancelstatus := 1;
ELSIF orderstatus = 'Shipped' THEN 
    -- Order is shipped and cannot be canceled
    cancelstatus := 2;
ELSE 
    -- Order can be canceled
    -- Update order status to 'Canceled'
    UPDATE orders
        SET status = 'Canceled'
        WHERE order_id = orderid;
        COMMIT;
    cancelstatus := 3;
END IF;
EXCEPTION
    WHEN no_data_found THEN 
        -- Order does not exist
        ROLLBACK;
        cancelstatus := 0;
END;
/