建立序列(sequence)
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;取下一个分配值
select your_seq.nextval from dual;
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;取下一个分配值
select your_seq.nextval from dual;
create sequence idadd //idadd是序列名
increment by 1 //每次递增1
start with 1 //从1开始
nomaxvalue //没有最大值
nocycle
cache 10;
建立以后,例如要插入到tab1这个表中.(tab1有id,name两个字段)
insert into tab1 values(add.nextval,'abc');
这样就OK了.
2 start with 1
3 increment by 1;序列已创建。SQL> create table t (n number ,v varchar2(10));表已创建。SQL> create or replace trigger t_trg
2 before insert or update on t
3 for each row
4 begin
5 select a_sequence.nextval into :new.n from dual;
6 end;
7 /触发器已创建SQL> insert into t values(111,'ok');已创建 1 行。SQL> select * from t; N V
---------- ----------
1 ok
TMPVAR NUMBER;
BEGIN
SELECT MAX(自增列名) INTO TMPVAR FROM 方案名.表名;
IF TMPVAR IS NULL THEN
:NEW.自增列名:=1;
ELSE
:NEW.自增列名:=TMPVAR+1;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;