SELECT Patients.Name, 
       Patients.Address, 
       Patients.Disease_Name,
       Patients.Room_Id, 
       Rooms.Room_Type 
  FROM Patients 
       INNER JOIN Rooms 
          ON Patients.Room_Id=Rooms.Room_Id;

Develop a simplified conceptual data model for a hospital information system and write a
PL/SQL program using oracle to perform the following tasks.
a) Print the patient  details according to room wise.
b) Print the patient  details according to disease wise.
c) Print the patient  details according to the doctors attending them.

create or replace PROCEDURE get_employee_info_by_employee_id 
(   
p_employee_id   NUMBER DEFAULT -1
)
AS
    v_name Employee.Name%TYPE;
   v_email_address Employee.Email_Address%TYPE;
   v_hire_date Employee.Hire_Date%TYPE;
   v_update_date Employee.Update_Date%TYPE;
BEGIN
   -- Just SELECT away, returning column values into the variables. If
   -- the employee ID isn't found, Oracle will throw and you can pick
   -- up the pieces in the EXCEPTION block below.
   SELECT Name, Email_Address, Hire_Date, Update_Date
     INTO v_name, v_email_address, v_hire_date, v_update_date
     FROM Employee
     WHERE Employee_ID = p_employee_id;
  
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
   DBMS_OUTPUT.PUT_LINE('NAME: ' || v_name);
   DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || v_email_address);
   DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || v_hire_date);
   DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || v_update_date);
EXCEPTION
   -- If the query didn't find a row you'll end up here. In this case
   -- there's no need for any type of fancy exception handling; just
   -- reporting that the employee wasn't found is enough.
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee number ' || p_employee_id || ' not found.');
END;

SQL> declare
  2    v_name varchar2(20) := 'Littlefoot';
  3  begin
  4    dbms_output.put_line(v_name);
  5  end;
  6  /
                                             <-- there's no output, really
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Littlefoot                                   <-- ah, here it is!

PL/SQL procedure successfully completed.

SQL>

create or replace PROCEDURE get_patientinfo_info_by_roomwise
(   
p_patient_id   NUMBER DEFAULT -1
)
AS
    patient_name Patients.P_Name%TYPE;
   room_type Rooms.Room_Type %TYPE;

BEGIN
    SELECT Patients.P_Id, Patients.P_Name, Patients.P_Address, Patients.P_Sex, Patients.P_Phone,Patients.Disease_Name, Patients.Room_Id, Rooms.Room_Type FROM Patients INNER JOIN Rooms ON Patients.Room_Id=Rooms.Room_Id;
LOOP   
DBMS_OUTPUT.PUT_LINE('Patient Name: ' || patient_name);DBMS_OUTPUT.PUT_LINE('Room Type: ' || room_type);
END LOOP;

END