January 2nd, 2008, 10:40 AM
|
 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,485
| |
We can fetch from a cursor into one or more collections: -
DECLARE
-
TYPE NameList IS TABLE OF employees.last_name%TYPE;
-
TYPE SalList IS TABLE OF employees.salary%TYPE;
-
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
-
names NameList;
-
sals SalList;
-
TYPE RecList IS TABLE OF c1%ROWTYPE;
-
recs RecList;
-
PROCEDURE print_results IS
-
BEGIN
-
dbms_output.put_line('Results:');
-
IF names IS NULL OR names.COUNT = 0 THEN
-
RETURN; -- Don't print anything if collections are empty.
-
END IF;
-
FOR i IN names.FIRST .. names.LAST
-
LOOP
-
dbms_output.put_line(' Employee ' || names(i) || ': $' ||
-
sals(i));
-
END LOOP;
-
END;
-
BEGIN
-
dbms_output.put_line('--- Processing all results at once ---');
-
OPEN c1;
-
FETCH c1 BULK COLLECT INTO names, sals;
-
CLOSE c1;
-
print_results;
-
dbms_output.put_line('--- Processing 7 rows at a time ---');
-
OPEN c1;
-
LOOP
-
--LIMIT restricts the number of records to fetch.
-
FETCH c1 BULK COLLECT INTO names, sals LIMIT 7;
-
EXIT WHEN c1%NOTFOUND;
-
print_results;
-
END LOOP;
-
-- Loop exits when fewer than 7 rows are fetched. Have to
-
-- process the last few. Need extra checking inside PRINT_RESULTS
-
-- in case it is called when the collection is empty.
-
print_results;
-
CLOSE c1;
-
dbms_output.put_line('--- Fetching records rather than columns ---');
-
OPEN c1;
-
FETCH c1 BULK COLLECT INTO recs;
-
FOR i IN recs.FIRST .. recs.LAST
-
LOOP
-
-- Now all the columns from the result set come from a single record.
-
dbms_output.put_line(' Employee ' || recs(i).last_name || ': $'
-
|| recs(i).salary);
-
END LOOP;
-
END;
|
