这个SQL服务器片段的PLSQL (Oracle)等价物是什么?
BEGIN TRAN
INSERT INTO mytable(content) VALUES ("test") -- assume there's an ID column that is autoincrement
SELECT @@IDENTITY
COMMIT TRAN
在C#中,可以调用myCommand.ExecuteScalar()来检索新行的ID。
如何在Oracle中插入新行,并让JDBC获得新id的副本?
编辑: BalusC提供了一个非常好的起点。出于某种原因,JDBC不喜欢命名参数绑定。这会给出“不正确设置或注册的参数”SQLException。为什么会发生这种情况?
OracleConnection conn = getAppConnection();
String q = "BEGIN INSERT INTO tb (id) values (claim_seq.nextval) returning id into :newId; end;" ;
CallableStatement cs = (OracleCallableStatement) conn.prepareCall(q);
cs.registerOutParameter("newId", OracleTypes.NUMBER);
cs.execute();
int newId = cs.getInt("newId");
发布于 2010-08-24 07:01:01
通常情况下,您将使用Statement#getGeneratedKeys()
(另请参阅this answer获取示例),但是Oracle JDBC驱动程序目前(仍然)不支持这种方式。
最好的方法是使用带有RETURNING
子句的CallableStatement
:
String sql = "BEGIN INSERT INTO mytable(id, content) VALUES (seq_mytable.NEXTVAL(), ?) RETURNING id INTO ?; END;";
Connection connection = null;
CallableStatement statement = null;
try {
connection = database.getConnection();
statement = connection.prepareCall(sql);
statement.setString(1, "test");
statement.registerOutParameter(2, Types.NUMERIC);
statement.execute();
int id = statement.getInt(2);
// ...
或者在同一事务中的INSERT
之后触发SELECT sequencename.CURRVAL
:
String sql_insert = "INSERT INTO mytable(content) VALUES (?)";
String sql_currval = "SELECT seq_mytable.CURRVAL FROM dual";
Connection connection = null;
PreparedStatement statement = null;
Statement currvalStatement = null;
ResultSet currvalResultSet = null;
try {
connection = database.getConnection();
connection.setAutoCommit(false);
statement = connection.prepareStatement(sql_insert);
statement.setString(1, "test");
statement.executeUpdate();
currvalStatement = connection.createStatement();
currvalResultSet = currvalStatement.executeQuery(sql_currval);
if (currvalResultSet.next()) {
int id = currvalResultSet.getInt(1);
}
connection.commit();
// ...
发布于 2010-08-24 06:48:46
可以使用Oracle的子句。
insert into mytable(content) values ('test') returning your_id into :var;
请查看this link以获取代码示例。您需要Oracle 10g或更高版本,以及新版本的JDBC驱动程序。
发布于 2013-11-22 13:24:32
您可以通过显式选择关键字字段来使用getGeneratedKeys()。下面是一个代码片段:
// change the string to your connection string
Connection connection = DriverManager.getConnection("connection string");
// assume that the field "id" is PK, and PK-trigger exists
String sql = "insert into my_table(id) values (default)";
// you can select key field by field index
int[] colIdxes = { 1 };
// or by field name
String[] colNames = { "id" };
// Java 1.7 syntax; try-finally for older versions
try (PreparedStatement preparedStatement = connection.prepareStatement(sql, colNames))
{
// note: oracle JDBC driver do not support auto-generated key feature with batch update
// // insert 5 rows
// for (int i = 0; i < 5; i++)
// {
// preparedStatement.addBatch();
// }
//
// int[] batch = preparedStatement.executeBatch();
preparedStatement.executeUpdate();
// get generated keys
try (ResultSet resultSet = preparedStatement.getGeneratedKeys())
{
while (resultSet.next())
{
// assume that the key's type is BIGINT
long id = resultSet.getLong(1);
assertTrue(id != 0);
System.out.println(id);
}
}
}
详情请参阅:http://docs.oracle.com/cd/E16655_01/java.121/e17657/jdbcvers.htm#CHDEGDHJ
https://stackoverflow.com/questions/3552260
复制相似问题