|Oracle® Database Application Developer's Guide - Object-Relational Features
10g Release 1 (10.1)
Part Number B10799-01
This chapter provides basic information about working with objects. It explains what object types, methods, and collections are and describes how to create and work with a hierarchy of object types that are derived from a shared root type and are connected by inheritance.
This chapter contains these topics:
Oracle supports the varray and nested table collection datatypes.
A varray is an ordered collection of elements
A nested table can have any number of elements
If you need to store only a fixed number of items, or to loop through the elements in order, or you will often want to retrieve and manipulate the entire collection as a value, then use a varray.
If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or do mass insert/update/delete operations, then use a nested table.
See Also:"Design Considerations for Collections".
You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the type's constructor method. The name of a type's constructor method is simply the name of the type. You specify the elements of the collection as a comma-delimited list of arguments to the method.
Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection.
The following example illustrates a literal invocation of the constructor method for the nested table type
CREATE TYPE people_typ AS TABLE OF person_typ; / people_typ ( person_typ(1, 'John Smith', '1-800-555-1212'), person_typ(2, 'Diane Smith', NULL) )
The following examples show how it is used in SQL statements to insert values into a nested table.
CREATE TABLE people_tab ( group_no NUMBER, people_column people_typ ) NESTED TABLE people_column STORE AS people_column_nt; INSERT INTO people_tab VALUES ( 100, people_typ( person_typ(1, 'John Smith', '1-800-555-1212'), person_typ(2, 'Diane Smith', NULL)));
When you declare a table column to be of an object type or collection type, you can include a
DEFAULT clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The default clause must contain a literal invocation of the constructor method for that object or collection.
The following example shows how to use literal invocations of constructor methods to specify defaults:
Example 3-1 Creating the department_persons Table
CREATE TABLE department_persons ( dept_no NUMBER PRIMARY KEY, dept_name CHAR(20), dept_mgr person_typ DEFAULT person_typ(10,'John Doe',NULL), dept_emps people_typ DEFAULT people_typ() ) NESTED TABLE dept_emps STORE AS dept_emps_tab;
Note that the term
people_typ() is a literal invocation of the constructor method for an empty
A varray is an ordered set of data elements. All elements of a given varray are of the same datatype or a subtype of the declared one. Each element has an index, which is a number corresponding to the element's position in the array. The index number is used to access a specific element.
When you define a varray, you specify the maximum number of elements it can contain, although you can change this number later. The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called varrays.
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); /
VARRAYs of type
email_list_arr have no more than ten elements, each of datatype
Creating an array type, as with a SQL object type, does not allocate space. It defines a datatype, which you can use as:
The datatype of a column of a relational table.
An object type attribute.
The type of a PL/SQL variable, parameter, or function return value.
A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a
BLOB. See "Storage Considerations for Varrays".
You can create a
VARRAY type of
XMLType or of a
LOB type for procedural purposes, such as in PL/SQL or in view queries. However, database storage of a varray of those types is not supported. This means that you cannot create an object table or an object type column of a varray type of
XMLType or of a
See Also:Oracle Database SQL Reference for information and examples on the
A nested table is an unordered set of data elements, all of the same datatype. No maximum is specified in the definition of the table and the order of the elements is not preserved. You select, insert, delete, and update in a nested table just as you do with ordinary tables using the TABLE expression.
Elements of a nested table are actually stored in a separate storage table that contains a column that identifies the parent table row or object to which each element belongs. A nested table has a single column, and the type of that column is a built-in type or an object type. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.
In Example 3-2, the table type used for the nested tables is declared with the
OF statement. A table type definition does not allocate space. It defines a type, which you can use as:
The datatype of a column of a relational table
An object type attribute
A PL/SQL variable, parameter, or function return type
When a column in a relational table is of nested table type, Oracle stores the nested table data for all rows of the relational table in the same storage table. Similarly, with an object table of a type that has a nested table attribute, Oracle stores nested table data for all object instances in a single storage table associated with the object table. In Example 3-2, the
TABLE clause specifies the storage name for the nested table. The example uses
person_typ defined in Example 1-1.
See Also:See Figure 8-2, "Nested Table Storage"
Example 3-2 Creating and Populating Simple Nested Tables
CREATE TYPE people_typ AS TABLE OF person_typ; / CREATE TABLE students ( graduation DATE, math_majors people_typ, chem_majors people_typ, physics_majors people_typ) NESTED TABLE math_majors STORE AS math_majors_nt NESTED TABLE chem_majors STORE AS chem_majors_nt NESTED TABLE physics_majors STORE AS physics_majors_nt; INSERT INTO students (graduation) VALUES ('01-JUN-03'); UPDATE students SET math_majors = people_typ (person_typ(12, 'Bob Jones', '111-555-1212'), person_typ(31, 'Sarah Chen', '111-555-2212'), person_typ(45, 'Chris Woods', '111-555-1213')), chem_majors = people_typ (person_typ(51, 'Joe Lane', '111-555-1312'), person_typ(31, 'Sarah Chen', '111-555-2212'), person_typ(52, 'Kim Patel', '111-555-1232')), physics_majors = people_typ (person_typ(12, 'Bob Jones', '111-555-1212'), person_typ(45, 'Chris Woods', '111-555-1213')) WHERE graduation = '01-JUN-03';
A convenient way to access the elements of a nested table individually is to use a nested cursor.
CREATE TABLE people_tab ( people_column people_typ ) NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE users);
TABLESPACE clause is not specified, then the storage table of the nested table is created in the tablespace where the parent table is created. For multilevel nested tables, Oracle creates the child table in the same tablespace as its immediate preceding parent table.
The user can issue
MOVE statement to move a table to a different tablespace. If the user issues
MOVE statement on a table with nested table columns, it only moves parent table, no action is taken on the nested table's storage tables. If the user wants to move a nested table s storage table to a different tablespace, issue
MOVE on the storage table. For example:
ALTER TABLE people_tab MOVE TABLESPACE users; ALTER TABLE people_column_nt MOVE TABLESPACE example;
people_tab table is in
users tablespace and nested table is stored in the
In a varray of varrays, the entire varray is stored inline in the row unless it is larger than approximately 4000 bytes or
LOB storage is explicitly specified.
In a varray of nested tables, the entire varray is stored in a
LOB, with only the
LOB locator stored in the row. There is no storage table associated with nested table elements of a varray. The entire nested table collection is stored inside the varray.
You can explicitly specify
LOB storage for varrays. The following example does this for the varray elements of a nested table. As the example also shows, you can use the
COLUMN_VALUE keyword with varrays as well as nested tables.
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); / CREATE TYPE email_list_typ AS TABLE OF email_list_arr; / CREATE TABLE dept_email_list ( dept_no NUMBER, email_addrs email_list_typ) NESTED TABLE email_addrs STORE AS email_addrs_nt (VARRAY COLUMN_VALUE STORE AS LOB dept_emails_lob);
The following example shows explicit
LOB storage specified for a varray of varray type:
CREATE TYPE email_list_typ2 AS OBJECT ( section_no NUMBER, emails email_list_arr); / CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ2; / CREATE TABLE dept_email_list2 ( dept_no NUMBER, email_addrs email_varray_typ) VARRAY email_addrs STORE AS LOB dept_emails_lob2;
See Also:"Storage Considerations for Varrays".
When the element type of a
VARRAY type is a variable character or RAW type or a numeric type, you can increase the size of the variable character or RAW type or increase the precision of the numeric type. A new type version is generated for the
CASCADE are provided to either invalidate all dependent objects or propagate the change to its type and table dependents
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80); / ALTER TYPE email_list_arr MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;
The same change can be applied to nested table types.
CREATE TYPE email_list_tab AS TABLE OF VARCHAR2(30); / ALTER TYPE email_list_tab MODIFY ELEMENT TYPE VARCHAR2(40) CASCADE;
LIMIT syntax allows increasing the number of elements of a
VARRAY type. If the number of elements of the
VARRAY type is increased, a new type version is generated for the
VARRAY type and this is maintained as part of the history of the type changes.
CASCADE are provided to either invalidate all dependent objects or propagate the change to its type and table dependents.
CREATE TYPE email_list_typ AS OBJECT ( section_no NUMBER, emails email_list_arr); / CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ; / ALTER TYPE email_varray_typ MODIFY LIMIT 100 INVALIDATE;
VARRAY type is altered, changes are propagated to the dependent tables. See "Propagating VARRAY Size Change".
In the following examples,
email_addrs of type
email_list_typ already exists in table
dept_email_list as shown in the SQL examples in "Varray Storage".
To create a varray of LOB references, first define a
VARRAY type of type
email_list_typ2. For example:
CREATE TYPE ref_email_varray_typ AS VARRAY(5) OF REF email_list_typ2; /
Next define a column of the array type in
CREATE TABLE dept_email_list3 ( dept_no NUMBER, email_addrs ref_email_varray_typ) VARRAY email_addrs STORE AS LOB dept_emails_lob3;
Nested table of nested table type
Nested table of varray type
Varray of nested table type
Varray of varray type
Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type
Like ordinary, single-level collection types, multilevel collection types can be used as columns in a relational table or with object attributes in an object table.
A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. With a multilevel nested table collection of nested tables, the inner set of nested tables also requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.
Example 3-3 creates a multilevel collection type that is a nested table of nested tables. The example models a system of stars in which each star has a nested table collection of the planets revolving around it, and each planet has a nested table collection of its satellites. In the example, the SQL statements create a table
stars that contains a column
planets whose type is a multilevel collection. This multilevel collection is a nested table of an object type that has a nested table attribute
satellites. Separate nested table clauses are provided for the outer
planets nested table and for the inner
Example 3-3 Nested Table Storage
CREATE TYPE satellite_t AS OBJECT ( name VARCHAR2(20), diameter NUMBER); / CREATE TYPE nt_sat_t AS TABLE OF satellite_t; / CREATE TYPE planet_t AS OBJECT ( name VARCHAR2(20), mass NUMBER, satellites nt_sat_t); / CREATE TYPE nt_pl_t AS TABLE OF planet_t; / CREATE TABLE stars ( name VARCHAR2(20), age NUMBER, planets nt_pl_t) NESTED TABLE planets STORE AS planets_tab (NESTED TABLE satellites STORE AS satellites_tab);
The preceding example can refer to the inner
satellite nested table by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute, it has no name. The keyword
COLUMN_VALUE is provided for this case; you use it in place of a name for an inner nested table. For example:
CREATE TYPE inner_table AS TABLE OF NUMBER; / CREATE TYPE outer_table AS TABLE OF inner_table; / CREATE TABLE tab1 ( col1 NUMBER, col2 outer_table) NESTED TABLE col2 STORE AS col2_ntab (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);
Physical attributes for the storage tables can be specified in the nested table clause. For example:
CREATE TABLE stars ( name VARCHAR2(20), age NUMBER, planets nt_pl_t) NESTED TABLE planets STORE AS planets_tab ( PRIMARY KEY (NESTED_TABLE_ID, name) ORGANIZATION INDEX COMPRESS NESTED TABLE satellites STORE AS satellites_tab );
Every nested table storage table contains a column, referenceable by
NESTED_TABLE_ID, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns: one, referenceable by
NESTED_TABLE_ID, that keys its rows back to rows in its own parent table, and one hidden column referenced by the
NESTED_TABLE_ID column in its nested table children.
In the preceding example, nested table
planets is made an index-organized table (IOT) by adding the
INDEX clause and assigning the nested table a primary key in which the first column is
NESTED_TABLE_ID. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with
NESTED_TABLE_ID as the first column and index-organizing the table cause Oracle to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.
Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection. See "Nested Table Storage".
Only items whose datatypes are nested table collection types, including multilevel collection types, can be compared. See "Comparisons of Collections".
Like single-level collection types, multilevel collection types are created by calling the respective type's constructor method. Like the constructor methods for other user-defined types, a constructor for a multilevel collection type is a system-defined function that has the same name as the type and returns a new instance of it—in this case, a new multilevel collection. Constructor parameters have the names and types of the object type's attributes.
The following example calls the constructor for the multilevel collection type
nt_pl_t. This type is a nested table of planets, each of which contains a nested table of satellites as an attribute. The constructor for the outer nested table calls the
planet_t constructor for each planet to be created; each planet constructor calls the constructor for the satellites nested table type to create its nested table of satellites; and the satellites nested table type constructor calls the
satellite_t constructor for each satellite instance to be created.
INSERT INTO stars VALUES('Sun',23, nt_pl_t( planet_t( 'Neptune', 10, nt_sat_t( satellite_t('Proteus',67), satellite_t('Triton',82) ) ), planet_t( 'Jupiter', 189, nt_sat_t( satellite_t('Callisto',97), satellite_t('Ganymede', 22) ) ) ) );
This section describes the operations on collection datatypes.
There are two general ways to query a table that contains a column or attribute of a collection type. One way returns the collections nested in the result rows that contain them. The other way distributes or unnests collections such that each collection element appears on a row by itself.
The following queries use the
department_persons table shown in Example 3-1. The column
dept_emps is a nested table collection of
person_typ type. The
dept_emps collection column appears in the
SELECT list like an ordinary, scalar column. Querying a collection column in the
SELECT list like this nests the elements of the collection in the result row with which the collection is associated.
For example, the following query retrieves the collection of employees. The collection of employees is nested:
SELECT d.dept_emps FROM department_persons d; DEPT_EMPS(IDNO, NAME, PHONE) ------------------------------------------------------------------------------------------ PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-800-555-1212'), PERSON_TYP(2, 'Diane Smith', '1-800-555-1243'))
Results are also nested if an object-type column in the
SELECT list contains a collection attribute, even if that collection is not explicitly listed in the
SELECT list itself. For example, the query
department_persons would produce a nested result.
Not all tools or applications are able to deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this by using a
TABLE expression with the collection. A
TABLE expression enables you to query a collection in the
FROM clause like a table. In effect, you join the nested table with the row that contains the nested table.
TABLE expression can be used to query any collection value expression, including transient values such as variables and parameters.
Like the preceding example, the following query retrieves the collection of employees, but the collection is unnested:
SELECT e.* FROM department_persons d, TABLE(d.dept_emps) e; IDNO NAME PHONE ---------- ------------------------------ -------------------- 1 John Smith 1-800-555-1212 2 Diane Smith 1-800-555-1243
As the preceding example shows, a
TABLE expression can have its own table alias. In the example, a table alias for the
TABLE expression appears in the
SELECT list to select columns returned by the
TABLE expression uses another table alias to specify the table that contains the collection column that the
TABLE expression references. The expression
TABLE(d.dept_emps) specifies the
department_persons table as containing the
dept_emps collection column. A
TABLE expression can use the table alias of any table appearing to the left of it in a
FROM clause to reference a column of that table. This way of referencing collection columns is called left correlation.
In the example, the
department_persons table is listed in the
FROM clause solely to provide a table alias for the
TABLE expression to use. No columns from the
department_persons table other than the column referenced by the
TABLE expression appear in the result
The following example produces rows only for departments that have employees.
SELECT d.dept_no, e.* FROM department_persons d, TABLE(d.dept_emps) e;
To get rows for departments that have no employees, you can use outer-join syntax:
SELECT d.dept_no, e.* FROM department_persons d, TABLE(d.dept_emps) (+) e;
The (+) indicates that the dependent join between
e.dept_emps should be
NULL-augmented. That is, there will be rows of
department_persons in the output for which
NULL or empty, with
NULL values for columns corresponding to
The preceding examples show a
TABLE expression that contains the name of a collection. Alternatively, a
TABLE expression can contain a subquery of a collection.
The following example returns the collection of employees whose department number is
SELECT * FROM TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101);
There are these restrictions on using a subquery in a
The subquery must return a collection type
SELECT list of the subquery must contain exactly one item
The subquery must return only a single collection; it cannot return collections for multiple rows. For example, the subquery
department_persons succeeds in a
TABLE expression only if table
department_persons contains just a single row. If the table contains more than one row, the subquery produces an error.
Here is an example showing a
TABLE expression used in the
FROM clause of a
SELECT embedded in a
SELECT d.dept_no, CURSOR(SELECT * FROM TABLE(d.dept_emps)) FROM department_persons d WHERE d.dept_no = 101;
Unnesting queries can be used with multilevel collections, too, for both varrays and nested tables. The following example shows an unnesting query on a multilevel nested table collection of nested tables. From a table
stars in which each star has a nested table of planets and each planet has a nested table of satellites, the query returns the names of all satellites from the inner set of nested tables.
SELECT t.name FROM stars s, TABLE(s.planets) p, TABLE(p.satellites) t;
Because no columns of the base table
stars appear in the
SELECT list, the query is optimized to run directly against the
satellites storage table.
Outer-join syntax can also be used with queries of multilevel collections.
Inserts and updates that provide a new value for the entire collection
Inserting new elements into the collection
Deleting elements from the collection
Updating elements of the collection.
Oracle does not support piecewise updates on
VARRAY columns. However,
VARRAY columns can be inserted into or updated as an atomic unit.
For piecewise updates of nested table columns, the DML statement identifies the nested table value to be operated on by using the
The following DML statements demonstrate piecewise operations on nested table columns.
INSERT INTO TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) VALUES (5, 'Kevin Taylor', '1-800-555-6212'); UPDATE TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) e SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-800-555-6233') WHERE e.idno = 5; DELETE FROM TABLE(SELECT d.dept_emps FROM department_persons d WHERE d.dept_no = 101) e WHERE e.idno = 5;
For multilevel nested table collections, DML can be done atomically, on the collection as a whole, or piecewise, on selected elements. For multilevel varray collections, DML operations can be done only atomically.
The section "Constructors for Multilevel Collections" shows an example of inserting an entire multilevel collection with an
INSERT statement. Multilevel collections can also be updated atomically with an
UPDATE statement. For example, suppose
v_planets is a variable declared to be of the planets nested table type
nt_pl_t. The following statement updates
stars by setting the
planets collection as a unit to the value of
UPDATE stars s SET s.planets = :v_planets WHERE s.name = 'Aurora Borealis';
Piecewise DML is possible only on nested tables, not on varrays.
The following example shows a piecewise insert operation on the
planets nested table of nested tables: the example inserts a new planet, complete with its own nested table of
INSERT INTO TABLE( SELECT planets FROM stars WHERE name = 'Sun') VALUES ('Saturn', 56, nt_sat_t( satellite_t('Rhea', 83) ) );
The next example performs a piecewise insert into an inner nested table to add a satellite for a planet. Like the preceding, this example uses a
TABLE expression containing a subquery that selects the inner nested table to specify the target for the insert.
INSERT INTO TABLE( SELECT p.satellites FROM TABLE( SELECT s.planets FROM stars s WHERE s.name = 'Sun') p WHERE p.name = 'Uranus') VALUES ('Miranda', 31);
The conditions listed in this section allow comparisons of nested tables. There is no mechanism for comparing varrays. The SQL examples in this section use the nested tables created in Example 3-2.
Two nested tables are equal if they have the same named type, have the same cardinality, and their elements are equal. Elements are equal depending on whether they are equal by the elements own equality definitions, except for object types which require a map method.
SELECT p.name FROM students, TABLE(physics_majors) p WHERE math_majors = physics_majors:
In this example, the nested tables contain
person_typ objects which have an associated map method. See Example 1-1.
SELECT p.idno, p.name FROM students, TABLE(physics_majors) p WHERE physics_majors IN (math_majors, chem_majors);
[OF] condition checks whether a nested table is a subset of a another nested table, returning the result as a Boolean value. The
OF keyword is optional and does not change the functionality of
This operator is implemented only for nested tables because this is a multiset function only.
SELECT p.idno, p.name FROM students, TABLE(physics_majors) p WHERE physics_majors SUBMULTISET OF math_majors:
[OF] condition tests whether an element is a member of a nested table, returning the result as a Boolean value. The
OF keyword is optional and has no effect on the output.
SELECT graduation FROM students WHERE person_typ(12, 'Bob Jones', '1-800-555-1212') MEMBER OF math_majors;
person_typ (12, 'Bob Jones', '1-800-555-1212') is an element of the same type as the elements of the nested table
EMPTY condition checks whether a given nested table is empty or not empty, regardless of whether any of the elements are NULL. If a NULL is given for the nested table, the result is NULL. The result is returned as a Boolean value.
SELECT p.idno, p.name FROM students, TABLE(physics_majors) p WHERE physics_majors IS NOT EMPTY;
This section describes multiset operations with nested tables. The SQL examples in this section use the nested tables created in Example 3-2.
SELECT CARDINALITY(math_majors) FROM students;
COLLECT function is an aggregate function which would create a multiset from a set of elements. The function would take a column of the element type as input and create a multiset from rows selected. To get the results of this function you must use it within a
CAST function to specify the output type of
EXCEPT operator inputs two nested tables and returns a nested table whose elements are in the first nested table but not in the second nested table. The input nested tables and the output nested table are all type name equivalent.
DISTINCT options can be used with the operator. The default is
ALL option, for
ntab2, all elements in
ntab1 other than those in
ntab2 would be part of the result. If a particular element occurs
m times in
n times in
ntab2, the result will have (
n) occurrences of the element if
m is greater than
0 occurrences of the element.
DISTINCT option, any element that is present in
ntab1 which is also present in
ntab2 would be eliminated, irrespective of the number of occurrences.
SELECT math_majors MULTISET EXCEPT physics_majors FROM students WHERE graduation = '01-JUN-03';
There are two options associated with the operator:
DISTINCT. The default is
ALL. With the
ALL option, if a particular value occurs
m times in
n times in
ntab2, the result would contain the element
n) times. With the
DISTINCT option the duplicates from the result would be eliminated, including duplicates of
NULL values if they exist.
SELECT math_majors MULTISET INTERSECT physics_majors FROM students WHERE graduation = '01-JUN-03';
There are two options associated with the operator:
DISTINCT. The default is
ALL. With the
ALL option, all elements that are in
ntab2 would be part of the result, including all copies of
NULLs. If a particular element occurs
m times in
n times in
ntab2, the result would contain the element (
n) times. With the
DISTINCT option the duplicates from the result are eliminated, including duplicates of
NULL values if they exist.
SELECT math_majors MULTISET UNION DISTINCT physics_majors FROM students WHERE graduation = '01-JUN-03'; PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'), PERSON_TYP(31, 'Sarah Chen', '1-800-555-2212'), PERSON_TYP(45, 'Chris Woods', '1-800-555-1213')) SELECT math_majors MULTISET UNION ALL physics_majors FROM students WHERE graduation = '01-JUN-03'; PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'), PERSON_TYP(31, 'Sarah Chen', '1-800-555-2212'), PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'), PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'), PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'))
POWERMULTISET function generates all non-empty submultisets from a given multiset. The input to the
POWERMULTISET function could be any expression which evaluates to a multiset. The limit on the cardinality of the multiset argument is 32.
SELECT * FROM TABLE(POWERMULTISET( people_typ ( person_typ(12, 'Bob Jones', '1-800-555-1212'), person_typ(31, 'Sarah Chen', '1-800-555-2212'), person_typ(45, 'Chris Woods', '1-800-555-1213'))));
POWERMULTISET_BY_CARDINALITY(x, l) is equivalent to
x is a multiset and l is the specified cardinality.
The first input parameter to the
POWERMULTISET_BY_CARDINALITY could be any expression which evaluates to a nested table. The length parameter should be a positive integer, otherwise an error will be returned. The limit on the cardinality of the nested table argument is 32.
SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ ( person_typ(12, 'Bob Jones', '1-800-555-1212'), person_typ(31, 'Sarah Chen', '1-800-555-2212'), person_typ(45, 'Chris Woods', '1-800-555-1213')),2));
SET function converts a nested table into a set by eliminating duplicates, and returns a nested table whose elements are
DISTINCT from one another. The nested table returned is of the same named type as the input nested table.
SELECT SET(physics_majors) FROM students WHERE graduation = '01-JUN-03';