Oracle PL/SQL/Object Oriented Database/Varray

DML operations on collections.

  
SQL>
SQL> CREATE OR REPLACE TYPE BookList AS VARRAY(10) OF NUMBER(4);
  2  /
Type created.
SQL>
SQL>
SQL> CREATE TABLE class_material (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    required_reading BookList
  5  );
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2    v_CSBooks BookList := BookList(1000, 1001, 1002);
  3    v_HistoryBooks BookList := BookList(2001);
  4  BEGIN
  5    INSERT INTO class_material
  6      VALUES ("MUS", 100, BookList(3001, 3002));
  7
  8    INSERT INTO class_material VALUES ("CS", 102, v_CSBooks);
  9
 10    INSERT INTO class_material VALUES ("HIS", 101, v_HistoryBooks);
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from class_material;
DEP     COURSE
--- ----------
REQUIRED_READING
--------------------------------------------------------------------------------
MUS        100
BOOKLIST(3001, 3002)
CS         102
BOOKLIST(1000, 1001, 1002)
HIS        101
BOOKLIST(2001)

SQL>
SQL> drop table class_material;
Table dropped.
SQL>
SQL>

Varray of user types

 
SQL>
SQL>
SQL> create or replace type erratum_t as object
  2  ( code varchar2(4)
  3  , ch   number(2)
  4  , pg   number(3)
  5  , txt  varchar2(40)
  6  ) ;
  7  /
Type created.
SQL>
SQL> create or replace type errata_tab_t as table of erratum_t;
  2  /
Type created.
SQL>
SQL> describe errata_tab_t
 errata_tab_t TABLE OF ERRATUM_T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CODE                                               VARCHAR2(4)
 CH                                                 NUMBER(2)
 PG                                                 NUMBER(3)
 TXT                                                VARCHAR2(40)
SQL> drop type errata_tab_t force;
Type dropped.
SQL> drop type erratum_t    force;
Type dropped.
SQL>