471,306 Members | 917 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,306 developers and data experts.

Bulk-Fetching from a Cursor

debasisdas
8,127 Expert 4TB
We can fetch from a cursor into one or more collections:

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE NameList IS TABLE OF employees.last_name%TYPE;
  3. TYPE SalList IS TABLE OF employees.salary%TYPE;
  4. CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
  5. names NameList;
  6. sals SalList;
  7. TYPE RecList IS TABLE OF c1%ROWTYPE;
  8. recs RecList;
  9. PROCEDURE print_results IS
  10. BEGIN
  11. dbms_output.put_line('Results:');
  12. IF names IS NULL OR names.COUNT = 0 THEN
  13. RETURN; -- Don't print anything if collections are empty.
  14. END IF;
  15. FOR i IN names.FIRST .. names.LAST
  16. LOOP
  17. dbms_output.put_line(' Employee ' || names(i) || ': $' ||
  18. sals(i));
  19. END LOOP;
  20. END;
  21. BEGIN
  22. dbms_output.put_line('--- Processing all results at once ---');
  23. OPEN c1;
  24. FETCH c1 BULK COLLECT INTO names, sals;
  25. CLOSE c1;
  26. print_results;
  27. dbms_output.put_line('--- Processing 7 rows at a time ---');
  28. OPEN c1;
  29. LOOP
  30. --LIMIT restricts the number of records to fetch.
  31. FETCH c1 BULK COLLECT INTO names, sals LIMIT 7;
  32. EXIT WHEN c1%NOTFOUND;
  33. print_results;
  34. END LOOP;
  35. -- Loop exits when fewer than 7 rows are fetched. Have to
  36. -- process the last few. Need extra checking inside PRINT_RESULTS
  37. -- in case it is called when the collection is empty.
  38. print_results;
  39. CLOSE c1;
  40. dbms_output.put_line('--- Fetching records rather than columns ---');
  41. OPEN c1;
  42. FETCH c1 BULK COLLECT INTO recs;
  43. FOR i IN recs.FIRST .. recs.LAST
  44. LOOP
  45. -- Now all the columns from the result set come from a single record.
  46. dbms_output.put_line(' Employee ' || recs(i).last_name || ': $'
  47. || recs(i).salary);
  48. END LOOP;
  49. END;
Jan 2 '08 #1
0 5019

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Paul Rowe | last post: by
2 posts views Thread by Chris | last post: by
2 posts views Thread by php newbie | last post: by
5 posts views Thread by me | last post: by
6 posts views Thread by pk | last post: by
11 posts views Thread by Ted | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.