OraclePLSQL---Function SALARY_VALID() Scenario

Function  SALARY_VALID() Scenario

Suppose that our application requires a function which validates whether a proposed new salary conforms to the applicable business rules. The business rules might be as follows:

Ø  The maximum salary allowed is generally $65,000, although certain circumstances are exempted.

Ø  One exemption occurs if the employee is a department manager. If so, the salary limit is increased by $15,00.

Ø  Another exemption is granted based upon the number of projects the employee works on. Their salary limit may be increased by $2,500 per project.

Ø  A final special circumstance is based upon the number of dependents the employee has. Their salary limit may be increased by $45,00 per dependent.

CREATE OR REPLACE FUNCTION salary_persuasive_conclusion

 (

 INPUT_SSN IN CHAR,

 INPUT_INCOME IN NUMBER

 )

RETURN VARCHAR2

IS

     dept_manager NUMBER;

     employee_projects NUMBER;

     employee_dependents NUMBER;

     income_limit NUMBER;

BEGIN

/*

The computed salary limit begins at $65,000 but other exceptions need to be identified.

*/

     income_limit := 65000;

/*

If the database indicates that the employee is a department manager then their salary limit is increased by $1500.

*/

     SELECT count(*)

     INTO dept_manager

     FROM department

     WHERE department.mgrssn = input_ssn;

     IF dept_manager > 0 THEN

          income_limit := income_limit + 1500;

     END IF;

/*

If the database indicates the employee has worked on projects then their salary limit increases by $2500 per project.

*/

     SELECT count(*)

     INTO employee_projects

     FROM works_on

     WHERE works_on.essn = input_ssn;

     income_limit := income_limit + (employee_projects * 2500);

/*

Finally if the employee has dependents then their salary limit increases by $4500 per dependent.

*/

     SELECT count(*)

     INTO employee_dependents

     FROM dependent

     WHERE dependent.essn = input_ssn;

     income_limit := income_limit + (employee_dependents * 4500);

/*

The conclusion of the program is straightforward. If the proposed salary is judged as appropriate for this particular employee, then the function result indicates such and the program terminates when the result is returned to the calling program by means of the RETURN() statement. If the opposite is true, an appropriate termination also occurs.

*/

     IF INPUT_INCOME > income_limit THEN

        RETURN ('FALSE');

     ELSE

        RETURN ('TRUE');

     END IF;

 

END salary_persuasive_conclusion;

/

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

FUNCTION SALARY_PERSUASIVE_CONCLUSION compiled

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

DESCRIBE salary_persuasive_conclusion

Argument Name  Type     In/Out Default

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

<return value> VARCHAR2 OUT    unknown

INPUT_SSN      CHAR     IN     unknown

INPUT_INCOME   NUMBER   IN     unknown

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

SELECT LName,FName,Sex ,

ROUND(Salary * 1.65) AS "Proposed Salary",

salary_persuasive_conclusion (ssn, Salary * 1.65) AS "Valid"

FROM employee;

LNAME	FNAME	SEX	Proposed Salary	Valid
Bhutta	Ali	M	81675	FALSE
Khan	Sana	F	37125	TRUE
Shah	Faz	M	37125	TRUE
Waseem	Akram	M	56430	TRUE
Waqar	Ahmed	M	87012	FALSE
Hiraa	Hira	F	63855	TRUE
Saaad	Saad	M	59400	TRUE
Wardhaa	Alicia	F	37125	TRUE