oracle 函数返回的怎么会是表个数,而不是表记录数
0
悬赏园豆：20
[已解决问题]
解决于 2010-03-26 10:58
create or replace function auto_nid(tab in varchar2,sell in varchar2) return varchar2
is
inx varchar2(20);
iny number;
inz number;
revalues varchar2(20);
begin
inx:=1;
inz:=1;
select count(*) into iny from tab;
if (iny is null or iny=0 ) then
return iny;
else
while to_number(inx)<=iny loop
begin
select count(*) into inz from tab where sell=inx;
if inz=1 then
inx:=to_number(inx)+1;
revalues:=inx;
else
exit;
end if;
end;
end loop;
end if;
return revalues;
end;
表名是传过去的 tab,列名也是传过去的 sell
create or replace function auto_nid(tab in varchar2,sell in varchar2) return varchar2
is
inx varchar2(20);
iny number;
inz number;
revalues varchar2(20);
begin
inx:=1;
inz:=1;
select count(*) into iny from tab;
if (iny is null or iny=0 ) then
return iny;
else
while to_number(inx)<=iny loop
begin
select count(*) into inz from tab where sell=inx;
if inz=1 then
inx:=to_number(inx)+1;
revalues:=inx;
else
exit;
end if;
end;
end loop;
end if;
return revalues;
end;
最佳答案
0
用动态语句吧。
execute immediate into
create or replace function auto_nid(tab in varchar2,sell in varchar2) return varchar2
is
inx varchar2(20);
iny number;
inz number;
revalues varchar2(20);
v_sql varchar2(4000);
begin
inx:=1;
inz:=1;
v_sql:=' select count(1) from '||tab;
execute immediate v_sql into iny;
if iny>0 then
while to_number(inx)<=iny loop
v_sql:=' select count(1) from '||tab ||' where sell='||inx;
execute immediate v_sql into inz;
end loop;
end if;
end;
收获园豆：20
还是会出错 execute immediate v_sql into iny;这句出错
ora-00923:FROM deyword not found where expected
@林雨....:思路是这样的。v_sql要写对哦。
不行可以用个测试表来看看v_sql的值。
如:insert into test_table values(v_sql,sysdate);
commit;
'单引号要用两个的 ' '
通过select * from test_table看看v_SQL的值是否写对了。
