Pages

Function

A PL/SQL function is, basically, a procedure that explicitely returns a value.

Or, other way round, a procedure is a function that explicitely does not return anything.

In any case both procedure and function could return as many values as required in their parameter list, marking any parameter required to be a return value with the "OUT" tag. But only the explicit return value from function could be assigned directly to a value in the caller code as a result of invoking that indipendent code block.

Here is how to create a simple function accepting an input varchar2 parameter and returning another varchar2:

create or replace function country_name(in_id in varchar2)
return varchar2
is
l_country_name countries.country_name%type;
begin
select country_name
into l_country_name
from countries
where country_id = in_id;

return l_country_name;

exception
when no_data_found then
return 'No such country id';
when others then
return 'Unexpected error: ' || sqlerrm;
end;

In case of exception, we trap it and return just an error message to the caller.

Here is how we call the function we just created:

declare
l_country_name countries.country_name%type;
begin
l_country_name := country_name('UK');
dbms_output.put_line(l_country_name);
end;

And that's how to get rid of it:
drop function country_name;

Procedures, functions, and parameters are thoroughly discussed in chapter 17 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

No comments:

Post a Comment