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

Question

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

Answer 1

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;