It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Working with Collections

 An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. A collection groups elements of the same type in a specified order. Each element has a unique subscript that determines its position in the collection.

PL/SQL has three kinds of collections:
  • Associative arrays (formerly called "PL/SQL tables" or "index-by tables")
  • Nested tables
  • Variable-size arrays (varrays)
Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters to stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

This topic applies to collection types that you define inside a PL/SQL block or package, which are different from standalone stored collection types that you create with the CREATE TYPE Statement.
In a PL/SQL block or package, you can define all three collection types. With the CREATE TYPE statement, you can create nested table types and varray types, but not associative array types.

collection_type_definition ::=
TYPE type_name IS 
{ assoc_array_type_def
| nested_table_type_def
| varray_type_def
} ;

assoc_array_type_def ::=
TABLE OF element_type [ NOT NULL ]

nested_table_type_def ::=
TABLE OF element_type [ NOT NULL ]

varray_type_def ::=
{ VARRAY | VARYING ARRAY } ( size_limit )
OF element_type [ NOT NULL ]

collection_variable_dec ::=
collection_name type_name ;

element_type ::=
{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE

Keyword and Parameter Description
The name that you give to the variable of the collection type that you defined.

The data type of the collection element (any PL/SQL data type except REF CURSOR).

For a nested table:
  • If element_type is an object type, then the nested table type describes a table whose columns match the name and attributes of the object type.
  • If element_type is a scalar type, then the nested table type describes a table with a single, scalar type column called COLUMN_VALUE.
  • You cannot specify NCLOB for element_type. However, you can specify CLOB or BLOB.
For an associative array, the data type of its indexes—PLS_INTEGERBINARY_INTGER, or VARCHAR2.

Specifies that no element of the collection can have the value NULL.

The name that you give to the collection type that you are defining.

For an associative array, the length of the VARCHAR2 key by which it is indexed.

The type definition of a nested table or varray can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).

Understanding Associative Arrays (Index-By Tables)
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique. For example, key values might come from the primary key of a database table, from a numeric hash function, or from concatenating strings to form a unique string value.

Declaring Index-By-Table Collection Type:
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
  country_population('Greenland') := 100000; -- Creates new entry
  country_population('Iceland') := 750000;   -- Creates new entry
-- Looks up value associated with a string
  howmany := country_population('Greenland');
  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value 
-- Returns 'Antarctica' as that comes first alphabetically.
  which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically.  which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
  howmany := continent_population(continent_population.LAST);
Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.
Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

Understanding Nested Tables
PL/SQL nested tables represent sets of values. You can think of them as one-dimensional arrays with no declared number of elements. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.
Within the database, nested tables are column types that hold sets of values. Oracle 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. That gives you array-like access to individual rows.

Declaring Nested-Table Collection Type:

2 TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
happyfamily list_of_names_t := list_of_names_t (); 5
4 children list_of_names_t := list_of_names_t ();
; 7 BEGIN 8 happyfamily.EXTEND (4); 9 happyfa
6 parents list_of_names_t := list_of_names_t ( )mily (1) := ‘Veva’; 10 happyfamily (2) := ‘Chris’; 11 happyfamily (3) := ‘Eli’;
n.LAST) := ‘Chris’; 16 children
12 happyfamily (4) := ‘Steven’; 13 14 children.EXTEND; 15 children (childr e.EXTEND; 17 children (children.LAST) := ‘Eli’; 18 19 parents := happyfamily MULTISET EXCEPT children; 20
OP; 25 END;
21 FOR l_row IN 1 .. parents.COUNT 22 LOOP 23 DBMS_OUTPUT.put_line (parents (l_row)); 24 END L

Nested tables differ from arrays in two important ways:

Nested tables do not have a declared number of elements, while arrays have a predefined number as illustrated in Figure below. The size of a nested table can increase dynamically; however, a maximum limit is imposed. 
Nested tables might not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). You can delete elements from a nested table using the built-in procedure DELETE. The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.

Description of lnpls016.gif follows

Understanding Varrays

Items of type VARRAY are called varrays. They let you reference individual elements for array operations, or manipulate the collection as a whole. To reference an element, you use standard subscripting syntax (see Figure below). For example, Grade(3) references the third element in varray Grades.
A varray has a maximum size, which you specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, the current upper bound for varray Grades is 7, but you can increase its upper bound to maximum of 10. A varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.

Description of lnpls017.gif follows

Declare a varray of numbers:
TYPE numbers_vat IS VARRAY(10) 

Declare a varray of Records:

  IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25));
   TYPE names IS VARRAY(250) OF name_rec;

Initializes a varray using a constructor, which looks like a function with the same name as the collection type:


-- In the varray, we put an upper limit on the number of elements
   TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
   dept_names dnames_var;
-- Because dnames is declared as VARRAY(20), we can put up to 10-- elements in the constructor
   dept_names := dnames_var('Shipping','Sales','Finance','Payroll');

Choosing Between Nested Tables and Associative Arrays

Nested tables and associative arrays differ in persistence and ease of parameter passing.
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 FORALLstatement or BULK COLLECT clause).

Choosing Between Nested Tables and Varrays

Varrays are a good choice when:
  • The number of elements is known in advance.
  • 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:
  • Index values are not consecutive.
  • There is no set number of index values.
  • You must delete or update some elements, but not all elements at once.
  • You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.
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 only affect some elements of the collection.
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.


Oracle Access Management Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Oracle Access Management Online Training, Oracle Access Management Training, Oracle Access Management, Oracle Access Management Online Training| Oracle Access Management Training| Oracle Access Management| Courses at 21st Century Software Solutions
Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 -



Get professional trading signals sent to your mobile phone daily.

Follow our signals today & gain up to 270% a day.


BlueHost is ultimately one of the best hosting provider with plans for all of your hosting needs.


You Might Also Like

Related Posts with Thumbnails