RelationalDBDesignRelationalDBDesign





Create Procedure   «Prev  Next»
Lesson 4 Procedures that call procedures or functions
Objective Capabilities and restrictions for calling other procedures

Procedures that call Procedures or Functions

Question: Can you call a procedure in another stored procedure?
List the capabilities and restrictions for calling other procedures. You learned to create a procedure in the previous lesson. This lesson shows you how to combine the procedure and the function. You can use a function within a procedure or a procedure within a function. If you are using a function in a procedure, remember that if the function appears in a query or some other SQL command, the same restrictions apply to the function as if it were called in plain SQL. In other words, you cannot use a function that returns a Boolean value in a query, even if the query appears inside a procedure. The following table shows the capabilities and restrictions for calling functions and procedures. The two right columns show the capabilities and restrictions of the combination listed in the first column.

  Capability Restriction
Procedure calls function A procedure can call any function. If function returns a Boolean, you cannot use it in an SQL command inside the procedure.
Procedure calls a procedure A procedure can call any procedure. If the called procedure updates data that is also modified by the calling procedure, a deadlock can occur in which one procedure's action prevents the other procedure from successfully completing.
Function calls a function A function can call any function. None.
Function calls a procedure A function can call a procedure only if the procedure does not modify data. When a function calls a procedure, the procedure must not modify database data.

It is important to plan the connections between your sub-programs so that you do not receive unexpected errors caused by a restricted sub-program calling a sub-program that is unrestricted.
The next lesson teaches you to add a function to a procedure.

Procedure Calling Functions

The following PL/SQL is an example of a procedure calling functions.
SQL> set serveroutput on
SQL> declare
  2   procedure p1 (
  3    i1 in out number
  4   )
  5   as
  6   begin
  7    i1 := 200;
  8   end p1;
  9   function f1 
 10   return number
 11   is
 12    l1 number;
 13   begin
 14    p1(l1);
 15    return l1;
 16   end f1;
 17  begin
 18   dbms_output.put_line(f1);
 19  end;
 20  /
200

PL/SQL procedure successfully completed.

SQL> set serveroutput off