| 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.
|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.
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
2 procedure p1 (
3 i1 in out number
7 i1 := 200;
8 end p1;
9 function f1
10 return number
12 l1 number;
15 return l1;
16 end f1;
PL/SQL procedure successfully completed.
SQL> set serveroutput off