select from update row的實現,updaterow
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=1PL/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=2DELETE ID=4PL/SQL procedure successfully completed.
總結:
使用returning into子句可以在一條SQL中將insert、update和delete影響的行記錄指定欄位資訊select出來,其中insert和update都是執行之後的結果,delete是執行之前的結果。當然,其實這裡用的是PLSQL的文法實現。