Oracle存储过程插入员工失败，报唯一约束错误求助

问题背景 #

创建了用于插入新员工的存储过程p_new，期望通过传入参数完成数据插入，但调用后无法成功插入，还触发了唯一约束错误。以下是原存储过程及调用代码：

原存储过程代码 #

CREATE OR REPLACE PROCEDURE p_new(p_empid IN employees.employee_id%type,
                                             p_fname IN employees.first_name%type,
                                             p_lname IN employees.last_name%type,
                                             p_email IN employees.email%type,
                                             p_pnum IN employees.phone_number%type,
                                             p_hdate IN employees.hire_date%type,
                                             p_jid IN employees.job_id%type,
                                             p_salary IN employees.salary%type,
                                             p_comm IN employees.commission_pct%type,
                                             p_mid IN employees.manager_id%type,
                                             p_deptid IN employees.department_id%type) AS
                                       
    v_empid employees.employee_id%type;
    v_fname employees.first_name%type;
    v_lname employees.last_name%type;
    v_email employees.email%type;
    v_pnum employees.phone_number%type;
    v_hdate employees.hire_date%type;
    v_jid employees.job_id%type;
    v_salary employees.salary%type;
    v_comm employees.commission_pct%type;
    v_mid employees.manager_id%type;
    v_deptid employees.department_id%type;

    CURSOR c_emp IS
    select employee_id, first_name, last_name, email, phone_number,
    hire_date, job_id, salary, commission_pct, manager_id, department_id
    from employees
    WHERE employee_id=p_empid;
    
BEGIN
    OPEN c_emp;
    FETCH c_emp INTO
    v_empid, v_fname, v_lname, v_email, v_pnum, v_hdate,
    v_jid, v_salary, v_comm, v_mid, v_deptid; 
    INSERT INTO EMPLOYEES(employee_id, first_name, last_name, email, phone_number,
    hire_date, job_id, salary, commission_pct, manager_id, department_id)
    VALUES (v_empid, v_fname, v_lnamw, v_email, v_pnum, v_hdate,
    v_jid, v_salary, v_comm, v_mid, v_deptid);   
    CLOSE c_emp;
END;
/

原调用代码 #

DECLARE
    v_empid employees.employee_id%type:=600;
    v_fname employees.first_name%type:='text';
    v_lname employees.last_name%type:='string';
    v_email employees.email%type:='string';
    v_pnum employees.phone_number%type:=123456789;
    v_hdate employees.hire_date%type:='18-SEP-2000';
    v_jid employees.job_id%type:='string';
    v_salary employees.salary%type:=4000;
    v_comm employees.commission_pct%type:=0.5;
    v_mid employees.manager_id%type:=105;
    v_deptid employees.department_id%type:=30;
BEGIN
    p_new(v_empid, v_fname, v_lname,
    v_email,v_pnum,v_hdate,v_jid,v_salary,v_comm,
    v_mid,v_deptid);
END;
/

错误原因分析 #

1. 核心逻辑错误：存储过程完全偏离需求——本该用传入的参数插入新员工，却先查询了与传入p_empid匹配的现有员工数据，再将旧数据插入表中。若p_empid对应的员工已存在，必然触发employee_id的唯一约束；若p_empid不存在，查询结果为空，插入全null值会触发主键非空约束错误。
2. 变量拼写错误：INSERT语句中的v_lnamw是笔误，正确应为v_lname，导致last_name字段无法正确赋值。
3. 冗余代码：定义的局部变量和游标完全多余，既增加复杂度又容易引入错误。

修正后的存储过程代码 #

直接使用传入参数完成插入，删除冗余代码：

CREATE OR REPLACE PROCEDURE p_new(p_empid IN employees.employee_id%type,
                                             p_fname IN employees.first_name%type,
                                             p_lname IN employees.last_name%type,
                                             p_email IN employees.email%type,
                                             p_pnum IN employees.phone_number%type,
                                             p_hdate IN employees.hire_date%type,
                                             p_jid IN employees.job_id%type,
                                             p_salary IN employees.salary%type,
                                             p_comm IN employees.commission_pct%type,
                                             p_mid IN employees.manager_id%type,
                                             p_deptid IN employees.department_id%type) AS
BEGIN
    INSERT INTO EMPLOYEES(employee_id, first_name, last_name, email, phone_number,
                          hire_date, job_id, salary, commission_pct, manager_id, department_id)
    VALUES (p_empid, p_fname, p_lname, p_email, p_pnum, p_hdate,
            p_jid, p_salary, p_comm, p_mid, p_deptid);   
END;
/

额外注意事项 #

• 确保调用时传入的p_empid（示例中为600）在employees表中不存在，避免唯一约束冲突。
• 若employee_id是自增主键，建议移除该参数，改用序列生成主键值，避免手动赋值的冲突问题。

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