The syntaxes of general SQL statements can be directly used in Procedural language (PL) static SQL statements.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Generally, PL static SQL statements use the same syntaxes as the following SQL statements:
SELECTstatement, which is also referred to as a query.DML statements:
INSERT,UPDATE,DELETE, andMERGE.Transaction control language (TCL) statements:
COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION, andLOCK TABLE.
A pseudocolumn acts like a normal column, but is not stored in a table. Static SQL supports the following SQL pseudocolumns:
CURRVALandNEXTVALLEVELOBJECT_VALUEROWIDROWNUM
In OceanBase Database, PL static SQL supports complex data types for return values in the following scenarios:
SELECT INTO/BULK INTORETURING INTO/BULK INTOin DML statementsFETCH INTO/BULK INTO
Note
OceanBase Database V4.2.0 and later allow you to import data of multiple columns into a single RECORD variable by using the `SELECT INTO` statement or into multiple OBJECT variables by using the `BULK INTO` statement.
OceanBase Database supports the serialization of complex types in text protocols, which is compatible with Oracle. When complex data types exist in the result set of an SQL query, OceanBase Database can serialize values of the complex data types into Oracle-compatible text.
Assume that you execute a query in your database and the result set returned by the query contains a complex data type, such as a complex data type that contains multiple fields.
{
"id": 1001,
"name": "Alice",
"phoneNumbers": ["123-456-7890", "098-765-4321"]
}
After serialization, the output text is as follows:
ID: 1001, Name: Alice, PhoneNumbers: (123-456-7890, 098-765-4321)