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

Define inner function to calculate age

   <source lang="sql">

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></source>

Nested function in anonymous block

   <source lang="sql">

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></source>