Calling Stored Programs from Stored Programs
Calling one stored program from another is perfectly simple. You do this with the CALL statement, just as you would from the MySQL command-line client.
Figure 2-15 shows a simple stored procedure that chooses between two stored procedures based on an input parameter. The output of the stored procedure (l_bonus_amount is populated from an OUT parameter) is passed to a third procedure.
Here is an explanation of the significant lines:
|
Line(s) |
Explanation |
|---|---|
|
11 |
Determine if the employee is a manager. If he is a manager, we call the calc_manager_bonus stored procedure; if he is not a manager, we call the calc_minion_bonus stored procedure. |
|
12 and 14 |
With both stored procedures, pass in the employee_id and provide a variablel_bonus_amountto receive the output of the stored procedure. |
|
16 |
Call the grant_bonus stored procedure that passes as arguments the employee_id and the bonus amount, as calculated by the stored procedure we called in line 12 or 14. |
Figure 2-15. Example of calling one stored procedure from another