(Oracle SQL)捕获唯一约束错误 [英] (Oracle SQL) Catching a unique constraint error

问题描述

这是我的代码:

Declare
  violation_of_constraint EXCEPTION;
BEGIN
  -- (A FEW INSERTS HERE: A, B, C)
  SAVEPOINT X;
  -- (ANOTHER INSERT HERE: D)
  IF DUP_VAL_ON_INDEX THEN
    ROLLBACK TO X;
    COMMIT;
    RAISE violation_of_constraint;
  END IF;
EXCEPTION
  WHEN violation_of_constraint THEN
    DBMS_OUTPUT.PUT_LINE('Value already exists');
    COMMIT;
END;
/

(从sql命令行)运行它时，在第11行出现此错误:

expression is of wrong type (line 11 is "IF DUP_VAL_ON_INDEX THEN")

这怎么了?

解决方案

您需要定义一个嵌套的PL/SQL块，并将 EXCEPTION 块中的异常处理为WHEN DUP_VAL_ON_INDEX ...

应该不为IF DUP_VAL_ON_INDEX

Declare
violation_of_constraint EXCEPTION;
BEGIN
  BEGIN
  -- (A FEW INSERTS HERE: A, B, C)
  SAVEPOINT X;
  -- (ANOTHER INSERT HERE: D)
  EXCEPTION 
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK TO X;
    COMMIT;
    RAISE violation_of_constraint;
  END;
EXCEPTION
WHEN violation_of_constraint THEN
DBMS_OUTPUT.PUT_LINE('Value already exists');
COMMIT;
END;
/

This is my code:

Declare
  violation_of_constraint EXCEPTION;
BEGIN
  -- (A FEW INSERTS HERE: A, B, C)
  SAVEPOINT X;
  -- (ANOTHER INSERT HERE: D)
  IF DUP_VAL_ON_INDEX THEN
    ROLLBACK TO X;
    COMMIT;
    RAISE violation_of_constraint;
  END IF;
EXCEPTION
  WHEN violation_of_constraint THEN
    DBMS_OUTPUT.PUT_LINE('Value already exists');
    COMMIT;
END;
/

When I run it (from the sql command line) I get this error at line 11:

expression is of wrong type (line 11 is "IF DUP_VAL_ON_INDEX THEN")

What could it be wrong?

解决方案

You need to define a nested PL/SQL Block, and handle exception in EXCEPTION BLOCK as WHEN DUP_VAL_ON_INDEX...

It should NOT be IF DUP_VAL_ON_INDEX

Declare
violation_of_constraint EXCEPTION;
BEGIN
  BEGIN
  -- (A FEW INSERTS HERE: A, B, C)
  SAVEPOINT X;
  -- (ANOTHER INSERT HERE: D)
  EXCEPTION 
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK TO X;
    COMMIT;
    RAISE violation_of_constraint;
  END;
EXCEPTION
WHEN violation_of_constraint THEN
DBMS_OUTPUT.PUT_LINE('Value already exists');
COMMIT;
END;
/

这篇关于(Oracle SQL)捕获唯一约束错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！