There are four programs within this series that develop in sophistication. The first program does not actually grant a pay raise, but the others do. This program uses a subset of data taken from the employee file that comes as a demo with Oracle. I copied it over to SCOTT ownership using the technique explained in the copy file to another owner handout.
The purpose of this program is to create a table containing the each unique manager_id.
I will then use this in subsequent programs to calculate the salary.
H3>SQL CODE:
SQL> select * from employeez;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7369 SMITH JOHN 7902 800
7499 ALLEN KEVIN 7698 1600
7505 DOYLE JEAN 7839 2850
7506 DENNIS LYNN 7839 2750
7507 BAKER LESLIE 7839 2200
7521 WARD CYNTHIA 7698 1250
7555 PETERS DANIEL 7505 1250
7557 SHAW KAREN 7505 1250
7560 DUNCAN SARAH 7506 1250
7564 LANGE GREGORY 7506 1250
7566 JONES TERRY 7839 2975
7569 ALBERTS CHRIS 7839 3000
7600 PORTER RAYMOND 7505 1250
7609 LEWIS RICHARD 7507 1800
7654 MARTIN KENNETH 7698 1250
7676 SOMMERS DENISE 7507 1850
7698 BLAKE MARION 7839 2850
7782 CLARK CAROL 7839 2450
7788 SCOTT DONALD 7566 3000
7789 WEST LIVIA 7506 1500
7799 FISHER MATTHEW 7569 3000
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7820 ROSS PAUL 7505 1300
7839 KING FRANCIS 5000
7844 TURNER MARY 7698 1500
7876 ADAMS DIANE 7788 1100
7900 JAMES FRED 7698 950
7902 FORD JENNIFER 7566 3000
7916 ROBERTS GRACE 7569 2875
7919 DOUGLAS MICHAEL 7799 800
7934 MILLER BARBARA 7782 1300
7950 JENSEN ALICE 7505 750
7954 MURRAY JAMES 7506 750
32 rows selected.
SET SERVEROUTPUT ON
DECLARE
TYPE t_ManagerTable IS TABLE OF employeez.manager_id%TYPE
INDEX BY BINARY_INTEGER;
v_ManagerIdTable t_ManagerTable;
CURSOR c_Employee1 IS
SELECT manager_id FROM employeez;
v_ManagerId employeez.manager_id%TYPE;
v_FoundFlag BOOLEAN;
v_LoopCnt BINARY_INTEGER;
BEGIN
OPEN c_Employee1;
LOOP
FETCH c_Employee1 INTO v_ManagerId;
EXIT WHEN c_Employee1%NOTFOUND;
v_FoundFlag := FALSE;
v_LoopCnt := 1;
WHILE v_LoopCnt <= v_ManagerIdTable.COUNT LOOP
IF v_ManagerId = v_ManagerIdTable(v_LoopCnt) THEN
v_FoundFlag := TRUE;
EXIT;
END IF;
v_LoopCnt := v_LoopCnt + 1;
END LOOP;
IF v_FoundFlag = FALSE THEN
v_ManagerIdTable(v_ManagerIdTable.COUNT + 1) := v_ManagerId;
END IF;
END LOOP;
CLOSE c_Employee1;
FOR i IN 1..v_ManagerIdTable.COUNT LOOP
dbms_output.put_line(v_ManagerIdTable(i));
END LOOP;
END;
/
SET SERVEROUTPUT OFF
Because I am creating a table which will show no output, I used the dbms line to show the contents of the table after I had completed it. For this reason, I am using SET SERVEROUTPUT ON/OFF.
In the DECLARE, I first declare a table type that will hold the manager_id. Again remember that I want the results to be all manager ids with no duplicates. The type is t_managerTable and it is a table of employeez.manager_id%TYPE meaning the elements in the table will have the same type as the manager id on the employeez table. Then I assign the v_ManagerIdTable which is the name of this type that I am using in my code.
I then set up a cursor called c_employee1 which selects all of the manager ids from the employeez table.
Finally I set up variables to hold the manager_id, a boolean flag to be used to determine if the manager id is already in the table and a binary integer field that will be used in my processing.
In the begin, I first open the c_Employee1 cursor.
I then enter a outer loop, with an EXIT when there is nothing more to fetch from the c_Employee1 cursor. In the loop I will fetch an entry from the cursor into the field called v_ManagerId. I will also initialize the v_FoundFlag to False and the v_LoopCnt to 1.
I then enter an inner WHILE loop that will process while the v_LoopCtr that I initialized at 1 is less then or equal to the COUNT of the elements in the v_ManagerIDTable. Inside the loop, I am checking the v_managerId that I fetched against the things that are already in the v_ManagerIdTable one by one. If I find a match I am setting the v_FoundFlag to true so I will know the manager id is already in the table and exiting the loop. Notice that I can have an EXIT in a while loop to let me exit the loop before the condition in the while loop has een met. If I do not exit the table, after the IF, I increment the v_LoopCnt by 1. The inner loop has now ended.
In summary, the inner loop is checking the manager id from the fetch against each element in the manager table. When I have either found a match I will leave the loop because of the EXIT, if I have checked against all of the elements in the table and not found a match, I will leave the loop because of the WHILE.
At the end of the inner loop, if the v_FoundFlag is false (remember, that is what it was initialized to and if a match was found it was changed to true) then I will put the manager id into the table. This is done by assigning the manager id to the v_ManagerIdTable modified by the count of the elements in the table plus 1. If the v_FoundFlag is true, no processing will be done.
After the if, the END LOOP sends me back to the outer loop where I will fetch another manager id from the selection in the cursor.
When the outer loop is complete, there are no more manager ids in the cursor, the cursor is closed.
To show the results, I now have a loop that goes through the table and prints each
element. This is done with a FOR loop that moves from 1 to the count of the number of
elements in the table. The output that is produced from the dbms line is shown below.
SQL> @ pay_raise_1a 7902 7698 7839 7505 7506 7507 7566 7569 7788 7799 7782 PL/SQL procedure successfully completed.
This version of the program updates the file to give managers a 10% raise and non
managers a 2% raise. The found flag is used to make this determination.
DECLARE TYPE t_ManagerTable IS TABLE OF employeez.manager_id%TYPE INDEX BY BINARY_INTEGER; v_ManagerIdTable t_ManagerTable; CURSOR c_Employee1 IS SELECT manager_id FROM employeez; CURSOR c_Employee2 IS SELECT employee_id, salary FROM employeez FOR UPDATE OF salary; v_ManagerId employeez.manager_id%TYPE; v_EmployeeId employeez.employee_id%TYPE; v_Salary employeez.salary%TYPE; v_FoundFlag BOOLEAN; v_LoopCnt BINARY_INTEGER; v_TableIndx BINARY_INTEGER; BEGIN OPEN c_Employee1; LOOP FETCH c_Employee1 INTO v_ManagerId; EXIT WHEN c_Employee1%NOTFOUND; v_FoundFlag := FALSE; v_LoopCnt := 1; WHILE v_LoopCnt <= v_ManagerIdTable.COUNT LOOP IF v_ManagerId = v_ManagerIdTable(v_LoopCnt) THEN v_FoundFlag := TRUE; EXIT; END IF; v_LoopCnt := v_LoopCnt + 1; END LOOP; IF v_FoundFlag = FALSE THEN v_ManagerIdTable(v_ManagerIdTable.COUNT + 1) := v_ManagerId; END IF; END LOOP; CLOSE c_Employee1; OPEN c_Employee2; LOOP FETCH c_Employee2 INTO v_EmployeeId, v_Salary; EXIT WHEN c_Employee2%NOTFOUND; v_FoundFlag := FALSE; v_LoopCnt := 1; WHILE v_LoopCnt <= v_ManagerIdTable.COUNT LOOP IF v_EmployeeId = v_ManagerIdTable(v_LoopCnt) THEN v_FoundFlag := TRUE; EXIT; END IF; v_LoopCnt := v_LoopCnt + 1; END LOOP; IF v_FoundFlag = TRUE THEN v_Salary := 1.1 * v_Salary; ELSE v_Salary := 1.02 * v_Salary; END IF; UPDATE employeez SET salary = v_Salary WHERE CURRENT OF c_Employee2; END LOOP; CLOSE c_Employee2; COMMIT; END; /
In this example, I am accumulating the numbers of all of the managers in the manager table. Then I am processing all of the data that I gather with the second cursor against the manager table to determine if the person is a manager or not. Based on that determination, I will update the table giving the appropriate raise. In this example, I am also introducing a couple of new elements.
The first new element is the FOR UPDATE clause on the select within the second cursor. This clause is used to lock the current data being used so that no one else can access it while you are doing the update. When the transaction is complete, the exclusive use will be lifted. It is recommended that you lock rows before updates and deletes. There is also a NOWAIT clause that can be added to the FOR UPDATE that will return a message if the rows have been locked by other processing. IF THE NOWAIT is not used, Oracle will just keep waiting for access if the record is locked. The code is: FOR UPDATE NOWAIT entered in the SELECT. Note that the FOR UPDATE clause should be the last clause in the SELECT statement. The FOR UPDATE clause can also specify a column or columns as I have in this example in which case the column_reference is the column that the query is performed on.
The next new element is the WHERE CURRENT OF clause which is used in my UPDATE statement. The WHERE CURRENT clause is used when the FOR UPDATE is used in the cursor query to lock the rows. It references the current row in the explicit cursor that is referenced. This means that the update will be done on the current row from the cursor.
The logic of this second program encompasses the logic from the first program and adds the calculate and update logic. Again there are two cursors. The program loops through the first cursor data filling the table. It then checks the second cursor data against the elements in the table.
At the begin, I open the first cursor and then do the logic described in the first program to fill the table with unique manager numbers. When that loop is complete, I close the first cursor because I am finished with the information about manager_id from the table employeez.
I then open the second cursor which contains employee_id and salary from the same table, employeez. This cursor is contains the FOR UPDATE OF salary clause in the SELECT. In this loop I am performing until there are no more records in the cursor. Again I initialized the flag to false and the v_LoopCnt to 1.
I then enter the inner loop for this aspect of the processing which calls for processing until the v_LoopCnt is less than or equal to the count of the number of elements in the table. The IF test in this loop tests to see if the employee id matches one of the manager ids in the table. The loop continues until it has checked the employee id against all of the elements in the table or until a match is found. If a match is found, the flag is set to TRUE and the loop is exited. Note that each pass through the loop increments v_LoopCnt.
In summary. In the inner loop the employee id was checked against all manager ids in the table. If no match was found the flag stays at false, if a match was found the flag is set to true and the loop is terminated.
After the loop ends, the flag is checked. If the flag is true the salary is multiplied by 1.1 and if the flag is not true the salary is multiplied by 1.02. Then the table is updated setting the salary in the table to the newly calculated salary for the current record. At the end loop, the outer loop will be re-entered and the fetch will get a new employee id and salary and the processing will continue.
When the second cursor contains no more entries, the outer loop is terminated and the cursor is closed.
The output from the second program with the salary updates is shown below.
SQL> @ pay_raise_3
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM employeez;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7369 SMITH JOHN 7902 816
7499 ALLEN KEVIN 7698 1632
7505 DOYLE JEAN 7839 3135
7506 DENNIS LYNN 7839 3025
7507 BAKER LESLIE 7839 2420
7521 WARD CYNTHIA 7698 1275
7555 PETERS DANIEL 7505 1275
7557 SHAW KAREN 7505 1275
7560 DUNCAN SARAH 7506 1275
7564 LANGE GREGORY 7506 1275
7566 JONES TERRY 7839 3272.5
7569 ALBERTS CHRIS 7839 3300
7600 PORTER RAYMOND 7505 1275
7609 LEWIS RICHARD 7507 1836
7654 MARTIN KENNETH 7698 1275
7676 SOMMERS DENISE 7507 1887
7698 BLAKE MARION 7839 3135
7782 CLARK CAROL 7839 2695
7788 SCOTT DONALD 7566 3300
7789 WEST LIVIA 7506 1530
7799 FISHER MATTHEW 7569 3300
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7820 ROSS PAUL 7505 1326
7839 KING FRANCIS 5500
7844 TURNER MARY 7698 1530
7876 ADAMS DIANE 7788 1122
7900 JAMES FRED 7698 969
7902 FORD JENNIFER 7566 3300
7916 ROBERTS GRACE 7569 2932.5
7919 DOUGLAS MICHAEL 7799 816
7934 MILLER BARBARA 7782 1326
7950 JENSEN ALICE 7505 765
7954 MURRAY JAMES 7506 765
32 rows selected.
DECLARE
TYPE t_ManagerTable IS TABLE OF employeez.manager_id%TYPE
INDEX BY BINARY_INTEGER;
v_ManagerIdTable t_ManagerTable;
CURSOR c_Employee1 IS
SELECT manager_id FROM employeez;
CURSOR c_Employee2 IS
SELECT employee_id, salary FROM employeez
FOR UPDATE OF salary;
v_Salary employeez.salary%TYPE;
v_FoundFlag BOOLEAN;
v_LoopCnt BINARY_INTEGER;
BEGIN
-- Build a table of manager ID's
FOR v_EmployeeData1 IN c_Employee1 LOOP
v_FoundFlag := FALSE;
v_LoopCnt := 1;
WHILE (v_LoopCnt <= v_ManagerIdTable.COUNT) AND
(v_FoundFlag = FALSE) LOOP
IF v_EmployeeData1.manager_id = v_ManagerIdTable(v_LoopCnt) THEN
v_FoundFlag := TRUE;
END IF;
v_LoopCnt := v_LoopCnt + 1;
END LOOP;
IF v_FoundFlag = FALSE THEN
v_ManagerIdTable(v_ManagerIdTable.COUNT + 1) :=
v_EmployeeData1.manager_id;
END IF;
END LOOP;
-- Look in manager ID table to determine if employee is a manager
-- If employee is a manager increase pay by 10% else increase pay by 2%
FOR v_EmployeeData2 IN c_Employee2 LOOP
v_FoundFlag := FALSE;
v_LoopCnt := 1;
WHILE (v_LoopCnt <= v_ManagerIdTable.COUNT) AND
(v_FoundFlag = FALSE) LOOP
IF v_EmployeeData2.employee_id = v_ManagerIdTable(v_LoopCnt) THEN
v_FoundFlag := TRUE;
END IF;
v_LoopCnt := v_LoopCnt + 1;
END LOOP;
IF v_FoundFlag = TRUE THEN
v_Salary := 1.1 * v_EmployeeData2.salary;
ELSE
v_Salary := 1.02 * v_EmployeeData2.salary;
END IF;
UPDATE employeez
SET salary = v_Salary
WHERE CURRENT OF c_Employee2;
END LOOP;
COMMIT;
END;
/
The logic in this program has changed because the CURSOR FOR LOOP is used. However, the goals and the processing are the same. Notice that there are NO OPEN, CLOSE or FETCH statements in the code. The cursor FOR loop automates the handling of these commands. The syntax of the cursor FOR loop is the FOR followed by the record name (the name of the implicitly declared record) IN followed by the cursor name (the name of the declared cursor) and the word LOOP. This means that the statement of the record name is in essence its definition.
The first FOR loop has v_EmployeData1 as the implicitly defined record name and c_Employee1 (the first cursor) as the cursor name. The second FOR loop has v_EmployeeData2 as the implicitly defined record name and c_Employee2 (the second cursor) as the cursor name. Again, there are no open and closes statements and every iteration of the loop causes the equivalent of a fetch statement to happen.
Another change in this program is the WHILE does not have an EXIT in it. Instead the while has an AND condition that tests for everything checked AND the flag set to false. If inside the loop the flag gets reset to true, then the next time the WHILE is checked it will not meet both criteria and the loop will terminate. Obviously it will also terminate when all of the elements in the table have been compared to the id.
Notice also the IF statement which compares the implicitly defined record name.column name instead of simply the column name. This is required because the column name is within the record and will only be recognized in the context of the record.
The output below shows a second pay raise given to the employees based on the logic in pay_raise_4.
SQL> @ pay_raise_4
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM employeez;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7369 SMITH JOHN 7902 832.32
7499 ALLEN KEVIN 7698 1664.64
7505 DOYLE JEAN 7839 3448.5
7506 DENNIS LYNN 7839 3327.5
7507 BAKER LESLIE 7839 2662
7521 WARD CYNTHIA 7698 1300.5
7555 PETERS DANIEL 7505 1300.5
7557 SHAW KAREN 7505 1300.5
7560 DUNCAN SARAH 7506 1300.5
7564 LANGE GREGORY 7506 1300.5
7566 JONES TERRY 7839 3599.75
7569 ALBERTS CHRIS 7839 3630
7600 PORTER RAYMOND 7505 1300.5
7609 LEWIS RICHARD 7507 1872.72
7654 MARTIN KENNETH 7698 1300.5
7676 SOMMERS DENISE 7507 1924.74
7698 BLAKE MARION 7839 3448.5
7782 CLARK CAROL 7839 2964.5
7788 SCOTT DONALD 7566 3630
7789 WEST LIVIA 7506 1560.6
7799 FISHER MATTHEW 7569 3630
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7820 ROSS PAUL 7505 1352.52
7839 KING FRANCIS 6050
7844 TURNER MARY 7698 1560.6
7876 ADAMS DIANE 7788 1144.44
7900 JAMES FRED 7698 988.38
7902 FORD JENNIFER 7566 3630
7916 ROBERTS GRACE 7569 2991.15
7919 DOUGLAS MICHAEL 7799 832.32
7934 MILLER BARBARA 7782 1352.52
7950 JENSEN ALICE 7505 780.3
7954 MURRAY JAMES 7506 780.3
32 rows selected.
This example is really superior in efficiency, but it doesn't illustrate some of the points I wanted to go over as well, so I saved it for last. The manager table is defined first and will again hold unique manager_id numbers. Note that this program uses only one cursor to store the employee id, the salary and the manager id for that employee. The key to the efficiency is that the cursor is ordered by manager_id. This means that the managers will be placed in the table in order, so I can make assumptions about whether they are there or not.
The initial cursor FOR loop is building the table. If there is nothing in the table or if the manager id on the record that was just read by the FOR is not equal to the manager id in the table that COUNT is pointing to then the current manager id is put in the table with COUNT + 1.
The next loop is checking to see if the employee is a manager. This loop is basically the same as the loop in the previous version except that since everything is being done in one cursor, it refers to the same cursor. To review. The FOR sets the flag to false and the count to 1. Then the embedded/inner while loop is entered and it is performed until either the loop count becomes greater than the number of elements in the table or the flag gets set to false. Inside the loop the employee id on the current record is compared to an element in the table depending on the current value of loop count. If the employee number matches a value in the table then the flag is set to true. After the if the loop is incremented by 1. If when control returns to the WHILE the loop count is greater than the number of elements in the table indicating everything has been checked or the flag has been changed indicated a match was found, the loop will end.
The next paragraph checks the flag. If a match was found (flag is true) then the person gets a 10% raise. If a match was not found (flag is false) then the person gets a 2% raise. The if calculates the salary change. Then the code drops to the update which actually makes the salary change on the record. Again, the where current makes the update to the current record.
SQL> edit pay_raise_5
SET SERVEROUTPUT ON
DECLARE
TYPE t_ManagerTable IS TABLE OF employeez.manager_id%TYPE
INDEX BY BINARY_INTEGER;
v_ManagerIdTable t_ManagerTable;
CURSOR c_Employee1 IS
SELECT employee_id, salary, manager_id FROM employeez
ORDER BY manager_id
FOR UPDATE OF salary;
v_Salary employeez.salary%TYPE;
v_FoundFlag BOOLEAN;
v_LoopCnt BINARY_INTEGER;
BEGIN
-- Build a table of manager ID's
FOR v_EmployeeData1 IN c_Employee1 LOOP
IF v_ManagerIdTable.COUNT = 0
OR v_EmployeeData1.manager_id !=
v_ManagerIdTable(v_ManagerIdTable.COUNT) THEN
v_ManagerIdTable(v_ManagerIdTable.COUNT + 1) :=
v_EmployeeData1.manager_id;
END IF;
END LOOP;
-- Look in manager ID table to determine if employee is a manager
-- If employee is a manager increase pay by 10% else increase pay by 2%
FOR v_EmployeeData2 IN c_Employee1 LOOP
v_FoundFlag := FALSE;
v_LoopCnt := 1;
WHILE (v_LoopCnt <= v_ManagerIdTable.COUNT) AND
(v_FoundFlag = FALSE) LOOP
IF v_EmployeeData2.employee_id = v_ManagerIdTable(v_LoopCnt) THEN
v_FoundFlag := TRUE;
END IF;
v_LoopCnt := v_LoopCnt + 1;
END LOOP;
IF v_FoundFlag = TRUE THEN
v_Salary := 1.1 * v_EmployeeData2.salary;
ELSE
v_Salary := 1.02 * v_EmployeeData2.salary;
END IF;
UPDATE employeez
SET salary = v_Salary
WHERE CURRENT OF c_Employee1;
END LOOP;
COMMIT;
END;
/
SET SERVEROUTPUT OFF
SQL> @ pay_raise_5
PL/SQL procedure successfully completed.
Input truncated to 20 characters
SQL> SELECT * FROM employeez;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7369 SMITH JOHN 7902 848.97
7499 ALLEN KEVIN 7698 1697.93
7505 DOYLE JEAN 7839 3793.35
7506 DENNIS LYNN 7839 3660.25
7507 BAKER LESLIE 7839 2928.2
7521 WARD CYNTHIA 7698 1326.51
7555 PETERS DANIEL 7505 1326.51
7557 SHAW KAREN 7505 1326.51
7560 DUNCAN SARAH 7506 1326.51
7564 LANGE GREGORY 7506 1326.51
7566 JONES TERRY 7839 3959.73
7569 ALBERTS CHRIS 7839 3993
7600 PORTER RAYMOND 7505 1326.51
7609 LEWIS RICHARD 7507 1910.17
7654 MARTIN KENNETH 7698 1326.51
7676 SOMMERS DENISE 7507 1963.23
7698 BLAKE MARION 7839 3793.35
7782 CLARK CAROL 7839 3260.95
7788 SCOTT DONALD 7566 3993
7789 WEST LIVIA 7506 1591.81
7799 FISHER MATTHEW 7569 3993
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID SALARY
----------- --------------- --------------- ---------- ---------
7820 ROSS PAUL 7505 1379.57
7839 KING FRANCIS 6655
7844 TURNER MARY 7698 1591.81
7876 ADAMS DIANE 7788 1167.33
7900 JAMES FRED 7698 1008.15
7902 FORD JENNIFER 7566 3993
7916 ROBERTS GRACE 7569 3050.97
7919 DOUGLAS MICHAEL 7799 848.97
7934 MILLER BARBARA 7782 1379.57
7950 JENSEN ALICE 7505 795.91
7954 MURRAY JAMES 7506 795.91
32 rows selected.