[color=brown][size=x-large]1.Using an associative array[/size][/color]
[color=red] Chris
Steven
Veva
Eli[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]2.Using a nested table[/size][/color]
[color=red] Steven
Veva[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]3.Using a VARRAY[/size][/color]
[color=red] SURNAME
PARENT_NAMES
CHILDREN_NAMES
--------------------------------------------
Assurty
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')[/color]
1 DECLARE
2 TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
3 INDEX BY PLS_INTEGER;
4 happyfamily list_of_names_t;
5 l_row PLS_INTEGER;
6 BEGIN
7 happyfamily (2020202020) := 'Eli';
8 happyfamily (-15070) := 'Steven';
9 happyfamily (-90900) := 'Chris';
10 happyfamily (88) := 'Veva';
11
12 l_row := happyfamily.FIRST;
13
14 WHILE (l_row IS NOT NULL)
15 LOOP
16 DBMS_OUTPUT.put_line (happyfamily (l_row));
17 l_row := happyfamily.NEXT (l_row);
18 END LOOP;
19* END;
[color=red] Chris
Steven
Veva
Eli[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]2.Using a nested table[/size][/color]
REM Section A
SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
2 /
Type created.
REM Section B
SQL>
1 DECLARE
2 happyfamily list_of_names_t := list_of_names_t ( );
3 children list_of_names_t := list_of_names_t ( );
4 parents list_of_names_t := list_of_names_t ( );
5 BEGIN
6 happyfamily.EXTEND (4);
7 happyfamily (1) := 'Eli';
8 happyfamily (2) := 'Steven';
9 happyfamily (3) := 'Chris';
10 happyfamily (4) := 'Veva';
11
12 children.EXTEND;
13 children (1) := 'Chris';
14 children.EXTEND;
15 children (2) := 'Eli';
16
17 parents := happyfamily MULTISET EXCEPT children;
18
19 FOR l_row IN parents.FIRST .. parents.LAST
20 LOOP
21 DBMS_OUTPUT.put_line (parents (l_row));
22 END LOOP;
23* END;
[color=red] Steven
Veva[/color]
--------------------------------------------------------------------------------------------------------------------------------------------
[color=brown][size=x-large]3.Using a VARRAY[/size][/color]
REM Section A
SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
2 /
Type created.
SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
2 /
Type created.
REM Section B
SQL> CREATE TABLE family (
2 surname VARCHAR2(1000)
3 , parent_names first_names_t
4 , children_names child_names_t
5 );
Table created.
REM Section C
SQL>
1 DECLARE
2 parents first_names_t := first_names_t ( );
3 children child_names_t := child_names_t ( );
4 BEGIN
5 parents.EXTEND (2);
6 parents (1) := 'Samuel';
7 parents (2) := 'Charina';
8 --
9 children.EXTEND;
10 children (1) := 'Feather';
11
12 --
13 INSERT INTO family
14 (surname, parent_names, children_names
15 )
16 VALUES ('Assurty', parents, children
17 );
18 END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM family
2 /
[color=red] SURNAME
PARENT_NAMES
CHILDREN_NAMES
--------------------------------------------
Assurty
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')[/color]