RETURN Values in PL/SQL Oracle Tips by Burleson Consulting

RETURN Values in PL/SQL

 As stated above, a function must return a value of the datatype defined in the header.  The value can be a literal, a variable or a calculated value.  The actual value returned is defined in the RETURN clause XE "RETURN clause" .

create or replace function full_name
   (v_fname IN author.author_first_name%type,
    v_lname IN author.author_last_name%type)
   return varchar2
as
begin
   return initcap(v_fname||' '||v_lname);
end;
/

The example above concatenates two variables and passes the result through Oracle’s initcap function XE "initcap function"  before returning the final results.  A function can have multiple RETURN clauses but will terminate the function upon executing the first RETURN clause XE "RETURN clause" .  In the example below, the CASE statement will insure that one of the four RETURN clauses is executed.

SQL> create or replace function num2word
2    (n_number IN number)  
3    return varchar2  
4  as  
5  begin  
6    case n_number  
7      when 1 then return 'one';  
8      when 2 then return 'two';  
9      when 3 then return 'three';  
10    else return 'greater than 3';  
11    end case;  
12  end;  
13  /

Function created.

SQL> begin  
2    for i in 1 .. 4 loop  
3      dbms_output.put_line(num2word(i));  
4    end loop;  
5  end;  
6  / one two three greater than 3

PL/SQL procedure successfully completed.

If an IF/THEN statement is used, you may not reach a RETURN statement XE "RETURN statement"  and the function will raise an exception.

SQL> create or replace function num2word  
2    (n_number IN number)  
3    return varchar2  
4  as  
5  begin  
6    if n_number = 1  
7      then return 'one';  
8    elsif n_number = 2 then return 'two';  
9    elsif n_number = 3 then return 'three';  
10    end if;  
11  end;  
12  / 

Function created.

SQL> begin  
2    for i in 1 .. 4 loop  
3      dbms_output.put_line(num2word(i));  
4    end loop;  
5  end;  
6  /
one
two
three
begin
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "PUBS.NUM2WORD", line 9
ORA-06512: at line 3

Everything worked as expected except that when we passed a value greater than 3, the function ends without executing the RETURN clause XE "RETURN clause".  In this example, passing a too-high value causes the function to raise an exception.  To avoid this error, we add a RETURN clause at the end of the function to catch any missed values.  One of the most common causes of not executing a RETURN clause is due to the function raising an exception.  If the function has an exception handling section (discussed next in this Chapter) the exception handler must contain a RETURN clause or it will simply raise another exception. Remember, unlike a procedure, a function is never executed independently and it must be called to execute.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.