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; /
上面例子对于不熟悉PLSQL的不是很好理解,用一个简单的示例说明:
1. 创建测试表:
- <span style="font-family:Microsoft YaHei;font-size:14px;">create table tbl_returninto(
- id number,
- remark varchar2(5));</span>
- <span style="font-family:Microsoft YaHei;font-size:14px;">SQL> select * from tbl_returninto;
- ID REMARK
- ---------- --------------------------------------------------
- 2 one
- 3 two
- 4 three
- </span>
2. 插入一条记录,使用returning into在同一条SQL中获得插入的id值:
- <span style="font-family:Microsoft YaHei;font-size:14px;">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.</span>
3. 更新和删除一条记录,使用returning into获得更新和删除的id值:
- <span style="font-family:Microsoft YaHei;font-size:14px;">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.</span>
总结:
使用returning into子句可以在一条SQL中将insert、update和delete影响的行记录指定字段信息select出来,其中insert和update都是执行之后的结果,delete是执行之前的结果。当然,其实这里用的是PLSQL的语法实现。