SQL & PL/SQL

Hello everyone,

First of all, sorry about my poor english! I'm a brazilian undergraduate student but I never attended english classes, kinda auto-learned by attempts... well, I think this is enough to my introduction. Oops, that's something I need to tell also. I'm already kinda experienced with the following databases, but I am very newbie at Oracle. (Firebird[some years and currently using it], DBase[a long long time ago], Paradox[ a long time ago too], MSAccess (omg!!!) and MySQL).

I have the followin' situation:

My very simple test table:

CREATE TABLE TESTSEQ (
SEQNO NUMBER(4),
CLASS VARCHAR2(20),
... constraints, checks, everything else).

Some records:

SeqNo | Class
---------------------------------
001 | Red
002 | Red
003 | Red
001 | Green
002 | Green
001 | Blue
002 | Blue
003 | Blue

Ok, let's get into the problem. Each Class must be sequencially numbered. The easiest way is to use "SELECT MAX(SEQNO) WHERE CLASS = Something..", but I will have concurrency problems :)
Usually, the best solution for "autonumbering" is to use a SEQUENCE... then I can look for a SEQCOLORS.NEXTVAL... but I will need to restart it for the Classes.... this way can be a burden.
The "user application" can create a new sequence for each new class... SEQCOLOR_RED, SEQCOLOR_GREEN... and I think this solution is a lot better than mess with the value of a single sequence. (solution 3)
The last solution I though is to use a Single Table SEQCOLORS ( LAST_SEQ, CLASS ), and use a concurrency transaction to "lock" the SEQCOLORS table while get a new value... It's seems to me this is the best. (solution 4)

But, what could be really good and what I'm looking for is a way to create a safe concurrent "multi value sequence". It could be something like:

// CODE

CREATE SEQUENCE MVSEQ_COLORS MULTIVALUE;

MVSEQ_COLORS.ADDINDEX('GREEN');
MVSEQ_COLORS.ADDINDEX('RED');

SELECT MVSEQ_COLORS.NEXTVAL['GREEN'] FROM DUAL.

And know the one million question. Is there any approach for my dream-like sequence above? I'm glad for any tips and if i'm right about the "solution 4", i need some help to the "lock table" thing... the better approach to do it.

Thanks in advance ;)

[]'s

-----------
I though that could be good a pratical use:
Relations:
ORDER(_order_id_, description, other_fields...)
ORDER_ITEM(_order_id_, seqno, item_description, other_fields...)

In order_item, seqno must be restart each order... but at my problem I'll add itens to random orders frequently..

Edited by: Paulo Gurgel on 18/03/2010 22:39

------------
Like the answer below, a good and simple approach for real world to the composite primary key is to use a timestamp or a single sequence for everyone, not a multi-sequence. But this is not whats this thread about. The question is not about this particular model, but IF we require a "multi value sequence", what's the best approach. In my particular problem, a timestamp would be a perfect candidate key. (a case where the user or terminal/device is part of the primary key ^^)

"CREATE TABLE TESTSEQ (
DEVICE_ID NUMBER(7),
SEQDATE DATE,
CLASS VARCHAR2(20),
... constraints, checks, everything else)."

Edited by: Paulo Gurgel on 19/03/2010 09:28