Oracle删除关联表前检查主表行存在报no data found错误的优化方案

问题根因 #

• Oracle PL/SQL 中 SELECT INTO 语句查询结果为空时会直接抛出NO_DATA_FOUND异常，不会自动将变量赋值为 NULL，所以你的异常是查询不到匹配项目名称的行时直接触发的，走不到后续的 IF 判断
• 原代码逻辑写反：你需要当 ID 存在时才执行删除，但判断条件写的是IF PROJECT_ID_TO_DELETE IS NULL才删除，就算没有异常，逻辑也完全错误

最优优化方案 #

推荐使用聚合函数规避NO_DATA_FOUND异常，同时修正逻辑，不需要额外写异常捕获块，代码更简洁：

DECLARE
    PROJECT_ID PLS_INTEGER;
    PROJECT_ID_TO_DELETE PLS_INTEGER;
BEGIN
    PROJECT_ID := PROJECTS_SEQ.nextval; 
    -- 用MAX聚合函数，无匹配行时返回NULL，不会抛出异常
    SELECT MAX(PROJ_ID) INTO PROJECT_ID_TO_DELETE 
    FROM PROJECTS 
    WHERE PROJ_NAME_EN LIKE 'FD Project - CSB - A.1 - CFT - Final Reject of an Increase Movement - Seb';    
    
    -- 修正判断逻辑：ID不为空（存在匹配数据）时才执行删除，先删子表再删主表
    IF PROJECT_ID_TO_DELETE IS NOT NULL THEN
        DELETE FROM PROJECTS_ORG WHERE PROJ_ID = PROJECT_ID_TO_DELETE;
        DELETE FROM MVT_ONGOING WHERE MVT_ONGOING_PROJ_ID  = PROJECT_ID_TO_DELETE;
        DELETE FROM MVT_HISTORY WHERE MVT_HISTORY_PROJ_ID  = PROJECT_ID_TO_DELETE;
        DELETE FROM PROJECTS WHERE PROJ_ID  = PROJECT_ID_TO_DELETE;
    END IF;
    
    -- 原有插入逻辑保持不变
    Insert into PROJECTS (PROJ_ID, ...) values (PROJECT_ID, ...);
    Insert into PROJECTS_ORG (PROJ_ID, ...) values (PROJECT_ID, ...);
    Insert into MVT_ONGOING (MVT_ONGOING_PROJ_ID, ...) values (PROJECT_ID, ...);
    Insert into MVT_HISTORY (PROJ_ID, ...) values (PROJECT_ID, ...);
END;
/

可选方案：异常捕获处理 #

如果你需要严格校验项目名称的唯一性，可以用异常捕获的方式实现，同时可以处理重复项目名称的异常：

DECLARE
    PROJECT_ID PLS_INTEGER;
    PROJECT_ID_TO_DELETE PLS_INTEGER;
BEGIN
    PROJECT_ID := PROJECTS_SEQ.nextval; 
    SELECT PROJ_ID INTO PROJECT_ID_TO_DELETE 
    FROM PROJECTS 
    WHERE PROJ_NAME_EN LIKE 'FD Project - CSB - A.1 - CFT - Final Reject of an Increase Movement - Seb';    

    -- 查询到唯一数据时执行删除
    DELETE FROM PROJECTS_ORG WHERE PROJ_ID = PROJECT_ID_TO_DELETE;
    DELETE FROM MVT_ONGOING WHERE MVT_ONGOING_PROJ_ID  = PROJECT_ID_TO_DELETE;
    DELETE FROM MVT_HISTORY WHERE MVT_HISTORY_PROJ_ID  = PROJECT_ID_TO_DELETE;
    DELETE FROM PROJECTS WHERE PROJ_ID  = PROJECT_ID_TO_DELETE;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 无匹配数据时什么都不做，直接走后续插入逻辑
        NULL;
    WHEN TOO_MANY_ROWS THEN
        -- 存在多个同名项目时可自定义错误处理，比如抛出自定义异常
        RAISE_APPLICATION_ERROR(-20001, '存在多个匹配的项目名称，请先清理脏数据');
END;
/

注意事项 #

• 如果该项目名称可能对应多条 PROJECTS 记录，建议改用游标循环处理所有匹配 ID，避免漏掉删除数据
• 如果业务上要求项目名称唯一，可以给 PROJECTS 表的 PROJ_NAME_EN 字段添加唯一约束，避免产生脏数据

内容的提问来源于stack exchange，提问作者coeurdange57