最佳实践:.NET:如何针对oracle数据库返回PK? [英] Best practices: .NET: How to return PK against an oracle database?
问题描述
对于SQLServer，似乎普遍认为，假设您使用的是自动递增字段，则在插入的末尾添加SELECT SCOPE_IDENTITY()是返回新插入记录的PK的最佳方法.为PK.
With SQLServer, it seems to be generally accepted that adding a SELECT SCOPE_IDENTITY() to the end of your insert is the best way to return the PK of the newly-inserted record, assuming you're using an auto-increment field for the pk.
但是，我似乎找不到与Oracle相当的产品.
However, I can't seem to find the equivalent for Oracle.
最佳实践似乎是使用序列来生成PK，但是对于如何实现它也有不同的选择.您是让开发人员插入sequence.nexval还是使用触发器?
Best practice seems to be to use a sequence to generate the PK, but there are different options for how to implement even that. Do you leave it up to the developer to insert sequence.nexval, or use a trigger?
无论哪种情况，找回新ID似乎都是一个常见问题.
In either case, getting the new ID back seems to be a common problem.
我遇到的建议和解决方案包括:
Suggestions and solutions I've run across include:
- 创建一个存储过程以返回PK
- 运行seq.nextval中的选择ID，然后将其传递给插入
- 插入后选择max(id)(注意:请勿这样做！)
- 在插入中添加RETURNING子句
在这种情况下，最佳实践"解决方案应该是什么?
What should the "best practice" solution be for this situation?
推荐答案
您可以使用RETURNING子句在Oracle存储过程中执行此操作.
You can use the RETURNING clause to do this in Oracle stored procs.
例如:
TABLEA具有名称和EMP_ID.插入记录时，将在内部填充EMP_ID.
TABLEA has NAME and EMP_ID. EMP_ID is populated internally when records are inserted.
插入到TABLEA(NAME)值('BOB')中，将EMP_ID返回到o_EMP_ID；
INSERT INTO TABLEA(NAME) VALUES ('BOB') RETURNING EMP_ID INTO o_EMP_ID;
假设该行位于输出参数为o_EMP_ID的存储过程中.
That's assuming that line is in a stored proc with an output parameter of o_EMP_ID.
希望有帮助...如果没有帮助，这是一个更详细的示例:
Hope that helps... if not, here's a more detailed example:
http://download.oracle.com /docs/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm
这篇关于最佳实践:.NET:如何针对oracle数据库返回PK?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
