Write a pl/sql procedure for the following question. Show specific event: Given an event type (Birthday, Wedding, etc.) display all events of that type in all hotels along with the address of the hotel and the date of the event.

Question

question

bobbylove asked Nov 29, '18

Write a pl/sql procedure for the following question. Show specific event: Given an event type (Birthday, Wedding, etc.) display all events of that type in all hotels along with the address of the hotel and the date of the event.

The tables are below

Hotel Table have

Hotel_id, hotel_name, Hotel_city, Hotel_state, Hotel_zip,Hotel_phone,

Reservation Table have

Reservation_id, Hotel_id, Room_num, Service_id, Guest_name, Reservation_start_date, Reservation_end_date, cancelation_date, Num_of_guest, event_type

Room Table have

Room_num, Hotel_id, Room_type, Room_capacity, Room_cost

service table have

service_id, Service_type, Service_cost

This is what i tried. the procedure compiled, and when i excute it it just say procedure successfully completed but i want my output to be the print statment(dbms statment). Im not even sure if my code is correct (fit the question). my code is below.

set serveroutput on; 
 create or replace  procedure change_event (Event_Name in VARCHAR2)
IS
cursor c1 is select Event_type, Hotel_name , Hotel_city,Hotel_state, Hotel_Zip ,        hotel_phone 
from reservation, Hotel
where reservation.Hotel_id = hotel.hotel_id
and event_type = event_name;
R_row c1%rowtype; 
begin
for R_row in c1
loop
Dbms_output.put_line (R_row.event_type || '  '|| R_row.Hotel_name  ||' , '|| R_row.Hotel_city || ' , '||R_row.Hotel_state||'  '|| R_row.Hotel_Zip ||' , '|| R_row.hotel_phone );
end loop;
end;

exec change_event(' Birthday');

functions plsql procedure

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Nov 28, 2018 at 12:40 PM

What have you tried so far? Happy to help with homework but like to see that you’ve tried something too.

0 ·

Answer 1 · 2018-11-28T21:42:47Z

bobbylove answered Nov 28, '18

@KenJ This is what i tried. the procedure compiled, and when i excute it it just say procedure successfully completed but i want my output to be the print statment(dbms statment). Im not even sure if my code is correct (fit the question). my code is below. Thank you

set serveroutput on;  
create or replace  procedure change_event (Event_Name in VARCHAR2)
IS
cursor c1 is select Event_type, Hotel_name , Hotel_city,Hotel_state, Hotel_Zip ,hotel_phone
from reservation, Hotel
where reservation.Hotel_id = hotel.hotel_id
and event_type = event_name;
R_row c1%rowtype;
begin
for R_row in c1
loop
Dbms_output.put_line (R_row.event_type || '  '|| R_row.Hotel_name  ||' , '|| R_row.Hotel_city 
|| ' , '||R_row.Hotel_state||'  '|| R_row.Hotel_Zip ||' , '|| R_row.hotel_phone );
end loop;
end;

exec change_event(' Birthday');

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Nov 29, 2018 at 10:52 PM

The query itself seems reasonable for what you are after.  Can you redo the procedure and just return the query results rather than adding the complexity of the print statement?  That might help narrow things down (also, I notice a leading space in ‘ Birthday’.  Does that actually match any data?


set serveroutput on;

create or replace  procedure change_event (Event_Name in VARCHAR2)

IS

select Event_type, Hotel_name , Hotel_city,Hotel_state, Hotel_Zip ,hotel_phone
from reservation inner join Hotel
on reservation.Hotel_id = hotel.hotel_id
Where reservation.event_type = Event_Name;

end;

exec change_event(' Birthday');

0 ·

question