06502. 00000 - "PL/SQL: numeric or value error%s"

Question

嗨，我有一项任务要解决。我写了这个代码

CREATE OR REPLACE function hr_funct_task4(p_emp_id number)
return varchar
as
v_emp_last_name varchar2(25);
v_count         number;
no_emp_id       exception;
null_emp_id     exception;
begin
if p_emp_id is null
then
raise no_emp_id;
else
select count(*)
into v_count
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;
if v_count <> 0
then
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;
else
v_emp_last_name := 'No emp_id has salsry > 10000';
end if;
end if;
return v_emp_last_name;
exception
when no_emp_id then raise_application_error(-20001, 'No emp found! Please, input another emp_id.');
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;

我收到了正确的值，但也收到了错误06502。00000-"；PL/SQL：数值或数值错误%s"；，ORA-06512：在4号线上。

你能告诉我我做错了什么吗？谢谢

Answer 1

您已声明

v_emp_last_name varchar2(25);

后来修改为

v_emp_last_name employees.last_name%type;

(我认为last_name列不超过25个字符，是吗？(

---

然后，这就是你要做的：

else
v_emp_last_name := 'No emp_id has salsry > 10000';

你猜怎么着？

SQL> select length('No emp_id has salsry > 10000') from dual;
LENGTH('NOEMP_IDHASSALSRY>10000')
---------------------------------
28         --> 28 won't fit into 25
SQL>