OraclePLSQL--Creating Procedures RAISE_SALARY()

Creating Procedures  RAISE_SALARY()

This procedure implements the business rules for an application whenever an employee salary is to be raised.

·         We want to allow employee salaries to be raised only within the confines of a secure procedure. In this way employee raises will confirm strictly to the business rules enforced by the procedure. Towards that end, raises above 50% will be disallowed as being to excessive. Also, in the event that a raise percentage is not supplied to the procedure, we will assume a 5% raise by default.

·         Complete exception handling will be included in the program to guarantee orderly procedure termination regardless of the circumstances which might arise during processing

CREATE OR REPLACE PROCEDURE raise_salary

     (

     employee_ssn     IN CHAR,

     employee_pct     IN NUMBER     DEFAULT 5,

     result_message OUT CHAR

     )

AS

     old_salary          employee.salary%TYPE;

     increase_amount     NUMBER;

/*

Program-defined exceptions are declared here and are used to identify

exception events which will interrupt main program execution.

*/

     pct_too_high      EXCEPTION;

     update_error          EXCEPTION;

BEGIN

/*

Disallow raises which exceed 50% on the basis of the business rules.

*/

    IF employee_pct > 50 THEN

      RAISE pct_too_high;

    END IF;

    SELECT salary

    INTO old_salary

    FROM employee

    WHERE ssn = employee_ssn;

/*

If the existing salary is unknown or NULL, or if it is 0, then no raise is

possible. Otherwise, compute the raise amount and issue an update to the

database.

*/

    IF old_salary IS NOT NULL

    AND old_salary > 0 THEN

/*

Convert the employee_pct parameter value to a numeric percentage and update

the appropriate database column.

*/

      increase_amount := employee_pct / 100;

      UPDATE employee

      SET salary = salary + (salary * increase_amount)

      WHERE ssn = employee_ssn;

     

      IF SQL%ROWCOUNT <> 1 THEN

         RAISE update_error;

      END IF;

/*

Set the output parameter value if necessary.

*/

     ELSE

          result_message := 'Current salary is either NULL or 0';

     END IF;

EXCEPTION

/*

Set the output parameter value here as well based upon program-defined and

system-defined exceptions which might occur.

*/

   WHEN pct_too_high THEN

      result_message := 'Raise percentage may not exceed 50%';

     WHEN NO_DATA_FOUND THEN

          result_message := 'Employee ' || employee_ssn

               || ' not found';

     WHEN update_error THEN

          result_message := 'Database error';

          ROLLBACK;

     WHEN OTHERS THEN

          result_message := 'Unknown error';

END raise_salary;

/

----------------------------------

PROCEDURE RAISE_SALARY compiled

=========================================

DESCRIBE raise_salary

Argument Name  Type   In/Out Default

-------------- ------ ------ -------

EMPLOYEE_SSN   CHAR   IN     unknown

EMPLOYEE_PCT   NUMBER IN     unknown

RESULT_MESSAGE CHAR   OUT    unknown

=========================================

EXECUTE raise_salary ('123456789', 51, :output_text);

anonymous block completed

============================================

PRINT output_text

OUTPUT_TEXT

------------------------------------

Raise percentage may not exceed 50%