Platform For Learning Programming Language: PL/SQL Collections and Records

What is Collections and Records?

Collections: In a collection, the internal components are always of the same data type, and are called elements. You access each element by its unique subscript. Lists and arrays are classic examples of collections.

Record: In a record, the internal components can be of different data types, and are called fields. You access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

Types of PL/SQL Collection:

PL/SQL has three collection types, whose characteristics are summarized below:

Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created	Can Be Object Type Attribute
Associative array (or index-by table)	Unbounded	String or integer	Either	Only in PL/SQL block	No
Nested table	Unbounded	Integer	Starts dense, can become sparse	Either in PL/SQL block or at schema level	Yes
Variable-size array (varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level	Yes

Notes:

Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there are limits, but they are very high—for details.

Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).

A collection that is created in a PL/SQL block (with the syntax in Collection) is available only in that block. A nested table type or varray type that is created at schema level is stored in the database, and you can manipulate it with SQL statements.

A collection has only one dimension, but you can model a multidimensional collection by creating a collection whose elements are also collections. For examples, see Using Multidimensional Collections.

What is Associative Arrays (Index-By Tables)?

An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string.

Declaring and Using an Associative Array:

SET SERVEROUTPUT ON;
DECLARE
  TYPE marks IS TABLE OF NUMBER INDEX BY VARCHAR2(64); -- Associative array type
  student_marks marks;  -- Associative array variable
  i VARCHAR2(64);
  
BEGIN
  -- Add new elements to associative array:
  student_marks('MATH')  := 90;
  student_marks('ENGLISH')  := 70;
  student_marks('HISTORY')  := 90;

  i := student_marks.FIRST;
  
  WHILE i IS NOT NULL LOOP
      DBMS_Output.PUT_LINE('Marks of ' || i || ' is ' || TO_CHAR(student_marks(i)));
      i := student_marks.NEXT(i);
    END LOOP;
END;
/

Output:
anonymous block completed
Marks of ENGLISH is 70
Marks of HISTORY is 90
Marks of MATH is 90

What is Nested Tables?

a nested table is like a one-dimensional array with an arbitrary number of elements.Within the database, a nested table is a column type that holds a set of values. The database stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. These subscripts give you array-like access to individual rows.

Declaring and Using an Nested Tables:

SET SERVEROUTPUT ON;
DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30); -- Nested Table type
   v1 nested_type;  -- Nested Table variable
   total integer;
  
BEGIN
  -- Add new elements to Nested Table:
  v1 := nested_type('Shipping','Sales','Finance','Payroll');

  total := v1.count;
  
  FOR i IN 1 .. total LOOP
      dbms_output.put_line(v1(i));
   end loop;
END;
/

Output:
anonymous block completed
Shipping
Sales
Finance
Payroll

A Nested table differs from an Array in these important ways:

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 (that is, it always has consecutive subscripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.

What is Variable-Size Arrays (Varrays)?

A variable-size array (varray) is an item of the data type VARRAY. A varray has a maximum size, which you specify in its type definition. A varray can contain a varying number of elements, from zero (when empty) to the maximum size. A varray index has a fixed lower bound of 1 and an extensible upper bound. To access an element of a varray, you use standard subscripting syntax.

Declaring and Using an Varrays:

SET SERVEROUTPUT ON;
DECLARE
   TYPE varray_type IS VARRAY(5) OF INTEGER; -- VArray type
   v2 varray_type;  -- VArray variable
   total integer;
  
BEGIN
  -- Add new elements to VArray Table:
  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers

  total := v2.count;
  
  FOR i IN 1 .. total LOOP
      dbms_output.put_line(v2(i));
   end loop;
END;
/

Output:
anonymous block completed
1
2
3
4
5

Difference between Nested Tables and Associative Arrays?

A Nested table can be stored in a database column; therefore, you can use a nested table to simplify SQL operations in which you join a single-column table with a larger table. An associative array cannot be stored in the database.

An Associative array is appropriate for the following:

A relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked or a package is initialized
Passing collections to and from the database server

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, and then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).

Difference between Nested Tables and Varrays?

Varrays are a good choice when: (i) The number of elements is known in advance, (ii) the elements are usually accessed sequentially. When stored in the database, varrays keep their ordering and subscripts.

A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same tablespace.

You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements at once. However, this might be impractical for large numbers of elements.

Nested tables are a good choice when: (i) Index values are not consecutive. (ii) There is no set number of index values. (iii) You must delete or update some elements, but not all elements at once. (iv) You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.

Defining Collection Types

To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type.

You can define a collection type either at schema level, inside a package, or inside a PL/SQL block.

A collection type created at schema level is a standalone stored type. You create it with the CREATE TYPE statement. It is stored in the database until you drop it with the DROP TYPE statement.

What are Records?

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.

Declaring and Using an Records: To declare a record, you must first define a composite datatype; then declare a record for that type.

DECLARE
   TYPE DeptRecTyp IS RECORD (
      deptid NUMBER(4) NOT NULL := 99,
      dname  departments.department_name%TYPE,
      loc    departments.location_id%TYPE,
      region regions%ROWTYPE );
   dept_rec DeptRecTyp;
BEGIN
   dept_rec.dname := 'PURCHASING';
END;
/

Important Collection Methods:

A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain.

EXISTS(n): EXISTS(n) returns TRUE if the nth element in a collection exists; otherwise, it returns FALSE.
COUNT: COUNT returns the current number of elements in a collection. For varrays and nested tables COUNT always equals LAST.
LIMIT: LIMIT returns the maximum number of elements that a collection can have. If the collection has no maximum size, LIMIT returns NULL.
FIRST and LAST: For a collection indexed by integers, FIRST and LAST return the first and last (smallest and largest) index numbers.
PRIOR and NEXT: PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. If n has no successor, NEXT(n) returns NULL.
EXTEND: EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection.
TRIM: TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection.
DELETE: DELETE with no parameters removes all elements from a collection, setting COUNT to 0. DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing. DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is NULL, DELETE(m,n) does nothing.

Next Article: PL-SQL Subprograms (Procedure and Function)

For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)

Platform For Learning Programming Language

PL/SQL Collections and Records