如何修改Oracle存储过程UpdateDirector以校验输入的国家及国家代码合法性

问题现状 #

你现在的UpdateDirector存储过程有个业务规则漏洞：就算传入的COUNTRY和COUNTRY_CODE在COUNTRY表里根本不存在，它还是会老老实实更新DIRECTORS表的记录，这显然不符合要求。咱们来把这个问题解决掉。

解决思路 #

核心就是在执行更新前加一步校验：先去COUNTRY表里查一查，看看传入的国家名称和编码有没有对应的记录。如果有，再执行更新；没有的话，就终止操作并提示错误。另外我注意到原存储过程里的UPDATE语句居然更新了ID_DIR字段——咱们本来就是用这个主键来定位要更新的记录，更新它完全没必要，所以我把这部分去掉了，让代码更干净。

修改后的存储过程代码 #

create or replace procedure UpdateDirector(
    P_ID DIRECTORS.ID_DIR%TYPE,
    P_SURNAME DIRECTORS.SURNAME%TYPE,
    P_NAME IN DIRECTORS.NAME%TYPE,
    P_COUNTRY IN DIRECTORS.COUNTRY%TYPE,
    P_COUNTRY_CODE IN DIRECTORS.COUNTRY_CODE%TYPE
) is
    -- 声明变量存储匹配的国家记录数
    v_country_match_count NUMBER;
begin
    -- 校验传入的国家信息是否存在于COUNTRY表
    SELECT COUNT(*)
    INTO v_country_match_count
    FROM COUNTRY
    WHERE NAME = P_COUNTRY
      AND COUNTRY_CODE = P_COUNTRY_CODE;
      
    IF v_country_match_count > 0 THEN
        -- 国家信息有效，执行更新操作
        UPDATE DIRECTORS 
        SET SURNAME = P_SURNAME, 
            NAME = P_NAME, 
            COUNTRY = P_COUNTRY, 
            COUNTRY_CODE = P_COUNTRY_CODE 
        WHERE DIRECTORS.ID_DIR = P_ID;
        
        DBMS_OUTPUT.put_line('成功：导演记录已更新。');
    ELSE
        -- 未找到匹配的国家，终止更新并提示
        DBMS_OUTPUT.put_line('错误：国家 "' || P_COUNTRY || '"（编码：' || P_COUNTRY_CODE || '）不存在于COUNTRY表中，更新已取消。');
        -- 可选：抛出自定义异常强制回滚事务（如果需要严格的事务控制）
        -- RAISE_APPLICATION_ERROR(-20001, '传入的国家信息无效，更新已终止。');
    END IF;
exception
    when others then 
        DBMS_OUTPUT.put_line('发生意外错误：' || sqlerrm);
end;
/

补充说明 #

• 如果你的业务规则里COUNTRY_CODE是唯一的，那可以简化校验逻辑，只通过COUNTRY_CODE来匹配，不用同时校验国家名称——根据你的实际业务需求调整WHERE子句就行。
• 如果需要严格保证数据一致性，建议使用RAISE_APPLICATION_ERROR抛出自定义异常，这样会直接终止当前事务，避免任何无效的操作。

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