Initialized Value PL/SQL VARRARY

To begin, open SQL *Plus. Create an anonymous code block declaring a VARRAY type of 3 integers (Line 2), then a variable of the named type (int_varray) and assign NULL to each of the 3 members (Line 4). Using a FOR loop, iterate through the null varray (Lines 8-12). Assign values to each of the 3 members (Lines 14-16) and create a 2nd FOR loop to iterate through the varray again to show the assigned values (Lines 21-24).

Notice that the values for the null varray are blank and that the assigned values are printed out correctly.

Using Extend for PL/SQL VARRARY

In a new block of code, the varray type and variable are declared once again, however the values are not initialized. This code uses the Extend method(Line 12) of the varray type to add members to the varray and then initialize them.

The output appears to contain the 3 members of the varray and their assigned values from the FOR loop.

Using VARRAY type in a Cursor

The next sample will use a loop and a cursor to iterate through a table of students and their corresponding test scores and use a varray to output a grade letter for them based on the score. Create a table named studentExam with a varchar2 studentName and int Test_score.

Create several Insert statements to populate the table with studentNames and test scores.

In the declare section (Lines 2-10) of the code block, a grade_varray type is created and a variable of grade_varray type is declared. A cursor for the studentExam data is declared as is an integer placeholder and exam_grade variable to output the grade with the student exam data.

The FOR loop to add varray members and assign values is created after the BEGIN statement (Lines 12-22). This code is counting from 1 to 5 and then adding the correct i_chr integer values using a CASE statement to the value of 64. A CHR function is used to populate the correct grade letter (A-F) into the varray.

The exam_cursor is opened (Line 26) and iterated through using a CASE statement to check the test_score against a range of values to determine which varray member to assign to the variable exam_grade.

Note the check of CHR(65) against DUAL to verify that the ASCII value for the capital letter A is 65.

The Grade letters are output to prove that they were correctly assigned to the varray. The StudentName, scores and grades are also output. Note that they are evenly spaced because the RPAD function was used in the code block.

Note: This is the unformatted version of the output (without RPAD).

Creating VARRAY Types for Tables

The next sample uses a declared database varray type in a table. First, create the varray type of 5 varchar2 memeber then a table called emps to hold the empID, empName and the address in the varray data type.

To insert a value into this table, the addr_varray must be called as part of the values to be inserted. Each of the 5 members are assigned a value here, but it is not necessary.

Accessing VARRAY Table Types - SELECT Statement

To access the contents of the varray column, a nested table type will need to be created and used in the Select statement.

The select statement uses the reserved word column_value to access the rows in the nested table. The reserved word THE tells the column_value that it will be selected from a nested table query. A Cast on the varray datatype converts it to the nested table type. The result is a single column with the data rows entered from the emps table.

To view the empname with the data, a Union query would be required. Trying to view the data as a single call from the table will result in an error.

Using the Union query without some creative row numbering retrieves the data, but not in the order expected.

Using 0 for the ordernum for the empname in a Union with the column_name values sorted by rowNum retrieves the data in the correct order.

Additional VARRAY Methods

In addition, there are several other methods for use with varrays. This example shows how to use COUNT, TRIM and DELETE within a code block. Note that TRIM is not specifying which value to remove from the varray, so the last value in the array is the one removed. Delete removes all members.