select from update row的实现

DTCC大会上，阿里江疑的演讲中提到一个：select from update hot row;

不明白如何在Oracle中实现的，他的意思是在一条SQL中实现update和select这条update的字段信息。

经dbsnake指点，了解到这是模仿了Oracle的returning into子句，可以将使用的DML语句影响的行记录的指定列的值select出来。

官方文档中有示例：

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm

You can use the BULK COLLECT clause in the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement:

Example 11-15 Using BULK COLLECT With the RETURNING INTO Clause

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF employees.employee_id%TYPE;
   enums NumList;
   TYPE NameList IS TABLE OF employees.last_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM emp_temp WHERE department_id = 30
      RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
   END LOOP;
END;
/

注意： 需要在sqlplus中执行set serveroutput on子句。

上面例子对于不熟悉PLSQL的不是很好理解，用一个简单的示例说明：

1. 创建测试表：

create table tbl_returninto(
id number,
remark varchar2(5));

SQL> select * from tbl_returninto;
	ID REMARK
---------- --------------------------------------------------
	 2 one
	 3 two
	 4 three

2. 插入一条记录，使用returning into在同一条SQL中获得插入的id值：

SQL> declare
  2  l_id tbl_returninto.id%type;
  3  begin
  4  insert into tbl_returninto values(tr_seq.nextval, 'one')
  5  returning id into l_id;
  6  commit;
  7  dbms_output.put_line('id=' || l_id);
  8  end;
  9  /
id=1

PL/SQL procedure successfully completed.

3. 更新和删除一条记录，使用returning into获得更新和删除的id值：

SQL> declare l_id tbl_returninto.id%type;
  2  begin
  3  update tbl_returninto
  4  set remark = 'one2'
  5  where id = 2
  6  returning id into l_id;
  7  dbms_output.put_line('UPDATE ID=' || l_id);
  8  delete from tbl_returninto where remark = 'three'
  9  returning id into l_id;
 10  dbms_output.put_line('DELETE ID=' || l_id);
 11  commit;
 12  end;
 13  /
UPDATE ID=2
DELETE ID=4

PL/SQL procedure successfully completed.

总结：

使用returning into子句可以在一条SQL中将insert、update和delete影响的行记录指定字段信息select出来，其中insert和update都是执行之后的结果，delete是执行之前的结果。当然，其实这里用的是PLSQL的语法实现。