Geek asks and answers

Sql Insert statement return "zero / no rows insert"

I am writing an instruction INSERTto insert a single row into a table in a PL / SQL block. If this insert fails or the row is not inserted, I need to cancel the previously executed update statement.

I want to know under what circumstances an operator INSERTcan insert 0 rows. If an insertion fails due to some kind of exception, I can handle this in the exception block. Are there cases where it INSERTcan succeed, but not throw an exception, where do I need to check if there are any SQL%ROWCOUNT < 1?

+5
source share
3 answers

If your statement is INSERTstructured like INSERT ... VALUES, it either successfully inserts exactly one row or throws an exception. No need to check SQL%ROWCOUNT.

If your statement is INSERTstructured as INSERT ... SELECT, it is possible that the statement SELECTwill return 0 rows, the operator INSERTinserts 0 rows, and no exception will be thrown. If you think this is a mistake, you will need to check SQL%ROWCOUNTafter following the instructions INSERT.

+16
source

Yes, to find out how many lines depend on DML statements (INSERT, UPDATES, etc.), you can check the value SQL%ROWCOUNT

INSERT INTO TABLE
SELECT col1, col2,....
  FROM TAB;

if SQL%ROWCOUNT=0 then
   RAISE_APPLICATION_ERROR(-20101, 'No records inserted');
end if;
+2
source

:

  • , INSERT INTO... VALUES DML - , 1 (), , ORA

  • , INSERT INTO... VALUES DML : IGNORE_ROW_ON_DUPE_KEY, 1 (-) (-), (-) (-) , , ORA

  • , INSERT... SELECT , 0 , , SELECT

0

More articles:


All Articles