Re: User Sequence Numbers

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Sat, 01 Feb 2003 00:37:07 GMT
Message-ID: <Xns9314C71BC974Dpobox002bebubcom@216.148.227.77>

Hermann Angstl <hal_at_camline.com> wrote in news:3E3A5713.72F51802_at_camline.com:

> Matthias Rogel wrote:
>

>> you don't want to update, so you don't need to lock a row

>
> I dont' watn to update - but i want to have exclusive access to the
> *last* entry in the table (the one with the highest k value).
>

>> lock table foo in exclusive mode;

>
> This would be an overkill. Explanations follows ...
>

>> Oracle noticed that disadvantage and invented sequences.

>
> I' aware of sequences - but this is not exactely what i need. My real
> problem is a little bit more complicated. Let's assume we have the
> following two tables:
>

>|--------------|
>| parant_table |


> ----------------

>| parent_name  | <- the key
>| ...          |
>|              |
>|--------------|


>        |

>        | 1:n

>        |

>|--------------|
>| child_table  |


> ----------------

>| parent_name  |  <- the key
>| child_nr     |  <-
>| ...          |
>|              |
>|--------------|

>
>
> I want to create unique numbers in table child_table for each parent.
> The numbers have to unique only for each parent_name:
>
> parent_name child_nr
> -----------------------------
> joe 1
> joe 2
> joe 3
>
> john 1
> john 2
>
> ron 1
>

Hello Hal,

You may want to look at this number as derived data and use a view to generate it. You can do that using the analytic function row_number if you have 8i or above.

Here's what I mean.

SQL> create table t (parent_name varchar2(10),   2 child_id number);

Table created.

SQL> create sequence child_seq
  2 start with 1000 increment by 10;

Sequence created.

SQL> insert into t values ('&parent',child_seq.nextval); Enter value for parent: joe

1 row created.

SQL> /
Enter value for parent: joe

1 row created.

SQL> /
Enter value for parent: john

1 row created.

SQL> /
Enter value for parent: ron

1 row created.

SQL> /
Enter value for parent: john

1 row created.

SQL> /
Enter value for parent: joe

1 row created.

SQL> select * from t;

PARENT_NAM CHILD_ID
---------- ----------

joe              1010
joe              1020
john             1030
ron              1040
john             1050
joe              1060

6 rows selected.

SQL> create view v as
  2 select parent_name,

  3     row_number() over (
  4         partition by parent_name order by child_id) child_nr

  5 from t;
View created.

SQL> select * from v;

PARENT_NAM CHILD_NR
---------- ----------

joe                 1
joe                 2
joe                 3
john                1
john                2
ron                 1

6 rows selected.

Hth
Martin Received on Fri Jan 31 2003 - 18:37:07 CST