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

问题描述

我知道，在甲骨文我可以从一个插入的行生成的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：我使用的甲骨文，不是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，然后在你的函数，你执行你的插入。假设你有正确设置你的触发器，你将能够通过查询它来回报您的序列的值。

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.

下面是一些链接，可以帮助：

Here are some links to help:

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

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

希望这有助于！

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