Sequence with variable

In SQL we will be having a开发者_JS百科 sequence. But it should be appended to a variable like this

M1,M2,M3,M4....

Any way of doing this ?

Consider having the prefix stored in a separate column in the table, e.g.:

CREATE TABLE mytable (
   idprefix VARCHAR2(1) NOT NULL,
   id       NUMBER NOT NULL,
   CONSTRAINT mypk PRIMARY KEY (idprefix, id)
);

In the application, or in a view, you can concatenate the values together. Or, in 11g you can create a virtual column that concatenates them.

I give it 99% odds that someone will say "we want to search for ID 12345 regardless of the prefix" and this design means you can have a nice index lookup instead of a "LIKE '%12345'".

select 'M' || my_sequence.nextval from dual;