в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:21Z

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.



<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==

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



<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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Collections/Nested Tables - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия