Oracle 递增/递减序列的pl/sql程序

SQL> create table customer
  2  (
  3  customer_id int primary key,
  4  customer_name varchar2(100)
  5  );

Table created.

SQL>
SQL> insert into customer values (1,'John');

1 row created.

SQL> insert into customer values (2,'Sue');

1 row created.

SQL> insert into customer values (3,'Mary');

1 row created.

SQL> insert into customer values (4,'Peter');

1 row created.

SQL> insert into customer values (5,'Maria');

1 row created.

SQL> insert into customer values (6,'Steven');

1 row created.

SQL>
SQL> create or replace view customer_token as
  2  select c.*, row_number() over ( order by customer_name ) as token
  3  from customer c;

View created.

SQL>
SQL> select * from customer_token;

CUSTOMER_ID CUSTOMER_NAME             TOKEN
----------- -------------------- ----------
          1 John                          1
          5 Maria                         2
          3 Mary                          3
          4 Peter                         4
          6 Steven                        5
          2 Sue                           6

6 rows selected.

SQL>
SQL> delete from customer where customer_id in (3,4);

2 rows deleted.

SQL>
SQL> select * from customer_token;

CUSTOMER_ID CUSTOMER_NAME             TOKEN
----------- -------------------- ----------
          1 John                          1
          5 Maria                         2
          6 Steven                        3
          2 Sue                           4

4 rows selected.
复制

SQL> drop view customer_token;

View dropped.

SQL>
SQL> alter table customer add token int;

Table altered.

SQL>
SQL> drop table tmp purge;

Table dropped.

SQL>
SQL> create global temporary table tmp ( customer_id int primary key, tk int );

Table created.

SQL>
SQL> insert into tmp select customer_id,  row_number() over ( order by customer_name ) as tk
  2  from customer;

4 rows created.

SQL>
SQL> update
  2    ( select c.customer_id, c.token, t.tk
  3      from customer c, tmp t
  4      where c.customer_id = t.customer_id
  5    )
  6    set token = tk;

4 rows updated.

SQL>
SQL>
SQL> create or replace
  2  trigger cust_trg
  3  after delete on customer
  4  begin
  5    lock table customer in exclusive mode;
  6    delete tmp;
  7
  8    insert into tmp select customer_id,  row_number() over ( order by customer_name ) as tk
  9    from customer;
 10
 11    update
 12      ( select c.customer_id, c.token, t.tk
 13        from customer c, tmp t
 14        where c.customer_id = t.customer_id
 15      )
 16      set token = tk
 17      where token != tk;
 18  end;
 19  /

Trigger created.

SQL>
SQL> delete from customer where customer_id = 1;

1 row deleted.

SQL>
SQL> select * from customer;

CUSTOMER_ID CUSTOMER_NAME             TOKEN
----------- -------------------- ----------
          2 Sue                           3
          5 Maria                         1
          6 Steven                        2

3 rows selected.
复制