10-10. Deleting a Record from a Collection
Problem
You need to randomly select employees from a collection. Using a random generator may select the same employee more than once, so you need to remove the record from the collection before selecting the next employee.
Solution
Invoke the built-in DELETE method on the collection. For example, the following code creates a collection of employees and then randomly selects one from the collection. The selected employee is removed from the collection using the DELETE method. This process is repeated until three employees have been selected.
DECLARE
CURSOR driver IS
SELECT last_name
FROM employees;
TYPE rec_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER;
recs rec_type;
j INTEGER;
BEGIN
OPEN driver;
FETCH driver BULK COLLECT INTO recs;
CLOSE driver;
DBMS_RANDOM.INITIALIZE(TO_NUMBER (TO_CHAR (SYSDATE, 'SSSSS') ) );
FOR i IN 1..3 LOOP
-- Randomly select an employee
j := MOD (ABS (DBMS_RANDOM.RANDom), recs.COUNT) + 1;
DBMS_OUTPUT.PUT_LINE (recs(j).last_name);
-- Move all employees up one postion in the collection
FOR k IN j+1..recs.COUNT LOOP
recs(k-1) := recs(k);
END LOOP;
-- Remove the last element in the collection
-- so the random number generator has the correct count.
recs.DELETE(recs.COUNT);
END LOOP;
DBMS_RANDOM.TERMINATE;
END;
Executing this block of code produces the following results:
Olson
Chung
Seo
How It Works
The collection recs is populated with employee names via a BULK COLLECT. The FOR .. LOOP selects three employees at random by generating a random number between 1 and the number of records in the collection. Once an employee is selected, their name is removed from the collection, and the DELETE method is used to reduce the number of elements, which changes the value returned by the COUNT method for the next randomly generated number.
Note: The DELETE method applies only to collections that are indexed. You can invoke DELETE only if the collection's underlying TYPE definition contains the INDEX BY clause.