插入Oracle的方式错误-如何处理？
我刚刚找到了以下代码：
select max(id) from TABLE_NAME ...
... do some stuff ...
insert into TABLE_NAME (id, ... )
VALUES (max(id) + 1, ...)
我可以为 PK 创建一个序列，但是有一堆现有代码（经典 asp、不属于该项目的现有 asp.net 应用程序）不会使用它。
我应该忽略它，还是有办法在不进入现有代码的情况下修复它？
我认为最好的选择就是做：
insert into TABLE_NAME (id, ... )
VALUES (select max(id) + 1, ...)
选项？
I've just found the following code:
select max(id) from TABLE_NAME ...
... do some stuff ...
insert into TABLE_NAME (id, ... )
VALUES (max(id) + 1, ...)
I can create a sequence for the PK, but there's a bunch of existing code (classic asp, existing asp.net apps that aren't part of this project) that's not going to use it.
Should I just ignore it, or is there a way to fix it without going into the existing code?
I'm thinking that the best option is just to do:
insert into TABLE_NAME (id, ... )
VALUES (select max(id) + 1, ...)
Options?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以在表上创建一个触发器，用从序列中获取的值覆盖 ID 的值。
这样您仍然可以使用其他现有代码并且不会出现并发插入问题。
如果您无法更改其他软件并且他们仍然执行 select max(id)+1 插入，那是最不幸的。 然后您可以做的是：
对于您自己的插入，请使用序列并使用 -1*（序列值）填充 ID 字段。
这样插入就不会干扰已有的程序，也不会与已有的程序冲突。
（在没有 id 值的情况下进行插入，并使用触发器用序列的负值填充 ID）。
You can create a trigger on the table that overwrites the value for ID with a value that you fetch from a sequence.
That way you can still use the other existing code and have no problems with concurrent inserts.
If you cannot change the other software and they still do the select max(id)+1 insert that is most unfortunate. What you then can do is:
For your own insert use a sequence and populate the ID field with -1*(sequence value).
This way the insert will not interfere with the existing programs, but also not conflict with the existing programs.
(of do the insert without a value for id and use a trigger to populate the ID with the negative value of a sequence).
正如其他人所说，您可以使用序列覆盖数据库触发器中的最大值。 但是，如果任何应用程序代码使用该值，如下所示，则可能会导致问题：
As others have said, you can override the max value in a database trigger using a sequence. However, that could cause problems if any of the application code uses that value like this:
在插入行触发器之前使用序列。 select max(id) + 1 在多关注环境中不起作用。
Use a seqeunce in a before insert row trigger. select max(id) + 1 doesn't work in a multi concerrency environment.
这很快就变成了对应用程序架构的讨论，尤其是当问题归结为“我应该做什么？”时。
Oracle 中的主键确实需要来自序列，并且由于您正在应用程序代码中处理复杂的插入逻辑（至少是父/子插入），因此您应该进入现有代码，正如您所说（因为触发器可能会获胜）帮不了你）。
一种极端情况是，您可以取消应用程序的直接 SQL 访问，并让它们调用服务，以便集中插入/更新/删除代码。 或者您可以使用某种 MVC 架构重写代码。 我认为两者对于你的情况来说都太过分了。
id 列是否至少设置为真正的主键，以便存在防止重复发生的约束？ 如果没有，就从这里开始。
一旦主键就位，或者如果已经就位，插入开始失败只是时间问题； 当他们开始失败时你就会知道，对吗？ 如果没有，请记录错误。
现在修复应用程序代码。 当您在那里时，您至少应该编写和调用帮助程序代码，以便数据库交互发生在尽可能少的地方。 然后为其他开发人员提供一些领导，并确保他们也使用帮助程序代码。
This quickly turns in to a discussion of application architecture, especially when the question boils down to "what should I do?"
Primary keys in Oracle really need to come from sequences and since you're dealing with complex insert logic (parent/child inserts, at least) in your application code, you should go into the existing code, as you say (since triggers probably won't help you).
On one extreme you could take away direct SQL access from applications and make them call services so the insert/update/delete code can be centralized. Or you could rewrite your code using some sort of MVC architecture. I'm assuming both are overkill for your situation.
Is the id column at least set to be a true primary key so there's a constraint that will keep duplicates from occurring? If not, start there.
Once the primary key is in place, or if it already is, it's only a matter of time until inserts start to fail; you'll know when they start to fail, right? If not, get on the error-logging.
Now fix the application code. While you're in there, you should at least write and call helper code so your database interactions are in as few places as possible. Then provide some leadership to the other developers and make sure they use the helper code too.
大问题：有人依赖PK的价值吗？ 如果没有，我建议使用触发器，从序列中获取 id 并设置它。 插入根本不会指定和 id。
我不确定但是
当会话到达该代码时， 可能会导致问题。 可能是 oracle 读取表（计算 max(id)），然后尝试获取 PK 上的锁以进行插入。 如果是这种情况，两个并发会话可能会尝试使用相同的 ID，从而导致第二个会话出现异常。
您可以向触发器添加一些日志记录，以检查是否已处理已设置 ID 的插入。 所以你知道你仍然需要寻找一些使用旧代码的地方。
Big question: does anybody rely on the value of the PK? If not I would recommend using a trigger, fetching the id from a sequence and setting it. The inserts wouldn't specify and id at all.
I am not sure but the
might cause problems when to sessions reach that code. It might be that oracle reads the table (calculating max(id)) and then trys to get the lock on the PK for insertion. If that is the case two concurrent session might try to use the same id, causing an exception in the second session.
You could add some logging to the trigger, to check if inserts get processed that already have an ID set. So you know you have still to hunt down some place where the old code is used.
可以通过获取变量中的最大值然后将其插入表中来完成，如
Declare
v_max int；
从表中选择 max(id) 到 v_max 中；
插入表值((v_max+rownum),val1,val2....,valn);
犯罪;
这将在单个插入以及批量插入中创建序列。
It can be done by fetching the max value in a variable and then just insert it in the table like
Declare
v_max int;
select max(id) into v_max from table;
insert into table values((v_max+rownum),val1,val2....,valn);
commit;
This will create a sequence in a single as well as Bulk inserts.