PL/SQL - Arrays

Hello, aspiring programmers! Today, we're going to dive into the exciting world of PL/SQL arrays. As your friendly neighborhood computer science teacher, I'm here to guide you through this journey step by step. Don't worry if you're new to programming – we'll start from the basics and work our way up. So, grab your virtual notepads, and let's get started!

What are Arrays in PL/SQL?

Before we jump into the deep end, let's understand what arrays are. Imagine you have a bunch of chocolates, and instead of keeping them scattered all over your desk, you decide to put them in a neat little box. That box is like an array – it's a container that holds multiple items of the same type.

In PL/SQL, an array is a collection of elements of the same data type. It allows you to store and manipulate multiple values under a single variable name. Pretty handy, right?

Types of Arrays in PL/SQL

PL/SQL offers three types of arrays:

1. Associative Arrays (Index-By Tables)
2. Nested Tables
3. Variable-size Arrays (VARRAYs)

Let's focus on VARRAYs for now, as they're a great starting point for beginners.

Creating a Varray Type

A VARRAY (Variable-size Array) is a type of array in PL/SQL that has a maximum size limit. It's like buying a chocolate box with a fixed number of compartments – you can't add more chocolates than the box can hold!

Here's how you create a VARRAY type:

CREATE OR REPLACE TYPE number_array AS VARRAY(5) OF NUMBER;

Let's break this down:

• CREATE OR REPLACE TYPE: This tells Oracle to create a new type or replace an existing one.
• number_array: This is the name we're giving to our new type.
• AS VARRAY(5): We're defining it as a VARRAY with a maximum of 5 elements.
• OF NUMBER: Each element in this array will be a NUMBER data type.

Now, let's see how we can use this VARRAY in a PL/SQL block:

DECLARE
    my_numbers number_array := number_array(10, 20, 30, 40, 50);
BEGIN
    FOR i IN 1..my_numbers.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_numbers(i));
    END LOOP;
END;
/

Output:

Element 1: 10
Element 2: 20
Element 3: 30
Element 4: 40
Element 5: 50

Let's dissect this code:

1. We declare my_numbers as our VARRAY and initialize it with 5 numbers.
2. We use a FOR loop to iterate through the array.
3. my_numbers.COUNT gives us the number of elements in the array.
4. We use my_numbers(i) to access each element and print it.

Working with VARRAYs

Now that we've created and initialized a VARRAY, let's explore some operations we can perform on it.

Adding Elements

Remember, we can't add more elements than the maximum size we defined. Let's try to add an element:

DECLARE
    my_numbers number_array := number_array(10, 20, 30);
BEGIN
    my_numbers.EXTEND;
    my_numbers(4) := 40;

    FOR i IN 1..my_numbers.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_numbers(i));
    END LOOP;
END;
/

Output:

Element 1: 10
Element 2: 20
Element 3: 30
Element 4: 40

Here, we used EXTEND to increase the size of the array by 1, then assigned a value to the new element.

Removing Elements

We can also remove elements from the end of the array:

DECLARE
    my_numbers number_array := number_array(10, 20, 30, 40, 50);
BEGIN
    my_numbers.TRIM(2);  -- Remove last 2 elements

    FOR i IN 1..my_numbers.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_numbers(i));
    END LOOP;
END;
/

Output:

Element 1: 10
Element 2: 20
Element 3: 30

The TRIM method removes elements from the end of the array.

Common Methods for VARRAYs

Here's a table of common methods you can use with VARRAYs:

Method	Description
COUNT	Returns the number of elements in the array
FIRST	Returns the index of the first element
LAST	Returns the index of the last element
LIMIT	Returns the maximum size of the array
EXTEND	Increases the size of the array
TRIM	Decreases the size of the array
EXISTS	Checks if an element exists at a specific index

Practical Example: Student Grades

Let's put our knowledge to use with a practical example. Imagine you're a teacher (like me!) and you want to store and calculate the average grade for a student:

CREATE OR REPLACE TYPE grade_array AS VARRAY(5) OF NUMBER;

DECLARE
    student_grades grade_array := grade_array(85, 90, 78, 92, 88);
    total_score NUMBER := 0;
    average_score NUMBER;
BEGIN
    -- Calculate total score
    FOR i IN 1..student_grades.COUNT LOOP
        total_score := total_score + student_grades(i);
    END LOOP;

    -- Calculate average
    average_score := total_score / student_grades.COUNT;

    DBMS_OUTPUT.PUT_LINE('Average Score: ' || ROUND(average_score, 2));
END;
/

Output:

Average Score: 86.6

This example shows how you can use a VARRAY to store a student's grades, then calculate and display their average score.

Conclusion

Congratulations! You've just taken your first steps into the world of PL/SQL arrays. We've covered the basics of VARRAYs, how to create and manipulate them, and even put them to practical use. Remember, arrays are like chocolate boxes – they keep your data organized and easy to access. As you continue your programming journey, you'll find arrays to be incredibly useful tools in your coding toolkit.

Keep practicing, stay curious, and don't be afraid to experiment. Before you know it, you'll be array-zing everyone with your PL/SQL skills! (Sorry, I couldn't resist a little teacher humor there.)

Happy coding, future database wizards!

Credits: Image by storyset