PL static SQL supports direct use of SQL syntax in PL 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 syntax as the following SQL statements:
SELECTstatement, which is also referred to as a query.Data manipulation language (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 of SQL statements in the following scenarios:
SELECT INTO/BULK INTORETURING INTO/BULK INTOin DML statementsFETCH INTO/BULK INTO
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)