CREATE OR REPLACE PROCEDURE avail_reduction_in_booking(
    reservation_id IN INT,
    reduction_percent IN NUMBER
)
AS
    v_original_price Halls.price%TYPE;
    v_reduced_price Halls.price%TYPE;
BEGIN
    -- Retrieve the original price of the hall
    SELECT price
    INTO v_original_price
    FROM Halls
    WHERE hall_id = (
        SELECT hall_id
        FROM Reservations
        WHERE reservation_id = reservation_id
    );
 
    -- Calculate the reduced price
    v_reduced_price := v_original_price - (v_original_price * reduction_percent / 100);
 
    -- Update the booking price of the hall
    UPDATE Halls
    SET price = v_reduced_price
    WHERE hall_id = (
        SELECT hall_id
        FROM Reservations
        WHERE reservation_id = reservation_id
    );
 
    -- Display the original and reduced prices
    DBMS_OUTPUT.PUT_LINE('Original Price: $' || v_original_price);
    DBMS_OUTPUT.PUT_LINE('Reduced Price: $' || v_reduced_price);
END;
/
 
BEGIN
    avail_reduction_in_booking(1, 10); -- Example usage: Apply 10% reduction for reservation ID 1
END;
/
 
SET SERVEROUTPUT ON

CkNSRUFURSBPUiBSRVBMQUNFIFBST0NFRFVSRSBhdmFpbF9yZWR1Y3Rpb25faW5fYm9va2luZygKICAgIHJlc2VydmF0aW9uX2lkIElOIElOVCwKICAgIHJlZHVjdGlvbl9wZXJjZW50IElOIE5VTUJFUgopCkFTCiAgICB2X29yaWdpbmFsX3ByaWNlIEhhbGxzLnByaWNlJVRZUEU7CiAgICB2X3JlZHVjZWRfcHJpY2UgSGFsbHMucHJpY2UlVFlQRTsKQkVHSU4KICAgIC0tIFJldHJpZXZlIHRoZSBvcmlnaW5hbCBwcmljZSBvZiB0aGUgaGFsbAogICAgU0VMRUNUIHByaWNlCiAgICBJTlRPIHZfb3JpZ2luYWxfcHJpY2UKICAgIEZST00gSGFsbHMKICAgIFdIRVJFIGhhbGxfaWQgPSAoCiAgICAgICAgU0VMRUNUIGhhbGxfaWQKICAgICAgICBGUk9NIFJlc2VydmF0aW9ucwogICAgICAgIFdIRVJFIHJlc2VydmF0aW9uX2lkID0gcmVzZXJ2YXRpb25faWQKICAgICk7CgogICAgLS0gQ2FsY3VsYXRlIHRoZSByZWR1Y2VkIHByaWNlCiAgICB2X3JlZHVjZWRfcHJpY2UgOj0gdl9vcmlnaW5hbF9wcmljZSAtICh2X29yaWdpbmFsX3ByaWNlICogcmVkdWN0aW9uX3BlcmNlbnQgLyAxMDApOwoKICAgIC0tIFVwZGF0ZSB0aGUgYm9va2luZyBwcmljZSBvZiB0aGUgaGFsbAogICAgVVBEQVRFIEhhbGxzCiAgICBTRVQgcHJpY2UgPSB2X3JlZHVjZWRfcHJpY2UKICAgIFdIRVJFIGhhbGxfaWQgPSAoCiAgICAgICAgU0VMRUNUIGhhbGxfaWQKICAgICAgICBGUk9NIFJlc2VydmF0aW9ucwogICAgICAgIFdIRVJFIHJlc2VydmF0aW9uX2lkID0gcmVzZXJ2YXRpb25faWQKICAgICk7CgogICAgLS0gRGlzcGxheSB0aGUgb3JpZ2luYWwgYW5kIHJlZHVjZWQgcHJpY2VzCiAgICBEQk1TX09VVFBVVC5QVVRfTElORSgnT3JpZ2luYWwgUHJpY2U6ICQnIHx8IHZfb3JpZ2luYWxfcHJpY2UpOwogICAgREJNU19PVVRQVVQuUFVUX0xJTkUoJ1JlZHVjZWQgUHJpY2U6ICQnIHx8IHZfcmVkdWNlZF9wcmljZSk7CkVORDsKLwoKQkVHSU4KICAgIGF2YWlsX3JlZHVjdGlvbl9pbl9ib29raW5nKDEsIDEwKTsgLS0gRXhhbXBsZSB1c2FnZTogQXBwbHkgMTAlIHJlZHVjdGlvbiBmb3IgcmVzZXJ2YXRpb24gSUQgMQpFTkQ7Ci8KClNFVCBTRVJWRVJPVVRQVVQgT04=