Oracle使用游标进行分批次更新数据的6种方式及速度比对
1.情景展示
一共有22w条数据, 需要将A表的主键更新至B表的指定字段,如何快速完成更新?
2.解决方案
声明:
解决方案不只一种,该文章只介绍快速游标法及代码实现;
两张表的ID和ID_CARD字段都建立了索引。
方式一:使用隐式游标(更新一次提交1次)
--快速游标法 BEGIN FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据') LOOP /* LOOP循环的是TEMP_CURSOR(逐条读取TEMP_CURSOR) */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = TEMP_CURSOR.ID WHERE ID_CARD = TEMP_CURSOR.ID_CARD; COMMIT; --提交 END LOOP; END;
执行时间:
方式二:使用隐式游标(更新1000次提交1次)(推荐使用)
/* 使用隐式游标进行分批次更新 */ DECLARE V_COUNT NUMBER(10); BEGIN /* 隐式游标 */ FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据') LOOP /* 业务逻辑 */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = TEMP_CURSOR.ID WHERE ID_CARD = TEMP_CURSOR.ID_CARD; /* 更新一次,+1 */ V_COUNT := V_COUNT + 1; /* 1000条提交1次 */ IF V_COUNT >= 1000 THEN COMMIT; --提交 V_COUNT := 0; --重置 END IF; END LOOP; COMMIT; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交 END;
执行时间:
方式三:显式游标+分批次更新(1000条1提交)
/* 使用游标进行分批次更新 */ DECLARE V_COUNT NUMBER(10); V_INDEX_ID PRIMARY_INDEX10.ID%TYPE; V_ID_CARD PRIMARY_INDEX10.ID_CARD%TYPE; CURSOR TEMP_CURSOR IS SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据'; BEGIN OPEN TEMP_CURSOR; LOOP /* 取得一行游标数据并放到对应变量中 */ FETCH TEMP_CURSOR INTO V_INDEX_ID, V_ID_CARD; /* 如果没有数据则退出 */ EXIT WHEN TEMP_CURSOR%NOTFOUND; /* 业务逻辑 */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = V_INDEX_ID WHERE ID_CARD = V_ID_CARD; /* 更新一次,+1 */ V_COUNT := V_COUNT + 1; /* 1000条提交1次 */ IF V_COUNT >= 1000 THEN COMMIT; --提交 V_COUNT := 0; --重置 END IF; END LOOP; COMMIT; -- 提交所有数据,把这个去掉,可以查看是否是自己想要的效果,再决定是否提交 CLOSE TEMP_CURSOR; END;
执行时间:
10000条1提交,执行时间:
方式四:显式游标+数组(更新一次提交一次)(使用BULK COLLECT)
/* 使用游标+数组进行更新(更新一次提交一次) */ DECLARE /* 创建数组:一列多行 */ TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE; TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE; /* 起别名 */ V_INDEX_ID TYPE_INDEX_ID; V_ID_CARD TYPE_ID_CARD; /* 将查询出来的数据放到游标里 */ CURSOR TEMP_CURSOR IS SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据'; BEGIN OPEN TEMP_CURSOR; LOOP /* 取得1000行游标数据并放到对应数组中,每次读取1000条数据 */ FETCH TEMP_CURSOR BULK COLLECT INTO V_INDEX_ID, V_ID_CARD LIMIT 1000; /* 如果没有数据则退出 */ EXIT WHEN TEMP_CURSOR%NOTFOUND; /* 遍历数据 */ FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP /* 业务逻辑 */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = V_INDEX_ID(I) WHERE ID_CARD = V_ID_CARD(I); COMMIT; END LOOP; END LOOP; CLOSE TEMP_CURSOR; END;
执行时间:
方式五: 显式游标+数组(1000条提交一次)(使用BULK COLLECT)
/* 使用游标+数组进行更新(1000条提交一次) */ DECLARE /* 创建数组:一列多行 */ TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE; TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE; /* 起别名 */ V_INDEX_ID TYPE_INDEX_ID; V_ID_CARD TYPE_ID_CARD; /* 将查询出来的数据放到游标里 */ CURSOR TEMP_CURSOR IS SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据'; BEGIN OPEN TEMP_CURSOR; LOOP /* 取得1000行游标数据并放到对应数组中 */ FETCH TEMP_CURSOR BULK COLLECT INTO V_INDEX_ID, V_ID_CARD LIMIT 1000; /* 如果没有数据则退出 */ EXIT WHEN TEMP_CURSOR%NOTFOUND; /* 遍历数据 */ FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP --或者:FOR I IN 1 .. V_INDEX_ID.COUNT LOOP /* 业务逻辑 */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = V_INDEX_ID(I) WHERE ID_CARD = V_ID_CARD(I); IF I >= V_INDEX_ID.LAST THEN COMMIT; --提交 END IF; END LOOP; END LOOP; CLOSE TEMP_CURSOR; END;
执行时间:
方式六:推荐使用(使用BULK COLLECT和FORALL)
/* 使用游标+数组进行更新(BULK COLLECT和FORALL) */ DECLARE /* 创建数组:一列多行 */ TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE; TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE; /* 起别名 */ V_INDEX_ID TYPE_INDEX_ID; V_ID_CARD TYPE_ID_CARD; /* 将查询出来的数据放到游标里 */ CURSOR TEMP_CURSOR IS SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE T1.ID_CARD = T2.ID_CARD AND T1.REMARK = '**市****区数据' AND T2.REMARK = '**市****区数据'; BEGIN OPEN TEMP_CURSOR; LOOP /* 取得1000行游标数据并放到对应数组中 */ FETCH TEMP_CURSOR BULK COLLECT INTO V_INDEX_ID, V_ID_CARD LIMIT 1000; /* 如果没有数据则退出 */ EXIT WHEN TEMP_CURSOR%NOTFOUND; /* 遍历数据 */ FORALL I IN 1 .. V_INDEX_ID.COUNT-- 或者V_INDEX_ID.FIRST .. V_INDEX_ID.LAST /* 业务逻辑 */ UPDATE VIRTUAL_CARD10 SET INDEX_ID = V_INDEX_ID(I) WHERE ID_CARD = V_ID_CARD(I); COMMIT; --提交 END LOOP; CLOSE TEMP_CURSOR; END;
执行时间:
从Oracle8开始,oracle为PL/SQL引入了两个新的数据操纵语言(DML)语句:BULK COLLECT和FORALL。
这两个语句在PL/SQL内部进行一种数组处理;BULK COLLECT提供对数据的高速检索,FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。
Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。
小结:
数据量小的时候可以用方式二,数据量大的时候推荐使用方式六;
一定要建索引。
以上就是Oracle使用游标进行分批次更新的6种方式及速度比对的详细内容,更多关于Oracle 游标的资料请关注猪先飞其它相关文章!
相关文章
- 这篇文章主要介绍了Oracle使用like查询时对下划线的处理方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-16
- 这篇文章主要介绍了Java连接数据库oracle中文乱码解决方案,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-05-16
- 这篇文章主要给大家介绍了关于C#连接Oracle数据库字符串(引入DLL)的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...2020-06-25
- 这篇文章主要介绍了Oracle 实现将查询结果保存到文本txt中的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-07
- 今天教各位小伙伴怎么用Python连接oracle,文中附带非常详细的图文示例,对正在学习的小伙伴们很有帮助哟,需要的朋友可以参考下...2021-05-18
oracle实现动态查询前一天早八点到当天早八点的数据功能示例
这篇文章主要介绍了oracle实现动态查询前一天早八点到当天早八点的数据功能,涉及Oracle针对日期时间的运算与查询相关操作技巧,需要的朋友可以参考下...2020-07-11- 这篇文章主要介绍了python如何从Oracle读取数据生成图表,帮助大家更好的利用python处理数据,感兴趣的朋友可以了解下...2020-10-14
Oracle 两个逗号分割的字符串,获取交集、差集(sql实现过程解析)
这篇文章主要介绍了Oracle 两个逗号分割的字符串,获取交集、差集的sql实现过程解析,本文给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下...2020-07-11- 这篇文章主要介绍了linux服务器下oracle开机自启动设置,本文分步骤给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下...2020-07-11
- 这篇文章主要介绍了oracle按天,周,月,季度,年查询排序功能,本文给出了sql语句,每种方法给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下...2020-07-11
- 这篇文章主要介绍了Oracle如何设置表空间数据文件大小,文中讲解非常细致,帮助大家更好的理解和学习,感兴趣的朋友可以了解下...2020-07-22
- 这篇文章介绍了.net连接oracle的3种实现方法,有需要的朋友可以才可以一下...2021-09-22
Oracle利用errorstack追踪tomcat报错ORA-00903 无效表名的问题
这篇文章主要介绍了Oracle利用errorstack追踪tomcat报错ORA-00903 无效表名,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2020-07-11Maven中央仓库正式成为Oracle官方JDBC驱动程序组件分发中心(推荐)
这篇文章主要介绍了Maven中央仓库正式成为Oracle官方JDBC驱动程序组件分发中心,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2020-12-08- 这篇文章主要介绍了Oracle用户自定义异常实现过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-09-29
- 这篇文章主要给大家介绍了关于oracle索引测试的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-01-17
- 这篇文章主要介绍了C#实现远程连接ORACLE数据库的方法,通过自定义函数db_connection_test实现远程连接Oracle数据库的功能,是非常实用的技巧,需要的朋友可以参考下...2020-06-25
PL/SQL登录Oracle数据库报错ORA-12154:TNS:无法解析指定的连接标识符已解决(本地未安装Oracle需要连接服务器上的)
这篇文章主要介绍了PL/SQL登录Oracle数据库报错ORA-12154:TNS:无法解析指定的连接标识符已解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-07-11- 这篇文章主要介绍了Oracle dbf文件移动的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2020-07-11
- 这篇文章主要介绍了mybatis使用oracle进行添加数据的方法,本文给大家分享我的心得体会,需要的朋友可以参考下...2021-04-27