Oracle PL/SQL Tutorial/Collections/Nested Tables

Материал из SQL эксперт
Версия от 10:04, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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.



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>


cardinality

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>


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.



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.


Deleting internal elements from a collection

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>


Insert value into table with nested type colunm

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>


Nested Tables

  1. A nested table is an unordered set of any number of elements, all of the same data type.
  2. A nested table has a single column.
  3. The type of that column may be a built-in database type or an object type.
  4. 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.
  5. You can insert, update, and delete individual elements in a nested table.



declare
  type <NestedTable> is table of <ElementType>;
...
create or replace type <NestedTable> is table of <ElementType>;


Nested type column

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>


Table with subquery

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.


Type alias for user-defined type in select statement

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Collections/Nested_Tables&oldid=2916»