Oracle主键列自增函数DDL:
使用方法:
create or replace function FUNC_NEXTID
(tablename in varchar2) return number is
--pragma autonomous_transaction;
nextid number(12);
begin
begin
select nvl(max(id),-1) into nextid from tSequence where upper(name) = upper(tablename); --for update;
exception
when no_data_found then nextid := 0;
-- commit;
end;
if nextid = -1 then
INSERT INTO tsequence(name,id) VALUES(tablename,1);
nextid := 1;
else
nextid := nextid + 1;
update tSequence set id = nextid where upper(name) = upper(tablename);
end if;
-- commit;
return(nextid);
end FUNC_NEXTID;
使用方法:
insert into
infosend(ID,CreateTime,InfoType,InfoTo,InfoFrom,Title,content)
VALUES(func_nextId('InfoSend'),
sysdate,1,13905911234,'lucky@google.com','资讯','测试消息!');