Skip to Main Content
  • Questions
  • pl/sql program to increment/decrement sequences

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, satish.

Asked: February 14, 2017 - 9:50 am UTC

Last updated: February 15, 2017 - 5:57 am UTC

Version: 4.1

Viewed 1000+ times

You Asked

my question is
i am a shopkeeper and i am provide a token number for every customers in database if two and three customers are removed in the database after token number is automatically arrange in sequence.so how to solve this problem in pl/sql

and Connor said...

The easy way - just dont store that sequence - you can dynamically derive a token number, eg

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.


If you do *really* need to store it, then you can use the same facility to update a column in the table - but this also means, that for every customer deleted you really have to lock the entire table, because potentially every customer is updated.

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.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.