Hey everyone. In this post we're diving into something really powerful – Collections of Records. You know how sometimes you need to work with multiple related pieces of data at once? That's exactly what we're going to learn today.
Understanding Collections of Records
Think of a collection of records like a filing cabinet. Each drawer can hold multiple folders, and each folder contains organized information. In PL/SQL, we have three types of these “filing cabinets”, and I'm going to show you how each one works.
1. Associative Arrays – The Flexible Filing Cabinet
Let's start with associative arrays. These are like having a filing cabinet where you can add as many folders as you want, and you get to choose how to label them. Here's a practical example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE -- First, let's create our "folder" structure TYPE employee_rec_type IS RECORD ( employee_id NUMBER, full_name VARCHAR2(100), salary NUMBER ); -- Now create our "filing cabinet" TYPE employee_aa_type IS TABLE OF employee_rec_type INDEX BY PLS_INTEGER; -- This is our actual cabinet employee_aa employee_aa_type; |
“Okay folks, look at what we're doing here. We're building something really cool in three steps – let me break it down for you:
First, in this line here:
1 2 3 4 5 |
TYPE employee_rec_type IS RECORD ( employee_id NUMBER, full_name VARCHAR2(100), salary NUMBER ); |
We're creating a template for storing information about one employee. Think of it like an employee form with three fields: their ID, their full name, and their salary. This is our basic record structure.
Now, for the second part:
1 2 |
TYPE employee_aa_type IS TABLE OF employee_rec_type INDEX BY PLS_INTEGER; |
This is where it gets interesting. We're saying ‘Hey, we want to store many of these employee records together.' It's like creating a filing system where each employee record has a number. That INDEX BY PLS_INTEGER part? That's just saying we'll use numbers to find each employee – like giving each folder a number in a filing cabinet.
And finally:
1 |
employee_aa employee_aa_type; |
This is where we actually create our filing cabinet. It's empty right now, but we can start putting employee records into it.
So basically, we've created a way to store multiple employee records and find them using numbers. It's like having a bunch of employee forms organized in a numbered filing system. Pretty neat, right? In just these few lines, we've set up a structure that can hold as many employees as we need!”
Let me show you how we can use this to store employee information:
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Let's get some employee data CURSOR emp_cursor IS SELECT employee_id, first_name || ' ' || last_name AS full_name, salary FROM employees WHERE ROWNUM <= 5; BEGIN -- Store each employee record FOR emp_rec IN emp_cursor LOOP employee_aa(emp_rec.employee_id) := emp_rec; END LOOP; |
See what we did there? We're using the employee ID as our “label” for each folder.
Let me show you what's happening here – it's actually pretty cool!
First, look at this part:
1 2 3 4 5 6 |
CURSOR emp_cursor IS SELECT employee_id, first_name || ' ' || last_name AS full_name, salary FROM employees WHERE ROWNUM <= 5; |
What we're doing here is creating a cursor – think of it like a shopping list of employees we want to grab from our database. We're saying ‘Hey database, give me the first 5 employees, and for each one, I want their ID, their full name (which we're creating by combining first and last name), and their salary.' The ROWNUM <= 5 part is just limiting it to 5 employees so we don't grab the whole database!
Now, here's where the magic happens:
1 2 3 4 |
BEGIN FOR emp_rec IN emp_cursor LOOP employee_aa(emp_rec.employee_id) := emp_rec; END LOOP; |
This is like going through our shopping list and putting each item where it belongs. For each employee we found (emp_rec), we're storing them in our filing cabinet (remember employee_aa from before?) using their employee ID as the folder number.
It's like saying: ‘Take each employee from our list, and put their information in our filing cabinet using their employee ID as the label.' So if we have an employee with ID 100, their information goes into folder 100 in our cabinet.
Now let's see how to read this information:
1 2 3 4 5 6 7 8 9 10 |
-- Let's look at what we stored FOR i IN employee_aa.FIRST .. employee_aa.LAST LOOP IF employee_aa.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE( 'Employee ID: ' || employee_aa(i).employee_id || ', Name: ' || employee_aa(i).full_name || ', Salary: ' || employee_aa(i).salary ); END IF; END LOOP; |
Okay, now this is where we actually look at what we stored in our filing cabinet. Let me break this down in a way that makes sense:
1 |
FOR i IN employee_aa.FIRST .. employee_aa.LAST LOOP |
Think of this like saying ‘Let's start at the first folder in our cabinet and go all the way to the last one.' FIRST gives us the lowest employee ID we stored, and LAST gives us the highest.
1 |
IF employee_aa.EXISTS(i) THEN |
This line is really important – it's like checking ‘Hey, is there actually an employee folder here?' Because, you know, we might have employee 101 and 103, but no 102. We don't want to try looking at an empty folder!
1 2 3 4 5 |
DBMS_OUTPUT.PUT_LINE( 'Employee ID: ' || employee_aa(i).employee_id || ', Name: ' || employee_aa(i).full_name || ', Salary: ' || employee_aa(i).salary ); |
And here's where we actually look at what's in each folder and print it out. For each employee we found, we're displaying their ID, name, and salary in a nice formatted way.
Let's complete the code so that we can run it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
DECLARE -- Record type for employee information TYPE employee_rec_type IS RECORD ( employee_id NUMBER, full_name VARCHAR2(100), salary NUMBER ); -- Associative array type to store multiple employees TYPE employee_aa_type IS TABLE OF employee_rec_type INDEX BY PLS_INTEGER; -- Declare the associative array variable employee_aa employee_aa_type; -- Cursor to get first 5 employees CURSOR emp_cursor IS SELECT employee_id, first_name || ' ' || last_name AS full_name, salary FROM employees WHERE ROWNUM <= 5; -- Variable for counting records v_count NUMBER := 0; BEGIN -- Store employee records in the associative array FOR emp_rec IN emp_cursor LOOP employee_aa(emp_rec.employee_id) := emp_rec; v_count := v_count + 1; END LOOP; -- Display header DBMS_OUTPUT.PUT_LINE('Employee List Report'); DBMS_OUTPUT.PUT_LINE('-------------------'); -- Check if we have any records IF employee_aa.COUNT > 0 THEN -- Display all stored employees FOR i IN employee_aa.FIRST .. employee_aa.LAST LOOP IF employee_aa.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE( 'Employee ID: ' || employee_aa(i).employee_id || ', Name: ' || employee_aa(i).full_name || ', Salary: $' || TO_CHAR(employee_aa(i).salary, '99,999.00') ); END IF; END LOOP; -- Display total count DBMS_OUTPUT.PUT_LINE('-------------------'); DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count); ELSE DBMS_OUTPUT.PUT_LINE('No employees found.'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found in employees table.'); WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Value too large for defined data type.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; / |
So when you run this, you'll see something like:
1 2 |
Employee ID: 100, Name: Steven King, Salary: 24000 Employee ID: 101, Name: Neena Kochhar, Salary: 17000 |
It's like opening each folder in our filing cabinet, one by one, and reading out what's inside. And that EXISTS check makes sure we don't try to read from empty folders. Pretty clever, right?
2. Nested Tables – The Storable Filing Cabinet
Now, let's look at nested tables. These are special because you can actually store them in the database. Let me show you:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE -- Create our department record structure TYPE department_rec_type IS RECORD ( department_id NUMBER, department_name VARCHAR2(30), manager_id NUMBER ); -- Create our nested table type TYPE department_nt_type IS TABLE OF department_rec_type; -- Initialize our nested table department_nt department_nt_type := department_nt_type(); |
Alright, let's look at what we're doing here – it's similar to our employee example but with a cool twist.
1 2 3 4 5 |
TYPE department_rec_type IS RECORD ( department_id NUMBER, department_name VARCHAR2(30), manager_id NUMBER ); |
First, we're creating our ‘department form' – just like before, but this time for departments instead of employees. Each department has three pieces of info: its ID, name, and the ID of the manager in charge.
1 |
TYPE department_nt_type IS TABLE OF department_rec_type; |
Here's where it gets interesting – we're creating what's called a nested table. Think of it like a dynamic list that can grow or shrink. Unlike our previous example with employees (where we used an indexed table), this is more like a stack of department forms that we can add to or remove from easily.
1 |
department_nt department_nt_type := department_nt_type(); |
And finally, we're creating our actual list and initializing it. That := department_nt_type() part is super important – it's like saying ‘Hey, give me an empty list to start with.' If we forget this part, it's like trying to use a list that doesn't exist yet!
The cool thing about nested tables (compared to what we did with employees) is that we can actually store these in the database if we want to. It's like having a stack of department forms that we can either keep in memory or file away in the database.
Now, watch how we add departments to this collection:
1 2 3 4 5 6 7 8 |
BEGIN -- Get some department data FOR dept_rec IN (SELECT department_id, department_name, manager_id FROM departments WHERE ROWNUM <= 5) LOOP department_nt.EXTEND; -- Make room for new record department_nt(department_nt.LAST) := dept_rec; END LOOP; |
Okay, let's look at what's happening here – this is where we actually start filling up our department list:
1 2 3 |
FOR dept_rec IN (SELECT department_id, department_name, manager_id FROM departments WHERE ROWNUM <= 5) LOOP |
This part is grabbing the first 5 departments from our database. It's like saying ‘Give me the first 5 department cards from our filing cabinet.' We're getting their ID, name, and manager ID all at once.
1 |
department_nt.EXTEND; -- Make room for new record |
This line is super important! Before we can add a new department, we need to make space for it. It's like adding a new slot to our list. Without this, we'd be trying to put something in a space that doesn't exist yet.
1 |
department_nt(department_nt.LAST) := dept_rec; |
And here we're actually storing the department info. LAST means ‘put it at the end of our list.' It's like adding each new department card to the end of our stack.
So for each department we find, we:
- Make space for it (EXTEND)
- Put it at the end of our list (LAST)
It's different from our employee example because here we're just adding departments one after another at the end of the list, instead of putting them in specific numbered slots.
Makes sense? Think of it like adding cards to a deck – you're just putting each new card on top of the stack!”
3. Varrays – The Fixed-Size Filing Cabinet
Finally, let's look at varrays. Think of these like a filing cabinet with a fixed number of drawers:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE -- Create our job record structure TYPE job_rec_type IS RECORD ( job_id VARCHAR2(10), job_title VARCHAR2(35), min_salary NUMBER ); -- Create our varray type - notice the '10' - that's our maximum size TYPE job_va_type IS VARRAY(10) OF job_rec_type; -- Initialize our varray job_va job_va_type := job_va_type(); |
Alright, now we're looking at something a bit different – let me show you what's happening here:
1 2 3 4 5 |
TYPE job_rec_type IS RECORD ( job_id VARCHAR2(10), job_title VARCHAR2(35), min_salary NUMBER ); |
First, we're creating our job ‘form' – just like we did with employees and departments. Each job has three pieces of information: an ID, a title, and the minimum salary for that position.
1 |
TYPE job_va_type IS VARRAY(10) OF job_rec_type; |
Now here's where it gets interesting – see that VARRAY(10)? This is different from our previous examples. We're saying ‘I want a list of jobs, but I know for sure I'll never need more than 10 spots.' It's like having a box with exactly 10 slots – no more, no less. This is perfect when you know the maximum size you'll ever need.
1 |
job_va job_va_type := job_va_type(); |
And finally, we're creating our actual job list and initializing it. Just like with nested tables, we need to initialize it, or it's like trying to use a box that doesn't exist yet!
The big difference here compared to our other examples is:
- Unlike the employee example (associative array), we can't choose our own index numbers
- Unlike the department example (nested table), we can't keep adding items forever
- We're limited to exactly 10 items – it's like having a fixed-size box
This is perfect for situations where you know exactly how many items you might need. Think of it like a parking lot with 10 spaces – you know exactly how many cars can fit!
Putting It All Together – A Complete Example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
DECLARE -- First, let's create a complete employee management system -- Employee basic record TYPE employee_rec_type IS RECORD ( employee_id NUMBER, first_name VARCHAR2(50), last_name VARCHAR2(50), hire_date DATE ); -- Phone record TYPE phone_rec_type IS RECORD ( phone_type VARCHAR2(10), phone_number VARCHAR2(20) ); -- Address record TYPE address_rec_type IS RECORD ( street VARCHAR2(100), city VARCHAR2(50), state VARCHAR2(25), postal_code VARCHAR2(10) ); -- Collection for phone numbers TYPE phone_list_type IS TABLE OF phone_rec_type; -- Complete employee record with nested elements TYPE complete_emp_rec_type IS RECORD ( basic_info employee_rec_type, address address_rec_type, phone_numbers phone_list_type ); -- Create a collection of complete employee records TYPE employee_list_type IS TABLE OF complete_emp_rec_type INDEX BY PLS_INTEGER; -- Declare our main collection employees_list employee_list_type; -- Temporary variables v_emp_id NUMBER := 1; BEGIN -- Let's add a couple of employees -- First Employee employees_list(v_emp_id).basic_info.employee_id := v_emp_id; employees_list(v_emp_id).basic_info.first_name := 'John'; employees_list(v_emp_id).basic_info.last_name := 'Smith'; employees_list(v_emp_id).basic_info.hire_date := SYSDATE; -- Address employees_list(v_emp_id).address.street := '123 Main St'; employees_list(v_emp_id).address.city := 'Boston'; employees_list(v_emp_id).address.state := 'MA'; employees_list(v_emp_id).address.postal_code := '02108'; -- Initialize phone numbers collection employees_list(v_emp_id).phone_numbers := phone_list_type(); -- Add phone numbers employees_list(v_emp_id).phone_numbers.EXTEND(2); employees_list(v_emp_id).phone_numbers(1).phone_type := 'HOME'; employees_list(v_emp_id).phone_numbers(1).phone_number := '555-0123'; employees_list(v_emp_id).phone_numbers(2).phone_type := 'MOBILE'; employees_list(v_emp_id).phone_numbers(2).phone_number := '555-4567'; -- Second Employee v_emp_id := 2; employees_list(v_emp_id).basic_info.employee_id := v_emp_id; employees_list(v_emp_id).basic_info.first_name := 'Jane'; employees_list(v_emp_id).basic_info.last_name := 'Doe'; employees_list(v_emp_id).basic_info.hire_date := SYSDATE-30; employees_list(v_emp_id).address.street := '456 Park Ave'; employees_list(v_emp_id).address.city := 'New York'; employees_list(v_emp_id).address.state := 'NY'; employees_list(v_emp_id).address.postal_code := '10022'; employees_list(v_emp_id).phone_numbers := phone_list_type(); employees_list(v_emp_id).phone_numbers.EXTEND(1); employees_list(v_emp_id).phone_numbers(1).phone_type := 'MOBILE'; employees_list(v_emp_id).phone_numbers(1).phone_number := '555-8901'; -- Now let's display all our employee information FOR emp_id IN employees_list.FIRST .. employees_list.LAST LOOP -- Basic Info DBMS_OUTPUT.PUT_LINE('=== Employee Information ==='); DBMS_OUTPUT.PUT_LINE('ID: ' || employees_list(emp_id).basic_info.employee_id); DBMS_OUTPUT.PUT_LINE('Name: ' || employees_list(emp_id).basic_info.first_name || ' ' || employees_list(emp_id).basic_info.last_name); DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(employees_list(emp_id).basic_info.hire_date, 'DD-MON-YYYY')); -- Address DBMS_OUTPUT.PUT_LINE('--- Address ---'); DBMS_OUTPUT.PUT_LINE(employees_list(emp_id).address.street); DBMS_OUTPUT.PUT_LINE(employees_list(emp_id).address.city || ', ' || employees_list(emp_id).address.state || ' ' || employees_list(emp_id).address.postal_code); -- Phone Numbers DBMS_OUTPUT.PUT_LINE('--- Phone Numbers ---'); FOR i IN 1 .. employees_list(emp_id).phone_numbers.COUNT LOOP DBMS_OUTPUT.PUT_LINE( RPAD(employees_list(emp_id).phone_numbers(i).phone_type, 8) || ': ' || employees_list(emp_id).phone_numbers(i).phone_number ); END LOOP; DBMS_OUTPUT.PUT_LINE('------------------------'||CHR(10)); END LOOP; END; |
When you run this code, you'll get output like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
=== Employee Information === ID: 1 Name: John Smith Hire Date: 02-JAN-2025 --- Address --- 123 Main St Boston, MA 02108 --- Phone Numbers --- HOME : 555-0123 MOBILE : 555-4567 ------------------------ === Employee Information === ID: 2 Name: Jane Doe Hire Date: 03-DEC-2024 --- Address --- 456 Park Ave New York, NY 10022 --- Phone Numbers --- MOBILE : 555-8901 ------------------------ |
This example shows how we can bring together everything we've learned about records and collections to create a robust data structure. We've used:
- Nested records for structured data
- Collections for multiple phone numbers
- An associative array to store multiple employees
- Proper initialization and population of nested structures
- Organized display of hierarchical data
This is the kind of structure you might use in a real-world application. It's organized, flexible, and can handle complex data relationships effectively.
Remember, the key to working with complex structures like this is to break them down into logical components and build them up piece by piece. Start with the smallest units (like phone numbers) and work your way up to the complete structure.
Choosing the Right Type
Now, here's a practical way to think about which type to use:
- Use Associative Arrays when:
- You need a temporary list in memory
- You want to use custom indexes (like employee IDs)
- You don't know how many items you'll have
- Use Nested Tables when:
- You need to store the data in the database
- The number of items might vary a lot
- You need to remove items from the middle
- Use Varrays when:
- You know the maximum number of items
- Order is important
- You need a more compact storage
Let's Practice!
Try creating a simple collection of records. Start with something like a list of books:
- Create a record type for book details
- Create a collection of these records
- Add some sample books
- Display the information
Remember, the key is to choose the right type of collection for your needs. Think about:
- Do you need to store it in the database?
- Do you know the maximum size?
- Do you need flexible indexing?
Any questions so far? Don't hesitate to ask – collections of records are super useful in real-world applications, and I want to make sure you're comfortable with them.