PL/SQL Arrays

PL/SQL, an extension of SQL developed through Oracle, empowers builders with robust programming skills for powerful database management. Among its many capabilities, arrays stand out as an essential tool for organizing and manipulating data correctly. In this article, we'll dive deep into the sector of PL/SQL arrays, overlaying their definition, introduction, and realistic examples.

In this article, we are going to discuss the different types through which arrays can be declared in PL/SQL. we will look into the concept of indexes in SQL, exploring their types, and best practices for creating and managing them.

What Are Arrays in PL/SQL?

Arrays, mainly called Varrays (Variable Arrays), are dynamic information systems that permit the storage of multiple values of the same information type underneath a single variable name. Unlike simple variables, arrays offer an effective approach to dealing with statistics collections, facilitating streamlined facts control and manipulation of data.

Declaring a Varray in PL/SQL

To use Varrays in PL/SQL, you first need to define the Varrays type, specifying the maximum number of elements it can hold and the data type of the elements. This type of declaration is followed by the creation of a variable based on the Varray type.

Syntax for Creating a Varray Type

CREATE TYPE num_array AS VARRAY(10) OF INT;

• array_name: The name of the Varray type.
• max_elements: The maximum number of elements the Varray can hold.
• data_type: The type of data the Varray will store (e.g., NUMBER, VARCHAR2).

Examples of PL/SQL Array

Let's look at practical examples to understand how Varrays can be used in PL/SQL.

Example 1: Creating and Using a Varray of Integers

Let's look into a practical example using a Varray of integers. In this situation, we declare and use a Varray named 'numbers':

DECLARE
    numbers num_array;
BEGIN
    numbers := num_array(1, 2, 3, 4, 5);
    -- Perform operations on 'numbers'
    FOR i IN 1..numbers.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || numbers(i));
    END LOOP;

    -- Update an element
    numbers(3) := 30;

    -- Display the updated elements
    FOR i IN 1..numbers.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Updated Element ' || i || ': ' || numbers(i));
    END LOOP;
END;

In this case, the Varray 'numbers' is initialized with integers from 1 to 5 and operations are performed, showcasing the simplicity of dealing with numerical collections.

Output:

Explanation: This PL/SQL code publicizes a variable number of the VARRAY type num_array, initializes it with values, plays operations on it (displaying elements), updates one among its factors, after which shows the updated factors.

• The num_array type is created to hold up to 10 integers.
• The Varray numbers is declared and initialized with five values.
• Elements are accessed and updated using indexing, with changes displayed using DBMS_OUTPUT.PUT_LINE.

Output:

Element 1: 1
Element 2: 2
Element 3:3
Element 4: 4
Element 5: 5

• Update the detail at index three (value three) to 30.
• Use a loop to display the updated elements.

Output:

 Updated Element 1: 1
 Updated Element 2: 2
 Updated Element 3: 30
 Updated Element 4: 4
 Updated Element 5: 5

Example 2: Creating a Varray of Strings

Varrays are versatile and no longer restrained to numerical statistics sorts. Consider a scenario where you create and use a Varray of characters:

CREATE TYPE char_array AS VARRAY(5) OF CHAR(10);

This example introduces a `char_array` Varray able to protect as much as 5 strings, each with a most period of 10 characters.

DECLARE
    -- Declare a variable of the VARRAY type
    my_array char_array := char_array('Apple', 'Banana', 'Cherry');

BEGIN
    -- Display elements
    FOR i IN 1..my_array.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_array(i));
    END LOOP;

    -- Insert a new element
    my_array.EXTEND;
    my_array(4) := 'Date';

    -- Display elements after insertion
    FOR i IN 1..my_array.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_array(i));
    END LOOP;

    -- Update an element
    my_array(2) := 'Blueberry';

    -- Display elements after update
    FOR i IN 1..my_array.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Updated Element ' || i || ': ' || my_array(i));
    END LOOP;
END;

Output:

Explanation: This PL/SQL code broadcasts a variable my_array of the VARRAY (Variable Size Array) kind char_array and plays various operations on it, which includes showing factors, inserting a new detail, updating an existing element, and displaying the elements again after each operation.

• Declare a VARRAY variable `my_array` of kind `char_array`.
• Initialize it with 3 factors: 'Apple', 'Banana', 'Cherry'.
• Display factors the usage of a loop.

Output:

Element 1: Apple
Element 2: Banana
Element 3: Cherry

• Use `EXTEND` to growth array size.
• Insert 'Date' at index four.
• Display updated elements.

Output:

 Element 1: Apple
 Element 2: Banana
 Element 3: Cherry
 Element 4: Date

• Update element at index 2 to 'Blueberry'.
• Display final elements after the update.

 Updated Element 1: Apple
 Updated Element 2: Blueberry
 Updated Element 3: Cherry
 Updated Element 4: Date

Example 3: Defining a Varray Using %ROWTYPE or %TYPE

PL/SQL offers the flexibility to define a Varray based at the records sort of a table column using `%ROWTYPE` or `%TYPE`:

CREATE TYPE empp.CROWTYPE AS TABLE OF VARCHAR2(50);
-- Assuming empp.CROWTYPE is a nested table type
CREATE TYPE emp_details AS VARRAY(50) OF empp.CROWTYPE;

Explanation: In this situation, `emp_details` is a Varray designed to save up to 50 rows from the `emp` table, demonstrating the adaptability of PL/SQL arrays to numerous statistics sorts.

• The Varray name_array is defined based on the data type of the emp_name column from the employees table.
• The Varray is then initialized with a list of names, making it easy to work with employee data.

Important Points About PL/SQL Arrays

• Varrays have a fixed maximum size, which must be specified during type creation. This size cannot be changed once defined.
• Varrays can be passed as parameters to procedures and functions.
• PL/SQL automatically manages memory for Varrays. You can use methods like EXTEND to add elements and TRIM or DELETE to remove them.
• Indexes start from 1, and trying to access an index outside the valid range will raise a SUBSCRIPT_BEYOND_COUNT exception.