| 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