This topic describes the Returning feature of AliSQL. This feature enables data manipulation language (DML) statements to return result sets and provides the DBMS_TRANS package for you to trace the execution of DML statements.

Background information

The execution results of MySQL statements are divided into two types: result sets and OK or error packets. An OK or error packet contains attributes such as the number of affected and scanned records. However, the execution of a DML statement (INSERT, UPDATE, or DELETE) is often followed by the execution of the SELECT statement to query current records. In such cases, the Returning feature enables the server to respond to the client only once by combining the execution results of the two statements into a result set.

Syntax

DBMS_TRANS.returning(<Field_list>,<Statement>);

The following table describes the parameters.

Parameter Description
Field_list The fields expected to return. If you enter more than one field, make sure that they are separated with commas (,). Native fields and wildcards (*) are supported in the target table, but operations such as calculation and aggregation are not supported.
Statement The DML statement to execute. Only INSERT, UPDATE, and DELETE statements are supported.

Precautions

dbms_trans.returning() is not a transactional statement. It inherits the context of the specified transaction based on the DML statement you want to execute. To terminate a transaction, you must explicitly submit it or roll it back.

INSERT Returning

The server returns the records that were inserted into the target table by the INSERT statement.

Example:

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;

mysql> call dbms_trans.returning("*", "insert into t(id) values(NULL),(NULL)");
+----+------+---------------------+
| 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)
Note
  • If you do not specify the Field_list parameter, the server returns an OK or error packet.
    mysql> 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
    
    mysql> select * from t;
    +----+------+---------------------+
    | id | col1 | col2                |
    +----+------+---------------------+
    |  1 |    1 | 2019-09-03 10:40:55 |
    |  2 |    1 | 2019-09-03 10:40:55 |
    |  3 |    1 | 2019-09-03 10:41:06 |
    |  4 |    1 | 2019-09-03 10:41:06 |
    +----+------+---------------------+
    4 rows in set (0.00 sec)
  • The Returning feature only supports the insert values syntax and does not support other syntaxes such as create as and insert select.
    mysql> call dbms_trans.returning("", "insert into t select * from t");
    ERROR 7527 (HY000): Statement didn't support RETURNING clause

UPDATE Returning

The server returns the records updated in the target table by the UPDATE statement.

Example:

mysql> call dbms_trans.returning("id, col1, col2", "update t set col1 = 2 where id >2");
+----+------+---------------------+
| 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)
Note The Returning feature does not allow the UPDATE statement to be executed on more than one table.

DELETE Returning

The server returns the records deleted from the target table by the DELETE statement.

Example:

mysql> call dbms_trans.returning("id, col1, col2", "delete from t where id < 3");
+----+------+---------------------+
| 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)