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>