处理 ORACLE 异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1975290/
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
Handle ORACLE Exceptions
提问by RRUZ
I need to handle the ORA-01400 error(cannot insert NULL into ("SCHEMA"."TABLE_NAME"."COLUMN_NAME") ) using a exception handle.
我需要使用异常句柄处理ORA-01400 错误（无法将 NULL 插入 ("SCHEMA"."TABLE_NAME"."COLUMN_NAME") ）。
ORACLE Predefine a few Exceptions like (ACCESS_INTO_NULL, ZERO_DIVIDE and so on), but apparently does not define an Exception for the ORA-01400 error, how do I handle this particular error code?
ORACLE 预定义了一些异常，如（ACCESS_INTO_NULL、ZERO_DIVIDE 等），但显然没有为 ORA-01400 错误定义异常，我该如何处理这个特定的错误代码？
I need something like this (other suggestions are accepted).
我需要这样的东西（接受其他建议）。
....
...
INSERT INTO MY_TABLE (CODE, NAME) VALUES (aCode,aName);
COMMIT;
EXCEPTION
WHEN NULL_VALUES THEN /* i don't know this value , exist?*/
Do_MyStuff();
WHEN OTHERS THEN
raise_application_error(SQLCODE,MY_OWN_FORMAT_EXCEPTION(SQLCODE,SQLERRM),TRUE);
END;
回答by Adam Hawkes
The pre-defined PL/SQL exceptions are specialto Oracle. You really can't mess with those. When you want to have a set of predefined exceptions of your own you can't declare them "globally" like the standard ones. Instead, create an exceptionspackage which has all of the exception declarations and use that in your applicationcode.
预定义的 PL/SQL 异常是Oracle特有的。你真的不能惹那些。当您想拥有一组自己的预定义异常时，您不能像标准异常那样“全局”声明它们。相反，创建一个包含所有异常声明的异常包，并在您的应用程序代码中使用它。
Example:
例子：
CREATE OR REPLACE PACKAGE my_exceptions
AS
insert_null_into_notnull EXCEPTION;
PRAGMA EXCEPTION_INIT(insert_null_into_notnull, -1400);
update_null_to_notnull EXCEPTION;
PRAGMA EXCEPTION_INIT(update_null_to_notnull, -1407);
END my_exceptions;
/
Now use the exception defined in the package
现在使用包中定义的异常
CREATE OR REPLACE PROCEDURE use_an_exception AS
BEGIN
-- application specific code ...
NULL;
EXCEPTION
WHEN my_exceptions.insert_null_into_notnull THEN
-- application specific handling for ORA-01400: cannot insert NULL into (%s)
RAISE;
END;
/
回答by Egor Rogov
You can handle exception by its code like this:
您可以通过其代码处理异常，如下所示：
....
...
INSERT INTO MY_TABLE (CODE, NAME) VALUES (aCode,aName);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1400 THEN
Do_MyStuff();
ELSE
raise_application_error(SQLCODE,MY_OWN_FORMAT_EXCEPTION(SQLCODE,SQLERRM),TRUE);
END IF;
END;
回答by Vincent Malgrat
you can define your own exceptions, like variables (they will have the same scope as other variables so you can define package exception, etc...):
您可以定义自己的异常，例如变量（它们将与其他变量具有相同的范围，因此您可以定义包异常等...）：
SQL> DECLARE
2 NULL_VALUES EXCEPTION;
3 PRAGMA EXCEPTION_INIT(NULL_VALUES, -1400);
4 BEGIN
5 INSERT INTO t VALUES (NULL);
6 EXCEPTION
7 WHEN null_values THEN
8 dbms_output.put_line('null value not authorized');
9 END;
10 /
null value not authorized
PL/SQL procedure successfully completed
回答by Gerard Banasig
INSERT INTO MY_TABLE (CODE, NAME) VALUES (aCode,aName);
COMMIT;
EXCEPTION
WHEN NULL_VALUES /* i don't know this value , exist?*/
emesg := SQLERRM;
dbms_output.put_line(emesg);
WHEN OTHERS THEN
emesg := SQLERRM;
dbms_output.put_line(emesg);
END;
SQLERRM shows the sql error message
SQLERRM 显示 sql 错误信息
