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.