|
Chapter 6 Database Interaction and Cursors |
|
The following sections offer some practical applications of cursors. They are also designed to be programs you can put to use in your own environments with a few changes. The following files on the companion disk offer additional examples:
Explanation of how to emulate a cursor variable with local modules
Explanation of how to guarantee unique entry with cursors
Code required to guarantee unique entry with cursors
A hefty percentage of our code can be taken up with validating the entry or selection of foreign keys. Consider the example of an application which maintains companies and employees of that company. On the employee maintenance screen, I want to let my user enter the name or partial name of the company that employs a person. If the user has identified a unique company, the form displays that name and stores the company ID on the null canvas. If the user's entry finds more than one match, a message is displayed. If no matches are found, the entry is rejected.
How should I implement this requirement? Well, the first thing that comes to the minds of many programmers is the following:
Use a cursor which, with a single fetch, employs the COUNT built-in to compute the total number of companies that match the enemy.
This is, perhaps, the most obvious and direct solution to the requirement -- when it is phrased as follows:
To find out if the user's entry has more than one match, count up just how many matches there are.
There are, however, two serious problems with using the COUNT group function in my cursor:
The cursor does far too much work on my behalf. By using COUNT, the cursor must scan through all records (or, I hope, those identified by the index) and count up the total number of matching records. Yet, all I really need to know is whether more than one company matched the entry. The performance penalty on this COUNT could be severe if the query goes out over a network or if the user's entry matches many of the records. What if a user entered a percent sign ( %) ? All records would then match. An application should never punish a user for poorly thought-out data entry.
The cursor does not do enough for me. If the COUNT-based query did return a value of 1, I would still have to go back to the company table and SELECT the ID for that company with another cursor. As a result, I would have coded the same query twice. This redundancy introduces maintenance and performance issues.
You should use COUNT only when you need to know or display the total number of matches for the user's entry. In this scenario, I don't really need that total; I need only to know if the total is greater than one (i.e., if there is more than one match). I can obtain this knowledge in a much more efficient and straightforward manner.
Use a cursor that, with multiple fetches, determines if there are at least two companies that match the entry. This approach takes a bit more sophistication and thought, but is always a better performer and offers more flexibility to programmers.
To employ the multiple-fetch technique, take the following steps:
Declare a cursor which returns the company_id of all companies that match the value in the item:
CURSOR company_cur IS SELECT company_id FROM company WHERE company_name LIKE :company.company_name || '%';
Fetch twice against this cursor. If I can fetch twice successfully (company_cur%NOTFOUND is FALSE both times), that means that there is more than one match for the company name. If I can fetch only once before the %NOTFOUND cursor attribute returns FALSE, then I have found a unique match. If the very first fetch fails, then there is no match for the name.
Because my cursor returns the company_id, I do not have to perform another select once I have determined that I have a unique match. I simply use the ID that was provided in the first fetch.
The procedure in the following example supports the foreign key validation requirements with a double fetch against the cursor (it is coded for Oracle Forms, but can be adapted easily to other tool environments):
/* Filename on companion disk: fkval.fp */ PROCEDURE validate_company (comp_name_inout IN OUT company.company_name%TYPE, comp_id_out OUT company.company_id%TYPE) IS /* Cursor as explained above */ CURSOR company_cur IS SELECT company_id, company_name FROM company WHERE company_name LIKE comp_name_inout || '%'; /* Declare two records against the same cursor. */ company_rec company_cur%ROWTYPE; duplicate_rec company_cur%ROWTYPE; BEGIN /* Open and perform the first fetch against cursor. */ OPEN company_cur; FETCH company_cur INTO company_rec; IF company_cur%NOTFOUND THEN /* Not even one match for this name. Display message and reject. */ MESSAGE (' No company found with name like "' || comp_name_inout || '".'); CLOSE company_cur; RAISE FORM_TRIGGER_FAILURE; ELSE /* || Found one match. Now FETCH again, but this time FETCH into the || duplicate_rec record. This is just a "place holder". I don't || need to see the contents of the record. I just need to know if || I can successfully retrieve another record from the cursor. */ FETCH company_cur INTO duplicate_rec; IF company_cur%NOTFOUND THEN /* || Found 1 match, but not second. Unique! Assign values to || the OUT parameters and close the cursor. */ comp_id_out := company_rec.company_id; comp_name_inout := company_rec.company_name; CLOSE company_cur; ELSE /* || At least two matches found for this name. I don't know how || many more and I do not care. Reject with message. */ MESSAGE (' More than one company matches name like "' || comp_name_inout || '".'); CLOSE company_cur; RAISE FORM_TRIGGER_FAILURE; END IF; END IF; END;
Call this procedure in the When-Validate-Item trigger so that any changes to the company name can be validated. Here is an example of an actual call to validate_company:
validate_company (:employee.company_name, :employee.company_id);
Notice that the first parameter (the company name) is an IN OUT parameter. I want to let the user enter just a part of the name and let the application figure out if that entry is enough to uniquely identify a company. If a single match is found, the form replaces the partial entry with the full name.
I believe strongly that we should design our applications to allow the user to enter the minimal amount of information necessary to get the job done. Our applications should be smart enough to take advantage of the dumb, brute strength of our CPUs in order to lift some of the burden off the user.
As discussed earlier, a cursor with a SELECT...FOR UPDATE syntax issues a row-level lock on each row identified by the query. I encountered a very interesting application of this feature while helping a client resolve a problem.
The client offers a distribution package which tracks warehouse inventory. The work queue screen assigns warehouse floor packers their next tasks. The packer opens the screen and requests a task. The screen finds the next unassigned task and assigns it to the packer. A task might involve collecting various products together for shipment or returning products to the shelf. Completion of this task can take anywhere between one and ten minutes. When the task is completed, the packer will commit the changes or close the screen, performing an implicit commit.
For the amount of time it takes a packer to finish the task, that record must be tagged as "assigned" so that no other packer is given the same job to do. The first attempt at implementing this feature involved the use of a status flag. Whenever a packer was assigned a task, the flag on that task was set to ASSIGNED and the task record committed. The screen then excludes that task from the work queue. The problem with this approach is that the status had to be committed to the database so that other users could see the new status. This commit not only interrupted the actual transaction in the screen, but also created a number of headaches:
What if the user never completes the task and exits the screen? The form would have to detect this scenario (and there are generally many ways to cancel/exit) and update the status flag to AVAILABLE, which involves yet another commit.
Worse yet, what if the database goes down while the user is performing the task? That task will disappear from the work queue until manual intervention resets the status.
My client needed a mechanism by which the task could be flagged as UNAVAILABLE without having to perform commits, build complex checks into the form, and develop crash-recovery guidelines. They needed a program that would step through each of the open tasks in priority until it found a task that was unassigned. The SELECT...FOR UPDATE construct proved to be the perfect answer, in combination with two queries against the task table -- an explicit cursor and an implicit cursor using a FOR UPDATE clause.
The function in the following example returns the primary key of the next unassigned task using a cursor against the task table to look through all open tasks in priority order. The tasks returned by this first cursor include those which are assigned but "in process" (and should therefore not be assigned again). For each task retrieved from this cursor, the function then tries to obtain a lock on that record using the FOR UPDATE...NOWAIT clause. If the SELECT statement cannot obtain a lock, it means that task is being handled by another packer. So the function fetches the next task and tries, once again, to obtain a lock, continuing on in this fashion until a free task is found or the last task is fetched.
Notice that the next_task function does not perform any commits, so it doesn't have to do any kind of complicated clean-up. It simply requests the lock and returns the primary key for that task. The calling program can then offer this task to the packer who will issue the commit, freeing the lock, when she or he is done with the task:
/* Filename on companion disk: selupdt.sf */ FUNCTION next_task RETURN task.task_id%TYPE IS /* Cursor of all open tasks, assigned and unassigned */ CURSOR task_cur IS SELECT task_id FROM task WHERE task_status = 'OPEN' ORDER BY task_priority, date_entered DESC; /* The record for the above cursor */ task_rec task_cur%ROWTYPE; /* || An exception for error ORA-00054: || "resource busy and acquire with NOWAIT specified" */ record_locked EXCEPTION PRAGMA EXCEPTION_INIT (record_locked, -54); /* || Variables which determine whether function should continue || to loop through the cursor's records. */ found_unassigned_task BOOLEAN := FALSE; more_tasks BOOLEAN := TRUE; /* The primary key of the unassigned task to be returned */ return_value task.task_id%TYPE := NULL; BEGIN /* Open the cursor and start up the loop through its records */ OPEN task_cur; WHILE NOT found_unassigned_task AND more_tasks LOOP /* Fetch the next record. If nothing found, we are done */ FETCH task_cur INTO task_rec; more_tasks := task_cur%FOUND; IF more_tasks THEN /* || A record was fetched. Create an anonymous block within || the function so that I can trap the record_locked || exception and still stay inside the cursor loop. */ BEGIN /* Try to get a lock on the current task */ SELECT task_id INTO return_value FROM task WHERE task_id = task_rec.task_id FOR UPDATE OF task_id NOWAIT; /* || If I get to this line then I was able to get a lock || on this particular task. Notice that the SELECT INTO || has therefore already set the function's return value. || Now set the Boolean to stop the loop. */ found_unassigned_task := TRUE; EXCEPTION WHEN record_locked THEN /* Record was already locked, so just keep on going */ NULL; END; END IF; END LOOP; /* || Return the task id. Notice that if an unassigned task was NOT || found, I will simply return NULL per declaration default. */ CLOSE task_cur; RETURN return_value; EXCEPTION /* || General exception handler for the function: if an error occurred, || then close the cursor and return NULL for the task ID. */ WHEN OTHERS THEN CLOSE task_cur; RETURN NULL; END;
|
|
|
|
| 6.12 Cursor Variables |
|
7. Loops |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.