PL/SQL allows users to insert record values into a table or update one or more rows in a table. However, the number and types of elements in the record must be sufficient to represent a complete row.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
When you update a record, you can use the pseudocolumn ROW 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 |
+-----------+------------------------+
The following restrictions apply to record insertions and updates:
Record variables can only be used in the following locations:
Notice
Record variables cannot be used in the
SELECTlist,WHEREclause,GROUP BYclause, orORDER BYclause.The keyword
ROWcan only be used on the left side of theSETclause. However,ROWcannot be used with subqueries.In an
UPDATEstatement, if you useROW, only oneSETclause is allowed.If the
VALUESclause of anINSERTstatement contains a record variable, the clause cannot contain any other variables or values.If the
INTOclause of theRETURNINGclause contains a record variable, the clause cannot contain any other variables or values.
The following features are not supported:
Nested
RECORDtypesFunctions that return a
RECORDtypeInserting and updating records using the
EXECUTE IMMEDIATEstatement.