4-4. Creating a Stored Procedure
Problem
There is a database task that you are performing on a regular basis. Rather than executing a script that contains lines of PL/SQL code each time you execute the task, you want to store the code in the database so that you can simply execute the task by name or so that you can schedule it to execute routinely via Oracle Scheduler.
Note See Chapter 11 for information on scheduling PL/SQL jobs using Oracle Scheduler.
Solution
Place the code that is used to perform your task within a stored procedure. The following example creates a procedure named INCREASE_WAGE to update the employee table by giving a designated employee a pay increase. Of course, you will need to execute this procedure for each eligible employee in your department. Storing the code in a procedure makes the task easier to perform.
CREATE OR REPLACE PROCEDURE INCREASE_WAGE (empno_in IN NUMBER,
pct_increase IN NUMBER,
upper_bound IN NUMBER) AS
emp_count NUMBER := 0;
emp_sal employees.salary%TYPE;
Results VARCHAR2(50);
BEGIN
SELECT salary
INTO emp_sal
FROM employees
WHERE employee_id = empno_in;
IF emp_sal < upper_bound
AND round(emp_sal + (emp_sal * pct_increase), 2) < upper_bound THEN
UPDATE employees
SET salary = round(salary + (salary * pct_increase),2)
WHERE employee_id = empno_in;
results := 'SUCCESSFUL INCREASE';
ELSE
results := 'EMPLOYEE MAKES TOO MUCH, DECREASE RAISE PERCENTAGE';
END IF;
DBMS_OUTPUT.PUT_LINE(results);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'No employee match for the given ID'),
END;
The following are the results from executing the procedure for employee number 198. In the example, the employee is being given a 3 percent increase and an upper bound of $5,000.
BEGIN
increase_wage(198,.03,5000);
END;
SUCCESSFUL INCREASE
Statement processed.
How It Works
In the example, the procedure accepts three parameters: the employee number, the percent of increase they will receive, and an upper salary bound. You can then invoke the procedure by name, passing in the required parameters.
The procedure first searches the database for the provided employee number. If a record for that employee is found, then the employee record is queried for the current salary. If the salary is less than the upper bound and the resulting new salary will still be less than the upper bound, then the increase will be applied via an UPDATE statement. If the employee is not found, then an alert message will be displayed. As you can see, this procedure can be called for any individual employee, and it will increase their wage accordingly as long as the increase stays within the bound.
Stored procedures can be used to encapsulate functionality and store code in the database data dictionary. Much like a function, they can accept zero or more values as parameters, including collections. A stored procedure is structured in much the same way as a function in that it includes a header, an executable section, and an optional exception-handling block. However, a procedure cannot include a RETURN clause in the header, and it does not return a value.
For example, in the solution to this recipe, the procedure contains the following header:
CREATE OR REPLACE PROCEDURE INCREASE_WAGE (empno_in IN NUMBER,
pct_increase IN NUMBER,
upper_bound IN NUMBER) AS
The header uses the OR REPLACE clause to indicate that this procedure should replace any procedure with the same name that already exists. The procedure accepts three parameters, and although all of them are NUMBER type, any datatype can be accepted as a parameter. The declaration section comes after the header, and any cursors, variables, or exceptions that need to be declared should be taken care of in that section. Next, the actual work that the procedure will do takes place between the BEGIN and END keywords. Note that the header does not contain a RETURNS clause since procedures cannot return any values.
The advantage of using procedures is that code can be encapsulated into a callable named routine in the data dictionary and can be called by many users. To create a procedure in your schema, you must have the CREATE PROCEDURE system privilege. You can create a stored procedure in another schema if you have the CREATE ANY PROCEDURE system privilege.