Oracle PL/SQL/Object Oriented Database/Varray

Материал из SQL эксперт
Перейти к: навигация, поиск

DML operations on collections.

   <source lang="sql">
 

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>

 </source>
   
  


Varray of user types

   <source lang="sql">

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>

 </source>