10-4. Creating and Accessing Record Collections
Problem
You need to load records from a database table or view into a simple data structure that would benefit from use in a loop body or to pass as a parameter to another function or procedure. You want to act upon sets of records as a single unit.
Solution
Use a TYPE
to define a TABLE
based on the database table structure. The following example declares a cursor and then uses it to declare the table of records. The result is a variable named recs
that holds the data fetched by the cursor.
DECLARE
CURSOR driver IS
SELECT *
FROM employees;
TYPE emp_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER;
recs emp_type;
total number := 0.0;
BEGIN
OPEN DRIVER; FETCH DRIVER ...
Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.