Cursor Pay Raise Example
Separate Speaker Notes
There are four programs in the notes that are also covered in this presentation. The notes handout is titled: This is a series of examples dealing with cursors and tables.
The data is a subset of data taken from the employee file that comes as a demo with Oracle.
The purpose of this anonymous block is to create a table containing each unique manager_id.
This table will then be used in further examples.
In this program or block, I am setting up a table where I will store each unique manager_id.
I am using a cursor to process the multiple records.
Note the dbms has nothing to do with the logic - I put it and the FOR loop in there to show the information in the table after processing was complete.
Remember that open cursor executes the SELECT in the cursor and fills the cursor with the requested data.
The first record in the cursor is 7902. Since it is the first, the loop isn't entered and the flag remains FALSE which means that the number gets put in the table.
The second number in the select is also unique so it gets entered in the table.
Note that this time the while loop was entered and the comparison to what was already in the table (7902) did not result in equality so the record was written to the table.
The third record did not match either of the previous two so it too was written in the table.
The fourth record was a match to the third. This was found on the third pass through the WHILE loop and the flag was set to TRUE which meant that the record was not put in the table.
This also was not unique and did not get put in the table. Note that the match was found on the third pass through the WHILE loop.
This number was also not unique. It matched the second element in the table so only two passes through the WHILE loop were needed before the flag got set to TRUE and the loop was EXITed.
Note this processing will continue until all of the unique numbers have been put in the table.
Note that we looked at the logic that moved the first three numbers to the table.
This program modifies the previous example. I am still gathering the unique manager id numbers in the c_employee1 cursor. I am storing the data in the second cursor for update purposes. Notice the FOR UPDATE of salary clause.
Finally I am processing each record by checking to see if they are a manager or not. If they are a manager I will give them a 10% raise, otherwise I will give them a 2% raise.
This is a continuation of pay_raise_3.
This code deals with the second cursor which I am processing to give the people a salary increase. If the persons employee number matches a number in the table, then they are a manager and they get a 10% raise. Otherwise they get a 2% raise.
Notice the logic here. All the unique employee numbers of managers (manager ids) were stored. Now we are processing the records and checking to see if an employee is indeed a manager!
There are more efficient coding techniques that I could have used, but I was trying to handle each concept separately. The last example in this presentation will illustrate some efficiency concepts.
Notice that I am dealing with the flag and the counter in both loops with both cursors. I simply set them appropriately. This may be confusing and is not necessarily the best way to code. You might want to define separate flags and separate counters for each cursor and each loop for clarification.
See the notes for additional comments on FOR UPDATE OF in the cursor and WHERE CURRENT OF in the update.
This is the output with the increased salaries. Note I eliminated the headers for the second page/group on the slide so the data would fit!
Okay here we go...
This version of the program uses a cursor FOR loop which processes rows in an explicit cursor defined in the declare. Even though this is an explicit cursor, the cursor is automated within the FOR loop and the processing is handled implicitly. The FOR loop opens the cursor, fetches a row for each iteration of the loop, closes the cursor and terminates the automatic processing after the last row has been fetched and processed. When using the FOR, you must declare the cursor but the processing is automated.
The goals and processing are the same, but there have been changes to the logic because of the CURSOR FOR LOOP which requires no OPEN, CLOSE, or FETCH.
The syntax is the 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.
I have repeated some of the notes here, but there is more information in the notes that should be read.
You should also check an outside Oracle source for more information on this topic.
Note that again we are using a CURSOR FOR LOOP and again there is no EXIT within the while. The flag exit has been combined in the condition on the WHILE by using an AND.
The rest of the processing is the same.
Note that a second pay raise has been given to the employees since this program was run on the same table.
Again I eliminated the headers for the second page/group to fit on the slide.
This block of code uses only one cursor to store the information from the file. It still has the table to hold the unique manager id numbers.
The key to this working is the ORDER BY manager_id clause in the SELECT statement associated with the cursor.
The logic to make this work is in the IF statement in the FOR loop.
This slide shows the process used in putting the unique manager ids into the table. Notice that because the manager ids are being processed in sequence the first one in each group gets put in the table and I can compare to that one unitl there is a change. When the change happens, the next new manager id gets put in the table.
Once the table has been filled as illustrated on the previous two slides, the processing proceeds to check to see if the employee is a manager and decide on the salary increase and update the table.
Again note that this was run on the same file so the employees now have a third pay raise.
See the notes for additional explanations about the logic in this block.