ORACLE PROJECTS: PL/SQL - Collections (Part -1 )

#=======================================================================

# Writer : Lokanadham Thandlam

#=======================================================================

/*A composite data type stores values that have internal components.

You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually.

Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables.

PL/SQL lets you define two kinds of composite data types, collection and record.

You can use composite components wherever you can use composite variables of the same type.

In a collection, the internal components always have the same data type, and are called elements.

You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable,

you either define a collection type and then create a variable of that type or use %TYPE.

In a record, the internal components can have different data types, and are called fields.

You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable,

you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

You can create a collection of records, and a record that contains collections.

PL/SQL Collection Type:

1. Associative array (PL/SQL table or index-by table)

2. VARRAY (variable-size array)

3. Nested table*/

--Example 5–1 Associative ARRAY INDEXED BY STRING

DECLARE

-- Associative array indexed by string:

TYPE population IS TABLE OF NUMBER -- Associative array type

INDEX BY VARCHAR2 (64); -- indexed by string

city_population population; -- Associative array variable

i VARCHAR2 (64); -- Scalar variable

BEGIN

-- Add elements (key-value pairs) to associative array:

city_population ('Smallville') := 2000;

city_population ('Midland') := 750000;

city_population ('Megalopolis') := 1000000;

-- Change value associated with key 'Smallville':

city_population ('Smallville') := 2001;

-- Print associative array:

i := city_population.FIRST; -- Get first element of array

WHILE i IS NOT NULL

LOOP

DBMS_OUTPUT.PUT_LINE (

'Population of ' || i || ' is ' || city_population (i));

i := city_population.NEXT (i); -- Get next element of array

END LOOP;

END;

--Example 5–2 FUNCTION Returns Associative ARRAY INDEXED BY PLS_INTEGER

DECLARE

TYPE sum_multiples IS TABLE OF PLS_INTEGER

INDEX BY PLS_INTEGER;

n PLS_INTEGER := 5; -- number of multiples to sum for display

sn PLS_INTEGER := 10; -- number of multiples to sum

m PLS_INTEGER := 3; -- multiple

FUNCTION get_sum_multiples (multiple IN PLS_INTEGER, num IN PLS_INTEGER)

RETURN sum_multiples

s sum_multiples;

BEGIN

FOR i IN 1 .. num

LOOP

s (i) := multiple * ( (i * (i + 1)) / 2); -- sum of multiples

END LOOP;

RETURN s;

END get_sum_multiples;

BEGIN

DBMS_OUTPUT.PUT_LINE (

'Sum of the first '

|| TO_CHAR (n)

|| ' multiples of '

|| TO_CHAR (m)

|| ' is '

|| TO_CHAR (get_sum_multiples (m, sn) (n)));

END;

--Example 5–3 Declaring Associative ARRAY CONSTANT

CREATE OR REPLACE PACKAGE My_Types

AUTHID CURRENT_USER

TYPE My_AA IS TABLE OF VARCHAR2 (20)

INDEX BY PLS_INTEGER;

FUNCTION Init_My_AA

RETURN My_AA;

END My_Types;

CREATE OR REPLACE PACKAGE BODY My_Types

FUNCTION Init_My_AA

RETURN My_AA

Ret My_AA;

BEGIN

Ret (-10) := '-ten';

Ret (0) := 'zero';

Ret (1) := 'one';

Ret (2) := 'two';

Ret (3) := 'three';

Ret (4) := 'four';

Ret (9) := 'nine';

RETURN Ret;

END Init_My_AA;

END My_Types;

DECLARE

v CONSTANT My_Types.My_AA := My_Types.Init_My_AA ();

BEGIN

DECLARE

Idx PLS_INTEGER := v.FIRST ();

BEGIN

WHILE Idx IS NOT NULL

LOOP

DBMS_OUTPUT.PUT_LINE (TO_CHAR (Idx, '999') || LPAD (v (Idx), 7));

Idx := v.NEXT (Idx);

END LOOP;

END;

--Example 5–4 VARRAY (VARIABLE-SIZE ARRAY)

DECLARE

TYPE Foursome IS VARRAY (4) OF VARCHAR2 (15); -- VARRAY type

-- varray variable initialized with constructor:

team Foursome

:= Foursome ('John',

'Mary',

'Alberto',

'Juanita');

PROCEDURE print_team (heading VARCHAR2)

BEGIN

DBMS_OUTPUT.PUT_LINE (heading);

FOR i IN 1 .. 4

LOOP

DBMS_OUTPUT.PUT_LINE (i || '.' || team (i));

END LOOP;

DBMS_OUTPUT.PUT_LINE ('---');

END;

BEGIN

print_team ('2001 Team:');

team (3) := 'Pierre'; -- Change values of two elements

team (4) := 'Yvonne';

print_team ('2005 Team:');

-- Invoke constructor to assign new values to varray variable:

team :=

Foursome ('Arun',

'Amitha',

'Allan',

'Mae');

print_team ('2009 Team:');

END;

--Example 5–5 NESTED TABLE OF LOCAL TYPE

DECLARE

TYPE Roster IS TABLE OF VARCHAR2 (15); -- nested table type

-- nested table variable initialized with constructor:

names Roster

:= Roster ('D Caruso',

'J Hamil',

'D Piro',

'R Singh');

PROCEDURE print_names (heading VARCHAR2)

BEGIN

DBMS_OUTPUT.PUT_LINE (heading);

FOR i IN names.FIRST .. names.LAST

LOOP -- For first to last element

DBMS_OUTPUT.PUT_LINE (names (i));

END LOOP;

DBMS_OUTPUT.PUT_LINE ('---');

END;

BEGIN

print_names ('Initial Values:');

names (3) := 'P Perez'; -- Change value of one element

print_names ('Current Values:');

names := Roster ('A Jansen', 'B Gupta'); -- Change entire table

print_names ('Current Values:');

END;

--Example 5–6 NESTED TABLE OF STANDALONE TYPE

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type)

AUTHID DEFINER

i NUMBER;

BEGIN

i := nt.FIRST;

IF i IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('nt is empty');

ELSE

WHILE i IS NOT NULL

LOOP

DBMS_OUTPUT.PUT ('nt.(' || i || ') = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR (nt (i)), 'NULL'));

i := nt.NEXT (i);

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE ('---');

END print_nt;

DECLARE

nt nt_type := nt_type (); -- nested table variable initialized to empty

BEGIN

print_nt (nt);

nt :=

nt_type (90,

29,

58);

print_nt (nt);

END;

--Example 5–7 Initializing Collection (VARRAY) VARIABLE TO EMPTY

DECLARE

TYPE Foursome IS VARRAY (4) OF VARCHAR2 (15);

team Foursome := Foursome (); -- initialize to empty

PROCEDURE print_team (heading VARCHAR2)

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;

--Example 5–8 DATA TYPE COMPATIBILITY FOR Collection Assignment

DECLARE

TYPE triplet IS VARRAY (3) OF VARCHAR2 (15);

TYPE trio IS VARRAY (3) OF VARCHAR2 (15);

group1 triplet := triplet ('Jones', 'Wong', 'Marceau');

group2 triplet;

group3 trio;

BEGIN

group2 := group1; -- succeeds

group3 := group1; -- fails

END;

--Example 5–9 Assigning NULL VALUE TO NESTED TABLE VARIABLE

DECLARE

TYPE dnames_tab IS TABLE OF VARCHAR2 (30);

dept_names dnames_tab

:= dnames_tab ('Shipping',

'Sales',

'Finance',

'Payroll'); -- Initialized to non-null value

empty_set dnames_tab; -- Not initialized, therefore null

PROCEDURE print_dept_names_status

BEGIN

IF dept_names IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('dept_names is null.');

ELSE

DBMS_OUTPUT.PUT_LINE ('dept_names is not null.');

END IF;

END print_dept_names_status;

BEGIN

print_dept_names_status;

dept_names := empty_set; -- Assign null collection to dept_names.

print_dept_names_status;

dept_names :=

dnames_tab ('Shipping',

'Sales',

'Finance',

'Payroll'); -- Re-initialize dept_names

print_dept_names_status;

END;

--Example 5–10 Assigning SET Operation Results TO NESTED TABLE VARIABLE

DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1 nested_typ := nested_typ (1, 2, 3);

nt2 nested_typ := nested_typ (3, 2, 1);

nt3 nested_typ

:= nested_typ (2,

3);

nt4 nested_typ := nested_typ (1, 2, 4);

answer nested_typ;

PROCEDURE print_nested_table (nt nested_typ)

output VARCHAR2 (128);

BEGIN

IF nt IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('Result: null set');

ELSIF nt.COUNT = 0

THEN

DBMS_OUTPUT.PUT_LINE ('Result: empty set');

ELSE

FOR i IN nt.FIRST .. nt.LAST

LOOP -- For first to last element

output := output || nt (i) || ' ';

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Result: ' || output);

END IF;

END print_nested_table;

BEGIN

answer := nt1 MULTISET UNION nt4;

print_nested_table (answer);

answer := nt1 MULTISET UNION nt3;

print_nested_table (answer);

answer := nt1 MULTISET UNION DISTINCT nt3;

print_nested_table (answer);

answer := nt2 MULTISET INTERSECT nt3;

print_nested_table (answer);

answer := nt2 MULTISET INTERSECT DISTINCT nt3;

print_nested_table (answer);

answer := SET (nt3);

print_nested_table (answer);

answer := nt3 MULTISET EXCEPT nt2;

print_nested_table (answer);

answer := nt3 MULTISET EXCEPT DISTINCT nt2;

print_nested_table (answer);

END;

--Example 5–11 Two-Dimensional VARRAY (VARRAY OF VARRAYS)

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;

--Example 5–12 NESTED TABLES OF NESTED TABLES AND VARRAYS OF Integers

DECLARE

TYPE tb1 IS TABLE OF VARCHAR2 (20); -- nested table of strings

vtb1 tb1 := tb1 ('one', 'three');

TYPE ntb1 IS TABLE OF tb1; -- nested table of nested tables of strings

vntb1 ntb1 := ntb1 (vtb1);

TYPE tv1 IS VARRAY (10) OF INTEGER; -- varray of integers

TYPE ntb2 IS TABLE OF tv1; -- nested table of varrays of integers

vntb2 ntb2 := ntb2 (tv1 (3, 5), tv1 (5, 7, 3));

BEGIN

vntb1.EXTEND;

vntb1 (2) := vntb1 (1);

vntb1.DELETE (1); -- delete first element of vntb1

vntb1 (2).DELETE (1); -- delete first string from second table in nested table

END;

--Example 5–13 NESTED TABLES OF Associative Arrays AND VARRAYS OF Strings

DECLARE

TYPE tb1 IS TABLE OF INTEGER

INDEX BY PLS_INTEGER; -- associative arrays

v4 tb1;

v5 tb1;

TYPE aa1 IS TABLE OF tb1

INDEX BY PLS_INTEGER; -- associative array of

v2 aa1; -- associative arrays

TYPE va1 IS VARRAY (10) OF VARCHAR2 (20); -- varray of strings

v1 va1 := va1 ('hello', 'world');

TYPE ntb2 IS TABLE OF va1

INDEX BY PLS_INTEGER; -- associative array of varrays

v3 ntb2;

BEGIN

v4 (1) := 34; -- populate associative array

v4 (2) := 46456;

v4 (456) := 343;

v2 (23) := v4; -- populate associative array of associative arrays

v3 (34) :=

va1 (33,

456,

656,

343); -- populate associative array varrays

v2 (35) := v5; -- assign empty associative array to v2(35)

v2 (35) (2) := 78;

END;

--Example 5–14 Comparing VARRAY AND NESTED TABLE Variables TO NULL

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;

--Example 5–15 Comparing NESTED TABLES FOR Equality AND Inequality

DECLARE

TYPE dnames_tab IS TABLE OF VARCHAR2 (30); -- element type is not record type

dept_names1 dnames_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;

--Example 5–16 Comparing NESTED TABLES WITH SQL MULTISET Conditions

DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1 nested_typ := nested_typ (1, 2, 3);

nt2 nested_typ := nested_typ (3, 2, 1);

nt3 nested_typ

:= nested_typ (2,

3);

nt4 nested_typ := nested_typ (1, 2, 4);

PROCEDURE testify (truth BOOLEAN := NULL, quantity NUMBER := NULL)

BEGIN

IF truth IS NOT NULL

THEN

DBMS_OUTPUT.PUT_LINE (

CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);

END IF;

IF quantity IS NOT NULL

THEN

DBMS_OUTPUT.PUT_LINE (quantity);

END IF;

END;

BEGIN

testify (truth => (nt1 IN (nt2, nt3, nt4))); -- condition

testify (truth => (nt1 SUBMULTISET OF nt3)); -- condition

testify (truth => (nt1 NOT SUBMULTISET OF nt4)); -- condition

testify (truth => (4 MEMBER OF nt1)); -- condition

testify (truth => (nt3 IS A SET)); -- condition

testify (truth => (nt3 IS NOT A SET)); -- condition

testify (truth => (nt1 IS EMPTY)); -- condition

testify (quantity => (CARDINALITY (nt3))); -- function

testify (quantity => (CARDINALITY (SET (nt3)))); -- 2 functions

END;

--TABLE 5–2 Collection Methods

--Method

TYPE Description

DELETE

Procedure

Deletes elements from collection.

TRIM

Procedure

Deletes elements from end of varray or nested table.

EXTEND

Procedure

Adds elements to end of varray or nested table.

EXISTS

Function

Returns TRUE if and only if specified element of varray or nested table exists.

FIRST

Function

Returns first index in collection.

LAST

Function

Returns last index in collection.

COUNT

Function

Returns number of elements in collection.

LIMIT

Function

Returns maximum number of elements that collection can have.

PRIOR

Function

Returns index that precedes specified index.

Function

Returns index that succeeds specified index.

--Example 5–17 DELETE Method with Nested Table

DECLARE

nt nt_type := nt_type(11, 22, 33, 44, 55, 66);

BEGIN

print_nt (nt);

nt.DELETE (2); -- Delete second element

print_nt (nt);

nt (2) := 2222; -- Restore second element

print_nt (nt);

nt.DELETE (2, 4); -- Delete range of elements

print_nt (nt);

nt (3) := 3333; -- Restore third element

print_nt (nt);

nt.DELETE; -- Delete all elements

print_nt (nt);

END;

--Example 5–18 DELETE Method WITH Associative ARRAY INDEXED BY STRING

DECLARE

TYPE aa_type_str IS TABLE OF INTEGER

INDEX BY VARCHAR2 (10);

aa_str aa_type_str;

PROCEDURE print_aa_str

i VARCHAR2 (10);

BEGIN

i := aa_str.FIRST;

IF i IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('aa_str is empty');

ELSE

WHILE i IS NOT NULL

LOOP

DBMS_OUTPUT.PUT ('aa_str.(' || i || ') = ');

DBMS_OUTPUT.PUT_LINE (NVL (TO_CHAR ( (aa_str (i)), 'NULL')) ;i := aa_str.NEXT(i);

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('---');

END print_aa_str;

BEGIN

aa_str('M') := 13;

aa_str('Z') := 26;

aa_str('C') := 3;

print_aa_str;

aa_str.DELETE; -- Delete all elements

print_aa_str;

aa_str('M') := 13; -- Replace deleted element with same value

aa_str('Z') := 260; -- Replace deleted element with new value

aa_str('C') := 30; -- Replace deleted element with new value

aa_str('W') := 23; -- Add new element

aa_str('J') := 10; -- Add new element

aa_str('N') := 14; -- Add new element

aa_str('P') := 16; -- Add new element

aa_str('W') := 23; -- Add new element

aa_str('J') := 10; -- Add new element

print_aa_str;

aa_str.DELETE('C'); -- Delete one element

print_aa_str;

aa_str.DELETE('N','W'); -- Delete range of elements

print_aa_str;

aa_str.DELETE('Z','M'); -- Does nothing

print_aa_str;

END;

--Example 5–19 TRIM Method WITH NESTED TABLE

DECLARE

nt nt_type

:= nt_type (11,

22,

33,

44,

55,

66);

BEGIN

print_nt (nt);

nt.TRIM; -- Trim last element

print_nt (nt);

nt.DELETE (4); -- Delete fourth element

print_nt (nt);

nt.TRIM (2); -- Trim last two elements

print_nt (nt);

END;

--Example 5–20 EXTEND Method WITH NESTED TABLE

DECLARE

nt nt_type := nt_type (11, 22, 33);

BEGIN

print_nt (nt);

nt.EXTEND (2, 1); -- Append two copies of first element

print_nt (nt);

nt.DELETE (5); -- Delete fifth element

print_nt (nt);

nt.EXTEND; -- Append one null element

print_nt (nt);

END;

--Example 5–21 EXISTS Method WITH NESTED TABLE

DECLARE

TYPE NumList IS TABLE OF INTEGER;

n NumList

:= NumList (1,

7);

BEGIN

n.DELETE (2); -- Delete second element

FOR i IN 1 .. 6

LOOP

IF n.EXISTS (i)

THEN

DBMS_OUTPUT.PUT_LINE ('n(' || i || ') = ' || n (i));

ELSE

DBMS_OUTPUT.PUT_LINE ('n(' || i || ') does not exist');

END IF;

END LOOP;

END;

--Example 5–22 FIRST AND LAST VALUES FOR Associative ARRAY INDEXED BY PLS_INTEGER

DECLARE

TYPE aa_type_int IS TABLE OF INTEGER

INDEX BY PLS_INTEGER;

aa_int aa_type_int;

PROCEDURE print_first_and_last

BEGIN

DBMS_OUTPUT.PUT_LINE ('FIRST = ' || aa_int.FIRST);

DBMS_OUTPUT.PUT_LINE ('LAST = ' || aa_int.LAST);

END print_first_and_last;

BEGIN

aa_int (1) := 3;

aa_int (2) := 6;

aa_int (3) := 9;

aa_int (4) := 12;

DBMS_OUTPUT.PUT_LINE ('Before deletions:');

print_first_and_last;

aa_int.DELETE (1);

aa_int.DELETE (4);

DBMS_OUTPUT.PUT_LINE ('After deletions:');

print_first_and_last;

END;

--Example 5–23 FIRST AND LAST VALUES FOR Associative ARRAY INDEXED BY STRING

DECLARE

TYPE aa_type_str IS TABLE OF INTEGER

INDEX BY VARCHAR2 (10);

aa_str aa_type_str;

PROCEDURE print_first_and_last

BEGIN

DBMS_OUTPUT.PUT_LINE ('FIRST = ' || aa_str.FIRST);

DBMS_OUTPUT.PUT_LINE ('LAST = ' || aa_str.LAST);

END print_first_and_last;

BEGIN

aa_str ('Z') := 26;

aa_str ('A') := 1;

aa_str ('K') := 11;

aa_str ('R') := 18;

DBMS_OUTPUT.PUT_LINE ('Before deletions:');

print_first_and_last;

aa_str.DELETE ('A');

aa_str.DELETE ('Z');

DBMS_OUTPUT.PUT_LINE ('After deletions:');

print_first_and_last;

END;

--Example 5–24 Printing VARRAY WITH FIRST AND LAST IN FOR LOOP

DECLARE

TYPE team_type IS VARRAY (4) OF VARCHAR2 (15);

team team_type;

PROCEDURE print_team (heading VARCHAR2)

BEGIN

DBMS_OUTPUT.PUT_LINE (heading);

IF team IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('Does not exist');

ELSIF team.FIRST IS NULL

THEN

DBMS_OUTPUT.PUT_LINE ('Has no members');

ELSE

FOR i IN team.FIRST .. team.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (i || '. ' || team (i));

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE ('---');

END;

BEGIN

print_team ('Team Status:');

team := team_type (); -- Team is funded, but nobody is on it.

print_team ('Team Status:');

team := team_type ('John', 'Mary'); -- Put 2 members on team.

print_team ('Initial Team:');

team :=

team_type ('Arun',

'Amitha',

'Allan',

'Mae'); -- Change team.

print_team ('New Team:');

END;

ORACLE PROJECTS

MENUS

Tuesday, 14 January 2014

PL/SQL - Collections (Part -1 )