在过程中为每列引发父键完整性约束

Question

我在将数据插入表中的包中有以下代码。

我能够获得父键未找到异常。如何专门获取每列的约束违规消息？ 例如，如果违反proj_id则引发异常，如果违反proj_code则引发另一个异常。

PROCEDURE add_project(
     p_proj_id              project.proj_id%TYPE,
     p_proj_desc        project.proj_desc%TYPE,
     p_proj_code  project.proj_code%TYPE,
     p_proj_date      project.proj_date%TYPE
     )
      IS
    parent_not_found exception;
    pragma exception_init(parent_not_found, -2291);
     BEGIN
        INSERT
            INTO projects (proj_id,proj_desc,proj_code,proj_date) values
                  (p_proj_id,p_proj_desc,p_proj_code,p_proj_date);
exception
when parent_not_found then
raise_application_error(-20001,'Invalid');
     END;

Answer 1

看看EXCEPTION_INIT Pragma。

DECLARE
    l_parentnotfound    exception;
    l_res               integer;
    PRAGMA EXCEPTION_INIT(l_parentnotfound, -2291);
BEGIN
    -- check if the parent key exists
    select 1 into l_res from codes where code = proj_code;
    -- if not, raise exception
    if l_res <> 1 then
        raise l_parentnotfound;
    end if;
    INSERT INTO projects (proj_id,proj_desc,proj_code,proj_date) values (p_proj_id,p_proj_desc,p_proj_code,p_proj_date);
EXCEPTION WHEN l_parentnotfound THEN
    -- handle the error
END;

或者您可以使用WHEN OTHERS并查找SQLCODE.

编辑：

请注意，您不会被迫手动检查父键是否存在以引发异常，您可以保留：

DECLARE
    l_parentnotfound    exception;
    l_res               integer;
    PRAGMA EXCEPTION_INIT(l_parentnotfound, -2291);
BEGIN
    INSERT INTO projects (proj_id,proj_desc,proj_code,proj_date) values (p_proj_id,p_proj_desc,p_proj_code,p_proj_date);
EXCEPTION WHEN l_parentnotfound THEN
    -- handle the error
END;

但是，如果您希望能够轻松获得违反的约束名称，则手动为每列引发异常会很有用。您也可以尝试使用 USER_CONSTRAINTS 和 USER_CONS_COLUMNS 获取约束名称。

获取违反约束名称的另一种方法是分析SQLERRM错误消息。

Answer 2

试试

create PROCEDURE add_project(p_proj_id   project.proj_id%TYPE,
                      p_proj_desc project.proj_desc%TYPE,
                      p_proj_code project.proj_code%TYPE,
                      p_proj_date project.proj_date%TYPE) IS
BEGIN
  INSERT INTO projects
    (proj_id, proj_desc, proj_code, proj_date)
  values
    (p_proj_id, p_proj_desc, p_proj_code, p_proj_date);
  --add this   
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
END;