Oracle PL/SQL Tutorial/Function Procedure Packages/Nested Functions

Define inner function to calculate age

SQL> set serveroutput on
SQL> set echo on
SQL> declare
  2    age   binary_integer;
  3
  4    birth_date    date;
  5    current_date  date;
  6
  7    function age_as_of (birth_date in date, as_of_date in date) return positive is
  8        as_of_year  natural;    --a year of 00 is valid.
  9        as_of_month positive;
 10        as_of_day   positive;
 11
 12        birth_year    natural;  --a year of 00 is valid.
 13        birth_month   positive;
 14        birth_day     positive;
 15
 16      age   positive;
 17    begin
 18        as_of_year := to_number(to_char(as_of_date,"yyyy"));
 19        as_of_month := to_number(to_char(as_of_date,"mm"));
 20        as_of_day := to_number(to_char(as_of_date,"dd"));
 21
 22        birth_year := to_number(to_char(birth_date,"yyyy"));
 23        birth_month := to_number(to_char(birth_date,"mm"));
 24        birth_day := to_number(to_char(birth_date,"dd"));
 25
 26        if as_of_month > birth_month then
 27           age := as_of_year - birth_year;
 28        elsif (as_of_month = birth_month) and (as_of_day >= birth_day) then
 29           age := as_of_year - birth_year;
 30        else
 31           age := as_of_year - birth_year - 1;
 32        end if;
 33
 34        return age;
 35    end;
 36  begin
 37
 38    current_date := to_date("11-13-1997","mm-dd-yyyy");
 39    birth_date   := to_date("11-15-1961","mm-dd-yyyy");
 40
 41
 42    age := age_as_of (birth_date, current_date);
 43    dbms_output.put_line(age);
 44
 45  end;
 46  /
35
PL/SQL procedure successfully completed.
SQL>
SQL>

Nested function in anonymous block

SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> declare
  2
  3     function myFunction (myValue in number,myFactor in number) return number is
  4
  5         myFactor_to_use  number;
  6         minimum_wage  number := 5;
  7     begin
  8         if (myFactor is null) or (myFactor < minimum_wage) then
  9             myFactor_to_use := minimum_wage;
 10         else
 11             myFactor_to_use := myFactor;
 12         end if;
 13
 14         return myValue * myFactor_to_use;
 15      end;
 16  begin
 17    dbms_output.put_line(myFunction(40,10));
 18    dbms_output.put_line(myFunction(40,2));
 19    dbms_output.put_line(myFunction(40,null));
 20  end;
 21  /
400
200
200
PL/SQL procedure successfully completed.
SQL>
SQL>