Answer to Question #48248 in Databases | SQL | Oracle | MS Access for Asker31

Question #48248
For the follow tables: create table customer( cid integer, --- customer ID cname varchar(50), --- customer name primary key (cid)); create table product( pid integer, --- product ID pname varchar(50), ---- Product name price number, --- price of product primary key (pid)); create table orders( oid integer, --- order ID cid integer,--- customer ID primary key (oid), foreign key (cid) references customer(cid)); create table order_line( oid integer, --- order ID pid integer, --- product ID quantity integer, --- quantity of product in the order primary key (oid, pid), foreign key (oid) references orders(oid), foreign key (pid) references product(pid)); Write a PL/SQL procedure to print out the names of customers who have bought a certain product. The product name is the input parameter
Expert's answer
Question 1.

Create or Replace ProcedureCustomer_List(Product_name varchar)
Is
v_customer_name varchar(50);
Cursor c_Customer Is
Select Distinct
c.CNAME
From
order_line ol
Left Join
product p
On
ol.PID = p.PID
Left Join
orders o
On
ol.OID = o.OID
Left Join
customer c
On
o.CID = c.CID
Where
p.PNAME = Product_name;
Begin
open c_Customer;
LOOP
Fetch c_Customer Into v_customer_name;
Exit When c_Customer%NOTFOUND;

dbms_output.put_line(v_customer_name);
End LOOP;
Close c_Customer;
End;

Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be first!

Leave a comment

Ask Your question

LATEST TUTORIALS
New on Blog
APPROVED BY CLIENTS
paypal