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 Go to next message
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 #661823 is a reply to message #661821] Mon, 03 April 2017 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
We 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

Re: How can I convert a SQL command to MySql [message #661825 is a reply to message #661823] Mon, 03 April 2017 23:10 Go to previous messageGo to next message
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 #661826 is a reply to message #661825] Mon, 03 April 2017 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
>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.
explain the difference & why SQL is not sufficient & only PL/SQL is required.
so how will you know what is correct when you admittedly don't know PL/SQL?

Re: How can I convert a SQL command to MySql [message #661827 is a reply to message #661826] Mon, 03 April 2017 23:40 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Is SQL is sufficient for the requirement?
Is the same query use for Oracle also? I am only familiar with MySQL database,
In many oracle commands I have seen Declare , Begin, End. so the confusion.
(Please note this question was for an assignment task.)

[Updated on: Mon, 03 April 2017 23:40]

Report message to a moderator

Re: How can I convert a SQL command to MySql [message #661828 is a reply to message #661827] Mon, 03 April 2017 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
vinodkalpaka wrote on Mon, 03 April 2017 21:40
Is SQL is sufficient for the requirement?
Is the same query use for Oracle also? I am only familiar with MySQL database,
In many oracle commands I have seen Declare , Begin, End. so the confusion.
(Please note this question was for an assignment task.)
What is the requirement?

I don't know what problem you are trying to solve.

How will you or I known when correct answer is posted?
Re: How can I convert a SQL command to MySql [message #661830 is a reply to message #661828] Tue, 04 April 2017 00:00 Go to previous messageGo to next message
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 #661833 is a reply to message #661830] Tue, 04 April 2017 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: How can I convert a SQL command to MySql [message #661834 is a reply to message #661833] Tue, 04 April 2017 00:47 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Ok. Michel.

I have found a PL SQL code for another database system here.


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;

So use the same method? here it use "DBMS_OUTPUT.PUT_LINE" for printing.
Re: How can I convert a SQL command to MySql [message #661836 is a reply to message #661834] Tue, 04 April 2017 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 21325
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't forget to SET SERVEROUTPUT ON before running your PL/SQL code; otherwise, you won't see anything on the screen.

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>

Re: How can I convert a SQL command to MySql [message #661837 is a reply to message #661834] Tue, 04 April 2017 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vinodkalpaka wrote on Tue, 04 April 2017 07:47

...
I have found a PL SQL code for another database system here.
...
So use the same method? here it use "DBMS_OUTPUT.PUT_LINE" for printing.
No, just use the SQL statement.
No need of PL/SQL.

Re: How can I convert a SQL command to MySql [message #661838 is a reply to message #661837] Tue, 04 April 2017 01:18 Go to previous messageGo to next message
Littlefoot
Messages: 21325
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
He has to use PL/SQL.

Quote:

Requirement:

write a PL/SQL program using oracle ...
Re: How can I convert a SQL command to MySql [message #661839 is a reply to message #661837] Tue, 04 April 2017 01:27 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Ok,I shall try with SQL statement for this assignment.
Re: How can I convert a SQL command to MySql [message #661840 is a reply to message #661839] Tue, 04 April 2017 01:35 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Littlefoot , can you please convert my sql to PL/SQL.
I do not want the exact thing, but a rough one. That means, no need to write all columns, just need two or three,
Re: How can I convert a SQL command to MySql [message #661841 is a reply to message #661840] Tue, 04 April 2017 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is your problem changing the code you posted for your specific query?

Re: How can I convert a SQL command to MySql [message #661842 is a reply to message #661841] Tue, 04 April 2017 01:46 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Michel, I just need the PL SQL version of the SQL query.
I have attached my er diagram and table data in my initial replies.
Re: How can I convert a SQL command to MySql [message #661843 is a reply to message #661842] Tue, 04 April 2017 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what is your problem doing it?
Or do you mean you are a "do my work" kind of man?

Re: How can I convert a SQL command to MySql [message #661844 is a reply to message #661843] Tue, 04 April 2017 01:53 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Michel, actually am new to Oracle. I know the SQL commands, I just want to compare the SQL and PL/SQL.
If I have the PL/SQL version of my SQL commands, It will be nice to do further tasks.
So I just need a a version of any one of my SQL query (to PL SQL).
Re: How can I convert a SQL command to MySql [message #661845 is a reply to message #661844] Tue, 04 April 2017 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have one for another query, what is your problem to use yours instead?

Re: How can I convert a SQL command to MySql [message #661846 is a reply to message #661845] Tue, 04 April 2017 02:13 Go to previous messageGo to next message
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 #661848 is a reply to message #661846] Tue, 04 April 2017 02:29 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
For processing multiple rows in PL/SQL you will have to look into Cursor Loops. (Have a look at the examples)
Re: How can I convert a SQL command to MySql [message #661849 is a reply to message #661848] Tue, 04 April 2017 03:12 Go to previous messageGo to next message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Ok, Thomas, I shall have an R&D on this area.
Re: How can I convert a SQL command to MySql [message #661850 is a reply to message #661846] Tue, 04 April 2017 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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:51
We 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.


Re: How can I convert a SQL command to MySql [message #661852 is a reply to message #661849] Tue, 04 April 2017 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

vinodkalpaka wrote on Tue, 04 April 2017 10:12
Ok, Thomas, I shall have an R&D on this area.
For this you have to read the book I gave you in my first and latest answers.

[Updated on: Tue, 04 April 2017 03:17]

Report message to a moderator

Re: How can I convert a SQL command to MySql [message #661854 is a reply to message #661852] Tue, 04 April 2017 03:28 Go to previous message
vinodkalpaka
Messages: 12
Registered: April 2017
Junior Member
Ok, Thank you for the info, Michel, Thomas ,Littlefoot and BlackSwan .
Previous Topic: Need help with SQL
Next Topic: how to send tablespace mail alerts with PL/SQL
Goto Forum:
  


Current Time: Tue Jan 16 20:16:00 CST 2018

Total time taken to generate the page: 0.01656 seconds