使用sequence啊--stgsh库,auditintfcreate sequence sequence_modify_record minvalue 1 maxvalue 99999999999999999999 start with 1 increment by 1 cache 20 / 使用: select sequence_modify_record.nextval into v_num from dual;或者 insert table_name(usernum,user_emp) values(sequence_modify_record.nextval,'username'); commit;
select decode(count(编号),0,1,max(to_number(编号)+1) from table
可以写一个采番(采番就是你说的自动增加)的方法。然后在座插入操作时调用这个方法。 具体方法如下: create or replace function Func_caifan(no IN VARCHAR2) return varchar2 is seq_nextseq VARCHAR2(200) ; seq_prefix VARCHAR2(240) ; seq_seq VARCHAR2(240) ; CURSOR cur_caifan IS SELECT seq, trim(to_char(to_number(SUBSTR(seq,3,12 ))+1,'0000000000')) AS nextseq, prefix FROM t_seq_mst WHERE del_flg = '0' AND seqid=no ; begin OPEN cur_caifan; LOOP FETCH cur_caifan INTO seq_seq, seq_nextseq, seq_prefix; --カーソルの存在を判断する EXIT WHEN cur_caifan%NOTFOUND; end loop; CLOSE cur_caifan; seq_seq := seq_prefix||seq_nextseq; UPDATE t_seq_mst SET seq = seq_seq WHERE seqid = no; return(seq_seq); end Func_caifan;
minvalue 1
maxvalue 99999999999999999999
start with 1
increment by 1
cache 20
/
使用:
select sequence_modify_record.nextval into v_num from dual;或者
insert table_name(usernum,user_emp) values(sequence_modify_record.nextval,'username');
commit;
from table
具体方法如下:
create or replace function Func_caifan(no IN VARCHAR2)
return varchar2
is
seq_nextseq VARCHAR2(200) ;
seq_prefix VARCHAR2(240) ;
seq_seq VARCHAR2(240) ; CURSOR cur_caifan
IS
SELECT seq,
trim(to_char(to_number(SUBSTR(seq,3,12 ))+1,'0000000000')) AS nextseq,
prefix
FROM
t_seq_mst
WHERE
del_flg = '0'
AND
seqid=no ;
begin OPEN cur_caifan; LOOP
FETCH cur_caifan
INTO seq_seq,
seq_nextseq,
seq_prefix; --カーソルの存在を判断する
EXIT WHEN cur_caifan%NOTFOUND;
end loop;
CLOSE cur_caifan;
seq_seq := seq_prefix||seq_nextseq;
UPDATE t_seq_mst
SET seq = seq_seq
WHERE seqid = no; return(seq_seq);
end Func_caifan;
一致不明白cache有什么用,设置cache =20后 ,有时候sequence会一下子跳20,奇怪