MySQL和oracle 中插入 一 通用 INSERT INTO 表名称 VALUES (值1, 值2,....) 二 不同点
mysql中可以同时插入多条数据
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....),(值1, 值2,....)
oracle 用这种方法就会报错(没有正常结束)
寻思良久,疯狂百度 啊 啊 啊 。。。。。
通过下面2种方法
1.
insert into baob_monipaixu
(jlsx,jlrq,jlts,dingdh,lcdv24,chanx,shangyhrq,kouf,banbh,usercenter)
<foreach collection="list" item="item" index="index" open="(" close=")" separator="union">
select
#{item.jlsx},
#{item.jlrq},
#{item.jlts},
#{item.dingdh},
#{item.lcdv24},
#{item.chanx},
#{item.shangyhrq},
#{item.kouf},
#{item.banbh},
#{usercenter}
from dual
</foreach>
我是把要插入的多条数据放在list集合中 进行遍历出来的
2.
<insert id="insertSomething" parameterType="java.util.List" useGeneratedKeys="false">
begin
<foreach collection="list" item="item" index="index" separator=";">
insert into TABLE_OPERATION
(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6)
values
(
#{item.field1},
#{item.field2},
#{item.field3},
#{item.field4},
#{item.field5},
#{item.field6}
)
</foreach>
;end;
</insert>
上面的2钟方法提炼一下 就是我们常用的
insert into select 方法 语法如下
语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1
例子:
<insert id="insertZhidpc" parameterType="com.isoftstone.bussiness.order.model.Ddmx">
insert into ck_zhidpc t (ID,USERCENTER,DINGDH,CHANX,DINGDLX,CHUZZKSRQ,CHUZZJSRQ,BIAOS,CREATOR,CREATE_TIME)(
select SEQ_CK_ZHIDPC.NEXTVAL,#{usercenterappoint},t1.dingdh,#{chanxappoint},'I2',to_date(#{chuzzksrqappoint},'yyyy-MM-dd'),to_date(#{chuzzjsrqappoint},'yyyy-MM-dd'),'1','',sysdate
from dd_ddmx t1 left join ck_zhidpc a on t1.dingdh = a.dingdh
where
dingdly = 'CO' and a.dingdh is null
<if test="usercenter != null and usercenter != ''">
and t1.usercenter = #{usercenter}
</if>
<if test="chej != null and chej != ''">
and t1.chej = #{chej}
</if>
<if test="chanx != null and chanx != ''">
and t1.chanx = #{chanx}
</if>
)
</insert>