Returning-PolarDB(PolarDB)-阿里云帮助中心

INSERT, UPDATE, and DELETE statements normally return only the number of affected rows. To inspect the actual row data after a data manipulation language (DML) operation, you typically issue a follow-up SELECT—adding an extra round trip to your application. The returning feature eliminates that extra query by having the server return the affected rows as a result set in the same response.

PolarDB-X provides this through the DBMS_TRANS package via a stored procedure call.

Prerequisites

Before you begin, ensure that you have:

A PolarDB-X Standard Edition instance with the MySQL 8.0 engine

Syntax

CALL dbms_trans.returning("<Field_list>", "<Statement>");

Parameter	Description
`Field_list`	The columns to return, separated by commas. Supports column names and the wildcard `*`. Calculation and aggregation expressions are not supported. Pass an empty string (`""`) to suppress the result set and get an OK or ERR packet instead.
`Statement`	The DML statement to execute. Supports INSERT, UPDATE, and DELETE.

Limitations

Scope	Limitation
INSERT	Only INSERT VALUES-style statements are supported. INSERT SELECT and CREATE TABLE AS SELECT are not supported.
UPDATE	Multi-table UPDATE is not supported.
`Field_list`	Calculation and aggregation expressions are not supported.

Usage notes

dbms_trans.returning() is not itself a transactional statement. It inherits the transaction context of the DML statement it wraps. To end the transaction, explicitly commit or roll it back.

INSERT returning

INSERT returning returns the rows as they were committed—including auto-generated values and column defaults. This is especially useful when inserting rows with AUTO_INCREMENT primary keys or columns with DEFAULT values, because you get the final stored values back without a follow-up query.

Example:

Create a table and insert two rows:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) NOT NULL DEFAULT '1',
  `col2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CALL dbms_trans.returning("*", "insert into t(id) values(NULL),(NULL)");

Output:

+----+------+---------------------+
| id | col1 | col2                |
+----+------+---------------------+
|  1 |    1 | 2019-09-03 10:39:05 |
|  2 |    1 | 2019-09-03 10:39:05 |
+----+------+---------------------+
2 rows in set (0.01 sec)

The result includes the auto-generated id values and the DEFAULT values for col1 and col2.

If Field_list is empty (""), the server returns an OK packet instead of a result set.

CALL dbms_trans.returning("", "insert into t(id) values(NULL),(NULL)");
-- Query OK, 2 rows affected (0.01 sec)
-- Records: 2  Duplicates: 0  Warnings: 0

INSERT SELECT and CREATE TABLE AS SELECT are not supported. Attempting them returns an error:

CALL dbms_trans.returning("", "insert into t select * from t");
-- ERROR 7527 (HY000): Statement didn't support RETURNING clause

UPDATE returning

UPDATE returning returns the affected rows with their new values after the update is applied.

Example:

Update rows where id > 2 and return the updated rows:

CALL dbms_trans.returning("id, col1, col2", "update t set col1 = 2 where id > 2");

Output:

+----+------+---------------------+
| id | col1 | col2                |
+----+------+---------------------+
|  3 |    2 | 2019-09-03 10:41:06 |
|  4 |    2 | 2019-09-03 10:41:06 |
+----+------+---------------------+
2 rows in set (0.01 sec)

Multi-table UPDATE is not supported.

DELETE returning

DELETE returning returns the rows as they existed before deletion. Use this to capture deleted data in the same operation without a separate query.

Example:

Delete rows where id < 3 and return the deleted rows:

CALL dbms_trans.returning("id, col1, col2", "delete from t where id < 3");

Output:

+----+------+---------------------+
| id | col1 | col2                |
+----+------+---------------------+
|  1 |    1 | 2019-09-03 10:40:55 |
|  2 |    1 | 2019-09-03 10:40:55 |
+----+------+---------------------+
2 rows in set (0.00 sec)