Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Sequence

A sequence is a database object, which can generate unique, sequential integer values.
It can be used to automatically generate primary key or unique key values.
A sequence can be either in an ascending or descending order.

Syntax:
      Create sequence <seq_name> [increment bty n] [start with n] [maxvalue n] [minvalue n] [cycle/nocycle] [cache/nocache];

By default the sequence starts with 1, increments by 1 with min value of 1 and with no cycle,no cache.
Cache option pre-allocates a set of sequence numbers and retains them in memory for faster access.

Ex:
     SQL> create sequence s;
     SQL> create sequence s increment by 10 start with 100 minvalue 5 maxvalue 200 cycle cache 20;

USING SEQUENCE

SQL> create table student(no number(2),name varchar(10));
SQL> insert into student values(s.nextval, ‘saketh’);

Ø Initially currval is not defined and nextval is starting value.
Ø After that nextval and currval are always equal.

CREATING ALPHA-NUMERIC SEQUENCE

SQL> create sequence s start with 111234;
SQL> Insert into student values (s.nextval || translate (s.nextval,’1234567890’,’abcdefghij’));

ALTERING SEQUENCE

We can alter the sequence to perform the following.
Ø Set or eliminate minvalue or maxvalue.
Ø Change the increment value.
Ø Change the number of cached sequence numbers.

Ex:
     SQL> alter sequence s minvalue 5;
     SQL> alter sequence s increment by 2;
     SQL> alter sequence s cache 10;

DROPPING SEQUENCE




SQL> drop sequence s;
Share this article :

2 comments:

  1. Hi Dayakar,

    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.

    Showing error converting data type varchar to numeric.
    This code:
    declare @code as varchar(250)
    set @code = '8164,8165,8166,8167,8168'
    select * from mac_accounts where acc_code in (@code)
    A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

    Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.

    Merci,
    Lee

    ReplyDelete
  2. Salama Aleikum,


    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on #topic!

    I need your help please, I am new at PL/SQL

    I need to run a select command with multiple values, if a break down this query into 3 pieces and run them,they works perfectly, but Ive got problems whenever i execute this query because there are multiple values into the where clause and i think its necessary to create lines break so it may work perfectly

    how can I get through this?

    SELECT job_name, command,description,days_of_week,start_mins,machine, J2.CREATE_STAMP, J2.MODIFY_STAMP
    FROM MDBADMIN.UJO_JOBROW JR JOIN MDBADMIN .ujo_job2 J2
    ON (JR.JOID = J2.JOID)
    WHERE job_name IN ('coscl_PM_zip_log','coscl_PM_clear_log','coscl_PM_alarma_fs','coscl_PM_pmapp1_PM_up','coscl_PM_control_recargas_cod_11','coscl_PM_control_recargas_ingreso','coscl_PM_control_rec_error_cc_agrup_v1'.'coscl_PM_control_procesos_diarios','coscl_PM_rep_recar_x_estado','coscl_PM_control_tiempos_recargas',
    'coscl_PM_control_porcentaje','coscl_PM_control_puertos_bloq','coscl_PM_control_errores', 'coscl_PM_control_java','coscl_PM_control_error_cache');







    Thank you very much and will look for more postings from you.


    ,Merci

    ReplyDelete