Home » SQL & PL/SQL » SQL & PL/SQL » How can I convert a SQL command to MySql
How can I convert a SQL command to MySql [message #661821] |
Mon, 03 April 2017 22:33 |
|
vinodkalpaka
Messages: 12 Registered: April 2017
|
Junior Member |
|
|
I Know Sql for the mysql database. But how can I create PL/SQL for oracle from my sql command?
Actually I need to write a PL/SQL program using oracle to print the patient details according to room wise. and need to print the patient details according to the doctors attending them. I have another table for doctors, and patient table contains a foreign key - Doctor_Id.
Example:
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;
|
|
|
|
Re: How can I convert a SQL command to MySql [message #661825 is a reply to message #661823] |
Mon, 03 April 2017 23:10 |
|
vinodkalpaka
Messages: 12 Registered: April 2017
|
Junior Member |
|
|
Please see the files. It contains ER diagram.
and data of the tables.
https://www.dropbox.com/sh/tvnsv18lkk1g865/AACeIDak7D7wLiM9bH8hwIMBa?dl=0
Requirement:
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. (I have not created a separate table for disease)
c) Print the patient details according to the doctors attending them.
What I have done in SQL:
a)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;
b) SELECT Patients.P_Id, Patients.P_Name, Patients.P_Address, Patients.P_Sex, Patients.P_Phone,Patients.Disease_Name FROM Patients order by Disease_Name;
c) SELECT Patients.P_Id, Patients.P_Name, Patients.P_Address, Patients.P_Sex, Patients.P_Phone,Patients.Disease_Name, Patients.Doctor_Id, Doctors.Name, Doctors.Phone, Doctors.address FROM Patients INNER JOIN Doctors ON Patients.Doctor_Id=Doctors.Id;
But This is in SQL, I need it in PL SQL. Actually I am not familiar with Oracle PL/SQL.
So I need the conversion..
I just need a basic idea about the conversion or difference. I am not worried about whether your help gives an accurate result.
[Updated on: Mon, 03 April 2017 23:23] Report message to a moderator
|
|
|
|
|
|
Re: How can I convert a SQL command to MySql [message #661830 is a reply to message #661828] |
Tue, 04 April 2017 00:00 |
|
vinodkalpaka
Messages: 12 Registered: April 2017
|
Junior Member |
|
|
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.
I am not worried the DataModel and ER diagram section, but the issue is with the second part of the problem. "write a
PL/SQL program using oracle to perform the following tasks."
[Updated on: Tue, 04 April 2017 00:07] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How can I convert a SQL command to MySql [message #661846 is a reply to message #661845] |
Tue, 04 April 2017 02:13 |
|
vinodkalpaka
Messages: 12 Registered: April 2017
|
Junior Member |
|
|
my one do not have the sections for declare, begin and output the variable. Those sections are very new to me.
for example:
for the question: Print the patient details according to room wise.
I used the query
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;
So when I convert it to PL/SQL
I assume I have to write PL/SQL like following.
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
Please confirm, whether I am correct or not, (I do not put all output I required,)
[Updated on: Tue, 04 April 2017 02:15] Report message to a moderator
|
|
|
|
|
Re: How can I convert a SQL command to MySql [message #661850 is a reply to message #661846] |
Tue, 04 April 2017 03:14 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:my one do not have the sections for declare, begin and output the variable
And yours has what?
Maybe you should think to SHOW us.
Quote:Please confirm, whether I am correct or not, (I do not put all output I required,)
I confirm you are not correct.
Once more:
BlackSwan wrote on Tue, 04 April 2017 05:51We don't have your tables.
We don't have your data.
We don't have your requirements.
We don't know what the results should be.
Why do you expect we can provide you any answers?
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
Michel Cadot wrote on Tue, 04 April 2017 07:28
SQL and PL/SQL are 2 different things.
PL/SQL is a programming language which may or not include SQL statements.
SQL is the language to retrieve data from the database.
Database SQL Reference
PL/SQL User's Guide and Reference
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Use SQL*Plus and copy and paste your session, the WHOLE session.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:42:43 CDT 2024
|