Home » SQL & PL/SQL » SQL & PL/SQL » Toggle Sequence (Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 )
Toggle Sequence [message #378895] Fri, 02 January 2009 05:49 Go to next message
sonalir
Messages: 6
Registered: October 2007
Location: mumbai
Junior Member

Hi Experts,
How to create sequence that will generates only numbers like
1,-1,1,-1,1,-1 etc.
Also provide me site address to read detail concept related to sequence.

Thanks And Regards,
Sonali
Re: Toggle Sequence [message #378897 is a reply to message #378895] Fri, 02 January 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create sequence s 
  2  start with 1
  3  increment by -2
  4  minvalue -1
  5  maxvalue 1
  6  cycle
  7  nocache
  8  /

Sequence created.

SQL> select s.nextval from dual;
   NEXTVAL
----------
         1

1 row selected.

SQL> /
   NEXTVAL
----------
        -1

1 row selected.

SQL> /
   NEXTVAL
----------
         1

1 row selected.

SQL> /
   NEXTVAL
----------
        -1

1 row selected.

SQL> /
   NEXTVAL
----------
         1

1 row selected.

SQL> /
   NEXTVAL
----------
        -1

1 row selected.

Regards
Michel
Re: Toggle Sequence [message #378916 is a reply to message #378895] Fri, 02 January 2009 11:13 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Nice one Michel.

For a higher performance one (caching enabled), this would be an alternative.
SQL> create sequence t_seq;

Sequence created.

SQL> select decode(mod(t_seq.nextval, 2),
  2                1, 1,
  3                0, -1) X from dual;

         X
----------
         1

SQL> /

         X
----------
        -1

SQL> /

         X
----------
         1
Previous Topic: updating the table and putting the salary in desc order
Next Topic: Quit the stored procedure
Goto Forum:
  


Current Time: Fri Dec 02 16:46:25 CST 2016

Total time taken to generate the page: 0.24591 seconds