Exploring collections with example
Download as DOCX, PDF0 likes400 views
PL/SQL provides three collection types: associative arrays, nested tables, and variable-size arrays. Associative arrays use keys to access unbounded elements, nested tables are like one-dimensional arrays with an arbitrary number of elements, and variable-size arrays store a fixed number of elements in sequential order. Both associative arrays and nested tables can have object types as elements, but only nested tables can be stored in database columns.
![A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in
the collection.
PL/SQL provides three collection types:
Index-by tables or Associative array
Nested table
Variable-size array or Varray
Oracle documentation provides the following characteristics for each type of collections:
Collection Type
Number of
Elements
Associative array (or index-by table) Unbounded
Nested table
Unbounded
Variable-size array (Varray)
Bounded
Where
Created
Can Be
Object
Type
Attribute
Subscript
Type
Dense or
Sparse
String or
integer
Either
Only in
PL/SQL
block
No
Integer
Starts
dense,
can
become
sparse
Either in
PL/SQL
block or at
schema
level
Yes
Integer
Always
dense
Either in
PL/SQL
block or at
schema
level
Yes
We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables.
Both types of PL/SQL tables i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation.
However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.
Index-By Table
An index-by table (also called an associative array) 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.
An index-by table is created using the following syntax. Here we are creating an index-by table namedtable_name whose keys will be
of subscript_type and associated values will be of element_type
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Example:
Following example how to create a table to store integer values along with names and later it prints the same list of names.
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name
VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish')
salary_list('Minakshi')
:= 62000;
:= 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;](https://image.slidesharecdn.com/exploringcollectionswithexample-140215131750-phpapp02/85/Exploring-collections-with-example-1-320.jpg)
![Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed
Nested Tables
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.
An 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 declaration of an index-by table, but there is no INDEX BY clause.
A nested table can be stored in a database column and so it could 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 SQL prompt, it produces 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](https://image.slidesharecdn.com/exploringcollectionswithexample-140215131750-phpapp02/85/Exploring-collections-with-example-3-320.jpg)
More Related Content
What's hot (19)
Viewers also liked (9)
Similar to Exploring collections with example (20)
Exploring collections with example
- 1. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection. PL/SQL provides three collection types: Index-by tables or Associative array Nested table Variable-size array or Varray Oracle documentation provides the following characteristics for each type of collections: Collection Type Number of Elements Associative array (or index-by table) Unbounded Nested table Unbounded Variable-size array (Varray) Bounded Where Created Can Be Object Type Attribute Subscript Type Dense or Sparse String or integer Either Only in PL/SQL block No Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes Integer Always dense Either in PL/SQL block or at schema level Yes We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables. Both types of PL/SQL tables i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot. Index-By Table An index-by table (also called an associative array) 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. An index-by table is created using the following syntax. Here we are creating an index-by table namedtable_name whose keys will be of subscript_type and associated values will be of element_type TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name; Example: Following example how to create a table to store integer values along with names and later it prints the same list of names. DECLARE TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); salary_list salary; name VARCHAR2(20); BEGIN -- adding elements to the table salary_list('Rajnish') salary_list('Minakshi') := 62000; := 75000; salary_list('Martin') := 100000; salary_list('James') := 78000;
- 2. -- printing the table name := salary_list.FIRST; WHILE name IS NOT null LOOP dbms_output.put_line ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP; END; / When the above code is executed at SQL prompt, it produces following result: Salary of Rajnish is 62000 Salary of Minakshi is 75000 Salary of Martin is 100000 Salary of James is 78000 PL/SQL procedure successfully completed. Example: Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as: Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ DECLARE CURSOR c_customers is select name from customers; TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer; name_list c_list; counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter|| '):'||name_list(counter)); END LOOP; END; / When the above code is executed at SQL prompt, it produces following result: Customer(1): Ramesh Customer(2): Khilan
- 3. Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed Nested Tables 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. An 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 declaration of an index-by table, but there is no INDEX BY clause. A nested table can be stored in a database column and so it could 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 SQL prompt, it produces 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
- 4. PL/SQL procedure successfully completed. Example: Elements of an nested table table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as: Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ DECLARE CURSOR c_customers is SELECT name FROM customers; TYPE c_list IS TABLE of customers.name%type; name_list c_list := c_list(); counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); END LOOP; END; / When the above code is executed at SQL prompt, it produces following result: Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed. PL/SQL Array 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, but it is often more useful 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.
- 5. An array is a part of collection type data and it stands for variable-size arrays. We will study other collection types in a later chapter 'PL/SQL Collections'. Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically. 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 VRRAY 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 VRRAY 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; Example 1 The following program illustrates using varrays: DECLARE type namesarray IS VARRAY(5) OF VARCHAR2(10); type grades IS VARRAY(5) OF INTEGER; names namesarray; marks grades; total integer;
- 6. 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 SQL prompt, it produces the following result: Student: Kavita Marks: 98 Student: Pritam Student: Ayan Marks: 97 Marks: 78 Student: Rishav Student: Aziz Marks: 87 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. Example 2 Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept: We will use the CUSTOMERS table stored in our database as: Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ Following example makes use of cursor which you will study in detail in a separate chapter. DECLARE CURSOR c_customers is SELECT name FROM customers; type c_list is varray (6) of customers.name%type; name_list c_list := c_list();
- 7. counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter + 1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); END LOOP; END; / When the above code is executed at SQL prompt, it produces the following result: Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed.