SQL Oracle：如何获取刚刚插入的行的序列号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/361304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How do I get the sequence number of the row just inserted?
提问by Mark Harrison
How do I get the sequence number of the row just inserted?
如何获取刚刚插入的行的序列号？
回答by Mark Harrison
insert ... returning.
插入...返回。
declare
s2 number;
begin
insert into seqtest2(x) values ('aaa') returning seq into s2;
dbms_output.put_line(s2);
end;
"seq" here refers to the name of the column whose value you want to store into variable s2.
此处的“seq”指的是您要将其值存储到变量 s2 中的列的名称。
in python:
在蟒蛇中：
myseq=curs.var(cx_Oracle.NUMBER)
curs.prepare("insert into seqtest2(x) values('bbb') returning seq into :x")
curs.execute(None, x=myseq)
print int(myseq.getvalue())
回答by Dan Vinton
Edit:as Mark Harrison pointed out, this assumes that you have control over how the id of your inserted record is created. If you have full control and responsibility for it, this shouldwork...
编辑：正如马克哈里森指出的那样，这假设您可以控制如何创建插入记录的 id。如果你有完全的控制权和责任，这应该有效......
Use a stored procedure to perform your insert and return the id.
使用存储过程执行插入并返回 id。
eg: for a table of names with ids:
例如：对于带有 id 的名称表：
PROCEDURE insert_name(new_name IN names.name%TYPE,
new_name_id OUT names.id%TYPE)
IS
new_id names.id%TYPE;
BEGIN
SELECT names_sequence.nextVal INTO new_id FROM dual;
INSERT INTO names(id, name) VALUES(new_id, new_name);
new_name_id := new_id;
END;
Using stored procedures for CRUD operations is a good idea regardless if you're not using an ORM layer, as it makes your code more database-agnostic, helps against injection attacks and so on.
无论您是否使用 ORM 层，对 CRUD 操作使用存储过程都是一个好主意，因为它使您的代码与数据库无关，有助于抵御注入攻击等。
回答by Alexey Honorio
My answer in C#, considering that table "testtable" has an "ID" collumn as PK with an autoinc sequence and field "testname" is a varchar field.
我在 C# 中的回答，考虑到表“testtable”有一个“ID”列作为带有 autoinc 序列的 PK 和字段“testname”是一个 varchar 字段。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;
namespace ConsoleApplication3
{
class Program
{
public static void Main(string[] args)
{
OracleConnection cn = new OracleConnection("your connection string here");
string sql = "INSERT INTO testtable(testname) VALUES('testing2') RETURNING id INTO :LASTID";
OracleParameter lastId = new OracleParameter(":LASTID", OracleDbType.Int32);
lastId.Direction = ParameterDirection.Output;
using (OracleCommand cmd = new OracleCommand(sql, cn))
{
cn.Open();
cmd.Parameters.Add(lastId);
cmd.ExecuteNonQuery();
Console.WriteLine("Last ID: " + lastId.Value.ToString());
cn.Close();
}
Console.WriteLine();
Console.ReadKey(false);
}
}
}
