如何使用ExecuteScalar获取插入行生成的id？

Question

我知道在Oracle中，我可以将生成的id（或任何其他列）作为输出参数从插入的行中获取。 例如：

insert into foo values('foo','bar') returning id into :myOutputParameter

使用ExecuteScalar而不是ExecuteNonQuery是否有相同的方法？

我不想使用输出参数或存储过程。

附注：我正在使用Oracle，而不是SQL Server！！！

- andrecarlucci

---

将此作为注释，因为它是推测性的，但Sql Server有“@@SCOPE_IDENTITY”，因此语句可以是“insert into fo....; select @@SCOPE_IDENTITY”... Oracle是否有类似的功能？ - Rob

对于 SQL Server，我不了解这个系统变量 @@SCOPE_IDENTITY，但我知道一个 IDENT_CURRENT 标量函数可以实现相同的功能（SELECT IDENT_CURRENT(N'TableName')）。 - Will Marcouiller

Answer 1

如果您使用的是Oracle，您必须使用ExecuteNonQuery和ResultParameter来实现此操作，无法将其编写为查询语句。

using (OracleCommand cmd = con.CreateCommand()) {
    cmd.CommandText = "insert into foo values('foo','bar') returning id into :myOutputParameter";
    cmd.Parameters.Add(new OracleParameter("myOutputParameter", OracleDbType.Decimal), ParameterDirection.ReturnValue);
    cmd.ExecuteNonQuery(); // an INSERT is always a Non Query
    return Convert.ToDecimal(cmd.Parameters["myOutputParameter"].Value);
}

- Christian13467

---

ParameterDirection.ReturnValue 和 ParameterDirection.Output 有什么区别？ - Timeless

ParameterDirection.ReturnValue 是函数的结果。ParameterDirection.Output 用于在方法（函数或过程）中定义为 out 或 inout 的参数。 - Christian13467

唉！无论我尝试什么，每次在ExecuteNonQuery处都会收到“ORA-12537：网络会话：文件结尾”的错误信息 :( - Harry Pehkonen

如果使用“Oracle.ManagedDataAccess.Client”，则返回“ORA-12537：网络会话：文件结尾”错误，但是使用“System.Data.OracleClient”正常工作。 - samir105

有人知道当前的.NET Oracle数据访问组件是否存在漏洞吗？ - Christian13467

我遇到了ORA-12537错误：网络会话：带有ParameterDirection.Output的文件结束。我尝试了ParameterDirection.ReturnValue并且它起作用了。 - Pedro Silva

Answer 2

Oracle使用序列作为其身份列，如果我们这样说的话。

如果您为表主键设置了序列，则还必须编写触发器，该触发器将Sequence.NextValue或类似值插入到主键字段中。

假设您已经熟悉这个概念，只需查询序列，然后您就会得到答案。在Oracle中非常常见的做法是制作一个函数，该函数将返回一个INT，在函数内部执行INSERT。假设您已正确设置触发器，则可以通过查询来返回序列的值。

以下是一个实例：

CREATE TABLE my_table (
    id_my_table INT PRIMARY KEY
    description VARCHAR2(100) NOT NULL
)

CREATE SEQUENCE my_table_seq
   MINVALUE 1
   MAXVALUE 1000
   START WITH 1
   INCREMENT BY 2
   CACHE 5;

如果您想自己管理自增，可以按照以下步骤进行：

INSERT INTO my_table (
    id_my_table,
    description
) VALUES (my_table_seq.NEXTVAL, "Some description");
COMMIT;

另一方面，如果您不想关心主键的自增，可以使用触发器来实现。

CREATE OR REPLACE TRIGGER my_table_insert_trg
    BEFORE INSERT ON my_table FOR EACH ROW
BEGIN
    SELECT my_table_seq.NEXTVAL INTO :NEW.id_my_table FROM DUAL;
END;

然后，当你要插入数据时，只需按照以下方式键入INSERT语句：

INSERT INTO my_table (description) VALUES ("Some other description");
COMMIT;

在执行INSERT操作之后，您可能希望进行以下操作：

SELECT my_table_seq.CURRVAL

或者像这样选择您序列的实际值。

以下是一些有用的链接：

http://www.orafaq.com/wiki/Sequence

http://www.orafaq.com/wiki/AutoNumber_and_Identity_columns

希望这能帮到您！

- Will Marcouiller

---

嗨，威尔， 很好，我没有想到seq.CURRVAL。但是这个安全吗？我的意思是，如果有人在我的两次调用（插入、选择）之间插入了一行，会怎样？ - andrecarlucci

通常这些操作都是相对于您的数据库连接/会话来进行的，以防止出现这种情况。我在Oracle中找不到它的确切说明，但MySQL、Access、SQL Server等都是这样工作的。 - Crazy Joe Malloy

Oracle将按顺序执行插入操作。一旦完成第一个INSERT语句并返回新的当前序列值，它将执行其他插入操作。正如疯狂乔·马洛伊先生所说，无论如何都可以做到这一点。 :)我们不能忘记Oracle以事务方式工作。因此，在INSERT语句未提交之前，不会从序列中选择NEXTVALUE。无论如何，如果我记得正确的话，在执行事务时会有某种锁（表或序列）存在。 - Will Marcouiller

seq.CURRVAL是您当前Oracle会话的会话值。它只会在您执行另一个seq.NEXTVAL时发生变化。其他人对seq.NEXTVAL的引用不会影响您的seq.CURRVAL。 - Metro

只是一个小细节，但是我认为 "SELECT my_table_seq.CURRVAL" 应该改为 "SELECT my_table_seq.CURRVAL from DUAL"。 - glaucon

Answer 3

您可以使用以下代码。

    using (OracleCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = @"INSERT INTO my_table(name, address)
            VALUES ('Girish','Gurgaon India')
            RETURNING my_id INTO :my_id_param";
        OracleParameter outputParameter = new OracleParameter("my_id_param", OracleDbType.Decimal);
        outputParameter.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(outputParameter);        
        cmd.ExecuteNonQuery();        
        return Convert.ToDecimal(outputParameter.Value);
    }

- Girish Gupta

Answer 4

如果可以在表中添加一个名为“guid”的列，那么可能的一种方法是： 从C#插入一条记录时，生成一个GUID并将其写入guid列。 然后使用生成的GUID执行SELECT操作，您就得到了插入记录的ID :)

- cranky974

Answer 5

Select  t.userid_pk From Crm_User_Info T
Where T.Rowid = (select max(t.rowid) from crm_user_info t) 

这将返回您所需的ID

- fahad