在违反约束时显示自定义消息PL / SQL [英] Display custom message when constraint is violated PL/SQL

问题描述

我正在写一个pl / sql过程。我有一个约束不允许值低于0和500以上的列。如果违反此约束，我需要显示一个自定义消息（例如ID超出范围）。目前这是异常，也是获取的输出。有另一个过程输出错误，因此使用raise_applcation_error。

I'm writing a pl/sql procedure. I've got a constraint on a column which doesn't allow values below 0 and above 500. I need to display a custom message if this constraint is violated (eg. "ID out of range"). Currently this is the exception and also the output in getting. There is another procedure that is outputting the error, hence the use raise_applcation_error.

异常

when VALUE_ERROR then
raise_application_error(-20002, 'Customer ID out of range');

错误讯息

"ORA-20000: ORA-02290: check constraint (s5849497.CK_ID_RANGE) violated"

我想要什么

"ORA-20000: Customer ID out or range"

这是整个区块，如果它有帮助

Here is the whole block if it helps

set serveroutput on;

---------------------------------------------------------------
alter table customer
add constraint ck_id_range
check (custid > 0 and custid < 500);
---------------------------------------------------------------

create or replace procedure ADD_CUSTOMER_TO_DB(pcustid number, pcustname varchar2) as

begin
    insert into customer
    values (pcustid,pcustname,0,'OK');
exception
when DUP_VAL_ON_INDEX then
  raise_application_error(-20001, 'Duplicate customer ID');
when VALUE_ERROR then
  raise_application_error(-20002, 'Customer ID out of range');
when others then
  raise_application_error(-20000, SQLERRM);
end;

谢谢

推荐答案

我猜你得到一个ORA-02290错误。在异常处理程序中捕获此异常的方法是为特定错误代码（在本例中为-2290）声明并初始化异常，然后在处理程序中使用您的自定义异常：

I'm guessing that you're getting an ORA-02290 error. The way to catch this in an exception handler is to declare and initialize an exception for the particular error code (in this case, -2290) and then use your custom exception in the handler:

create or replace procedure ADD_CUSTOMER_TO_DB(pcustid number,
                                               pcustname varchar2)
as
  eCheck_constraint_violated  EXCEPTION;
  PRAGMA EXCEPTION_INIT(eCheck_constraint_violated, -2290);
begin
    insert into customer
    values (pcustid,pcustname,0,'OK');
exception
when DUP_VAL_ON_INDEX then
  raise_application_error(-20001, 'Duplicate customer ID');
when eCheck_constraint_violated then
  raise_application_error(-20002, 'Customer ID out of range');
when others then
  raise_application_error(-20000, SQLERRM);
end;

如上所述，我刚刚用新定义的异常替换了VALUE_ERROR。

As you'll see above, I just replaced VALUE_ERROR with the newly-defined exception.

如果偶然的话你得到的错误不是-2290，只是把你在 PRAGMA EXCEPTION_INIT中看到的错误

If by chance the error you're getting is not -2290, just put the error you're seeing in PRAGMA EXCEPTION_INIT invocation above instead of -2290.

分享并享受。

这篇关于在违反约束时显示自定义消息PL / SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！