Collection and records S.Y.B.Sc. Computer science Advanced database concepts
associative arrays
Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements.
an associative array is single-dimensional. It means that an associative array has a single column of data in each row, which is similar to a one-dimension array.
an associative array is unbounded, meaning that it has a predetermined limits number of elements.
an associative array is sparse because its elements are not sequential. In other words, an associative array may have gaps between elements.
Finally, an associative array has elements which have the same data type, or we call them homogenous elements.
An associative array can be indexed by numbers or characters.
Declaring an associative array is a two-step process. First, you declare an associative array type. And then, you declare an associative array variable of that type.
Declaring an associative array type
Syntax for declaring an associative array type:
TYPE associative_array_type IS TABLE OF datatype [NOT NULL] INDEX BY index_type;
In this syntax:
- The
associative_array_typeis the name of the associative array type. - The
datatypeis the data type of the elements in the array. - The
index_typeis the data type of the index used to organize the elements in the array. - Optionally, you can specify
NOT NULLto force every element in the array must have a value. - Accessing associative array elements
- To access an array element, you use this syntax:
VARRAY
The PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type.
A varray is used to store an ordered collection of data, however it is often better to think of an array as a collection of variables of the same type.
All varrays consist of contiguous memory locations.
The lowest address corresponds to the first element and the highest address to the last element.
An array is a part of collection type data and it stands for variable-size arrays. Creating a Varray Type
A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.
The basic syntax for creating a VARRAY type at the schema level is −
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>
Where,
varray_type_name is a valid attribute name,
n is the number of elements (maximum) in the varray,
element_type is the data type of the elements of the array.
Maximum size of a varray can be changed using the ALTER TYPE statement.
For example,
CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/
Type created.
The basic syntax for creating a VARRAY type within a PL/SQL block is −
TYPE varray_type_name IS VARRAY(n) of <element_type>
For example −
TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;
Let us now work out on a few examples to understand the concept −
Example 1
The following program illustrates the use of varrays −
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Total 5 Students
Student: Kavita Marks: 98
Student: Pritam Marks: 97
Student: Ayan Marks: 78
Student: Rishav Marks: 87
Student: Aziz Marks: 92
PL/SQL procedure successfully completed.
Please note −
In Oracle environment, the starting index for varrays is always 1.
You can initialize the varray elements using the constructor method of the varray type, which has the same name as the varray.
Varrays are one-dimensional arrays.
A varray is automatically NULL when it is declared and must be initialized before its elements can be referenced
Nested Table
A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects −
An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
An array is always dense, i.e., it always has consecutive subscripts.
A nested array is dense initially, but it can become sparse when elements are deleted from it.
A nested table is created using the following syntax −
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;
This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.
A nested table can be stored in a database column.
It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.
Example
The following examples illustrate the use of nested table −
DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;
names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92
PL/SQL procedure successfully completed.
Important Differences Between Nested Tables and Arrays
Conceptually, a nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in these important ways:
An array is always dense. A nested array is dense initially, but it can become sparse, because you can delete elements from it.
Figure shows the important differences between a nested table and an array.
Figure of Array and Nested Table
Appropriate Uses for Nested Tables
A nested table is appropriate when:
The number of elements is not set.
Index values are not consecutive.
You must delete or update some elements, but not all elements simultaneously.
Nested table data is stored in a separate store table, a system-generated database table.
When you access a nested table, the database joins the nested table with its store table.
This makes nested tables suitable for queries and updates that affect only some elements of the collection.
You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.
Collection Constructors
A collection constructor (constructor) is a system-defined function with the same name as a collection type, which returns a collection of that type.
Note:
This topic applies only to varrays and nested tables. In this topic, collection means varray or nested table.
The syntax of a constructor invocation is:
collection_type ( [ value [, value ]... ] )
If the parameter list is empty, the constructor returns an empty collection. Otherwise, the constructor returns a collection that contains the specified values.
You can assign the returned collection to a collection variable (of the same type) in the variable declaration and in the executable part of a block.
Example: Initializing Collection (Varray) Variable to Empty
This example invokes a constructor twice: to initialize the varray variable team to empty in its declaration, and to give it new values in the executable part of the block. The procedure print_team shows the initial and final values of team. To determine when team is empty, print_team uses the collection method COUNT.
DECLARE
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
team Foursome := Foursome(); -- initialize to empty
PROCEDURE print_team (heading VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF team.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Empty');
ELSE
FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_team('Team:');
team := Foursome('John', 'Mary', 'Alberto', 'Juanita');
print_team('Team:');
END;
/
Multidimensional Collections
Although a collection has only one dimension, you can model a multidimensional collection with a collection whose elements are collections.
In this example, nva is a two-dimensional varray—a varray of varrays of integers.
DECLARE
TYPE t1 IS VARRAY(10) OF INTEGER; -- varray of integer
va t1 := t1(2,3,5);
TYPE nt1 IS VARRAY(10) OF t1; -- varray of varray of integer
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
i INTEGER;
va1 t1;
BEGIN
i := nva(2)(3);
DBMS_OUTPUT.PUT_LINE('i = ' || i);
nva.EXTEND;
nva(5) := t1(56, 32); -- replace inner varray elements
nva(4) := t1(45,43,67,43345); -- replace an inner integer element
nva(4)(4) := 1; -- replace 43345 with 1
nva(4).EXTEND; -- add element to 4th varray element
nva(4)(5) := 89; -- store integer 89 there
END;
/
Result:
i = 73
Collection Comparisons
To determine if one collection variable is less than another (for example), you must define what less than means in that context and write a function that returns TRUE or FALSE.
You cannot compare associative array variables to the value NULL or to each other.
Except for Comparing Nested Tables for Equality and Inequality, you cannot natively compare two collection variables with relational operators. This restriction also applies to implicit comparisons. For example, a collection variable cannot appear in a DISTINCT, GROUP BY, or ORDER BY clause.
Comparing Varray and Nested Table Variables to NULL
Use the IS[NOT] NULL operator when comparing to the NULL value.
You can compare varray and nested table variables to the value NULL with the "IS [NOT] NULL Operator", but not with the relational operators equal (=) and not equal (<>, !=, ~=, or ^=).
Following Example of Comparing Varray and Nested Table Variables to NULL
This example compares a varray variable and a nested table variable to NULL correctly.
DECLARE
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); -- VARRAY type
team Foursome; -- varray variable
TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type
names Roster := Roster('Adams', 'Patel'); -- nested table variable
BEGIN
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('team IS NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('team IS NOT NULL');
END IF;
IF names IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('names IS NOT NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('names IS NULL');
END IF;
END;
/
Result:
team IS NULL
names IS NOT NULL
Comparing Nested Tables for Equality and Inequality
Two nested table variables are equal if and only if they have the same set of elements (in any order).
If two nested table variables have the same nested table type, and that nested table type does not have elements of a record type, then you can compare the two variables for equality or inequality with the relational operators equal (=) and not equal (<>, !=, ~=, ^=).
Comparing Nested Tables for Equality and Inequality
This example compares nested table variables for equality and inequality with relational operators.
DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record type
dept_names1dnames_tab:=
dnames_tab('Shipping','Sales','Finance','Payroll');
dept_names2 dnames_tab :=
dnames_tab('Sales','Finance','Shipping','Payroll');
dept_names3 dnames_tab :=
dnames_tab('Sales','Finance','Payroll');
BEGIN
IF dept_names1 = dept_names2 THEN
DBMS_OUTPUT.PUT_LINE('dept_names1 = dept_names2');
END IF;
IF dept_names2 != dept_names3 THEN
DBMS_OUTPUT.PUT_LINE('dept_names2 != dept_names3');
END IF;
END;
/
Result:
dept_names1 = dept_names2
dept_names2 != dept_names3
Collection Methods
A collection method is a PL/SQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain.
Table summarizes the collection methods.
Table : Collection Methods
The basic syntax of a collection method invocation is:
collection_name.method
Collection Types Defined in Package Specifications
A collection type defined in a package specification is incompatible with an identically defined local or standalone collection type.
Identically Defined Package and Local Collection Types
In this example, the package specification and the anonymous block define the collection type NumList identically. The package defines a procedure, print_numlist, which has a NumList parameter. The anonymous block declares the variable n1 of the type pkg.NumList (defined in the package) and the variable n2 of the type NumList (defined in the block). The anonymous block can pass n1 to print_numlist, but it cannot pass n2 to print_numlist.
CREATE OR REPLACE PACKAGE pkg AS
TYPE NumList IS TABLE OF NUMBER;
PROCEDURE print_numlist (nums NumList);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
PROCEDURE print_numlist (nums NumList) IS
BEGIN
FOR i IN nums.FIRST..nums.LAST LOOP
DBMS_OUTPUT.PUT_LINE(nums(i));
END LOOP;
END;
END pkg;
/
DECLARE
TYPE NumList IS TABLE OF NUMBER; -- local type identical to package type
n1 pkg.NumList := pkg.NumList(2,4); -- package type
n2 NumList := NumList(6,8); -- local type
BEGIN
pkg.print_numlist(n1); -- succeeds
pkg.print_numlist(n2); -- fails
END;
/
Result:
pkg.print_numlist(n2); -- fails
*
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to 'PRINT_NUMLIST'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
Record Variables
You can create a record variable in any of these ways:
Define a RECORD type and then declare a variable of that type.
Use %ROWTYPE to declare a record variable that represents either a full or partial row of a database table or view.
Use %TYPE to declare a record variable of the same type as a previously declared record variable.
Declaring a Record Variable that Always Represents Full Row
To declare a record variable that always represents a full row of a database table or view, use this syntax:
variable_name table_or_view_name%ROWTYPE;
%ROWTYPE Variable Represents Full Database Table Row
This example declares a record variable that represents a row of the table departments, assigns values to its fields, and prints them.
DECLARE
dept_rec departments%ROWTYPE;
BEGIN
-- Assign values to fields:
dept_rec.department_id := 10;
dept_rec.department_name := 'Administration';
dept_rec.manager_id := 200;
dept_rec.location_id := 1700;
-- Print fields:
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.department_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.department_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.manager_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.location_id);
END;
/
Result:
dept_id: 10
dept_name: Administration
mgr_id: 200
loc_id: 1700
