1、创建一个function 函数 ,参数分别是“表名”和“自增列明”
函数如下:
create or replace function auto_nid(tab_name 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_name ;
execute immediate v_sql into iny;
if (iny is null or iny=0 ) then
return '1';
else
while to_number(inx)<=iny loop
begin
v_sql:='select max(to_number('||sell||')) from ' ||tab_name;
execute immediate v_sql into inz;
if inz>0 then
inx:=to_number(inz)+1;
revalues:=inx;
else
exit;
end if;
end;
end loop;
end if;
return revalues;
end;
2、调用方法
insert into table_name values (auto_nid('table_name','table_id')');