4-5. Executing a Stored Procedure
Problem
You want to execute a stored procedure from SQL*Plus.
Solution
Open SQL*Plus, and connect to the database schema that contains the procedure you are interested in executing. Execute the procedure by issuing the following command:
EXEC procedure_name([param1, param2,...]);
For instance, to execute the procedure that was created in Recipe 4-3, you would issue the following command:
EXEC increase_wage(198, .03, 5000);
This would invoke the INCREASE_WAGE procedure, passing three parameters: EMPLOYEE_ID, a percentage of increase, and an upper salary bound.
You can also execute a stored procedure by creating a simple anonymous block that contains the procedure call, as depicted in the following code:
Using this technique, invoking the stored procedure that was created in Recipe 4-3 would resemble the following:
BEGIN
increase_wage(198,.03,5000);
END;
Both techniques work equally well, but the latter would be better to use if you wanted to execute more than one procedure or follow up with more PL/SQL statements. If you are running a single procedure from SQL*Plus, then using EXEC is certainly a good choice.
How It Works
A stored procedure can be executed using the EXEC keyword. You can also type EXECUTE entirely. Both the long and shortened versions will work.
It is also possible to execute a procedure that is contained within other schemas, if the current user has execute privileges on that procedure. In such a scenario, use dot notation to qualify the procedure name. Here's an example:
EXEC different_schema.increase_wage(emp_rec.employee_id, pct_increase, upper_bound);
Note To learn more about privileges regarding stored programs, please take a look at Recipe 4-11.
A procedure can also be invoked from within another procedure by simply typing the name and placing the parameters inside parentheses, if there are any. For instance, the following lines of code demonstrate calling a procedure from within another procedure. The procedure in this example invokes the procedure that was shown in Recipe 4-3.
CREATE OR REPLACE PROCEDURE grant_raises (pct_increase IN NUMBER,
upper_bound IN NUMBER) as
CURSOR emp_cur is
SELECT employee_id, first_name, last_name
FROM employees;
BEGIN
-- loop through each record in the employees table
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
increase_wage(emp_rec.employee_id, pct_increase, upper_bound);
END LOOP;
END;
The procedure GRANT_RAISES applies an increase across the board to all employees. It loops through all employee records, and the INCREASE_WAGE procedure is called with each iteration. The procedure is called without the use of the EXEC keyword since it is being invoked by another procedure rather than directly from the SQL*Plus command line.