Collections and Records

Updated on 2024-12-06 GMT+08:00

View PDF

**Table 1** Types
No.	Oracle Database	GaussDB
1	Associative array (or index-by table)	Supported.
2	VARRAY (variable-size array)	Supported.
3	Nested table	Supported.
4	record	Supported.

**Table 2** Syntax
No.	Oracle Database	GaussDB	Difference
1	Associative array (or index-by table) syntax: TABLE OF datatype [ NOT NULL ] INDEX BY { PLS_INTEGER \| BINARY_INTEGER \| VARCHAR2 ( v_size ) \| data_type }	Supported, with differences.	GaussDB does not support the PLS_INTEGER type. In GaussDB, the value of data_type can be a base data type or a record type, collection type, or array type defined in a stored procedure. The ref cursor type is not supported. In GaussDB, NOT NULL does not take effect in the syntax. That is, the system does not check whether an element is NULL. For details, see "Stored Procedure > Arrays, Collections, and Records > Collections" in Developer Guide.
2	VARRAY (variable-size array) syntax: { VARRAY \| [ VARYING ] ARRAY } ( size_limit ) OF datatype [ NOT NULL ]	Supported, with differences.	GaussDB does not support the NOT NULL syntax. In GaussDB, datatype cannot be set to varray (varray cannot be nested). To make the size_limit function take effect, enable the varray_compat parameter in the behavior_compat_options GUC parameter. For details, see "Stored Procedure > Arrays, Collections, and Records > Arrays" in Developer Guide.
3	Nested table syntax: TABLE OF datatype [ NOT NULL ]	Supported, with differences.	In GaussDB, NOT NULL does not take effect in the syntax. For details, see "Stored Procedure > Arrays, Collections, and Records > Collections" in Developer Guide.
4	record syntax: TYPE record_type IS RECORD ( field_definition [, field_definition]... ) ;	Supported.	Record columns can be defined as NOT NULL or a default value can be specified. If the record type is nested in other types, the default value and NOT NULL of the record type do not take effect. If a record variable is created using the package.record_type access type, the default value and NOT NULL of the record variable do not take effect. For details, see "Stored Procedure > Arrays, Collections, and Records > Records" in Developer Guide.

**Table 3** Constructor
No.	Oracle Database	GaussDB
1	collection_type ( [ value [, value ]... ] )	Supported.

**Table 4** Variable assignment
No.	Oracle Database	GaussDB	Difference
1	Associative array (or index-by table)	Supported.	-
2	VARRAY (variable-size array)	Supported, with differences.	Values of different VARRAY data in GaussDB can be assigned to each other, depending on whether elements in the data can be implicitly converted to each other. For details, see "Stored Procedure > Arrays, Collections, and Records > Arrays" in Developer Guide.
3	Nested table	Supported.	-
4	record	Supported, with differences.	Values of different record data in GaussDB can be assigned to each other, depending on whether columns can be implicitly converted. For details, see "Stored Procedure > Arrays, Collections, and Records > Records" in Developer Guide.

**Table 5** Collection operators
No.	Oracle Database	GaussDB	Difference
1	=	Supported, with differences.	Oracle Database: The sequence of collection members is ignored during comparison. GaussDB: The comparison is performed strictly based on the sequence of collection members.
2	<>	Supported, with differences.	Oracle Database: The sequence of collection members is ignored during comparison. GaussDB: The comparison is performed strictly based on the sequence of collection members.
3	IS[NOT] NULL	Supported.	-
4	^=	Supported, with differences.	Oracle Database: The sequence of collection members is ignored during comparison. GaussDB: The comparison is performed strictly based on the sequence of collection members.
5	~=	Not supported.	-
6	IS[NOT] A SET	Not supported.	-
7	IS [NOT] EMPTY	Not supported.	-
8	expr [ NOT ] MEMBER [ OF ] nested_table	Not supported.	-
9	nested_table1 [ NOT ] SUBMULTISET [ OF ] nested_table2	Not supported.	-
10	[NOT] IN	Supported.	Oracle Database: The sequence of collection members is ignored during comparison. GaussDB: The comparison is performed strictly based on the sequence of collection members.

**Table 6** MULTISET functions
No.	Oracle Database	GaussDB
1	MULTISET UNION [ALL \| DISTINCT]	Supported.
2	MULTISET EXCEPT [ALL \| DISTINCT]	Supported.
3	MULTISET INTERSECT [ALL \| DISTINCT]	Supported.

**Table 7** Collection type functions
No.	Oracle Database	GaussDB	Difference
1	exists(idx)	Supported.	-
2	extend[(count[, idx])]	Supported, with differences.	GaussDB supports only nested tables.
3	delete[(idx1[, idx2])]	Supported.	-
4	trim[(n)]	Supported, with differences.	GaussDB supports only nested tables.
5	count	Supported.	-
6	first	Supported.	-
7	last	Supported.	-
8	prior(idx)	Supported.	-
9	next(idx)	Supported.	-
10	limit	Supported, with differences.	GaussDB supports only nested tables.

**Table 8** Record variable operations
No.	Oracle Database	GaussDB
1	Constructors	Supported.
2	%ROWTYPE to declare a variable	Supported.
3	Defining constants	Not supported.

**Table 9** Collection-related functions
No.	Oracle Database	GaussDB
1	unnest_table(anynesttable)	Supported.
2	unnest_table(anyindexbytable)	Supported.

Parent topic: PL/SQL Language

Previous topic: Control Statements

Next topic: Static SQL Statements

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot