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:
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:
This is, perhaps, the most obvious and direct solution to the requirement -- when it is phrased as follows:
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:
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:
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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.