Oracle10g Enhancements to Collections


Oracle10 g Enhancements to Collections

In this section, you ll learn about the following enhancements made to collections in Oracle10 g :

  • Associative arrays

  • Ability to change the size or precision of an element type

  • Ability to increase the number of elements in a varray

  • Ability to use varray columns in temporary tables

  • Ability to use a different tablespace for a nested table s storage table

  • ANSI support for nested tables

    Note  

    The various DDL statements that create the items shown in this section are contained in the collection_schema_10g.sql script. The script connects as collection_user with a password of collection_password , so if you re using a different password (or you re using a service name ), you ll need to edit the script first. Also, you must have first run collection_schema.sql before you attempt to run collection_schema_10g.sql .

Associative Arrays

An associative array is a set of key and value pairs. You can get the value from the array using the key (which may be a string) or an integer that specifies the position of the value in the array. The following example procedure customers_associative_array() performs the following tasks :

  • Creates an associative array type named assoc_array_typ .

  • Creates an object named customer_array of the type assoc_array_typ . customer_array is used to store the ages of customers by name. The key in customer_array is the name, and the value is the age.

  • Assigns age values to customers by name in customer_array .

  • Displays the contents of customer_array using DBMS_OUTPUT.PUT_LINE() .

     CREATE OR REPLACE PROCEDURE customers_associative_array AS  TYPE assoc_array_typ IS TABLE OF NUMBER INDEX BY VARCHAR2(15);  customer_array assoc_array_typ; BEGIN  customer_array('Jason') := 32;  customer_array('Steve') := 28;  customer_array('Fred') := 43;  customer_array('Cynthia') := 27;  DBMS_OUTPUT.PUT_LINE('customer_array[''Jason''] = '  customer_array('Jason'));  DBMS_OUTPUT.PUT_LINE('customer_array[''Steve''] = '  customer_array('Steve'));  DBMS_OUTPUT.PUT_LINE('customer_array[''Fred''] = '  customer_array('Fred'));  DBMS_OUTPUT.PUT_LINE('customer_array[''Cynthia''] = '  customer_array('Cynthia')); END customers_associative_array; / 

The following example connects as collection_user , sets server output on, and calls customers_associative_array() :

  CONNECT collection_user/collection_password   SET SERVEROUTPUT ON   CALL customers_associative_array();  customer_array['Jason'] = 32 customer_array['Steve'] = 28 customer_array['Fred'] = 43 customer_array['Cynthia'] = 27 

Changing the Size or Precision of an Element Type

You can change the size of an element type in a varray or nested table when the element type is one of the character, numeric, or raw types. Earlier in the section Creating a Varray Type, you saw the following example that creates a varray type and table:

 CREATE TYPE varray_address_typ AS VARRAY(2) OF VARCHAR2(50); / CREATE TABLE customers_with_varray (id INTEGER PRIMARY KEY,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  addresses varray_address_typ); 

The following example changes the size of the VARCHAR2 elements in varray_address_typ to 60:

 ALTER TYPE varray_address_typ MODIFY ELEMENT TYPE VARCHAR2(60) CASCADE; 

The CASCADE option propagates the change to the dependent objects in the database. In the example, the dependent object is the customers_with_varray table. You can also use the INVALIDATE option to invalidate dependent objects to immediately recompile the type.

Increasing the Number of Elements in a Varray

You can increase the number of elements in a varray. The following example increases the number of elements in varray_address_typ to 5:

 ALTER TYPE varray_address_typ MODIFY LIMIT 5 CASCADE; 

Using Varrays in Temporary Tables

You can use varrays in temporary tables. The following example creates a temporary table named cust_with_varray_temp_table that contains a varray named addresses of type varray_address_typ :

 CREATE GLOBAL TEMPORARY TABLE cust_with_varray_temp_table (id INTEGER PRIMARY KEY,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  addresses varray_address_typ); 
Note  

This table (along with the other tables featured in the rest of this chapter) is created by the collection_schema_10g.sql script.

Using a Different Tablespace for a Nested Table s Storage Table

By default, a nested table s storage table is created in the same tablespace as the parent table. In Oracle10 g , you can specify a different tablespace for a nested table s storage table. The following example creates a table named cust_with_nested_table that contains a nested table named addresses of type nested_table_address_typ . Notice the tablespace for the storage table nested_addresses2 is to be stored in the users tablespace:

 CREATE TABLE cust_with_nested_table (id INTEGER PRIMARY KEY,  first_name VARCHAR2(10),  last_name VARCHAR2(10),  addresses nested_table_address_typ) NESTED TABLE  addresses STORE AS  nested_addresses2 TABLESPACE users; 

You must have a tablespace named users in order for this example to work. For this reason I ve commented out the example in the collection_schema_10g.sql script. You can see all the tablespaces you have access to by performing the following query:

 SELECT tablespace_name FROM user_tablespaces; 

You can then use one of your available tablespaces in the previous CREATE TABLE example. Just edit the example in the collection_schema_10g.sql script and run the CREATE TABLE example by cutting and pasting the statement into SQL*Plus.

ANSI Support for Nested Tables

The American National Standards Institute (ANSI) specification includes a number of operators for multisets and arrays that are now implemented in Oracle varrays and nested tables. You ll learn about these new operators in the following sections.

Equal and Not-Equal Operators

You use the equal ( = ) and not-equal (<>) operators to compare nested tables, which 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.

The following procedure equal_example() performs the following tasks:

  • Creates a type named nested_table_typ .

  • Creates objects named customer_nested_table1 , customer_nested_table2 , and customer_nested_table3 of the type nested_table_typ . These objects are used to store names of customers.

  • Assigns the names Fred, George, and Susan to customer_nested_table1 and customer_nested_table2 . Assigns the names John, George, and Susan to customer_nested_table3 .

  • Uses = to compare customer_nested_table1 and customer_nested_table2 , which are equal. Displays the results using DBMS_OUTPUT.PUT_LINE() .

  • Uses <> to compare customer_nested_table1 and customer_nested_table3 , which are not equal because the first names are different. Displays the results using DBMS_OUTPUT.PUT_LINE() .

     CREATE OR REPLACE PROCEDURE equal_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  customer_nested_table2 nested_table_typ;  customer_nested_table3 nested_table_typ;  result BOOLEAN; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan');  customer_nested_table2 :=  nested_table_typ('Fred', 'George', 'Susan');  customer_nested_table3 :=  nested_table_typ('John', 'George', 'Susan');  result := customer_nested_table1 = customer_nested_table2;  IF result THEN  DBMS_OUTPUT.PUT_LINE('customer_nested_table1 equal to customer_nested_table2');  END IF;  result := customer_nested_table1 <> customer_nested_table3;  IF result THEN  DBMS_OUTPUT.PUT_LINE('customer_nested_table1 not equal to customer_nested_table3');  END IF; END equal_example; / 

The following example calls equal_example() , assuming you re still logged in as collection_user :

  CALL equal_example();  customer_nested_table1 equal to customer_nested_table2 customer_nested_table1 not equal to customer_nested_table3 

IN and NOT IN Operators

You can use the IN and NOT IN operators to check if the contents of one nested table appear or don t appear in the contents of another nested table. The following procedure in_example() illustrates the use of IN and NOT IN :

 CREATE OR REPLACE PROCEDURE in_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  customer_nested_table2 nested_table_typ;  customer_nested_table3 nested_table_typ;  result BOOLEAN; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan');  customer_nested_table2 :=   nested_table_typ('John', 'George', 'Susan');  customer_nested_table3 :=   nested_table_typ('Fred', 'George', 'Susan');  result := customer_nested_table3 IN (customer_nested_table1);  IF result THEN   DBMS_OUTPUT.PUT_LINE('customer_nested_table3 in customer_nested_table1');  END IF;  result := customer_nested_table3 NOT IN (customer_nested_table2);  IF result THEN   DBMS_OUTPUT.PUT_LINE('customer_nested_table3 not in customer_nested_table2');  END IF; END in_example; / 

The following example calls in_example() :

  CALL in_example();  customer_nested_table3 in customer_nested_table1 customer_nested_table3 not in customer_nested_table2 

SUBMULTISET Operator

You use the SUBMULTISET operator to check whether the contents of one nested table are a subset of another nested table. For example, Fred, George, and Susan are a subset of George, Fred, and Susan.

The following procedure submultiset_example() illustrates the use of SUBMULTISET :

 CREATE OR REPLACE PROCEDURE submultiset_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  customer_nested_table2 nested_table_typ;  customer_nested_table3 nested_table_typ;  result BOOLEAN; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan');  customer_nested_table2 :=   nested_table_typ('George', 'Fred', 'Susan');  result :=  customer_nested_table1 SUBMULTISET OF customer_nested_table2;  IF result THEN   DBMS_OUTPUT.PUT_LINE('customer_nested_table1 subset of customer_nested_table2');  END IF; END submultiset_example; / 

The following example calls submultiset_example() :

  CALL submultiset_example();  customer_nested_table1 subset of customer_nested_table2 

MULTISET Operator

You use the MULTISET operator to get a nested table whose elements are set to certain elements of two nested tables that are input to MULTISET . There are three MULTISET operators:

  • MULTISET UNION    Returns a nested table whose elements are set to the elements of the two input nested tables.

  • MULTISET INTERSECT    Returns a nested table whose elements are set to the elements that are common to the two input nested tables.

  • MULTISET EXCEPT    Returns a nested table whose elements are in the first input nested table but not in the second.

You may also use one of the following options with MULTISET :

  • ALL    Indicates that all applicable elements in the input nested tables are set in the returned nested table. ALL is the default.

  • DISTINCT    Indicates that only the distinct non-duplicate elements in the input nested tables are set in the returned nested table.

The following procedure multiset_example() illustrates the use of MULTISET :

 CREATE OR REPLACE PROCEDURE multiset_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  customer_nested_table2 nested_table_typ;  customer_nested_table3 nested_table_typ;  count_var INTEGER; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan');  customer_nested_table2 :=   nested_table_typ('George', 'Steve', 'Rob');  customer_nested_table3 :=   customer_nested_table1 MULTISET UNION customer_nested_table2;  DBMS_OUTPUT.PUT('UNION: ');  FOR count_var IN 1..customer_nested_table3.COUNT LOOP   DBMS_OUTPUT.PUT(customer_nested_table3(count_var)  ' ');  END LOOP;  DBMS_OUTPUT.PUT_LINE(' ');  customer_nested_table3 :=   customer_nested_table1 MULTISET UNION DISTINCT customer_nested_table2;  DBMS_OUTPUT.PUT('UNION DISTINCT: ');  FOR count_var IN 1..customer_nested_table3.COUNT LOOP   DBMS_OUTPUT.PUT(customer_nested_table3(count_var)  ' ');  END LOOP;  DBMS_OUTPUT.PUT_LINE(' ');  customer_nested_table3 :=   customer_nested_table1 MULTISET INTERSECT customer_nested_table2;  DBMS_OUTPUT.PUT('INTERSECT: ');  FOR count_var IN 1..customer_nested_table3.COUNT LOOP  DBMS_OUTPUT.PUT(customer_nested_table3(count_var)  ' ');  END LOOP;  DBMS_OUTPUT.PUT_LINE(' ');  customer_nested_table3 :=  customer_nested_table1 MULTISET EXCEPT customer_nested_table2;  DBMS_OUTPUT.PUT_LINE('EXCEPT: ');  FOR count_var IN 1..customer_nested_table3.COUNT LOOP  DBMS_OUTPUT.PUT(customer_nested_table3(count_var)  ' ');  END LOOP; END multiset_example; / 

The following example calls multiset_example() :

  CALL multiset_example();  UNION: Fred George Susan George Steve Rob UNION DISTINCT: Fred George Susan Steve Rob INTERSECT: George EXCEPT: 

CARDINALITY Operator

You use the CARDINALITY operator to get the number of elements in a nested table. The following procedure cardinality_example() illustrates the use of CARDINALITY :

 CREATE OR REPLACE PROCEDURE cardinality_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  cardinality_var INTEGER; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan');  cardinality_var := CARDINALITY(customer_nested_table1);  DBMS_OUTPUT.PUT_LINE('cardinality_var = '  cardinality_var); END cardinality_example; / 

The following example calls cardinality_example() :

  CALL cardinality_example();  cardinality_var = 3 

MEMBER OF Operator

You use the MEMBER OF operator to check whether an element is in a nested table. The following procedure member_of_example() illustrates the use of MEMBER OF :

 CREATE OR REPLACE PROCEDURE member_of_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  result BOOLEAN; BEGIN  customer_nested_table1 :=   nested_table_typ('Fred', 'George', 'Susan');  result := 'George' MEMBER OF customer_nested_table1;  IF result THEN   DBMS_OUTPUT.PUT_LINE('''George'' is a member');  END IF; END member_of_example; / 

The following example calls member_of_example() :

  CALL member_of_example();  'George' is a member 

SET Operator

The SET operator first converts a nested table into a set, removes duplicate elements from the set, and returns the set as a nested table. The following procedure set_example() illustrates the use of SET :

 CREATE OR REPLACE PROCEDURE set_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  customer_nested_table2 nested_table_typ;  count_var INTEGER; BEGIN  customer_nested_table1 :=   nested_table_typ('Fred', 'George', 'Susan', 'George');  customer_nested_table2 := SET(customer_nested_table1);  DBMS_OUTPUT.PUT('customer_nested_table2: ');  FOR count_var IN 1..customer_nested_table2.COUNT LOOP  DBMS_OUTPUT.PUT(customer_nested_table2(count_var)  ' ');  END LOOP;  DBMS_OUTPUT.PUT_LINE(' '); END set_example; / 

The following example calls set_example() :

  CALL set_example();  customer_nested_table2: Fred George Susan 

IS A SET Operator

You use the IS A SET operator to check if the elements in a nested table are distinct. The following procedure is_a_set_example() illustrates the use of IS A SET :

 CREATE OR REPLACE PROCEDURE is_a_set_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  result BOOLEAN; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan', 'George');  result := customer_nested_table1 IS A SET;  IF result THEN  DBMS_OUTPUT.PUT_LINE('Elements are all unique');  ELSE   DBMS_OUTPUT.PUT_LINE('Elements contain duplicates');  END IF; END is_a_set_example; / 

The following example calls is_a_set_example() :

  CALL is_a_set_example();  Elements contain duplicates 

IS EMPTY Operator

You use the IS EMPTY operator to check if a nested table doesn t contain elements. The following procedure, is_empty_example() , illustrates the use of IS EMPTY :

 CREATE OR REPLACE PROCEDURE is_empty_example AS  TYPE nested_table_typ IS TABLE OF VARCHAR2(10);  customer_nested_table1 nested_table_typ;  result BOOLEAN; BEGIN  customer_nested_table1 :=  nested_table_typ('Fred', 'George', 'Susan');  result := customer_nested_table1 IS EMPTY;  IF result THEN   DBMS_OUTPUT.PUT_LINE('Nested table is empty');  ELSE  DBMS_OUTPUT.PUT_LINE('Nested table contains elements');  END IF; END is_empty_example; / 

The following example calls is_empty_example() :

  CALL is_empty_example();  Nested table contains elements 

COLLECT Operator

You use the COLLECT operator to get a list of values as a nested table. You can cast the returned nested table to a nested table type using the CAST operator. The following query illustrates the use of COLLECT :

  SELECT COLLECT(first_name)   FROM customers_with_varray;  COLLECT(FIRST_NAME) -------------------------------------- SYSTPxBtv6XzZXLjgNAADug9CQQ==('Steve') 

POWERMULTISET Operator

You use the POWERMULTISET operator to get a sub-nested table from an input nested table. The following query illustrates the use of POWERMULTISET :

  SELECT *   FROM TABLE(   POWERMULTISET(nested_table_typ('This', 'is', 'a', 'test'))   );  COLUMN_VALUE ------------------------------------------- NESTED_TABLE_TYP('This') NESTED_TABLE_TYP('is') NESTED_TABLE_TYP('This', 'is') NESTED_TABLE_TYP('a') NESTED_TABLE_TYP('This', 'a') NESTED_TABLE_TYP('is', 'a') NESTED_TABLE_TYP('This', 'is', 'a') NESTED_TABLE_TYP('test') NESTED_TABLE_TYP('This', 'test') NESTED_TABLE_TYP('is', 'test') NESTED_TABLE_TYP('This', 'is', 'test') NESTED_TABLE_TYP('a', 'test') NESTED_TABLE_TYP('This', 'a', 'test') NESTED_TABLE_TYP('is', 'a', 'test') NESTED_TABLE_TYP('This', 'is', 'a', 'test') 
Note  

You cannot use POWERMULTISET in PL/SQL.

POWERMULTISET_BY_CARDINALITY Operator

You use the POWERMULTISET_BY_CARDINALITY operator to get a sub-nested table from an input nested table with a specified length (cardinality). The following query illustrates the use of POWERMULTISET_BY_CARDINALITY :

  SELECT *   FROM TABLE(   POWERMULTISET_BY_CARDINALITY(   nested_table_typ('This', 'is', 'a', 'test'), 3   )   );  COLUMN_VALUE -------------------------------------- NESTED_TABLE_TYP('This', 'is', 'a') NESTED_TABLE_TYP('This', 'is', 'test') NESTED_TABLE_TYP('This', 'a', 'test') NESTED_TABLE_TYP('is', 'a', 'test') 
Note  

You cannot use POWERMULTISET_BY_CARDINALITY in PL/SQL.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net