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

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%  

OraclePLSQL--Explicit Cursor Attributes

Explicit Cursor Attributes

Explicit Cursor Attributes
Statement	State	%FOUND	%NOTFOUND	%ISOPEN	%ROWCOUNT
OPEN	Before	Exception	Exception	FALSE	Exception
	After	NULL	NULL	TRUE	0
1stFETCH	Before	NULL	NULL	TRUE	0
	After	TRUE	FALSE	TRUE	1
Next FETCH	Before	TRUE	FALSE	TRUE	1
	After	TRUE	FALSE	TRUE	n + 1
Last FETCH	Before	TRUE	FALSE	TRUE	n + 1
	After	FALSE	TRUE	TRUE	n + 1
CLOSE	Before	FALSE	TRUE	TRUE	n + 1
	After	Exception	Exception	FALSE	Exception

The %FOUND cursor attribute signals that rows are available to retrieve from the cursor and the %NOTFOUND attribute signals that all rows have been retrieved from the cursor. The %ISOPEN attribute lets you know that the cursor is already open, and is something you should consider running before attempting to open a cursor. Like implicit cursors, the %ROWCOUNT attribute tells you how many rows you’ve fetched at any given point. Only the %ISOPEN cursor attribute works anytime without an error. The other three raise errors when the cursor isn’t open.

Cursor Attributes Example

Notice how the cursor attributes are used in this example:

Ø  First, although it has no bearing on the use of the cursor attributes, we modify the cursor declaration so that employees are processed in descending order of salary.

Ø  We only open the cursor after testing the %ISOPEN attribute to confirm that the cursor is currently closed. In this context this is an unnecessary step. However, in large and complex PL/SQL applications in which many different concurrent programs access multiple cursors at the same time, such a step may be essential.

Ø  Within the loop which processes each cursor record, we utilize the running value found within %ROWCOUNT.

Ø  For reasons similar to what was just mentioned for opening the cursor, we test the %ISOPEN attribute again before deciding to close the cursor

DECLARE

CURSOR Employees IS

SELECT *

FROM employee

ORDER BY salary DESC;

EmpRecord         employee%ROWTYPE;

BEGIN

IF NOT (Employees%ISOPEN) THEN

OPEN Employees;

END IF;

LOOP

FETCH Employees INTO EmpRecord;

EXIT WHEN Employees%NOTFOUND;

dbms_output.put_line ('Employee number ' ||

Employees%ROWCOUNT || ' ' ||

EmpRecord.LName || ' ' || ' ' || EmpRecord.FName || ' ' ||

'earns ' || EmpRecord.Salary);

END LOOP;

IF Employees%ISOPEN THEN

CLOSE Employees;

END IF;

END;

anonymous block completed

Employee number 1 Bhutta  Ali earns 55000

Employee number 2 Hiraa  Hira earns 43000

Employee number 3 Saaad  Saad earns 40000

Employee number 4 Waseem  Akram earns 38000

Employee number 5 Waqar  Ahmed earns 30000

Employee number 6 Shah  Faz earns 25000

Employee number 7 Khan  Sana earns 25000

Employee number 8 Wardhaa  Alicia earns 25000