SQL INSERT 是否会失败而不在 PL/SQL 中引发异常
是否存在 SQL（特别是 Oracle-PL/SQL）中的 INSERT 可能失败而不引发异常的情况？我在 INSERT 之后看到代码检查，它验证 SQL%ROWCOUNT = 1 ELSE 它引发了自己的用户定义的异常。我不明白这怎么可能发生。
Are there any cases where an INSERT in SQL (specifically Oracle-PL/SQL) can fail without an exception being thrown? I'm seeing checks in code after INSERT where it verifies that SQL%ROWCOUNT = 1 ELSE it raises its own user-defined exception. I don't see how that can ever happen.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
正如 @TonyAndrews 和 @GinoA 提到的，
INSERT可以通过多种方式返回一行以外的内容（触发器、INSERT INTO tablename SELECT...语法）。但更大的问题是您使用的是 PL/SQL。因此，
SQL%ROWCOUNT值可以用作确定程序执行流程的条件，包括发出COMMIT或ROLLBACK语句。即使只是引发用户定义的异常，调用 PL/SQL 块也可以自行处理异常。
编辑：有人应该修改问题标题以指示 PL/SQL（如问题本身所示），因为这与标题建议的 SQL 范围不同。
As @TonyAndrews and @GinoA mentioned, there are several ways an
INSERTcould return something other than exactly one row (triggers,INSERT INTO tablename SELECT...syntax).But the bigger issue is that you're in PL/SQL. As such, the
SQL%ROWCOUNTvalue can be used as a condition to determine the program execution flow including issuingCOMMITorROLLBACKstatements.Even with just raising a user-defined exception, the calling PL/SQL block can handle the exception itself.
EDIT: Someone should modify the question title to indicate PL/SQL (as indicated in the question itself), since that's not the same thing as SQL scope the title suggests.
它不可能无一例外地失败，不是。编写代码的开发人员可能不知道这一点。
可以想象，after 语句触发器可以删除刚刚插入的行。当然，INSERT...SELECT 可能找不到要插入的行，因此会导致 SQL%ROWCOUNT = 0。
It can't fail without an exception, no. Probably the developer who wrote the code didn't know that.
An after statement trigger could conceivably delete the row just inserted. And of course an INSERT...SELECT might find no rows to insert, and so would result in SQL%ROWCOUNT = 0.
除了 @mcabral 提到的基于触发器的问题之外，您可能会成功插入但插入 1 行以外的行。例如，
insert into blah(col1) select col2 from foo样式的插入。In addition to the trigger-based issue @mcabral mentioned, you could have an insert that is successful but inserts other than 1 row. For example, the
insert into blah(col1) select col2 from foostyle of insert.