DML operations on collections. : Varray « Object Oriented Database « Oracle PL / SQL






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>

   
  








Related examples in the same category

1.Varray of user types