|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. || 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
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