Java世界

批量插入（Test）

1. <insert id="insertData" parameterType="java.util.List" >  
2.         INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) (  
3.         <foreach collection="list" item="item" index="index" separator="union all">  
4.             select  
5.                 #{item.EMPNO,jdbcType=VARCHAR},  
6.                 #{item.ENAME,jdbcType=VARCHAR},  
7.                 #{item.JOB,jdbcType=VARCHAR},  
8.                 #{item.MGR,jdbcType=NUMERIC},  
9.                 #{item.MGR,jdbcType=NUMERIC},  
10.                 TO_DATE(#{item.HIREDATE,jdbcType=VARCHAR},'yyyy-mm-dd')  
11.                   from dual  
12.         </foreach>  
13.         )  
14. </insert> 
    
    =====================================================================
    
    
    
    declare    
      type t_array is table of t_target%rowtype;    
      t_data t_array;    
      cursor c is    
        select null id, owner, object_name, null object_id, null xx, null yy    
        from dba_objects;    
    begin    
      open c;    
      loop    
        fetch c bulk collect    
        into t_data limit 100;    
        
        forall i in 1 .. t_data.count    
          insert into t_target values t_data (i);    
        exit when c%notfound;    
      end loop;    
      close c;    
      commit;    
    end; 
    
    
    
    

    <insert id="insertByProc" statementType="CALLABLE">         
    {call insertPro(#{name},#{age},#{sex},#{password},#{num})}     
    </insert>
    
    1 CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`(IN `name_in` varchar(255),IN `age_in` int,IN `sex_in` varchar(255),IN `password_in` varchar(255),IN `num_in` int)  2 BEGIN  3     SET @a=0;  4 Label:LOOP  5     SET @a=@a+1;  6     INSERT INTO person ( name, age, sex, password) VALUES (name_in,age_in,sex_in,password_in);  7      IF @a=num_in THEN  8             LEAVE Label;  9         END IF; 10 END LOOP Label; 11 END;

    
    

posted on 2014-04-15 21:38 Rabbit 閱讀(1155) 評論(0)  編輯  收藏