1 / 6

Sequences

Sequences. Most application require the automatic generation of a numeric value . Oracle provides an automatic sequence generator of numeric values , which can have a maximum value up to 38 digits. A sequence can be defined to-- Generate numbers in ascending or descending

ranee
Télécharger la présentation

Sequences

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sequences • Most application require the automatic generation of a numeric value. Oracle provides an automatic sequence generator of numeric values, which can have a maximum value up to 38 digits. • A sequence can be defined to-- • Generate numbers in ascending or descending • Provide intervals between numbers • Caching of sequence numbers in memory etc. • Creating Sequences:- • Syntax:- • CREATE SEQUENCES sequence_name • [INCREMENT BY integervalue • START WITH integervalue • MAXVALUE integervalue • MINVALUE integervalue • CYCLE/NOCYCLE • CACHE integervalue/NOCACHE • ORDER /NOORDER ]

  2. KEYWORDS AND PARAMETERS:- Sequence_name :- Sequence is the name given to the sequence so that it can be referenced later. INCREMENT BY Increment by specifies the interval batween sequence no. It can be any positive or negative value but not zero . If this clause is omitted, the default value is 1. MINVALUE Specifies the sequences minimum value. MAXVALUE Maxvalue specifies the maximum value that a sequence can generate. START WITH Specifies the first sequence no.to be generated.The default for an ascending sequence is the sequences minimum value & for descending it is the the maximum value. CYCLE Cycle specifies that the sequence continues to generate values after reaching either its maximum or minimum value. NOCYCLE NOCYCLE specifies that a sequence cannot generate more values after reaching the maximum or minimum value.

  3. CACHE Cache specifies how many values of the sequence ORACLE preallocates & keep in memory for faster access. NOCACHE Nocache specifies that the value of the sequence are not preallocated.If the CACHE/NOCACHE is omitted ORACLE caches 20 sequence no. by default. ORDER Order guarentee that sequence no. are generated in order of request. NOORDER Noorder does not guarantee sequence no. are generated 1 order of request.If the ORDER/NOORDER clause is omitted it takes the NOORDER clause by default.

  4. Example:- To create a sequence order_seq which will start generating no.from 1 to 9999 in ascending order with an interval of 1. CREATE SEQUENCE order_seq INCREMENT BY 1 START WITH 1 MAXVALUE 9999 CYCLE; Referencing a Sequence:- Refer to the next value: SELECT sequence_name .NEXTVAL FROM dual; Refer to the current value: SELECT sequence_name .CURRVAL FROM dual; NOTE:- Every time NEXTVAL is referenced the sequence is automatically incremented from the old value to the new value & gives u the new value.The next new value is generated only if NEXTVAL is used again.

  5. Example:- Insert values in the sales_ordertable.Thes_order_no must be generated by using the order_seq sequence. INSERT INTO sales_order (s_order_no,s_order_date,client_no) VALUES(order_seq.nextval,sysdate,’Coo1’); Altering A Sequence:- Syntax:- ALTER SEQUENCE sequence_name [INCREMENT BY integervalue MAXVALUE integervalue MINVALUE integervalue CYCLE/NOCYCLE CACHE integervalue/NOCACHE ORDER /NOORDER ]

  6. Note:- The starting value of the sequence cannot be altered. Example:- Change the caching of the sequence order_seq to 30 & interval between two no. as 2. ALTER SEQUENCE order_seq INCREMENT BY 2 CACHE 30; Dropping A Sequence:- Syntax:- DROP SEQUENCE sequence_name; Example:- Drop the sequence order_seq. DROP SEQUENCE order_seq;

More Related