oracle自定义自增流水号

1.创建生成流水号的数据维护表

create table SEQT
(
  name VARCHAR2(20) not null,
  val  NUMBER(10) not null
)

 2.插入初始化数据

 

INSERT INTO seqt VALUES('one',100);  
INSERT INTO seqt VALUES('two',1000);  

 3.创建函数以生成序列号

 

CREATE OR REPLACE FUNCTION seq(seq_name varchar2)
return number is
pragma  AUTONOMOUS_TRANSACTION;
seq_maxValue number:=0;
begin
 UPDATE seqt SET val=(select MAX(val)+1 from seqt where name=seq_name) where name=seq_name;
 commit;
 select max(val) into seq_maxValue from seqt where name=seq_name;
 return seq_maxValue;
end;

 4.测试

select seq('one'),seq('one'),seq('two'),seq('two') from dual;

 

参考：http://hudeyong926.iteye.com/blog/894823

 

遇到问题：

ORA-14551:无法在查询中执行DML操作的解决方法

 

 

加入：PRAGMA AUTONOMOUS_TRANSACTION

 

 

 

评论

1 楼 cuishuangjia 2013-08-31  

http://cuishuangjia.iteye.com/blog/1934659