Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

By: FYIcenter.com

Part:   1  2  3  4   5  6  7 

(Continued from previous part...)

How To Call a Sub Procedure?

To call a sub procedure, just use the sub procedure name as a statement. Here is another example of calling a sub procedure:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS       
  2    PROCEDURE WELCOME_PRINT(S CHAR) AS      
  3    BEGIN         
  4      DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);  
  5    END;          
  6  BEGIN           
  7    WELCOME_PRINT('FYICenter');            
  8  END;            
  9  /

SQL> EXECUTE WELCOME;
Welcome to FYICenter

How To Define a Sub Function?

A sub function is a function defined and used inside another procedure or function. You need to define a sub function in the declaration part of the enclosing procedure or function. Sub function definition starts with the FUNCTION key word. Here is a sample script showing you how to define and use a sub function:

SQL> CREATE OR REPLACE PROCEDURE SUM_TEST AS      
  2    FUNCTION MY_SUM(X NUMBER, Y NUMBER)        
  3      RETURN NUMBER AS          
  4    BEGIN        
  5      RETURN X + Y;             
  6    END;         
  7  BEGIN          
  8    DBMS_OUTPUT.PUT_LINE('3 + 5 = ' || 
  9      TO_CHAR(MY_SUM(3,5))); 
 10    DBMS_OUTPUT.PUT_LINE('5 + 3 = ' ||
 11      TO_CHAR(MY_SUM(5,3))); 
 12  END;           
 13  /
 
SQL> EXECUTE SUM_TEST;
3 + 5 = 8
5 + 3 = 8

Can Sub Procedure/Function Be Called Recursively?

PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function:

SQL> CREATE OR REPLACE PROCEDURE FACTORIAL_TEST AS              
  2    FUNCTION FACTORIAL(N NUMBER)                             
  3      RETURN NUMBER AS                                       
  4    BEGIN            
  5      IF N <= 1 THEN 
  6        RETURN 1;    
  7      ELSE           
  8        RETURN N*FACTORIAL(N-1);                             
  9      END IF;        
 10    END;             
 11  BEGIN              
 12    DBMS_OUTPUT.PUT_LINE('3! = ' ||
 13      TO_CHAR(FACTORIAL(3)));
 14    DBMS_OUTPUT.PUT_LINE('10! = ' ||
 15      TO_CHAR(FACTORIAL(10)));
 16    DBMS_OUTPUT.PUT_LINE('64! = ' ||
 17      TO_CHAR(FACTORIAL(64)));
 18  END;
 19  /               
                        
SQL> EXECUTE FACTORIAL_TEST;
3! = 6
10! = 3628800
64! = 126886932185884164103433389335161480802000000000000...

There must be something wrong with the FACTORIAL() definition that causes those many extra '0's in the '64!' result.

What Happens If Recursive Calls Get Out of Control?

What happens if your code has bug on recursive procedure calls, which causes an infinite number nested procedure calls? The answer is so good. Oracle server seems to offer no protection calling stack limit. The script below shows you a badly coded recursive procedure. If you run it on an Oracle 10g XE server on Windows, your server will out of control and keep using virtual memory to satisfy the growing calling stack. You have to reboot your server to control back.

SQL> CREATE OR REPLACE PROCEDURE STACK_TEST AS
  2  --Warning: do not run this procedure on your server
  3    PROCEDURE STACK AS
  4    BEGIN
  5      STACK;
  6    END;
  7  BEGIN
  8    STACK;
  9  END;
 10  /

SQL> EXECUTE STACK_TEST;
(your server keep running with 100% CPU and memory usage)

(Continued on next part...)

Part:   1  2  3  4   5  6  7