ORACLE 8에서는 2가지의 집합 TYPE을 제공한다. :  nested table과 varray이다.
집합(collections)은 3GL program에서의 array처럼 작용한다. 다른 점이라면
1차원 밖에는 제공되지 않으며, integer 값에의해 indexing되는 것이다.

NESTED TABLE
=============

TABLE TYPE의 item을 nested table이라고 부른다.
nested table은 복수 개의 column을 포함할 수 있으며, variable, parameter,
result, attribute, column으로 사용될 수 있다.

이것들은 database 내에서 하나의 table의 한 column으로 생각된다.
실제의 data들은 특별한 순서없이 저장되나, pl/sql에서 조회할 때는
1부터 시작하는 연속적인 번호를 부여받게 된다.

nested table은 SQL, Pro*C, OCI, PL/SQL에서 모두 사용될 수 있으며,
이 때 range값은 1에서 2147483647까지 가능하다.

pl/sql 내에서 nested table은 일차원 array와 같게 처리되지만, array는 상한
선이 있으나 nested table은 상한선이 없다는 점에서 다르다고 할 수 있다.

nested table의 size는 dynamic하게 증가될 수 있다.



* NESTED TABLE의 사용예
--------------------------------------

/*
type의 삭제
*/
DROP TYPE course FORCE;
DROP TYPE courseList FORCE;

/*
drop type 시에 발생하기 쉬운 error */
/*
02303, "cannot drop or replace a type with type or table dependents"

// *Cause:  An attempt was made to drop or replace a type that has dependents.

// *Action: Drop all type(s) and table(s) depending on the type,
//          then retry the operation, or use the FORCE option.
*/


/*
type의 생성
*/
CREATE TYPE course AS OBJECT (
course_no number(4),
title varchar2(35),
credits number(1))
/
CREATE TYPE CourseList AS TABLE OF course
/

/*
nested table을 포함하는  table의 생성과 삭제
*/
DROP TABLE department_nested CASCADE CONSTRAINTS
/
CREATE TABLE department_nested (
name varchar2(20),
director varchar2(20),
office varchar2(20),
courses CourseList)
NESTED TABLE courses STORE AS tab_courses
/

/* nested table을 삭제하고자 할때
drop table nested_table_name을 사용할 수 없다.
이때 발생할 수 있는 error는 다음과 같다.

22914, 00000,"DROP of nested tables not supported"

//  *Cause:  Attempted to DROP a nested table.

//  *Action: nested tables cannot be explicitly dropped. nested tables
//       can only be dropped by dropping their containing parent table.
*/


/*
INDEX의 생성
*/
CREATE UNIQUE INDEX p_dept ON department_nested(name)
/

/*
nested table에 대한 index 생성
*/
CREATE UNIQUE INDEX n_course ON tab_courses(course_no)
/

/*
잘못된 sql: nested table column에 대한 index를 생성하려 할 때의 error 유발
*/
create index un_course on department_nested(courses)

22904, 00000, "invalid reference to a nested table column"
//  *Cause:  invalid use of a nested table column

//  *Action: remove invalid reference to the nested table column
*/

/*
row 전체에 대한 manipulating 예
*/
INSERT INTO department_nested
VALUES('ENGLISH','Lynn Saunders','BREAKSTONE Hall 205',
CourseList(Course(1002,'Expository Writing',3),
   Course(2020,'Film and Literature',4)
)
)
/
UPDATE department_nested
SET courses =
CourseList(Course(2810,'Discursive Writing',4),
   Course(3010,'Modern English Grammer',3)
)
WHERE name = 'ENGLISH'
/

INSERT INTO DEPARTMENT_NESTED (NAME, DIRECTOR, OFFICE)        VALUES('MATH','HWANKIM','SEOUL');
/
/*
위의 형태로 NESTED TABLE 없이 INSERT 한 경우는 NESTED TABLE 이 정의
되어 있지 않으므로 추가로 세부내역을 INSERT 할 수 없다.
*/

INSERT INTO
         THE(SELECT courses FROM department_nested WHERE name = 'MATH'
         values(2418,'Regression',4)

ERROR at line 1:
ORA-22908: reference to NULL table value

22908, 00000, "reference to NULL table value"
//  *Cause:  The evaluation of the THE subquery or nested table column
//           resulted in a NULL value implying a NULL table instance.
//           The THE subquery or nested table column must identify a
//           single non-NULL table instance.
//  *Action: Ensure that the evaluation of the THE subquery or nested table
//           column results in a single non-null table instance. If
//           happening in the context of an insert statement where the THE
//           subquery is the target of an insert, then ensure that an empty
//           nested table instance is created by updating the nested table
//           column of the parent table's row specifying an empty nested
//           table Constructor.

/* 다음과 같이 사용해야 한다. */

INSERT INTO DEPARTMENT_NESTED
   VALUES('KOREAN','Daniel','SEOUL',courselist())
/

/*
nested table의 각 row에 대한 manipulating예 반드시 "THE" keyword를 사용하
여야 한다. THE 이후의 subquery에서는 반드시 single column value을 return하
여야 하며,single column value 이상인 경우에는 runtime시에 error가 발생한다.
*/
INSERT INTO
THE(SELECT courses FROM department_nested WHERE name = 'ENGLISH')
VALUES(2418,'Modern Science Fiction',3)
/
UPDATE
THE( SELECT courses FROM department_nested WHERE name = 'ENGLISH')
SET credits = credits -3
WHERE course_no = 3010
/
DELETE
THE (SELECT courses FROM department_nested WHERE name = 'ENGLISH')
where credits = 0
/

/*
SQL*Plus에서의 결과 확인
*/
SELECT * FROM department_nested
/
/*
NAME                 DIRECTOR             OFFICE
-------------------- -------------------- --------------------
COURSES(COURSE_NO, TITLE, CREDITS)
--------------------------------------------------------------
ENGLISH              Lynn Saunders        BREAKSTONE Hall 205
COURSELIST(COURSE(2810, 'Discursive Writing', 4), COURSE(2418, 'Modern Science F
iction', 3))
*/


VARRAY
=======
또 다른 TYPE은 VARRAY이다.
VARRAY의 maximum size는 define시에 지정한 값으로 지정된다.

SQL*PLUS에서는 VARRAY의 요소는 개별적으로 handling할 수 없고,
PL/SQL, OCI, Pro*c 등에서 array style subscript로써 handling된다.


* VARRAY의 사용 예
--------------------------------------
DROP TYPE project FORCE
/
DROP TYPE ProjectList FORCE
/
CREATE TYPE project AS OBJECT (
project_no number(2),
title varchar2(35),
cost number(7,2)
)
/
CREATE TYPE ProjectList AS VARRAY(50) OF project
/
DROP TABLE department_varray
/
CREATE TABLE department_varray (
dept_id number(2),
name varchar2(15),
budget number(11,2),
projects ProjectList
)
/
DROP INDEX un_department_varray
/
CREATE INDEX un_department_varray ON department_varray(dept_id)
/
INSERT INTO department_varray
VALUES(30,'ACCOUNTING',1205700,
ProjectList( Project(1,'Design New Expense Report',3250),
Project(2,'Outsource Payroll',12350),
Project(3,'Evaluate Merger Proposal',2750)
)
)
/
INSERT INTO department_varray
VALUES(60,'SECURITY',750400,
ProjectList(Project(1,'Issue New Employee Budges',13500),
Project(2,'Find Missing Computer Chips',2750),
Project(3,'Upgrade Alarm System',3350)
)
)
/
UPDATE department_varray
SET projects =
ProjectList(Project(1,'Issue New Employee Budges',13500),
Project(2,'Develop New Patrol Plan',1250),
Project(3,'Inspect Emergency Exits',1900)
)
WHERE dept_id = 60
/
DELETE FROM department_varray WHERE dept_id = 30
/

/*
VARRAY를 사용한 table에서 VARRAY의 각 row를 manipulate하려면
반드시 PL/SQL procedural statement를 사용하여야 한다.

VARRAY사용시에는 "THE" keyword를 사용할 수 없다.
*/
DECLARE
v_dept_id number;
v_name varchar2(15);
v_project ProjectList;
BEGIN
SELECT dept_id,name,projects INTO v_dept_id,v_name,v_project
FROM department_varray
WHERE dept_id = 60;
DBMS_OUTPUT.PUT_LINE('******'||to_char(v_dept_id)||
'*****'||v_name||'******');
FOR i IN v_project.FIRST..v_project.LAST LOOP
           DBMS_OUTPUT.PUT_LINE(to_char(v_project(i).project_no)||'*'||
v_project(i).title||'*'||
to_char(v_project(i).cost)||' -> '||
to_char(v_project(i).cost*2)
);
v_project(i).cost := v_project(i).cost*2;
END LOOP;
UPDATE department_varray
SET projects = v_project
WHERE dept_id =60;
END;
/

/*
결과
******60*****SECURITY******
1*Issue New Employee Budges*13500 -> 27000
2*Develop New Patrol Plan*1250 -> 2500
3*Inspect Emergency Exits*1900 -> 3800
*/

SELECT * FROM department_varray
/

/* 결과
   DEPT_ID NAME                BUDGET
---------- --------------- ----------
PROJECTS(PROJECT_NO, TITLE, COST)
-----------------------------------------------------------------------
        60 SECURITY            750400
PROJECTLIST(PROJECT(1, 'Issue New Employee Budges', 27000), PROJECT(2, 'Develop
New Patrol Plan', 2500), PROJECT(3, 'Inspect Emergency Exits', 3800))

*/



* COLLECTION에서 사용할 수 있는 METHOD들
=========================================

o. EXISTS
EXISTS(n)은 만약 collection중 n번째 요소가 존재한다면 TRUE를 return한다.
반대의 경우는 FALSE를 return하게 된다. 또한  range밖의 값을 지정한 경우에도 FALSE를 return하게 된다.
IF courses.EXISTS(i) THEN
courses(i) := new_course;
.........

o. COUNT
COUNT는 현재 collection이 가지고 있는 element의 갯수를 return한다.
varray에서는 COUNT의 값은 언제나 LAST의 값과 동일하다.
nested table에서는 COUNT의 값은 일반적으로 LAST의 값과 동일하다.
그러나 만약 nested table의 중간 element를 delete하였다면,
COUNT는 LAST보다 작아지게 된다.

IF projects.COUNT = 15 THEN
......

혹은
FOR  i IN 1..projects.COUNT LOOP
.......

o. LIMIT
nested table에서는 maximun size가 없으므로 LIMIT는 NULL을 return하게 된다.
varray에서는 define시에 지정한 maximum값이 return된다.

o. FIRST/LAST
FIRST와 LAST는 collection내의 first/last index번호를 return한다.

o. PRIOR/NEXT
PRIOR(n)은 collection내의 n-1번째의 index number를 return한다.
없는 경우에는 NULL을 return한다.

n := courses.PRIOR(courses.FIRST);

o. EXTEND
collection의 size를 증가시키기 위해 EXTEND를 사용한다.

EXTEND는 3가지의 방식을 사용한다.
. EXTEND : 하나의  null element를 생성한다.
. EXTEND(n) : n개의  null element를 생성한다.
. EXTEND(n,i) : i번째 element의 n배 만큼의  null element를 생성한다.

courses.EXTEND(5,1);   -- 1번째 element의 5배를 생성한다.

EXTEND는 null collection을 자동으로 초기화하는데 사용할 수 없다.
예를 들어 TABLE이나 VARRAY type에 NOT NULL constraint를 걸어놓은 경우하면,
단순한 EXTEND를 사용할 수 없다.

element를 delete할 경우 pl/sql은 그 번호를 저장하고 있어서, extend를 하면
다음 element를 생성하게 된다.

example :  
DECLARE
TYPE CourseList IS TABLE OF varchar2(10);
courses CourseList;
BEGIN
courses := CourseList('AAA','BBB','CCC');
courses.DELETE(3);
/* 3번 element의 위치를 저장하여, 다음의 문장에서 3번
                   element가 아닌 4번 element를 추가하게 된다.
*/

courses.EXTEND;
/* 4번이 null element로 생성된 것이다.
*/

courses(4) := 'DDD';
END;

o. TRIM
TRIM은 2가지의 방식을 사용한다.
. TRIM : collection에서 마지막 한개의 element를 지운다.
. TRIM(n) : collection에서 마지막에서 n개의 element를 지운다.
ex: corses.TRIM(3);  -- 뒤에서 3개의 element를 지운다.

n의 값이 COUNT보다 클경우 SUBCRIPT_BEYOND_COUNT  exception을 만나게 된다.

o. DELETE
DELETE는 3가지 방식을 사용한다.
. DELETE : collection의 모든 element를 삭제한다.
. DELETE(n) : nested table에서 n번째 element를 삭제한다.
. DELETE(m,n) : m에서 n번째 까지의 element를 삭제한다.

DELETE할 element가 존재하지 않는다 하여도 exception이 발생하지는 않는다.

nested table로 사용하는 memory의 양은 dynamc하게 증감하게 된다
table전체를 delete하게 되면, 모든 memory가 free된다.



NESTED TABLE과 VARRAY의 다른점
==============================
o. nested table의 상한선이 없으나, varray는 maximum size가 지정된다.
o. nested table에서는 각각의 element들에 대한 delete가 가능하나 varray에서
   는 안된다.

o. varray의 자료는 같은 tablespace 내에 저장되나, nested table의 자료는
   별도의  다른 table에 저장된다.
o. nested table에는 index를 생성하여 사용할 수 있으나,varray에서는 사용할
   수 없다.