Oracle PL/SQL函数调用报ORA-06502数值或值错误问题咨询

存在的问题 #

• 变量长度不足：你定义的v_emp_last_name类型为varchar2(25)，但不满足条件时返回的字符串'No emp_id has salsry > 10000'字符长度已经超过25，赋值时就会触发长度不匹配的ORA-06502错误。
• 异常逻辑颠倒：判断p_emp_id为空时抛出的是no_emp_id异常，但异常块中no_emp_id对应的提示是“未找到员工”，预留的null_emp_id空值提示永远不会被触发。
• 查询逻辑冗余：先执行count(*)统计符合条件的员工数，再重新查询一次姓氏，同一条关联查询执行两次，没有必要，单次查询配合no_data_found异常处理即可实现，性能更优。
• 拼写错误：返回提示中的salsry为笔误，正确拼写为salary。
• 异常抛出逻辑不合理：当前代码遇到空入参时会抛出应用错误，如果你是批量遍历所有员工调用函数，抛出错误会直接中断整个执行流程。

修复后的代码参考 #

CREATE OR REPLACE function hr_funct_task4(p_emp_id number)
  return varchar2
as
  v_emp_last_name varchar2(50); -- 扩大长度适配提示文本
  null_emp_id     exception;
begin
  -- 入参非空校验
  if p_emp_id is null then
    raise null_emp_id;
  end if;

  -- 单次查询直接取结果
  select e.last_name
    into v_emp_last_name
    from jobs j
    inner join employees e on e.job_id = j.job_id
    where j.min_salary > 10000
      and e.employee_id = p_emp_id;

  return v_emp_last_name;

exception
  when no_data_found then 
    -- 没有符合条件的员工直接返回提示，不抛错
    return 'No emp_id has salary > 10000';
  when null_emp_id then 
    -- 如果不需要中断批量执行，也可以改成返回提示字符串
    raise_application_error(-20001, 'Input emp_id is null. Please, input not null value.');
end;

-- 调用代码无需修改
begin
  for i in
  (
    select hr_funct_task4(e.employee_id) as last_name
      from employees e
  )
  loop
    dbms_output.put_line(i.last_name);
  end loop;
end;

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