Collections of Records in PL/SQL – Making Complex Data Simple

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:

“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:

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:

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:

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:

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:

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:

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:

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:

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.

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!

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.

So when you run this, you'll see something like:

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:

Alright, let's look at what we're doing here – it's similar to our employee example but with a cool twist.

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.

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.

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:

Okay, let's look at what's happening here – this is where we actually start filling up our department list:

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.

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.

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:

  1. Make space for it (EXTEND)
  2. 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:

Alright, now we're looking at something a bit different – let me show you what's happening here:

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.

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.

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

When you run this code, you'll get output like this:

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:

  1. Create a record type for book details
  2. Create a collection of these records
  3. Add some sample books
  4. 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.

Rolar para cima