如何修复PL/SQL用户管理包及包体中的语法与逻辑错误？

原代码在创建包和包体时存在多处语法错误（如标点缺失、SQL Server语法混用、参数不匹配）和逻辑错误（如表名/列名错误、类型不匹配、过程返回值处理错误），以下是具体修复点和完整修正后的代码：

主要错误点及修复说明 #

• 包定义语法错误：包内每个过程声明结尾必须加分号，原代码中Checkuserlogin和rest_password声明后缺失分号；rest_password参数不匹配，包定义仅声明了r_password，但包体需要r_user参数指定重置用户，需补充该参数。
• 包体参数语法错误：Checkuserlogin的new_username参数后缺失空格，导致类型声明无效。
• 跨数据库语法混用：PL/SQL不支持SQL Server的SET NOCOUNT ON语句，需删除。
• 逻辑匹配错误：登录验证时用user_id=new_username，但user_id是INT类型，new_username是字符串类型，类型不匹配，应改为user_name=new_username。
• 过程返回值错误：PL/SQL过程不能直接用RETURN返回字符串，需添加OUT参数传递结果。
• 表名/列名错误：rest_password中更新的表是credentials（实际表为user_man_sys），列名password和username对应实际列应为user_password和user_name。
• 过程结构不完整：Checkuserlogin过程缺少END Checkuserlogin;语句，导致包体结构混乱。

完整修正后的代码 #

1. 用户表创建语句（无错误，保留） #

CREATE TABLE user_man_sys(
    user_id      INT NOT NULL,
    user_name    NVARCHAR2(20) NOT NULL,
    user_password INT,
    created_date DATE,
    PRIMARY KEY(user_id)
);

2. 修正后的包定义 #

CREATE OR REPLACE PACKAGE mypackage AS
    -- 新增用户存储过程
    PROCEDURE add_user(
        u_id           user_man_sys.user_id%type,
        u_name         user_man_sys.user_name%type,
        u_password     user_man_sys.user_password%type,
        u_created_date user_man_sys.created_date%type
    );
    
    -- 登录验证存储过程，返回验证结果（1=成功，0=失败）
    PROCEDURE check_user_login(
        p_username  user_man_sys.user_name%type,
        p_password  user_man_sys.user_password%type,
        p_result    OUT NUMBER
    );
    
    -- 重置密码存储过程，返回操作结果信息
    PROCEDURE reset_password(
        p_new_password  user_man_sys.user_password%type,
        p_username      user_man_sys.user_name%type,
        p_result_msg    OUT VARCHAR2
    );
END mypackage;
/

3. 修正后的包体 #

CREATE OR REPLACE PACKAGE BODY mypackage AS
    PROCEDURE add_user(
        u_id           user_man_sys.user_id%type,
        u_name         user_man_sys.user_name%type,
        u_password     user_man_sys.user_password%type,
        u_created_date user_man_sys.created_date%type
    ) IS
    BEGIN
        INSERT INTO user_man_sys(user_id, user_name, user_password, created_date)
        VALUES(u_id, u_name, u_password, u_created_date);
        -- 建议由调用方控制事务，如需自动提交可添加COMMIT;
    END add_user;

    PROCEDURE check_user_login(
        p_username  user_man_sys.user_name%type,
        p_password  user_man_sys.user_password%type,
        p_result    OUT NUMBER
    ) IS
    BEGIN
        SELECT CASE 
            WHEN EXISTS(
                SELECT 1 FROM user_man_sys 
                WHERE user_name = p_username AND user_password = p_password
            ) THEN 1 
            ELSE 0 
        END INTO p_result FROM DUAL;
    END check_user_login;

    PROCEDURE reset_password(
        p_new_password  user_man_sys.user_password%type,
        p_username      user_man_sys.user_name%type,
        p_result_msg    OUT VARCHAR2
    ) IS
    BEGIN
        UPDATE user_man_sys
        SET user_password = p_new_password
        WHERE user_name = p_username;

        IF SQL%ROWCOUNT > 0 THEN
            COMMIT;
            p_result_msg := '密码重置成功。';
        ELSE
            ROLLBACK;
            p_result_msg := '密码重置失败：无效用户名。';
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            p_result_msg := '密码重置失败：' || SQLERRM;
    END reset_password;
END mypackage;
/

补充说明 #

• 登录验证通过OUT参数p_result返回结果，1代表验证成功，0代表失败；
• 重置密码通过OUT参数p_result_msg返回操作结果，同时添加异常处理捕获错误；
• 统一将过程名改为小写开头（符合PL/SQL命名规范），避免与关键字冲突。

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