oracle如何给表加序列,oracle – 在表中添加和删除时使用序列

您可以让序列执行主键作业并创建基表的视图,然后选择

rownum作为要按顺序查看从1到N的数字的列：

sql> create table your_table(

2 tab_id number primary key,3 col number

4 )

5 ;

Table created

sql> create sequence gen_id;

Sequence created

sql> create trigger TR_PK_your_table

2 before insert on your_table

3 for each row

4 begin

5 :new.tab_id := gen_id.nextval; -- This kind of assignment is allowed in 11g

6 end; -- and higher,in version prior to 11g

7 / -- conventional select statement is used

Trigger created

sql> insert into your_table(col)

2 select level

3 from dual

4 connect by level <=7;

7 rows inserted

sql> commit;

Commit complete

sql> select *

2 from your_table;

TAB_ID COL

---------- ----------

1 1

2 2

3 3

4 4

5 5

6 6

7 7

7 rows selected

sql> create or replace view V_your_table

2 as

3 select tab_id

4,col

5,rownum as num

6 from your_table

7 ;

View created

sql> select *

2 from v_your_table;

TAB_ID COL NUM

---------- ---------- ----------

1 1 1

2 2 2

3 3 3

4 4 4

5 5 5

6 6 6

7 7 7

7 rows selected

sql> delete from your_table where tab_id in (3,5,6);

3 rows deleted

sql> commit;

Commit complete

sql> select *

2 from your_table;

TAB_ID COL

---------- ----------

1 1

2 2

4 4

7 7

sql> select *

2 from v_your_table;

TAB_ID COL NUM

---------- ---------- ----------

1 1 1

2 2 2

4 4 3

7 7 4

sql>