select from update row的实现
来源:互联网 发布:淘宝云盘在哪里 编辑:程序博客网 时间:2024/06/16 05:59
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的语法实现。
0 0
- select from update row的实现
- select from update row的实现
- [MySQL] SELECT ... FOR UPDATE 的 Row Lock 與 Table Lock
- 【MySQL】select for update 的Row Lock 与Table Lock
- UPDATE FROM SELECT.txt
- SQL UPDATE from SELECT
- mysql SELECT ... FROM UPDATE
- {"Batch update returned unexpected row count from update; actual row ?
- mysql的insert into from select 和update by select
- 有关Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1问题的解法
- 关于Hibernate的 Batch update returned unexpected row count from update异常
- Hibernate的Batch update returned unexpected row count from update错误
- 关于Hibernate的 Batch update returned unexpected row count from update异常
- 关于Hibernate的 Batch update returned unexpected row count from update异常
- 关于Hibernate的 Batch update returned unexpected row count from update异常
- SQL: Update from a Select
- select * from table_name for update; 和 select t.*, t.rowid from table_name t的区别
- Batch update returned unexpected row count from update [0]
- Android控件之ScrollView探究
- 编程基本功之数组交叉合并
- 8大排序算法图文讲解
- java6,7,8中String.intern进化史与深度剖析
- oracle10g/11g 新特性十二(数据库空间管理)
- select from update row的实现
- ongoDB 基础(七)复制
- 开源 java CMS - FreeCMS2.3 站内信
- HM测试数据处理
- 一个优秀程序员具备的15个特性
- Freemarker的常用技巧总结
- POJ 1006
- 【第八章】 对ORM的支持 之 8.3 集成iBATIS ——跟我学spring3
- 通过 ANE(Adobe Native Extension) 启动Andriod服务 推送消息(二)