SQL集合嵌套表varray
DECLARE
TYPE list_of_name_t IS TABLE OF person.first_name%TYPE INDEX BY PLS_INTEGER;
happyfamily list_of_name_t;
l_row PLS_INTEGER;
BEGIN
happyfamily (200002) :='Eli';
happyfamily (-1507) :='Steven';
happyfamily (-90): 'Chris';
happyfamily (88) :='Veva';

l_row := happyfamily.FIRST;
WHILE (l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE(happyfamily (l_row));
l_row := happyfamily.NEXT(l_row);
END LOOP;
END;

----
CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2(100)
/

DECLARE
happyfamily list_of_names_t := list_of_names_t ();
children list_of_names_t := list_of_names_t ();
parents list_of_names_t ;=list_of_namess_t();
BEGIN
happyfamily.EXTEND(3);
happyfamily (1) :='Eli';
happyfamily (2) :='Steven';
happyfamily (3) :='Chris';

children.EXTEND;
children (1) :='Chris';
children.EXTEND;
children (2) :='Eli';

parents :=happyfamily MULTISET EXCEPT children;

FOR l_row IN parents.FIRST .. parents.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(parents (l_row));
END LOOP;
END;

--------

CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
/
CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
/
CREATE TABLE family (
surname VARCHAR2(1000)
,parent_names first_names_t
,children_names child_names_t
);


DECLARE
parents first_names_t := first_names_t ();
children child_names_t := child_names_t ();
BEGIN
parents.EXTEND (2);
parents (1) :='Samuel';
parents (2) :='Charina';

children.EXTEND;
children (1) :='Feather';

INSERT INTO family ( surname, parents_names,children_names )
VALUES('Assurty',parents,children);
END;

SELECT * FROM family;