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;
遇到问题:
ORA-14551:无法在查询中执行DML操作的解决方法
加入:PRAGMA AUTONOMOUS_TRANSACTION