使用IN和OUT参数的PLSQL修改密码存储过程技术咨询

嘿，我看了你的存储过程代码，发现几个关键问题，咱们一步步来修正，让它能正常工作还更健壮：

原代码的核心问题 #

• IN参数不能被赋值：P_OLD_PW是IN类型参数，只能用来接收传入的值，不能用SELECT ... INTO给它赋值，得定义一个局部变量来存数据库里的密码。
• 验证逻辑搞反了：你现在的判断IF p_old_pw = USERS.PASSWORD逻辑不对，应该是把从数据库取出的密码和传入的旧密码做对比。
• 没处理异常场景：如果传入的用户名不存在，SELECT会抛出NO_DATA_FOUND异常，直接导致存储过程报错终止，得捕获这个情况。
• 错误反馈方式不对：PL/SQL存储过程不能用RETURN <error message>返回字符串，得通过输出参数或者异常来传递错误信息。

修正后的完整代码 #

CREATE OR REPLACE PROCEDURE CHANGE_PWD (
    P_USERNAME    IN  USERS.USERNAME%TYPE,
    P_OLD_PW      IN  USERS.PASSWORD%TYPE,
    P_NEW_PW      IN  USERS.PASSWORD%TYPE,
    P_SUCCES      OUT BOOLEAN,
    P_ERR_MSG     OUT VARCHAR2  -- 新增错误消息输出参数，方便调用方获取具体问题
) IS
    V_DB_PASSWORD USERS.PASSWORD%TYPE;  -- 局部变量存储数据库中的用户密码
BEGIN
    -- 初始化输出参数，避免默认NULL值带来的问题
    P_SUCCES := FALSE;
    P_ERR_MSG := '';

    -- 查询用户当前密码（注意：如果密码是加密存储的，这里要对传入的P_OLD_PW做同样加密后再对比）
    SELECT PASSWORD
    INTO V_DB_PASSWORD
    FROM USERS
    WHERE USERNAME = P_USERNAME;

    -- 验证旧密码是否匹配
    IF V_DB_PASSWORD = P_OLD_PW THEN
        -- 更新为新密码（加密存储的话，先对P_NEW_PW加密再存入）
        UPDATE USERS
        SET PASSWORD = P_NEW_PW
        WHERE USERNAME = P_USERNAME;
        
        COMMIT;  -- 根据你的事务管理策略调整，若调用方统一管理事务可去掉
        P_SUCCES := TRUE;
    ELSE
        P_ERR_MSG := '旧密码验证失败，请检查输入的旧密码';
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        P_ERR_MSG := '用户 ' || P_USERNAME || ' 不存在';
    WHEN OTHERS THEN
        P_ERR_MSG := '发生未知错误：' || SQLERRM;
END CHANGE_PWD;

关键修正点说明 #

1. 局部变量V_DB_PASSWORD：专门用来存储从数据库查询到的密码，避免修改IN参数，符合PL/SQL的参数规则。
2. 输出参数初始化：先把P_SUCCES设为FALSE，确保无论执行结果如何，输出参数都有明确值。
3. 异常捕获：处理了用户不存在的场景，同时用OTHERS捕获其他未知错误，通过P_ERR_MSG返回具体错误信息，方便调用方排查问题。
4. 事务控制：加上COMMIT让存储过程可以独立完成密码更新的事务，如果你是在更大的事务中调用这个存储过程，可以去掉COMMIT交给上层管理。

重要安全建议 #

永远不要明文存储密码！这是严重的安全风险，建议用哈希算法（比如SHA-256）加密后存储。示例代码如下：

-- 存储密码时加密
INSERT INTO USERS (USERNAME, PASSWORD)
VALUES ('test_user', DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW('test_pw', 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256));

-- 验证密码时，对传入的旧密码做同样加密再对比
IF V_DB_PASSWORD = DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(P_OLD_PW, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256) THEN
    -- 执行密码更新逻辑
END IF;

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