Static SQL Statements

1

SELECT

Supported, with differences.

GaussDB and Oracle Database are different in some scenarios.

GaussDB: Shared locks in different transactions do not block each other in the following scenarios:

SELECT FOR SHARE - SELECT FOR SHARE;

SELECT FOR SHARE - SELECT FOR KEY SHARE;

SELECT FOR KEY SHARE - SELECT FOR KEY SHARE;

SELECT FOR KEY SHARE - SELECT FOR NO KEY UPDATE;

In the preceding scenarios, because there is no blocking between locks, the lock will not be skipped when SKIP LOCKED is specified for data that has non-blocking locks in other transactions.

1

INSERT

Supported, with differences.

Oracle Database allows the number of columns in the target table to be greater than the number of columns in the subquery result. However, you must explicitly specify the names of columns to be inserted to ensure that the number of columns matches. In GaussDB, you can omit the names of columns to be inserted. In this case, the value of the first column in the subquery result is inserted into the first column of the target table, and so on. If the target table has more columns, NULL (if a column allows NULL) or the default value (if any) is inserted into each column.

2

UPDATE

Supported.

-

3

DELETE

Supported.

-

4

MERGE

Supported.

-

5

LOCK TABLE

Supported.

-

6

INSERT ALL

Supported, with differences.

• Oracle Database does not support alias setting for the tables of into_clause, but GaussDB supports.
• When into_clause specifies the sequence:
  • Oracle Database: If nextval is referenced for the first time, the next number of the current value is generated. Otherwise, the same number will always be returned.
  • GaussDB: The number generated by referencing nextval can increment automatically.
• The plan_hint statement can take effect in Oracle Database but does not take effect in GaussDB.
• Oracle Database allows the number of columns in the target table to be greater than the number of columns in the subquery result. However, you must explicitly specify the names of columns to be inserted to ensure that the number of columns matches. In GaussDB, you can omit the names of columns to be inserted. In this case, the value of the first column in the subquery result is inserted into the first column of the target table, and so on. If the target table has more columns, NULL (if a column allows NULL) or the default value (if any) is inserted into each column.

1

COMMIT

Supported.

-

2

ROLLBACK

Supported.

-

3

SAVEPOINT

Supported.

-

4

SET TRANSACTION

Supported, with differences.

GaussDB does not support the NAME string and USE ROLLBACK SEGMENT rollback_segment syntax.

1

CURRVAL and NEXTVAL

Supported.

-

2

LEVEL

Supported.

-

3

OBJECT_VALUE

Not supported.

-

4

ROWID

Not supported.

-

5

ROWNUM

Supported, with differences.

It is not recommended that the ROWNUM condition be used in the JOIN ON clause. In GaussDB, when the ROWNUM condition is used in the JOIN ON clause, the behavior in the LEFT JOIN, RIGHT JOIN, FULL JOIN, and MERGE INTO scenarios is different from that in other databases, causing risks in service migration.

1

SQL%FOUND

Supported, with differences.

GaussDB does not update the implicit cursor result after COMMIT or ROLLBACK. Oracle Database updates the implicit cursor result after COMMIT or ROLLBACK.

2

SQL%NOTFOUND

Supported, with differences.

3

SQL%ROWCOUNT

Supported, with differences.

4

SQL%ISOPEN

Supported, with differences.

5

SQL%BULK_ROWCOUNT

Not supported.

6

SQL%BULK_EXCEPTIONS

Supported, with differences.

1

CURSOR cursor_name [ parameter_list ] RETURN return_type;

Supported.

-

2

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]

IS select_statement;

Supported.

-

3

OPEN

Supported.

-

4

CLOSE

Supported, with differences.

GaussDB is automatically closed in the exception, but Oracle Database is not automatically closed in the exception.

5

FETCH

Supported.

-

6

CURRENT OF CURSOR

Supported.

-

1

SQL%FOUND

Supported.

2

SQL%NOTFOUND

Supported.

3

SQL%ROWCOUNT

Supported.

4

SQL%ISOPEN

Supported.

1

FOR LOOP

Supported, with differences.

In the FORALL+BULK COLLECT INTO scenario, the INTO variable returns only the execution result of a single DML statement in GaussDB, and returns the accumulated execution result of DML statements in Oracle Database.

1

Stored procedures

Supported.

2

Anonymous blocks

Supported.

3

Functions

Supported.

4

Packages

Supported.