oracle Sql Insert 语句返回“零/未插入行”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11877121/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Insert statement return "zero/no rows inserted"
提问by Sekhar
I am writing an INSERTStatement to insert one row into the table in a PL/SQL block. If this insert fails or no row is inserted then I need to rollback the previously executed update statement.
我正在编写一个INSERT语句，将一行插入到 PL/SQL 块中的表中。如果此插入失败或未插入任何行，则我需要回滚先前执行的更新语句。
I want to know under what circumstances the INSERTstatement could insert 0 rows. If the insert fails due to some exception, I can handle that in the exception block. Are there cases where the INSERTmight run successfully but not throw an exception where I need to check whether SQL%ROWCOUNT < 1?
我想知道在什么情况下该INSERT语句可以插入 0 行。如果插入因某些异常而失败，我可以在异常块中处理它。是否存在INSERT可能成功运行但不抛出异常的情况，我需要检查是否存在SQL%ROWCOUNT < 1？
回答by Justin Cave
If your INSERTstatement is structured as an INSERT ... VALUES, then it will either successfully insert exactly one row or generate an exception. There would be no need to check the SQL%ROWCOUNT.
如果您的INSERT语句结构为INSERT ... VALUES，那么它要么成功插入一行，要么生成异常。没有必要检查SQL%ROWCOUNT.
If your INSERTstatement is structured as an INSERT ... SELECT, then it is possible that the SELECTstatement will return 0 rows, the INSERTstatement will insert 0 rows, and no exception will be thrown. If you consider that to be an error, you would need to check the SQL%ROWCOUNTafter the INSERTstatement runs.
如果您的INSERT语句结构为INSERT ... SELECT，那么该SELECT语句可能会返回 0 行，该INSERT语句将插入 0 行，并且不会抛出异常。如果您认为这是一个错误，则需要检查语句运行SQL%ROWCOUNT后的情况INSERT。
回答by AnBisw
Yes, to find out how many rows are affected by DML statements (INSERT, UPDATES etc.), you can check the value of SQL%ROWCOUNT
是的，要找出受 DML 语句（INSERT、UPDATES 等）影响的行数，您可以检查 SQL%ROWCOUNT
INSERT INTO TABLE
SELECT col1, col2,....
FROM TAB;
if SQL%ROWCOUNT=0 then
RAISE_APPLICATION_ERROR(-20101, 'No records inserted');
end if;
回答by user8427372
To complete one of above comments:
要完成上述评论之一：
in case if you request
INSERT INTO ... VALUESDML statement without any hints, then indeed system will return either 1 row(s) created in case of success or ORA error in case of failurehowever if you request
INSERT INTO ... VALUESDML statement with hint: IGNORE_ROW_ON_DUPE_KEY, then you get either 1 row(s) created or 0 row(s) created in case when the row already exists and ORA error in case of other failuresand in case you request
INSERT ... SELECTas mentioned above by Justin, you can also expect to get 0 row(s) created as internal call to SELECT can indeed return
如果您在
INSERT INTO ... VALUES没有任何提示的情况下请求DML 语句，那么实际上系统将返回成功时创建的 1 行或失败时创建的 ORA 错误但是，如果您请求
INSERT INTO ... VALUES带有提示的 DML 语句：IGNORE_ROW_ON_DUPE_KEY，那么您将创建 1 行或 0 行，以防该行已存在，并在其他失败的情况下出现 ORA 错误如果您
INSERT ... SELECT按照 Justin 上面提到的方式请求，您还可以期望创建 0 行作为对 SELECT 的内部调用确实可以返回
