Oracle PL/SQL Tutorial/Collections/Nested Tables
Содержание
ANSI Support for Nested Tables
Equal and Not-Equal Operators
You use the equal (=) and not-equal (<>) operators to compare nested tables.
Two nested tables are considered equal when they satisfy all the following conditions:
All the tables are the same type.
All the tables are the same cardinality-that is, they contain the same number of elements.
All the elements are equal.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE equal_example AS
2 TYPE nestedTableType IS TABLE OF VARCHAR2(10);
3 myTable1 nestedTableType;
4 myTable2 nestedTableType;
5 myTable3 nestedTableType;
6 result BOOLEAN;
7 BEGIN
8 myTable1 := nestedTableType("A", "F", "G");
9 myTable2 := nestedTableType("B", "E", "H");
10 myTable3 := nestedTableType("C", "D", "I");
11
12 result := myTable1 = myTable2;
13 IF result THEN
14 DBMS_OUTPUT.PUT_LINE("myTable1 equal to myTable2" );
15 END IF;
16
17 result := myTable1 <> myTable3;
18 IF result THEN
19 DBMS_OUTPUT.PUT_LINE("myTable1 not equal to myTable3");
20 END IF;
21 END equal_example;
22 /
Procedure created. SQL> SQL> CALL equal_example(); myTable1 not equal to myTable3 Call completed. SQL></source>
cardinality
<source lang="sql">
SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
Table created. SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> create type errorNumberType 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 type errorNumberTableCollection as table of errorNumberType;
2 /
Type created. SQL> SQL> create table c as select * from courses; Table created. SQL> SQL> alter table c
2 add (errata errorNumberTableCollection) 3 nested table errata store as errata_tab;
Table altered. SQL> SQL> update c
2 set errata = errorNumberTableCollection();
10 rows updated. SQL> SQL> SQL> insert into table ( select errata from c where code = "SQL" )
2 values ( "SQL", 3, 45, "line." );
1 row created. SQL> SQL> select code, cardinality(errata)
2 from c 3 where errata is not empty;
CODE CARDINALITY(ERRATA)
-------------------
SQL 1 1 row selected. SQL> SQL> SQL> SQL> drop table courses; Table dropped. SQL> drop table c; Table dropped. SQL> drop type errorNumberTableCollection force; Type dropped. SQL> drop type errorNumberType force; Type dropped. SQL></source>
Creating a Nested Table Type
You create a nested table type using the CREATE TYPE statement:
If you don"t specify the maximum size of a nested table, you can insert any number of elements in a nested table.
<source lang="sql">
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ); 7 /
SQL> SQL> CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;
2 /
Type created.</source>
Deleting internal elements from a collection
<source lang="sql">
SQL> SQL> declare
2 type month_nt is table of VARCHAR2(20);
3 v_month_nt month_nt:=month_nt();
4 i number;
5 begin
6 v_month_nt.extend(3);
7 v_month_nt(1):="A";
8 v_month_nt(2):="B";
9 v_month_nt(3):="C";
10
11 v_month_nt.delete(2);
12 DBMS_OUTPUT.put_line("Count:"||v_month_nt.count);
13 DBMS_OUTPUT.put_line("Last:"||v_month_nt.last);
14
15 i:=v_month_nt.first;
16 loop
17 DBMS_OUTPUT.put_line(v_month_nt(i));
18 i:=v_month_nt.next(i);
19 if i is null
20 then
21 exit;
22 end if;
23 end loop;
24 end;
25 /
Count:2 Last:3 A C PL/SQL procedure successfully completed. SQL> SQL> SQL></source>
Insert value into table with nested type colunm
<source lang="sql">
SQL> SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
Table created. SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> create type errorNumberType 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 type errorNumberTableCollection as table of errorNumberType;
2 /
Type created. SQL> SQL> create table c as select * from courses; Table created. SQL> SQL> alter table c
2 add (errata errorNumberTableCollection) 3 nested table errata store as errata_tab;
Table altered. SQL> SQL> update c
2 set errata = errorNumberTableCollection();
10 rows updated. SQL> SQL> SQL> insert into table ( select errata from c where code = "SQL" )
2 values ( "SQL" , 3 , 45 , "Typo in last line." );
1 row created. SQL> SQL> drop table courses; Table dropped. SQL> drop table c; Table dropped. SQL> drop type errorNumberTableCollection force; Type dropped. SQL> drop type errorNumberType force; Type dropped. SQL></source>
Nested Tables
- A nested table is an unordered set of any number of elements, all of the same data type.
- A nested table has a single column.
- The type of that column may be a built-in database type or an object type.
- If the column in a nested table is an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type.
- You can insert, update, and delete individual elements in a nested table.
<source lang="sql">
declare
type <NestedTable> is table of <ElementType>;
... create or replace type <NestedTable> is table of <ElementType>;</source>
Nested type column
<source lang="sql">
SQL> SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
Table created. SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> create type errorNumberType 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 type errorNumberTableCollection as table of errorNumberType;
2 /
Type created. SQL> SQL> create table c as select * from courses; Table created. SQL> SQL> alter table c
2 add (errata errorNumberTableCollection) 3 nested table errata store as errata_tab;
Table altered. SQL> SQL> update c set errata = errorNumberTableCollection(); 10 rows updated. SQL> SQL> SQL> drop table courses; Table dropped. SQL> drop table c; Table dropped. SQL> drop type errorNumberTableCollection force; Type dropped. SQL> drop type errorNumberType force; Type dropped. SQL> SQL></source>
Table with subquery
<source lang="sql">
SQL> SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
Table created. SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> create type errorNumberType 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 type errorNumberTableCollection as table of errorNumberType;
2 /
Type created. SQL> SQL> create table c as select * from courses; Table created. SQL> SQL> alter table c
2 add (errata errorNumberTableCollection) 3 nested table errata store as errata_tab;
Table altered. SQL> SQL> update c set errata = errorNumberTableCollection(); 10 rows updated. SQL> SQL> SQL> insert into table ( select errata from c where code = "SQL" )
2 values ( "SQL", 3, 45, "line" );
1 row created. SQL> SQL> SQL> set linesize 70 SQL> break on row page SQL> SQL> SQL> update table ( select errata from c where code = "SQL") e
2 set e.ch = 7;
1 row updated. SQL> SQL> SQL> clear breaks breaks cleared SQL> SQL> SQL> drop table courses; Table dropped. SQL> drop table c; Table dropped. SQL> drop type errorNumberTableCollection force; Type dropped. SQL> drop type errorNumberType force; Type dropped.</source>
Type alias for user-defined type in select statement
<source lang="sql">
SQL> SQL> SQL> SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
Table created. SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> create type errorNumberType 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 type errorNumberTableCollection as table of errorNumberType;
2 /
Type created. SQL> SQL> create table c as select * from courses; Table created. SQL> SQL> alter table c
2 add (errata errorNumberTableCollection) 3 nested table errata store as errata_tab;
Table altered. SQL> SQL> update c set errata = errorNumberTableCollection(); 10 rows updated. SQL> SQL> SQL> insert into table ( select errata from c where code = "SQL" )
2 values ( "SQL", 3, 45, "line" );
1 row created. SQL> SQL> SQL> set linesize 70 SQL> break on row page SQL> SQL> SQL> SQL> set numwidth 3 SQL> col code format a7 SQL> col ch fold_before SQL> SQL> select code,c.description, e.ch, e.pg, e.txt
2 from c 3 join 4 table(c.errata) e 5 using (code);
CODE DESCRIPTION
------------------------------
CH PG TXT
------ ----------------------------------------
SQL SQL course
3 45 line
1 row selected. SQL> SQL> clear breaks breaks cleared SQL> SQL> SQL> drop table courses; Table dropped. SQL> SQL> drop type errorNumberTableCollection force; Type dropped. SQL> drop type errorNumberType force; Type dropped. SQL></source>
