静态网站 搜索,搭建织梦网站教程,wordpress悬浮小宠物,中秋节的网页制作模板昨晚更新了一批数据#xff0c;用update的老办法耗时20多分#xff0c;而用 merge into 不到2秒结束#xff0c;效率真是天壤之别。具体见下#xff1a;用T_TMP_SCHOOL(135868行)的BIRTH 字段更新T_TMP_NT_CUSTOMERDETAIL( 763119行) 的BIRTHDATE 字段#xff0c;连接条件…昨晚更新了一批数据用update的老办法耗时20多分而用 merge into 不到2秒结束效率真是天壤之别。具体见下用T_TMP_SCHOOL(135868行)的BIRTH 字段更新T_TMP_NT_CUSTOMERDETAIL( 763119行) 的BIRTHDATE 字段连接条件 T_TMP_SCHOOL.ID t_tmp_nt_customerdetail.SCHOOLID--表结构create table T_TMP_NT_CUSTOMERDETAIL(CUSTOMERID VARCHAR2(15) not null,DOCCATEGORY VARCHAR2(2) not null,DOCNUMBER VARCHAR2(20) not null,BIRTHDATE VARCHAR2(8),...........SCHOOLID VARCHAR2(60));create table T_TMP_SCHOOL(ID VARCHAR2(20),COMPANY VARCHAR2(100),NAME VARCHAR2(20),BIRTH VARCHAR2(20));--两个表的数据见下select count(1) from t_tmp_nt_customerdetail t; --763119select count(1) from t_tmp_school; --135868--为了验证结果测试前先清空birthdate的值共更改 135879 行update t_tmp_nt_customerdetail tset t.birthdate nullwhere t.schoolid is not null;---实现的过程create or replace procedure p_tmp_update_customerdetailisv_BeginTran INT : 0; -- 事务标志,初始值为0表示没有事务v_ErrCode INT;v_ErrMsg VARCHAR2(200); -- 处理异常变量begin-- 设置事务标志为1表示开始事务v_BeginTran : 1;merge into t_tmp_nt_customerdetail tusing (select b.id, b.birth from t_tmp_school b where b.birth is not null) aon (t.schoolid a.id)when matched thenupdate set t.birthdate a.birth where t.schoolid is not null;COMMIT;-- 提交事务并且置事务标志为0。v_BeginTran : 0;EXCEPTIONWHEN OTHERS THEN-- 如果异常回滚事务。IF v_BeginTran 1 THENROLLBACK;END IF;v_ErrCode : SQLCODE;v_ErrMsg : SUBSTR(SQLERRM, 1, 150);dbms_output.put_line(v_ErrCode);dbms_output.put_line(v_ErrMsg);end;--执行过程用时1.11秒SQL exec p_tmp_update_customerdetail;--再次验证结果先前清空birthdate的值已经有了返回 135879 行select count(1) from t_tmp_nt_customerdetail twhere t.schoolid is not nulland t.birthdate is not null;--而用下面类似的语句这些数据执行了24分钟多update t_tmp_nt_customerdetail tset t.birthdate (select b.birthfrom t_tmp_school bwhere t.schoolid b.id)where t.schoolid (select c.id from t_tmp_school c where t.schoolid c.id)and t.schoolid is not null; ---注为什么要写这个罗嗦的条件呢因为没有这个条件就把整个表的数据全部更新了因此必须写所以大家应该多实践不要被一些表面现象所蒙蔽。
