如何在oracle中插入multipleinsert语句

I want to execute multiple sql insert statements which are stored in string in java . Below is a string :

我想执行多个sql insert语句,这些语句存储在java中的字符串中。下面是一个字符串:

insert_string = "INSERT INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'rahul', 'START', '12-08-15 06:32:33.577676 PM');
INSERT INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'abhishek', 'START', '12-08-15 06:32:33.577676 PM');"

I tried to execute it by batchExecute(), but it is not working for me. Please suggest what should I do??

我尝试通过batchExecute()执行它,但它对我不起作用。请建议我该怎么办?

2 个解决方案

#1

Here is an edited example from: https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html, from the section Using Statement Objects for Batch Updates

以下是一个经过编辑的示例:https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html,来自使用批处理更新的语句对象部分

It seems like you need to split your insert_string between batch calls as follows:

您似乎需要在批处理调用之间拆分insert_string,如下所示:

Connection con = createConnection(); // Get the connection somehow
Statement stmt = null;

try {
    con.setAutoCommit(false);
    stmt = con.createStatement();

    stmt.addBatch("INSERT INTO temp values(" +
        "    TO_DATE('12-08-15','DD/MM/RRRR') - 1, " +
        "    'rahul', 'START', '12-08-15 06:32:33.577676 PM') " );

    stmt.addBatch("INSERT INTO temp values(" +
        "    TO_DATE ('12-08-15','DD/MM/RRRR') - 1, " + 
        "    'abhishek','START','12-08-15 06:32:33.577676 PM') ");

    // ...the rest of the statements

    int [] updateCounts = stmt.executeBatch();

    con.commit(); // don't forget to call commit

 } catch(BatchUpdateException b) {
    // Handle this Batch Update Exception
 } catch(SQLException ex) {
    // Handle regular SQL Exception;
 } finally {
    // Make sure to call this.
    if (stmt != null) { stmt.close(); }
        con.setAutoCommit(true);
 }

#2

Various SO answers such as this one and this one imply that you can't use your current approach.

各种SO答案,例如这一个和这个答案意味着你不能使用你当前的方法。

Marin's answer (which I've upvoted) shows how to batch the individual INSERT statements.

Marin的答案(我已经投票)显示了如何批处理各个INSERT语句。

You can also do this in a single query using the INSERT ALL statement. As with Marin's answer, you'll need to alter your SQL:

您也可以使用INSERT ALL语句在单个查询中执行此操作。与Marin的回答一样,您需要更改SQL:

INSERT ALL
  INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'rahul', 'START', '12-08-15 06:32:33.577676 PM')
  INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'abhishek', 'START', '12-08-15 06:32:33.577676 PM')
  SELECT * FROM DUAL;";

Also, I'd avoid the RRRR format string unless you happen to be working with a non-Y2K compliant application or it's your lifelong dream to create one. Taking it a step further, you don't even need TO_DATE here because Oracle supports the ANSI date literal syntax:

此外,我会避免使用RRRR格式字符串,除非您碰巧使用的是非Y2K兼容的应用程序,或者您的终身梦想是创建一个。更进一步,这里甚至不需要TO_DATE,因为Oracle支持ANSI日期文字语法:

INSERT ALL
  INTO temp values(DATE '2015-12-08' - 1, 'rahul', 'START', '12-08-15 06:32:33.577676 PM')
  INTO temp values(DATE '2015-12-08' - 1, 'abhishek', 'START', '12-08-15 06:32:33.577676 PM')
  SELECT * FROM DUAL;

Addendum

In case the last column being inserted is a timestamp type, I'd recommend using the ANSI timestamp literal. If you're relying on an implicit conversion using your default display format, that's a dangerous practice because someone can break your code by simply changing the default timestamp display format.

如果插入的最后一列是时间戳类型,我建议使用ANSI时间戳文字。如果您依赖于使用默认显示格式的隐式转换,那么这是一种危险的做法,因为有人可以通过简单地更改默认时间戳显示格式来破坏您的代码。

The answer updated with ANSI timestamp values goes like this (for the ANSI values you need to use a 24-hour clock):

使用ANSI时间戳值更新的答案如下所示(对于需要使用24小时时钟的ANSI值):

INSERT ALL
  INTO temp values(DATE '2015-12-08' - 1, 'rahul', 'START', TIMESTAMP '2015-12-08 18:32:33.577676')
  INTO temp values(DATE '2015-12-08' - 1, 'abhishek', 'START', TIMESTAMP '2015-12-08 18:32:33.577676')
  SELECT * FROM DUAL;

Understand that when you specify date and timestamp values like this your Oracle code works every time, regardless of the current session or database date/timestamp format settings.

了解当您指定这样的日期和时间戳值时,无论当前会话或数据库日期/时间戳格式设置如何,您的Oracle代码都会每次都有效。

Of course, since you're doing this through Java, an even better approach would be to use prepared statements. They're also immune to the date/timestamp format settings, plus there's the even more important benefit of defending against SQL injection.

当然,既然你是通过Java做的,那么更好的方法就是使用预处理语句。它们也不受日期/时间戳格式设置的影响,而且防御SQL注入还有更重要的好处。

#1