oracle 如何在 PL/SQL 中捕获约束违规？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23239172/
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
How to catch constraint violation in PL/SQL?
提问by Bob Jarvis - Reinstate Monica
CREATE TABLE LOCATION (
LOCID VARCHAR2(5)
, MINQTY NUMBER
, MAXQTY NUMBER
, PRIMARY KEY (LOCID)
, CONSTRAINT CHECK_LOCID_LENGTH CHECK (LENGTH(LOCID) = 5)
, CONSTRAINT CHECK_MINQTY_RANGE CHECK (MINQTY BETWEEN 0 AND 999)
, CONSTRAINT CHECK_MAXQTY_RANGE CHECK (MAXQTY BETWEEN 0 AND 999)
, CONSTRAINT CHECK_MAXQTY_GREATER_MIXQTY CHECK (MAXQTY >= MINQTY)
);
CREATE OR REPLACE PROCEDURE ADD_LOCATION_TO_DB(ploccode VARCHAR2, pminqty NUMBER, pmaxqty NUMBER) AS
BEGIN
INSERT INTO location(locid, minqty, maxqty) VALUES (ploccode, pminqty, pmaxqty);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20081, 'Duplicate Location ID');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20086,sqlerrm);
END;
I created the above table with constraints. Now I want to test these constraints in PL/SQL procedure by catching them in exceptions. But I'm confused how to do it.
我用约束创建了上面的表格。现在我想通过在异常中捕获它们来测试 PL/SQL 过程中的这些约束。但我很困惑如何做到这一点。
回答by Bob Jarvis - Reinstate Monica
The error which occurs when a check constraint is violated is ORA-02290. Although there is no "standard" definition for this it's easy enough to declare your own exception so you can catch the -2290 when it's thrown. Let's say we have a table created as follows:
违反检查约束时发生的错误是 ORA-02290。尽管对此没有“标准”定义，但声明您自己的异常很容易，因此您可以在抛出 -2290 时捕获它。假设我们创建了一个表，如下所示：
CREATE TABLE SOME_TABLE (COL1 CHAR(1) CHECK(COL1 IN ('Y', 'N')));
and that we then run the following block:
然后我们运行以下块：
DECLARE
-- First, declare and initialize an appropriate exception
CHECK_CONSTRAINT_VIOLATED EXCEPTION;
PRAGMA EXCEPTION_INIT(CHECK_CONSTRAINT_VIOLATED, -2290);
BEGIN
INSERT INTO SOME_TABLE(COL1) VALUES ('X'); -- will violate the check constraint
RETURN;
EXCEPTION
WHEN CHECK_CONSTRAINT_VIOLATED THEN -- catch the ORA-02290 exception
DBMS_OUTPUT.PUT_LINE('INSERT failed due to check constraint violation');
WHEN OTHERS THEN -- catch all other exceptions
DBMS_OUTPUT.PUT_LINE('Something else went wrong - ' || SQLCODE ||
' : ' || SQLERRM);
END;
If you create the table as shown earlier and then run the block above you'll find that the line 'INSERT failed due to check constraint violation' will show up on DBMS_OUTPUT.
如果您按前面所示创建表，然后运行上面的块，您会发现 DBMS_OUTPUT 上将显示“INSERT failed due to check constraint denied”这一行。
Share and enjoy.
分享和享受。
