如何使用 ExecuteScalar 从插入的行中获取生成的 id? [英] How to get the generated id from an inserted row using ExecuteScalar?

问题描述

我知道在 Oracle 中，我可以从插入的行中获取生成的 id(或任何其他列)作为输出参数.例如:

I know that in Oracle I can get the generated id (or any other column) from an inserted row as an output parameter. Ex:

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

有没有办法做同样的事情，但使用 ExecuteScalar 而不是 ExecuteNonQuery?

Is there a way to do the same, but using ExecuteScalar instead of ExecuteNonQuery?

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

I don't want to use output parameters or stored procedures.

ps:我用的是Oracle，不是sql server！！！

ps: I'm using Oracle, not sql server!!!

推荐答案

Oracle 使用序列作为他的标识列，如果我们可以这么说的话.

Oracle uses sequences as for his identity columns, if we may say so.

如果您为表主键设置了序列，您还必须编写一个触发器，将 Sequence.NextValue 左右插入到您的主键字段中.

If you have set a sequence for your table primary key, you also have to write a trigger that will insert the Sequence.NextValue or so into your primary key field.

假设您已经熟悉这个概念，只需查询您的序列，然后您就会得到答案.在 Oracle 中非常实践的是让自己成为一个返回 INT 的函数，然后在你的函数中执行你的 INSERT.假设您已正确设置触发器，您将能够通过查询返回序列的值.

Assuming that you are already familiar with this concept, simply query your sequence, then you will get your answer. What is very practiced in Oracle is to make yourself a function which will return an INT, then within your function, you perform your INSERT. Assuming that you have setup your trigger correctly, you will then be able to return the value of your sequence by querying it.

这是一个例子:

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;

如果您想自己管理自动增量，方法如下:

If you want to manage the auto-increment yourself, here's how:

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

另一方面，如果您不想关心 PRIMARY KEY 的增量，您可以继续使用触发器.

On the other hand, if you wish not to care about the PRIMARY KEY increment, you may proceed with a trigger.

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 语句:

Then, when you're inserting, you simply type the INSERT statement as follows:

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

在插入之后，我猜你会想要

After an INSERT, I guess you'll want to

SELECT my_table_seq.CURRVAL

或类似的东西来选择序列的实际值.

or something like this to select the actual value of your sequence.

这里有一些帮助链接:

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

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

希望这会有所帮助！

这篇关于如何使用 ExecuteScalar 从插入的行中获取生成的 id?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！