How to convert MYSQL to PL/SQL-CodePudding

This is my MYSQL code. Here I want to convert This to PL/SQL

Select 
products.productID,
products.productName,
orderDetails.quantity,
orderDetails.unitPrice,
orderDetails.unitPrice*orderDetails.quantity as sub_total,
orderDetails.discount as taxes 
from products 
inner join Orderdetails on products.productID=orderDetails.productID

How can I Convert this to PL/SQL?

CodePudding user response：

PL/SQL means "Oracle", as it is its procedural extension to SQL. In other words, we write "queries" in SQL, and we write procedures, functions, packages, triggers and stuff in PL/SQL.

If you're just confused and - actually - want to run that query in Oracle's SQL, you don't have to do anything as it would work just fine (presuming tables with those columns exist in a schema you're connected to). Though, I'd suggest you to use table aliases as they make code easier to read, e.g.

select 
  p.productid,
  p.productname,
  o.quantity,
  o.unitprice,
  o.unitprice * o.quantity as sub_total,
  o.discount as taxes 
from products p inner join orderdetails o on p.productid = o.productid;

If you really wanted to switch to PL/SQL, then an anonymous PL/SQL block would do (i.e. you don't need a procedure or a function; what you really need depends on what you want to do next). In PL/SQL, you have to select INTO something; for example, into locally declared variables. However, as your query doesn't contain where clause, it would return all rows whose productid values match in both tables, and that can be no rows, one row or many rows. For no rows, you'd have to handle no_data_found exception. For one row, it would work. For many rows, you'd have to handle too_many_rows exception. Therefore, it might be a good idea to use a cursor FOR loop - that's what I'll demonstrate - and simply display what's being found to the screen (I'll display only two values, though):

set serveroutput on
begin
  for cur_r in (select 
                  p.productid,
                  p.productname,
                  o.quantity,
                  o.unitprice,
                  o.unitprice * o.quantity as sub_total,
                  o.discount as taxes 
                from products p inner join orderdetails o on p.productid = o.productid
               )
  loop
    dbms_output.put_line(cur_r.productname ||', '|| cur_r.sub_total);
  end loop;
end;
/

As I said: how that code would really look like depends on what you want to do with it.