在包里一个这样的函数
function check_nickname (p_name in varchar) return VARCHAR
as
check_name varchar2(20):=p_name;
result_name varchar2(20);
i number:=0;
begin
loop
select nickname into result_name from passport where check_name=nickname;
if result_name is null then
return check_name;
else
i:=i+1;
check_name:=concat(p_name,to_char(i));
end if;
end loop;
return check_name;
end;
从逻辑来看,此函数的返回值应该是非null的,但实际上却绝对为null,请高手指点!
function check_nickname (p_name in varchar) return VARCHAR
as
check_name varchar2(20):=p_name;
result_name varchar2(20);
i number:=0;
begin
loop
select nickname into result_name from passport where check_name=nickname;
if result_name is null then
return check_name;
else
i:=i+1;
check_name:=concat(p_name,to_char(i));
end if;
end loop;
return check_name;
end;
从逻辑来看,此函数的返回值应该是非null的,但实际上却绝对为null,请高手指点!
2 as
3 check_name varchar2(20):=p_name;
4 result_name varchar2(20);
5 i number:=0;
6 begin
7 select nickname into result_name from (
8 select 'test1' as nickname from dual union all
9 select 'test2' as nickname from dual union all
10 select 'test3' as nickname from dual union all
11 select 'test4' as nickname from dual union all
12 select 'test5' as nickname from dual
13 ) where check_name=nickname;
14 i:=i+1;
15 check_name := concat(p_name,to_char(i));
16 return (check_name);
17 exception when no_data_found then
18 return (check_name);
19 end;
20 /Function createdSQL> select check_nickname('test1') from dual;CHECK_NICKNAME('TEST1')
--------------------------------------------------------------------------------
test11SQL> select check_nickname('test9') from dual;CHECK_NICKNAME('TEST9')
--------------------------------------------------------------------------------
test9
2. 没有查询到数据的时候,不是进行is null判断,而是异常处理
3. 不清楚i累计什么
2、可能是我判断方法错误,试试你的方法,谢谢
3、i不是累计,函数的功能是这样的:在表中查一个名字,如果名字已存在,则在名字后加个i,再查,直到最后check_name不存在于表中为止,返回check_name。
结论:
没有查询到数据的时候,应该用异常处理,而不是进行is null判断(根本执行不到这里,在查询时就已经发生了异常)
select nickname into result_name from passport where check_name=nickname
可以把这个语句改为
select count(*) into j from passport where check_name=nickname
然后判断j是否为0,为0就是没找到。
你提意的做法非常好,只是我结贴时你还没发贴,给不了你分了,
不好意思!