CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;使用触发器实现自增:
create or replace trigger your_seq_tri
before insert on your_table1 for each row
declare
next_id number;
begin
select your_seq.nextval into next_id from dual;
:new.id := next_id;
end;
create sequence emp_seq
increment by 1
start with 1
nomaxvalue
nocycle
cache 10引用序列
insert into emp
values(emp_seq.nextval,'zhaokeke')修改序列(不能修改启始值)
alter sequence emp_seq
increment by 10
maxvalue 10000
create sequence squ
increment by 1
start with 1
nomaxvalue
nocycle
cache 10然后用触发器实现自动生成id
create or replace trigger id_auto
before insert on tablename for each row
declare
id number;
begin
select squ.nextval into id from dual;
:new.id := next_id;
end;
function getnextid(theTableName)
dim sql,rs
sql="select nvl(max(id)+1,1) from " & theTableName
set rs.conn.execute(sql)
getnextid=rs(0)
end function需要时调用即可
CREATE SEQUENCE 序列号的名称
INCREMENT BY 1
START WITH 1
MAXVALUE 99999
CYCLE NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL例子:CREATE SEQUENCE SEQ_AA
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E28
MINVALUE 1 NOCYCLE
CACHE 20 NOORDER
创建触发器”
create or replace trigger 触发器名
before insert on 表名
for each row
begin
select 序列名. nextval into :new.字段名 from dual;
end;例子:
create or replace trigger tig_subject
before insert on tb_subject
for each row
begin
select seq_subject. nextval into :new.fd_sub_id from dual;
end;
友联创新系统集成有限公司(北京上地)
在天津招聘
熟悉oracle 有相关工作经验 学过c语言
工作地点:天津
其它事项面谈
有意象请将简历发至
[email protected]
[email protected]
联系人:王先生
increment by 1
start with 1 ----->>(自己需要开始的数字)
nomaxvalue 1000 ----->>(最大的增长数)
nocycle
在使用的时候: Tem_Seq.nextval 可以获取当前的最大值
CREATE SEQUENCE SEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E28
MINVALUE 1 NOCYCLE
CACHE 20 NOORDERend;
2.建立触发器
create or replace trigger tig_subject
before insert on tb_subject
for each row
begin
select seq. nextval into :new.id from dual;
end;
create sequence your_seq
nocycle
maxvalue 9999999999
start with 1;使用触发器实现自增:
create or replace trigger your_seq_tri
before insert on your_table1 for each row
declare
next_id number;
begin
select your_seq.nextval into next_id from dual;
:new.id := next_id;
end;