78W的数据使用forall 进行批量转移；
create or replace procedure test_forall(CURRENTPAGE number ) as 02.--CURRENTPAGE number :=2 ; 03.PAGESIZE number :=3000; 04.type indexType_dial_main_ra is table of his_product_deal_main_ra%rowtype index by pls_integer; 05.index_dial_main_ra indexType_dial_main_ra ; 06.cursor cursor_main_ra is SELECT 07. ID , 08.DEALTYPE , 09.PRODUCTID , 10.PRODUCTCODE , 11.DEALDATE , 12.FORMCREATOR , 13.REXMUSERID , 14.DEALPRICE , 15.DEALPRICECNY , 16.DEALPRICEUSD , 17.NO , 18.STATE , 19.BUYERID , 20.BUYERNAME , 21.BUYERAGENTID , 22.BUYERAGENTNAME , 23.BUYERDEALERID , 24.BUYERDEALERNAME , 25.BUYERMANAGEID , 26.BUYERMANAGENAME , 27.BUYERDEALCURRENCY , 28.BUYERBP , 29.BUYEREXGRATE , 30.BUYEROUTCURRENCY , 31.BUYERSPFLAG , 32.BUYERBRIDGE , 33.BUYERFEEPRICE , 34.BUYERFEEPRICECNY , 35.BUYERFEEPRICEUSD , 36.BUYERFEEPRICERM , 37.BUYERFEEPRICERMCNY , 38.BUYERFEEPRICERMUSD , 39.BUYERMKSURE , 40.BUYERREMFLAG , 41.BUYERSTLMID , 42.BUYERSTLMTNAME , 43.BUYERSTLMTBANK , 44.BUYERSTLMTNO , 45.SELLERID , 46.SELLERNAME , 47.SELLERAGENTID , 48.SELLERAGENTNAME , 49.SELLERDEALERID , 50.SELLERDEALERNAME , 51.SELLERMANAGEID , 52.SELLERMANAGENAME , 53.SELLERDEALCURRENCY , 54.SELLERBP , 55.SELLEREXGRATE , 56.SELLEROUTCURRENCY , 57.SELLERSPFLAG , 58.SELLERBRIDGE , 59.SELLERFEEPRICE , 60.SELLERFEEPRICECNY , 61.SELLERFEEPRICEUSD , 62.SELLERFEEPRICERM , 63.SELLERFEEPRICERMCNY , 64.SELLERFEEPRICERMUSD , 65.SELLERMKSURE , 66.SELLERREMFLAG , 67.SELLERSTLMID , 68.SELLERSTLMTNAME , 69.SELLERSTLMTBANK , 70.SELLERSTLMTNO , 71.CREATORID , 72.CREATETIME , 73.MODIFIERID , 74.MODIFYTIME , 75.CHECKTIME , 76.PROCESS , 77.MANO , 78.DEALTIMES , 79.NODEALDATE 80. 81. FROM (SELECT ra.* , 82. ROWNUM RN 83. FROM his_product_deal_main_ra ra 84. WHERE ROWNUM <= (CURRENTPAGE - 1) * PAGESIZE + PAGESIZE) 85. WHERE RN > (CURRENTPAGE - 1) * PAGESIZE ; 86.begin 87. 88.open cursor_main_ra ; 89.loop 90.fetch cursor_main_ra bulk collect into index_dial_main_ra ; 91.--insert into test_deal_main_ra 92.exit when cursor_main_ra%notfound ; 93.end loop ; 94.forall i in index_dial_main_ra.first .. index_dial_main_ra.count 95.insert into test_deal_main_ra values ( 96.index_dial_main_ra(i).ID , 97.index_dial_main_ra(i).DEALTYPE , 98.index_dial_main_ra(i).PRODUCTID , 99.index_dial_main_ra(i).PRODUCTCODE , 100.index_dial_main_ra(i).DEALDATE , 101.index_dial_main_ra(i).FORMCREATOR , 102.index_dial_main_ra(i).REXMUSERID , 103.index_dial_main_ra(i).DEALPRICE , 104.index_dial_main_ra(i).DEALPRICECNY , 105.index_dial_main_ra(i).DEALPRICEUSD , 106.index_dial_main_ra(i).NO , 107.index_dial_main_ra(i).STATE , 108.index_dial_main_ra(i).BUYERID , 109.index_dial_main_ra(i).BUYERNAME , 110.index_dial_main_ra(i).BUYERAGENTID , 111.index_dial_main_ra(i).BUYERAGENTNAME , 112.index_dial_main_ra(i).BUYERDEALERID , 113.index_dial_main_ra(i).BUYERDEALERNAME , 114.index_dial_main_ra(i).BUYERMANAGEID , 115.index_dial_main_ra(i).BUYERMANAGENAME , 116.index_dial_main_ra(i).BUYERDEALCURRENCY , 117.index_dial_main_ra(i).BUYERBP , 118.index_dial_main_ra(i).BUYEREXGRATE , 119.index_dial_main_ra(i).BUYEROUTCURRENCY , 120.index_dial_main_ra(i).BUYERSPFLAG , 121.index_dial_main_ra(i).BUYERBRIDGE , 122.index_dial_main_ra(i).BUYERFEEPRICE , 123.index_dial_main_ra(i).BUYERFEEPRICECNY , 124.index_dial_main_ra(i).BUYERFEEPRICEUSD , 125.index_dial_main_ra(i).BUYERFEEPRICERM , 126.index_dial_main_ra(i).BUYERFEEPRICERMCNY , 127.index_dial_main_ra(i).BUYERFEEPRICERMUSD , 128.index_dial_main_ra(i).BUYERMKSURE , 129.index_dial_main_ra(i).BUYERREMFLAG , 130.index_dial_main_ra(i).BUYERSTLMID , 131.index_dial_main_ra(i).BUYERSTLMTNAME , 132.index_dial_main_ra(i).BUYERSTLMTBANK , 133.index_dial_main_ra(i).BUYERSTLMTNO , 134.index_dial_main_ra(i).SELLERID , 135.index_dial_main_ra(i).SELLERNAME , 136.index_dial_main_ra(i).SELLERAGENTID , 137.index_dial_main_ra(i).SELLERAGENTNAME , 138.index_dial_main_ra(i).SELLERDEALERID , 139.index_dial_main_ra(i).SELLERDEALERNAME , 140.index_dial_main_ra(i).SELLERMANAGEID , 141.index_dial_main_ra(i).SELLERMANAGENAME , 142.index_dial_main_ra(i).SELLERDEALCURRENCY , 143.index_dial_main_ra(i).SELLERBP , 144.index_dial_main_ra(i).SELLEREXGRATE , 145.index_dial_main_ra(i).SELLEROUTCURRENCY , 146.index_dial_main_ra(i).SELLERSPFLAG , 147.index_dial_main_ra(i).SELLERBRIDGE , 148.index_dial_main_ra(i).SELLERFEEPRICE , 149.index_dial_main_ra(i).SELLERFEEPRICECNY , 150.index_dial_main_ra(i).SELLERFEEPRICEUSD , 151.index_dial_main_ra(i).SELLERFEEPRICERM , 152.index_dial_main_ra(i).SELLERFEEPRICERMCNY , 153.index_dial_main_ra(i).SELLERFEEPRICERMUSD , 154.index_dial_main_ra(i).SELLERMKSURE , 155.index_dial_main_ra(i).SELLERREMFLAG , 156.index_dial_main_ra(i).SELLERSTLMID , 157.index_dial_main_ra(i).SELLERSTLMTNAME , 158.index_dial_main_ra(i).SELLERSTLMTBANK , 159.index_dial_main_ra(i).SELLERSTLMTNO , 160.index_dial_main_ra(i).CREATORID , 161.index_dial_main_ra(i).CREATETIME , 162.index_dial_main_ra(i).MODIFIERID , 163.index_dial_main_ra(i).MODIFYTIME , 164.index_dial_main_ra(i).CHECKTIME , 165.index_dial_main_ra(i).PROCESS , 166.index_dial_main_ra(i).MANO , 167.index_dial_main_ra(i).DEALTIMES , 168.index_dial_main_ra(i).NODEALDATE 169.); 170. 171.commit ; 172.--dbms_output.put_line(index_dial_main_ra.count); 173.end test_forall; 174./
---测试代码 declare 02.begin 03.for i in 1..8 loop 04.dbms_output.put_line(i); 05.test_forall(i); 06.end loop ; 07.--dbms_output.put_line(round(CURRENTPAGE+1,0)); 08.end;
10W 条 提交一次 用时66秒 ；
