Oracle: variable-length array varray, nested table, Set

CREATETYPEvarray_typeASVARRAY (2) OFVARCHAR2 (50); this variable-length array can accommodate up to two data types: varchar2 (50) you can change the size or precision of element types to change the size of elements of the variable-length array type and nested table type.

Create type varray_type as varray (2) OF VARCHAR2 (50); this variable-length array can accommodate up to two data types: varchar2 (50) you can change the size or precision of element types to change the size of elements of the variable-length array type and nested table type.

CREATE TYPE varray_type AS VARRAY(2) OF VARCHAR2(50); 

This variable-length array can accommodate up to two data types: varchar2 (50)

Change the size or precision of the element type

YesVariable-length array type and nested table TypeThe size of the element.

ALTER TYPE varray_type MODIFY ELEMENT TYPE varchar2(100) CASCADE;

The CASCADE option changes the objects that have been propagated to the database. You can also useINVALIDATEOption to invalidate the dependent object

Increase the number of elements in a variable-length array.

ALTER TYPE vrray_nameMODIFY LIMIT 5 CASCADE;

Use a variable-length Array

CREATE TABLE table_name(column_name type,var_col_name varray_type);

Get Variable Length array information

DESC[RIBE] varray_type;SELECT * FROM user_varraysWHERE type_name = varray_name;

DESC obtains varray_type as varray (2) OF VARCHAR2 (50)

Fill in 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 a time.

Search for variable-length array elements

SELECT *FROM table_Name;

If there are multiple elements in the variable-length array, the output data column is a long column, the same as the table containing the object.

Change the variable length array element

To change an element of a variable-length array, you need to change other elements together. The entire variable-length array is regarded as a whole.

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

Create a nested table Type

CREATE TYPE table_type AS TABLE OF type;

Among them, type can be of any type, including varray and object, which are usually the majority of objects.

Use the nested table Type

CREATE TABLE table_name(  column_name type,  tab_col_name table_type)NESTED TABLE  table_col_nameSTORE AS  next_table_name [TABLESPACE user_name];

When creating a NESTED table, you need to create another table for the NESTED table type to save the data. The following sections of NESTED are doing this. The table name is next_table_name.

TABLESPACE can be used to place the created table to another space.

Obtain table information

SET DESCRIBE DEPTH 2DESC[RIBE] table_name;

You can also use the data dictionary to obtain information about nested tables.

SELECT *FROM user_nested_tablesWHERE table_name = xxxx;

Fill and search for nested table elements

Same as the variable-length Array Method

Modify nested table elements

Unlike a variable-length array, the elements of a nested table can be changed separately: the elements of a nested table can be inserted, modified, 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) TSET  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 to be modified. WHERE is the judgment statement. If table_type is of the object type, write 'x', 'xx' in expr2 ', 'xxx'

Delete:

DELETE FROM TABLE(  SELECT tab_col_name FROM table_name WHERE expr)TWHERE  VALUE(T) = table_type(    expr2  );

Set Method

EXISTS (N)	Returns TRUE if the nth element exists.
COUNT	Number of elements in this function set
DELETE DELETE (n) DELETE (n, m)	Delete A set Element L delete all elements L Delete nth Element L Delete n to m Elements
FIRST	Returns the index number of the first (smallest) element in the set. If the set is NULL, NULL is returned.
LAST	Returns the index number of the last (largest) element in the set. If the set is empty, NULL is returned.
NEXT (n)	Returns the index number of the nelement under the current element of the set. If it does not exist, NULL is returned.
PRIOR (n)	Returns the index number of the nelement before the current element in the set. If it does not exist, NULL is returned.
LIMIT	Returns the maximum number of elements created in varray.
EXTEND EXTEND (n) EXTEND (n, m)	Increase the size of the set. L add one and set it to null L add n, set to null L add n, set to m
TRIM TRIM (n)	Delete an element from the end of the Set L delete L Delete n

The call 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 the result is true;

IN and not in Operators

Used to check whether the content of a nested table exists in the content 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

Check whether the content of a nested table is a subset of another nested table.

Result: = var_tab_1 submulitset of var_tab_3 result is TRUE;

MULTISET set operator

Returns a nested set.

MULTISET UNION

MULTISET INTERSECT

MULTISET

And deliver the difference.

ALL

DISTINCT deduplication

Var_tab1: = var_tab2 mustiset union all var_tab2

CARDINALITY obtains the number of elements in a nested table.

CARDINALITY (var_tab1)

(What is the difference with count .. --!)

Member of operator

Checks whether an element of a nested table exists.

'Xxx' member of var_tab1; return BOOLEAN

SET Operator

Returns the result after deduplication of the input nested table.

Var_tab1: = SET (var_tab2 );

IS A SET

Each element must be different during determination.

Result: = var_tab1 is a set;

IS EMPTY

Judge whether the nested table is empty

COLLECT Operator

Return the Value List as a nested table. You can use the CAST operator to forcibly convert the returned nested table to a type of nested table.

SELECT COLLECT(column_name)FROM TABLEWHERE expr;

POWERMULTISET

Obtain the child nested table of the nested table

SELECT *FROM TABLE(  POWERMULTISET(table_tpye('1','2')));

Obtain

table_type('1')table_type('2')table_type('1','2')

Note: PS/SQL does not support this

POWERMULTISET_BY_CARDINALITY

Obtain the nested tables with the specified length below

SELECT *FROM TABLE(  POWERMULTISET_BY_CARDINALITY(table_tpye('1','2'),2));

Obtain

table_type('1','2')

PS/SQL does not support this