Hi there,

I am having a bit of trouble understanding how sql functions work when processing input from multiple tables. Here is the question:

"Please create a PL/SQL function get_customer_name that returns the customers name given Order ID. Please write an anonymous PL/SQL program to call this function with some Order ID as input, and print out the results."

Here is the code that I have, I am having difficulty seeing if it will work.

CREATE Function get_customer_name ( order_id IN number ) RETURN varchar2

IS

onumber number;

cursor c1 is

SELECT customer_t.customer_name, order_t.order_id

FROM customer_t, order_t

WHERE customer_t.customer_id = order_t.customer_id;

BEGIN

open c1;

fetch c1 into onumber;

if c1%notfound then

cnumber := 9999;

end if;

close c1;

RETURN onumber;

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END;

Tables I am using:

1. Customer_T - attributes of customer_name, customer_ID, customer_address, customer_phone

2. Order_T - attibutes of order_id, customer_id, order_date, sales_id

Where I am having issue is how to utilize this to write a function that allows me to input id and get a return of the customer's name. I can equijoin to do this in regular sql but how do I write a function that does this? please explain/assist. Thank you!