Recursive Function, Procedure, PLSQL in Oracle

advertisements

_____________________________________________________________________________________________________________________

In oracle we can write PLSQL blocks with recursive calling.

1.   See an example of recursive function for printing multiple table.

SQL> create or replace function mul (n number) return number as

begin
      IF n <= 1 THEN
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));
            RETURN 1;   
      ELSE
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));   
            return mul(n-1);
      end if;
end;
/
Function created.

SQL> select mul(10) from dual;

   MUL(10)
----------
         1
 
10 x 3 = 30
9 x 3 = 27
8 x 3 = 24
7 x 3 = 21
6 x 3 = 18
5 x 3 = 15
4 x 3 = 12
3 x 3 = 9
2 x 3 = 6
1 x 3 = 3

2.  PLSQL Block for factorial using Recursive Function

declare
      fvalue number;
       fa number;
function fact (n number) return number as
begin
      IF n < 1 THEN
            RETURN 1;   
      ELSE
            return n*fact(n-1);
      end if;
end;
begin
   fvalue:=1 ;
   fa:=fact(5);
   dbms_output.put_line('Factorial = '||to_char(fa));
end;
/
Factorial = 120
 
PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

Website Stats