17,377
社区成员
发帖
与我相关
我的任务
分享
create table a(id int ,name varchar2(10));
create sequence seq; ----序列
create or replace trigger tr
before insert on a
for each row
begin
select seq.nextval into :new.a from dual;
end; ----触发器
SQL> insert into a (b) values ('zhangs');
1 row inserted
SQL> insert into a (b) values ('lisi');
1 row inserted
SQL> insert into a (b) values ('fgggs');
1 row inserted
SQL> insert into a (b) values ('wangwun');
1 row inserted
SQL> select * from a;
A B
--------------------------------------- ----------
1 zhangs
2 lisi
3 fgggs
4 wangwun
SQL> delete from a where a=(select max(a) from a);
1 row deleted
SQL> insert into a(b) values('nhhh');
1 row inserted
SQL> select * from a;
A B
--------------------------------------- ----------
1 zhangs
2 lisi
3 fgggs
5 nhhh
create table tabid(id0 number(10));
create or replace procedure p_insert_id(tbl in varchar2,int1 int,int2 int) as
begin
For i in int1..int2
Loop
insert into tabid values(i);
End loop;
commit;
end;
--插入记录时:
insert into tableA(id) as select min(id0) from tabid;
--其它的字段可以以update tableA的方式来做。这样的话可以用于oracle,sqlserver,mysql和db2。
OPER@jf> create table test(aaa number);
Table created.
OPER@jf> create sequence seq_1 start with 1 nocache;
Sequence created.
OPER@jf> insert into test values(seq_1.nextval);
1 row created.
OPER@jf> /
1 row created.
OPER@jf> /
1 row created.
OPER@jf> commit;
Commit complete.
OPER@jf> select * from test;
AAA
----------
1
2
3
OPER@jf> delete from test where aaa=3;
1 row deleted.
OPER@jf> commit;
Commit complete.
OPER@jf> insert into test values(seq_1.nextval);
1 row created.
OPER@jf> commit;
Commit complete.
OPER@jf> select * from test;
AAA
----------
1
2
4
OPER@jf>