oracle用循环1到100求和_oracle循环取出表中每条数据

oracle批量更新之使用游标进行分批次更新的5种方式及速度比对 　　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 批量更新之update case when then Oracle 批量更新表字段 oracle 批量更新之将A表数据批量更新至B表 个人主页 Oracle 遍历游标的四种方式（for、fetch、while、BULK COLLECT）