Variable Length array in oracle varray, nested table, set usage, oraclevarray

Variable Length array in oracle varray, nested table, set usage, oraclevarray

Create a variable-length Array

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

You can change the size of elements of the variable-length array type and nested table type.

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 use the INVALIDATE option 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
Copy codeThe Code is as follows:
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

Copy codeThe Code is as follows:
Table_type ('1', '2 ')

PS/SQL does not support this