| 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
