INSERT INTO USER
(NAME,AGE,SEX)
VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');
Oracle写法：
//多次单条插入
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val1_1', 'val1_2', 'val1_3');
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val2_1', 'val2_2', 'val2_3');
INSERT INTO USER (NAME,AGE,SEX) VALUES ('val3_1', 'val3_2', 'val3_3');
//批量插入
INSERT ALL
INTO USER (NAME,AGE,SEX) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO USER (NAME,AGE,SEX) VALUES ('val2_1', 'val2_2', 'val2_3')
INTO USER (NAME,AGE,SEX) VALUES ('val3_1', 'val3_2', 'val3_3')
SELECT 1 FROM DUAL;
INSERT INTO USER (NAME,AGE,SEX)
select ('val1_1', 'val1_2', 'val1_3') from dual union all
select ('val2_1', 'val2_2', 'val2_3') from dual union all
select ('val3_1', 'val3_2', 'val3_3') from dual
这样一来，既简单又能少维护一套SQL，两全其美。
下面是XML文件里各种写法的代码。
<!--MySQL的批量插入-->
<insert id="batchInsertUser" databaseId="mysql">
INSERT INTO USER
(NAME,AGE,SEX)
VALUES
<foreach collection="userList" index="index" item="user" separator=",">
(#{user.name},#{user.age},#{user.sex}）
</foreach>
</insert>
<!--Oracle的批量插入-->
<insert id="batchInsertUser" databaseId="oracle">
BEGIN
<foreach collection="userList" index="index" item="user" separator=";">
INSERT INTO USER
(NAME,AGE,SEX)
VALUES
(#{user.name},#{user.age},#{user.sex}）
</foreach>
;END;
</insert>
