Oracle Tips by Burleson

Calling Functions from PL/SQL

In a PL/SQL block, a function can be called in a SQL statement (as seen above) or used in a simple assignment operation.

SQL> declare
  2    v_far  number := &Farenheit;
  3    v_cels number := 0;
  4  begin
  5    v_cels := f2c(v_far);
  6    dbms_output.put_line(
                'Degrees Celcius is '|| f2c(v_far));
  7  end;
  8  / 

Enter value for farenheit: 46 

old   2:   v_far  number := &Farenheit;
new   2:   v_far  number := 46; 

Degrees Celcius is 7.77777777777777777777777777777777777778 

Functions can also be used to validate variables.  In the example below the value of n_test is validated before additional processing.

SQL> create or replace function valid_numb
  2    (n_numb IN number)
  3    return boolean
  4  as
  5  begin
  6    if n_numb < 10 then return true;
  7    else return false;
  8    end if;
  9  end;
 10  / 

Function created.

SQL> declare
  2    n_test number := &Test;
  3  begin
  4    if (valid_numb(n_test))
  5      then dbms_output.put_line('Valid');
  6    else dbms_output.put_line('Invalid');
  7    end if;
  8  end;
  9  / 

Enter value for test: 8 

old   2:   n_test number := &Test;
new   2:   n_test number := 8; 

Valid 

PL/SQL procedure successfully completed. 

SQL> / 

Enter value for test: 12

old   2:   n_test number := &Test;
new   2:   n_test number := 12; 

Invalid 

PL/SQL procedure successfully completed.

This is a simple test to determine if the number is less than 10.  The function valid_numb returns a type Boolean, so it can be used as the condition of a comparison operation.  Line 4 calls the function in order to get a true/false value which is used to control the program execution. 

When should you use a procedure or a function?  Since most modules can be written either as a procedure or a function, the decision between them comes down to efficiency and maintainability.  Functions are preferred when a module returns one value.  This value may be a variable, record or array, but as long as the module always returns one value, it is a good candidate for a procedure. 

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