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=