Write a PL/SQL Procedure to Display All Hotel Names

Write a PL/SQL procedure to display all hotel names based on the hotel type passed. The procedure will take hotel type as an argument and it should display all the hotel names of that type. Use Cursor.

Consider the details given below:

Pl/SQL Procedure name: FindHotelDetails

Input parameter : htype IN hotel_details.hotel_type%TYPE

Sample Records:

Hotel_Details:

HOTEL_ID	HOTEL_NAME	HOTEL_TYPE	RATING
1	Saravana Bhavan	veg	4
2	Hari Bhavan	non veg	4.5
3	Asifa	non veg	4.3
4	Annapoorna	veg	4.5
5	Anandhas	veg	4

Functional Requirement

Procedure FindHotelDetails(hotel_type)

Sample Input:

FindHotelDetails('veg')

Sample Output

Hotel Names are :
Saravana Bhavan
Annapoorna
Anandhas

(Hint: Data is case sensitive. Use '/' to terminate the PLSQL block)

Step-by-Step Guide to Creating a PL/SQL Procedure to Display Hotel Names

Introduction to PL/SQL Procedures:
A PL/SQL procedure is a named block of PL/SQL code that performs a specific task. In this guide, we will walk through creating a procedure that displays hotel names based on a given hotel type.

Understanding the Task:
The procedure will accept a hotel type as input (e.g., 'veg' or 'non veg') and then display all hotel names that match the input type. This is useful for retrieving specific data based on user input.

Writing the Procedure Code:
Let's break down the procedure code step by step.

Procedure Declaration:
We start by declaring the procedure with a name. Here, it is called FindHotelDetails:

CREATE OR REPLACE PROCEDURE FindHotelDetails (htype IN hotel_details.hotel_type%type)

This line tells the database we are creating or replacing a procedure named FindHotelDetails that takes an input parameter htype.

Setting Up the Cursor:
We define a cursor to retrieve the hotel names that match the given hotel type:

CURSOR c_hotel IS SELECT Hotel_name FROM Hotel_Details WHERE Hotel_type = htype;

This allows us to fetch hotel names from the database where the type matches the input parameter.

Opening the Cursor:
The next step is to open the cursor and prepare to fetch data:

OPEN c_hotel;

This line opens the cursor and prepares it to start fetching hotel names.

Processing the Results:
Now, we loop through each row returned by the cursor and display the hotel names:

    LOOP
        FETCH c_hotel INTO v_hotel;
        EXIT WHEN c_hotel%NOTFOUND;
        dbms_output.put_line(v_hotel);
    END LOOP;
        

Each hotel name is fetched and displayed one by one. The loop exits when there are no more hotel names left to process.

Closing the Cursor:
After processing all hotel names, we close the cursor to free up resources:

CLOSE c_hotel;

This ensures that the cursor is properly closed after use.

Conclusion:
With this procedure in place, you can retrieve and display all hotel names that match a given type, ensuring a dynamic and flexible way to access specific data from the hotel database.