Help Center/ GaussDB/ Service Overview/ Compatibility with Oracle/ Distributed Instances/ PL/SQL Language/ Static SQL Statements
Updated on 2024-05-14 GMT+08:00
View PDF

Static SQL Statements

Table 1 Static query SQL statements

No.

Oracle

GaussDB

Difference

1

SELECT

Supported, with differences.

GaussDB and Oracle are different in some scenarios.

In GaussDB, FOR SHARE adds a shared lock to the retrieved rows. The shared locks of different transactions do not block each other. If data is locked by FOR SHARE in one transaction and SELECT FOR SHARE SKIP LOCKED is used in another transaction, SKIP LOCKED does not skip the lock.
Table 2 Static DML SQL statements

No.

Oracle

GaussDB

1

INSERT

Supported.

2

UPDATE

Supported.

3

DELETE

Supported.

4

MERGE

Supported.

5

LOCK TABLE

Supported.
Table 3 Static TCL SQL statements

No.

Oracle

GaussDB

Difference

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.
Table 4 Pseudocolumns

No.

Oracle

GaussDB

Difference

1

CURRVAL and NEXTVAL

Supported.

-

2

LEVEL

Not 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.
Table 5 Implicit cursor attributes

No.

Oracle

GaussDB

Difference

1

SQL%FOUND

Supported, with differences.

GaussDB does not update the implicit cursor result after COMMIT or ROLLBACK. Oracle 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

Not supported.
Table 6 Explicit cursor syntax and keywords

No.

Oracle

GaussDB

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.

5

FETCH

Supported.

6

CURRENT OF CURSOR

Supported.
Table 7 Explicit cursor attributes

No.

Oracle

GaussDB

1

SQL%FOUND

Supported.

2

SQL%NOTFOUND

Supported.

3

SQL%ROWCOUNT

Supported.

4

SQL%ISOPEN

Supported.
Table 8 Cursor loop

No.

Oracle

GaussDB

1

FOR LOOP

Supported.
Table 9 Scenarios supported by autonomous transactions

No.

Oracle

GaussDB

1

Stored procedures

Supported.

2

Anonymous blocks

Supported.

3

Functions

Supported.

4

Packages

Supported.
Parent topic: PL/SQL Language