Oracle PL/SQL/Object Oriented Database/Varray — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
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>