Stored Function

Stored Function

Create stored function is called get_cleaners_location. This function takes as input a cleaner’s

number and returns the cleaner’s depot address. Call the function from within an SQL statement

to select the cleaner’s name and location for a particular cleaner.

create or replace function get_cleaners_location (cleaner_num in cleaner.cno%type)

return depot.daddress %type as

dlocation depot.daddress %type;

begin

select daddress

into dlocation

from cleaner c, depot d

where cno= cleaner_num

and d.dno=c.dno;

return (dlocation);

end;

/

Function created.

select cname, get_cleaners_location (cno) "Address"

from cleaner

where cno='110';

CNAME Address

John Camden Road