使用Oracle序列从jdbc将日志ID插入2个表中? [英] Using Oracle sequence to insert log id into 2 tables from jdbc?

问题描述

我正在使用oracle序列将log id插入到tableA中，如下所示，

I am using oracle sequence for inserting log id into tableA as follows,

String SQL_PREP_INSERT = "INSERT INTO tableA (LOG_ID,USER_ID,EXEC_TIME) VALUES"
            + " (logid_seq.nextval, ?, ?)";

然后获取最近插入的值，

Then getting the recently inserted value,

String SQL_PREP_SEL = "SELECT max(LOG_ID) FROM tableA ";

stmt = con.prepareStatement(SQL_PREP_SEL);
stmt.execute();
ResultSet rs = stmt.getResultSet();
if (rs.next()) {
logid = rs.getInt(1);
}

并将其插入到tableB中，

And inserting it into tableB,

String SQL_PREP_INSERT_DETAIL = "INSERT INTO tableB (LOG_ID, RESPONSE_CODE, RESPONSE_MSG) VALUES"
                + " (?, ?)";

        stmt = con.prepareStatement(SQL_PREP_INSERT_DETAIL);
        stmt.setInt(1, logid);
        stmt.setString(2, respCode);
        stmt.setString(3, respMsg);
        stmt.execute();

有没有一种方法可以用Java而不是Oracle生成序列并立即插入两个表中，而不是从tableA中选择并插入tableB中?

Is there a way to generate sequence in Java instead of Oracle and insert into both tables at once, instead of selecting from tableA and inserting into tableB?

推荐答案

通常，选择MAX(log_id)不会为您提供与logid_seq.nextval提供的值相同的值.假设这是一个多用户系统，则其他一些用户可能已经插入了另一行，该行的log_id值比执行查询之前刚刚插入的行要大.

In general, selecting the MAX(log_id) is not going to give you the same value that logid_seq.nextval provided. Assuming that this is a multi-user system, some other user could have inserted another row with a larger log_id value than the row you just inserted before your query is executed.

假定两个INSERT语句都在同一会话中运行，最简单的选择可能是在第二个INSERT语句中使用logid_seq.currval. currval将返回返回到当前会话的序列的最后一个值，因此它将始终返回与第一条语句中的nextval调用生成的值相同.

Assuming that both INSERT statements are run in the same session, the simplest option is probably to use the logid_seq.currval in the second INSERT statement. currval will return the last value of the sequence that was returned to the current session so it will always return the same value that was generated by the nextval call in the first statement.

INSERT INTO tableB (LOG_ID, RESPONSE_CODE, RESPONSE_MSG) 
  VALUES( logid_seq.currval, ?, ? )

或者，您可以在第一个语句中使用RETURNING子句将序列值提取到局部变量中，并在第二个INSERT语句中使用它.但这可能比仅使用currval还要复杂.

Alternatively, you could use the RETURNING clause in your first statement to fetch the sequence value into a local variable and use that in the second INSERT statement. But that is probably more work than simply using the currval.

这篇关于使用Oracle序列从jdbc将日志ID插入2个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！