oracle新增主键

create or replace function func_nextid(i_tab in varchar2,i_column in varchar2)
return  number
as
v_no number(10);
v_tab varchar2(100);
v_sql varchar2(4000);
begin
  v_sql:='SELECT NVL(MAX('||i_column||'),-1) FROM '||i_tab;
  execute immediate v_sql into v_no;
  select upper(i_tab) into v_tab from dual;
  if v_no=-1 then
  insert into tseq(cvalue,tname,tspace,cname)
  select
  1,
    substr(v_tab,instr(v_tab,'.')+1,length(v_tab)-instr(v_tab,'.')),
    nvl(substr(v_tab,1,instr(v_tab,'.')-1),(select username from user_users)),
  upper(i_column)
    from dual;
  v_no:=1;
  else
    v_no:=v_no+1;
    delete from tseq where tname=substr(v_tab,instr(v_tab,'.')+1,length(v_tab)-instr(v_tab,'.')) and cname=upper(i_column) and tspace=nvl(substr(v_tab,1,instr(v_tab,'.')-1),(select DEFAULT_TABLESPACE from user_users));
  insert into tseq(cvalue,tname,tspace,cname)
  select
  v_no,
    substr(v_tab,instr(v_tab,'.')+1,length(v_tab)-instr(v_tab,'.')),
    nvl(substr(v_tab,1,instr(v_tab,'.')-1),(select DEFAULT_TABLESPACE from user_users)),
  upper(i_column)
    from dual;
  end if;
  return v_no;
end;

 

转载于:https://www.cnblogs.com/Babylon/p/9252515.html