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>