Oracle PL/SQL - Returning values with functions


The following function calculates the differences between total areas of circles with a number of radii.


SQL> declare-- from  w w  w  . j a va 2s  .  c o  m
  2      v_pi_nr NUMBER:=3.14;
  3      function f_getDiff_Nr(i_rad1_nr NUMBER,i_rad2_nr NUMBER)
  4      return NUMBER is
  5              v_area1_nr NUMBER;
  6              v_area2_nr NUMBER;
  7              v_out_nr NUMBER;
  8        function f_getArea_Nr (i_rad_nr NUMBER)
  9        return NUMBER
 10        is
 11       begin
 12          return v_pi_nr*(i_rad_nr**2);
 13        end;
 15     begin
 16          v_area1_nr := f_getArea_Nr (i_rad1_nr);
 17          v_area2_nr := f_getArea_Nr (i_rad2_nr);
 18          v_out_nr   :=v_area1_nr-v_area2_nr;
 19          return v_out_nr;
 20     end;
 21  begin
 22     DBMS_OUTPUT.put_line('Diff between 3 and 4: '||f_getDiff_Nr(4,3));
 23     DBMS_OUTPUT.put_line('Diff between 4 and 5: '||f_getDiff_Nr(5,4));
 24     DBMS_OUTPUT.put_line('Diff between 5 and 6: '||f_getDiff_Nr(6,5));
 25  end;
 26  /


There are two nested functions, one inside the other.

Each sub-program can have its own subprograms.

Functions have a specification block that starts with the keyword FUNCTION and may include parameters.

They include RETURN datatype whose data type declares what kind of data the function will return.

The body of all functions must have at least one RETURN statement.

Like procedures, that clause signals the immediate end of the execution of the subprogram.