Oracle variable-length array varray, nested tables, collection use method _oracle

To create a variable-length array type

This variable length array can hold up to two data, the type of the data is VARCHAR2 (50)

Change the size or precision of an element type

You can change the size of long array types and nested table type elements more.

ALTER TYPE varray_type 
MODIFY ELEMENT type VARCHAR2 (m) CASCADE;

Cascade option The change is propagated to the database since the object. You can also use the INVALIDATE option to invalidate dependent objects

Increase the number of elements in a variable-length array

ALTER TYPE vrray_name
MODIFY LIMIT 5 CASCADE;

Using variable-length arrays

CREATE TABLE table_name (
column_name type,
var_col_name varray_type
);

Get information for variable-length arrays

Desc[ribe] Varray_type;

SELECT * from 
user_varrays
WHERE type_name = varray_name;

DESC obtained is varray_type as Varray (2) of VARCHAR2 (50)

Fill variable-length array elements

INSERT into table_name VALUES (
 value,
 varray_type (
  ' xxxx ',
  ' xx ', '
  x '))
;

You can add multiple data to a variable-length array at once.

Find variable-length array elements

SELECT * FROM
table_name;

If there are more than one element in the variable-length array, it is output together, and the output data column is a long column, just like the table containing the object.

Change a long array element

To change an element of a long array, you need to change the other elements together and the entire variable length array as a whole.

UPDATE table_name
SET var_col_name = varray_type (' xxx ', ' xxxxxx ')
WHERE expr1;

To create a nested table type

CREATE type Table_type as table of TYPE;

Where type can be of any type, including Varray and object, usually object-mostly.

Using Nested table Types

CREATE Table table_name (
 column_name type,
 tab_col_name table_type
)
NESTED table
 Table_col_ Name
STORE as
 next_table_name [tablespace user_name];

When you create a nested table, you create a separate table for the nested table type to hold the data, NESTED the following section is doing it. The name of the table is: Next_table_name

Tablespace can place additional tables in another space.

Get table information

SET DESCRIBE DEPTH 2
desc[ribe] table_name;

You can also get information about nested tables directly through the data dictionary.

SELECT * from
user_nested_tables
WHERE table_name = xxxx;

Populating, finding nested table elements

As with variable-length array methods

To change a nested TABLE element

Unlike variable-length arrays, the elements of a nested table can be changed individually: Nested table elements can be inserted, changed, and deleted.

Insert:

INSERT into TABLE (
 SELECT tab_col_name from table_name WHERE expr)
 VALUES (
  table_type (' xxxx ')
 )
;

Change:

UPDATE TABLE (
 SELECT tab_col_name from table_name WHERE expr
) T
SET
 VALUE (t) = Table_type (
  ' xxxx ')
)
WHERE 
 VALUE (T) = Table_type (
  expr2
  );

T is the position of the nested table corresponding to the row of data that needs to be modified, where is the judgment statement, and if Table_type is the object type, Expr2 writes: ' x ', ' xx ', ' xxx '

Delete:

DELETE from TABLE (
 SELECT tab_col_name from table_name where expr
) T
WHERE
 VALUE (t) = Table_type C24/>EXPR2
 );

Collection method

EXISTS (N)	Returns true if the nth element exists
COUNT	The number of elements in the function collection
DELETE DELETE (N) DELETE (N,M)	To delete a collection element L Delete all elements L Delete Nth Element L Delete elements of N to M
The	Returns the first (smallest) element index number of the collection, or null if the collection is empty
Last	Returns the last (largest) element index number in the collection, or null if the collection is empty
NEXT (N)	Returns the index number of the lower n element of the current element of the collection, and returns null if it does not exist
PRIOR (N)	Returns the index number of the first n element of the current element of the collection, and returns null if it does not exist
LIMIT	Returns the maximum number of elements created in Varray
EXTEND EXTEND (N) EXTEND (N,M)	Increase the size of the collection. L add one, set to null L add n, set to NULL L add N, set to M
TRIM TRIM (N)	To remove an element from the end of a collection L Delete A L Delete N

The calling method is: Tab_col_name. COUNT

Nested table operator Operations

For example:

Var_tab_1 Table_type;
Var_tab_2 Table_type;
Var_tab_3 Table_type;
Reslut BOOLEAN;

Var_tab_1: =table_type (' 1 ', ' 2 ');
var_tab_2: =table_type (' 3 ', ' 4 ');
Var_tab_3: =table_type (' 2 ', ' 1 ');

result:= var_tab_1 =var_tab_3 result is true;
result:= var_tab_2 <>var_tab_3 result is true;

In and not in operators
Used to detect whether the contents of a nested table appear in the contents of a nested table.
Result:= var_tab_1 in (var_tab_3); result is TRUE;
Result:= var_tab_2 not in (Var_tab_3); result is TRUE;

Submulitset subset operator
Checks whether the contents of one nested table are a subset of another nested table
result:= var_tab_1 Submulitset of var_tab_3 result is true;

Multiset Set operator
Returns a set of nested sets
Multiset UNION
Multiset INTERSECT
Multiset EXCEPT
and the other.
All
DISTINCT to Heavy
VAR_TAB1: = var_tab2 mustiset UNION all VAR_TAB2

Cardinality to get the number of elements in a nested table
Cardinality (VAR_TAB1)
(What's the difference with count?). - -！ ）

Member of operator
Detects whether an element of a nested table exists
' xxx ' member of VAR_TAB1; Return Boolean

SET operator
Returns the incoming nested table to the back
VAR_TAB1: = SET (VAR_TAB2);

is A SET
Every element is different when judged.
result:= Var_tab1 is A SET;

is EMPTY
To determine whether a nested table is empty

COLLECT operator
Returns a list of values as a nested table, with the cast operator casting the returned nested table to a nested table type.

SELECT COLLECT (column_name) from
TABLE
WHERE expr;

Powermultiset

To get a child nested table of a nested table

SELECT *
from TABLE (
 powermultiset (Table_tpye (' 1 ', ' 2 '))
;

Get

Copy Code code as follows:

Table_type (' 1 ')
Table_type (' 2 ')
Table_type (' 1 ', ' 2 ')

Note: Ps/sql does not support this

Powermultiset_by_cardinality

Gets the nested table below the specified length

SELECT *
from TABLE (
 powermultiset_by_cardinality (Table_tpye (' 1 ', ' 2 '), 2)
);

Get

Copy Code code as follows:

Table_type (' 1 ', ' 2 ')

Ps/sql doesn't support this.