Creating a Stored Procedure That Uses Parameters

Creating a Stored Procedure That Uses Parameters

When you create a procedure or function, you can specify parameters that are passed to the procedure or function when it is called (or invoked). In Example 5-2, note the use of the IN option with procedure arguments emp_id and bonus_rate. For a discussion of IN and IN OUT argument options in PL/SQL subprograms, see "Using Local PL/SQL Procedures and Functions in PL/SQL Blocks".

Example 5-2 Creating a Stored Procedure That Uses Parameters

-- including OR REPLACE is more convenient when updating a subprogram
-- IN is the default for parameter declarations so it could be omitted
CREATE OR REPLACE PROCEDURE award_bonus (emp_id IN NUMBER, bonus_rate IN NUMBER)
  AS
-- declare variables to hold values from table columns, use %TYPE attribute
   emp_comm        employees.commission_pct%TYPE;
   emp_sal         employees.salary%TYPE;
-- declare an exception to catch when the salary is NULL
   salary_missing  EXCEPTION;
BEGIN  -- executable part starts here
-- select the column values into the local variables
   SELECT salary, commission_pct INTO emp_sal, emp_comm FROM employees
    WHERE employee_id = emp_id;
-- check whether the salary for the employee is null, if so, raise an exception
   IF emp_sal IS NULL THEN
     RAISE salary_missing;
   ELSE 
     IF emp_comm IS NULL THEN
-- if this is not a commissioned employee, increase the salary by the bonus rate
-- for this example, do not make the actual update to the salary
-- UPDATE employees SET salary = salary + salary * bonus_rate 
--   WHERE employee_id = emp_id;
       DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ' receives a bonus: ' 
                            || TO_CHAR(emp_sal * bonus_rate) );
     ELSE
       DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id 
                            || ' receives a commission. No bonus allowed.');
     END IF;
   END IF;
EXCEPTION  -- exception-handling part starts here
   WHEN salary_missing THEN
      DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || 
                           ' does not have a value for salary. No update.');
   WHEN OTHERS THEN
      NULL; -- for other exceptions do nothing
END award_bonus;
/

-- the following BEGIN..END block calls, or executes, the award_bonus procedure 
-- using employee IDs 123 and 179 with the bonus rate 0.05 (5%)
BEGIN
  award_bonus(123, 0.05);
  award_bonus(179, 0.05);
END;
/

The output of the calls is similar to:
Employee 123 received a bonus: 325
Employee 179 receives a commission. No bonus allowed.
When executed, this procedure processes an employee ID and a bonus rate. It uses the Id to select the salary and commission percentage of the employee from the employees table. If the salary is null, an exception is raised. If the employee does not receive a commission, the employee's salary is updated by the bonus rate; otherwise no update is made. For a discussion of exception handling, see "Handling PL/SQL Errors".