Collections and Records

1

Associative array (or index-by table)

Supported.

2

VARRAY (variable-size array)

Supported.

3

Nested table

Supported.

4

record

Supported.

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 GUC parameter behavior_compat_options.
• 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. 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.

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.

1

collection_type ( [ value [, value ]... ] )

Supported.

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.

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, 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.

1

MULTISET UNION [ALL | DISTINCT]

Supported.

2

MULTISET EXCEPT [ALL | DISTINCT]

Supported.

3

MULTISET INTERSECT [ALL | DISTINCT]

Supported.

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.

1

Constructors

Supported.

2

%ROWTYPE to declare a variable

Supported.

3

Defining constants

Not supported.

1

unnest_table(anynesttable)

Supported.

-

2

unnest_table(anyindexbytable)

Supported.

-

3

table(anyarray)

Not supported.

GaussDB uses the unnest (anyarray) function for equivalent rewriting.