PL programs allow you to insert records into a table or update one or more table rows. The number and types of elements in a record must represent a complete row.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
The pseudo column ROW is used to represent the matched row. Here is an example:
obclient> CREATE TABLE regions(region_id INT,region_name VARCHAR(50));
Query OK, 0 rows affected
obclient> INSERT INTO regions VALUES(1,'Europe'),(2,'Americas'),(3,'Asia'),(4,'Middle East and Africa');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM regions;
+-----------+------------------------+
| REGION_ID | REGION_NAME |
+-----------+------------------------+
| 1 | Europe |
| 2 | Americas |
| 3 | Asia |
| 4 | Middle East and Africa |
+-----------+------------------------+
4 rows in set
obclient> DECLARE
TYPE region_record_type IS RECORD (
id REGIONS.region_id%TYPE,
name REGIONS.region_name%TYPE
);
region_record region_record_type;
BEGIN
region_record.id := 5;
region_record.name := 'Antarctica';
insert into REGIONS values region_record;
region_record.id := 3;
region_record.name := 'Asia Update';
update REGIONS set ROW = region_record
where REGION_ID = region_record.id;
end;
/
Query OK, 0 rows affected
obclient> SELECT * FROM regions;
+-----------+------------------------+
| REGION_ID | REGION_NAME |
+-----------+------------------------+
| 1 | Europe |
| 2 | Americas |
| 3 | Asia Update |
| 4 | Middle East and Africa |
| 5 | Antarctica |
+-----------+------------------------+
Take note of the following limitations when you insert records into a table or update a table:
Record variables are allowed only in the following positions:
Notice
Record variables are not allowed in the
SELECTlist and theWHERE,GROUP BY, andORDER BYclauses.The
ROWkeyword is allowed only on the left side of theSETclause. TheROWkeyword cannot be used together with subqueries.Only one
SETclause is allowed in anUPDATEstatement that contains theROWkeyword.If the
VALUESclause in anINSERTstatement contains a record variable, no other variables or values are allowed in this clause.If the
INTOsubclause in aRETURNINGclause contains a record variable, no other variables or values are allowed in this subclause.
The following content is not supported:
Nested
RECORDtypeFunctions that returns data of the
RECORDtypeRecord insertion and table update by using the
EXECUTE IMMEDIATEstatement