Shulou(Shulou.com)06/01 Report--

Sequence meaning

The Oracle database provides sequence objects, which are self-growing serial numbers provided by the system and are usually used for self-growing primary keys or serial numbers of database data records.

Create syntax format and description

Create sequencemysequenc-Custom sequence name

Increment by 1-sets the step size value, that is, the isometric sequence that increases or decreases each time

Start with 1-sets the starting value of the sequence

Nomaxvalue-sets the maximum value of the sequence. The default value of nomaxvaule is 27 9.

Nocycle-sets whether the sequence is cyclic, that is, it starts from scratch when it reaches a certain value

Cache 10;-- sets the number of serial values stored in memory

Other matters

1. After the sequence is defined, you can use currval and nextval attributes. Currval represents the current value of the sequence, and nextval indicates that the sequence value is increased and returned.

2. It should be noted that when you initially use a sequence, you need to use nextval to return the initial value before you can use currval. If you use currval directly, you will get an error.

3. If you specify a cache value, ORACLE can pre-place some sequence in memory so that it can be accessed more quickly. After taking out the contents of cache, oracle automatically fetches another group to cache. Using cache may jump, for example, if the database suddenly fails to down (shutdown abort), the sequence in cache will be lost. So you can use nocache to prevent this situation when you create sequence.

4. You can either use the owner of the sequence or have ALTER ANY SEQUENCE permission to change the sequence. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

The example of Alter sequence

ALTER SEQUENCE mysequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE-- start from scratch after 10000

NOCACHE

5. Delete sequence: drop sequencemysequence

6. you can use triggers to associate table objects and play the role of sequences.

Create or replace trigger info_tigger

Before insert on info

For each rows

Begin

Select infotrigger.nextval into: new.sno from dual

End

/

7. Clear the cache in the shared pool

Alter system flush shared_pool;-- is dangerous, so it is not recommended to use it frequently.

8. Keep shared pool locked

Exec sys.dbms_shared_pool.keep ('sequence name','Q')-you can lock a sequence without sudden accidental damage

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.