问题场景
- 表数据
- 函数
create or replace function FN_get_interview_deptid_grade(version_id varchar2,
dept_id varchar2,
grade_id varchar2)
return varchar2 as
interview_value VARCHAR2(100);
begin
select t.msg
into interview_value
from t_interview_info t
where t.is_new = (case
when grade_id in ('1', '7', '10') then
0
else
1
end)
and t.version_id = version_id
and t.department_id = dept_id;
if interview_value is null then
interview_value := '';
end if;
return interview_value;
end;
- 调用
select fn_get_interview_deptid_grade('8a818479678214eb016783592508000b',
'10001',
'1') t
from dual;
- 报错
ORA-01422: 实际返回的行数超出请求的行数
但是单独执行
select t.msg
from t_interview_info t
where t.is_new = (case
when '1' in ('1', '7', '10') then
0
else
1
end)
and t.version_id = '8a818479678214eb016783592508000b'
and t.department_id = '10001';
只有一条
发现问题
函数的第一个参数名 version_id 和 表字段名一致,可能会导致函数中t.version_id = version_id恒为真
解决方法
1. 更改参数名
create or replace function FN_get_interview_deptid_grade(param_version_id varchar2,
param_dept_id varchar2,
param_grade_id varchar2)
2. 函数体内引用参数加上函数名 + 点
部分函数代码
where t.is_new = (case
when FN_get_interview_deptid_grade.grade_id in ('1', '7', '10') then
0
else
1
end)
and t.version_id = FN_get_interview_deptid_grade.version_id
and t.department_id = FN_get_interview_deptid_grade.dept_id;