批量提交时，可将数据封装成数组，传入存储过程，执行批量提交。例如：批量添加一批用户到数据库的user 表。
 

 Tb_user
 
 
 
    

     User_id(number)
    
 
 
    

     User_name(varchar2(50))
    
 
 
    

     User_con(varchar2(50))
    
 
 
    

     10001
    
 
 
    

     Jim
    
 
 
    

     America
    
 
 
    

     10002
    
 
 
    

     tom
    
 
 
    

     English
    
 
 
    

     10003
    
 
 
    

     张三
    
 
 
    

     中国
    
 
 
 

 数据库中定义一type。来封装数据。如下：
 

 User_infor:
 

 Create or replace type user_infor as object
 

 (user_id number,
 

 User_name varchar2(50),
 

 User_con varchar2(50)
 

 )
 

 User_array:
 

 Create or replace type user_array as is varchar2(100) of user_infor
 

 创建存储过程：
 

 Create or replace procedure INSER_USER_INFOR(user_in user_array,rs_return out number) as
 

 I int;
 

 Begin
 

 Rs_return:=200;
 

 While i<=user_in.count loop
 

 Insert into tb_user values(user_in(i),user_id,user_in(i).user_name,user_in(i).user_con);
 

 Commit;
 

 I:=i+1;
 

 End loop;
 

  
 

 End;
 

 Java 代码如下：
 

 Conn=connection;
 
CallableStatement proc=conn.prepareCall(“{call INSER_USER_INFOR(?,?) }”);

若表数据比较大，直接update会时间很慢，造成锁表，内存不够等问题，可以使用存储过程分批次进行更新提交，where条件最好加上索引，当然也可以批次删除；
 
drop procedure if exists upt_tbl_nm;
delimiter //
create procedure upt_tbl_nm()
begin
    declare ncnt int default 0;
    declare nrow int default 0;
    
    select count(*) into nrow from tbl_nm;
    repeat
        update tbl_nm set trans_dt='20200113' where trans_dt='20190113' limit 5000;
        set ncnt=ncnt+5000;
       until ncnt >= nrow
    end repeat;
end;
//
delimiter ;
call upt_tbl_nm();
drop procedure if exists upt_tbl_nm;

oracle 存储过程批量插入大量数据
 

declare
  numCount number;
  userName varchar2(512);
  email varchar2(512);
  markComment varchar2(512);
  maxId number;
  
begin
  numCount := 1;
  userName := 'userName number : .';
  email := 'email number : ';
  markComment := 'markComment number  .';

  for i in 1 .. 1000 loop
    for j in 1 .. 1000 loop 
      
      insert into userInfo (userId, userName, email, markComment) values 
      (numCount, userName||numCount, email||numCount, markComment||numCount);
      
      numCount := numCount + 1;
    end loop;
    commit;
  end loop;
end;
/


 

 

 CREATE OR REPLACE PROCEDURE p_insert_part
 IS
  
  
 --批量提交参数
 num NUMBER;
 begin
   num := 0;
   for i in 1 .. 20000
     loop
        num:=num+1;
       insert into SINO_LOANM (loan_code, creditor)
      values ('C01-20200609'|| to_char(num), 'KH20191628');
  
       num:=num+1;
       if mod(num,10000)=0 then
         commit;
       end if;
   end loop
   commit;
 end;
 

 ---执行存储过程
 begin
     p_insert_part ;
 end;