Learning Objectives
Upon Completion of this chapter you will be able to:
??Understand & write PL/SQL collection
Understand Collections
What is a Collection?
A collection is an ordered group of elements, all of the same type Features of Collection:
??It is a general concept that encompasses list, arrays and other familiar datatypes
??Each element has a unique subscript that determines its position in the collection
??Collections work like the arrays found in most third-generation programming language
Types of Collection
PL/SQL has two collection types:
??Tables
??Varrays
Tables comes in two flavors
Index by tables (formerly called PL/SQL tables):
Also known as associative arrays, it lets you look up elements using arbitrary numbers and strings for subscript values. They are similar to one-dimensional arrays and are referenced like arrays of records. Since Index-By tables can be passed as parameters, they can be used to move columns of data into and out of database tables or between client-side applications and stored subprograms
Nested Tables
Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.
Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. When the nested table is retrieve into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives array-like access to individual rows.
©Copyright 2004, Cognizant Academy, All Rights Reserved 52 Varrays
Items of type VARRAY are called Varrays.Allow to associate a single identifier with an entire collection. Manipulate the collection as a whole and reference individual elements easily. To reference an element, use standard subscripting syntax.
Define and Declare Collection
Define a collection type and declare variables of that type Syntax for Index-by tables:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2 (size_limit)];
INDEX BY key_type;
Type_name is a type specifier used later to declare collections.
Element_type is any PL/SQL datatype.
Key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER.
It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG.
You mustSpecify the size. (Varchar2 (1000);) Syntax for Nested tables:
TYPE type_name IS TABLE OF element_type [NOT NULL];
type_name is a type specifier used later to declare collections element_type is any PL/SQL datatype.
Syntax for Varrays:
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
type_name and element_type are the same as for nested tables.size_limit is a positive integer literal representing the maximum number of elements in the array When defining a VARRAY type, must specify its maximum size.
Example, of a type that stores up to 366 dates:
DECLARETYPE Calendar IS VARRAY (366) OF DATE;
Example of Index- by Tables DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE /* Create a type */
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp; /* Declaration of variable of collection type */
TYPE typ_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
empid_tab typ_number; /* Declaration of variable of collection type */
BEGIN /* Retrieve employee record. */
SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7468;
SELECT EMPID
BULK COLLECT INTO empid_tab FROM EMP;
IF empid_tab.last > 0 THEN FORALL I in 1.. empid_tab.last
©Copyright 2004, Cognizant Academy, All Rights Reserved 53 LOOP
Dbms_output.put_line(‘Empid: ‘||empid_tab(i)) END LOOP END IF;
END;
Example of Nested Tables:
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type /
CREATE TYPE Student AS OBJECT ( -- create object id_num INTEGER(4),
name VARCHAR2 (25), address VARCHAR2 (35), status CHAR(2),
courses CourseList) -- declare nested table as attribute /
Example of Varrays
CREATE TYPE ProjectList AS VARRAY (50) OF VARCHAR2 (16);
/
CREATE TABLE department ( -- create database table dept_id NUMBER(2),
name VARCHAR2(15), budget NUMBER(11,2),
-- Each department can have up to 50 projects.
projects ProjectList) /
Use Collection Methods
The following collection methods are used:
??FIRST - Returns the index of the first element in the collection.
??LAST - Returns the index of the last element in the collection.
??PRIOR (n) - Returns the index of the element prior to the specified element
??NEXT (n) - Returns the index of the next element after the specified element.
??EXTEND - Appends a single null element to the collection.
??EXTEND (n) - Appends n null elements to the collection.
??EXTEND (n1, n2) - Appends n1 copies of the n2th element to the collection
??TRIM - Removes a single element from the end of the collection.
??TRIM (n) - Removes n elements from the end of the collection.
??DELETE - Removes all elements from the collection.
??DELETE (n) - Removes element n from the collection.
??DELETE (n1, n2) - Removes all elements from n1 to n2 from the collection
©Copyright 2004, Cognizant Academy, All Rights Reserved 54
Learn Multi Level Collection
A Multi Level Collection is a collection within collections. Nested table of Varrays, Varrays of varrays etc are examples of Multi Level Collection
Multilevel VARRAY Example declare
type t1 is varray(10) of integer;
type nt1 is varray(10) of t1; -- multilevel va t1 := t1(2,3,5);
-- initialize multilevel varray
nva nt1 := nt1(va, t1(55,6,73), t1(2,4), i integer;
va1 t1;
begin
-- Multilevel access
i := nva(2)(3); -- i will get value 73 dbms_output.put_line(i);
SUMMARY
??A collection is a ordered group of elements, all of the same type
??Each element has a unique subscript that determines its position in the collection
??PL/SQL has two collection types: Tables, Varrays
??Tables comes in two flavors Index by tables (formerly called PL/SQL tables) and Nested Tables
Test Your Understanding
Study the following code:
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type /
CREATE TYPE Student AS OBJECT ( -- create object id_num INTEGER(4),
name VARCHAR2(25), address VARCHAR2(35), status CHAR(2),
courses CourseList) -- declare nested table as attribute /
If courses . DELETE(2); deletes element 2 and courses . DELETE(7,7) delete element 7Then courses. DELETE(6,3) delete which element?
©Copyright 2004, Cognizant Academy, All Rights Reserved 55