Shulou(Shulou.com)05/31 Report--

This article is about how to carry out sequence analysis in oracle. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

In oracle, sequence is the so-called sequence number, which is automatically incremented each time it is taken, and is generally used where you need to sort by sequence number.

1 、 Create Sequence

First of all, you must have CREATE SEQUENCE or CREATE ANY SEQUENCE permission

CREATE SEQUENCE emp_sequence

INCREMENT BY 1-add a few at a time

START WITH 1-count from 1

NOMAXVALUE-do not set the maximum

NOCYCLE-- accumulate all the time, not cycle

CACHE 10

Once you have defined emp_sequence, you can use CURRVAL,NEXTVAL

CURRVAL= returns the current value of sequence

NEXTVAL= increases the value of sequence and then returns the value of sequence

For example:

Emp_sequence.CURRVAL

Emp_sequence.NEXTVAL

Where you can use sequence:

-SELECT statements that do not include subqueries, snapshot and VIEW

In the subquery of the-INSERT statement

In the VALUES of-NSERT statement

-in the SET of UPDATE

You can look at the following examples:

INSERT INTO emp VALUES

(empseq.nextval, 'LEWIS',' CLERK',7902, SYSDATE, 1200, NULL, 20)

SELECT empseq.currval FROM DUAL

But it is important to note that:

-the first NEXTVAL returns the initial value; the subsequent NEXTVAL will automatically increase the INCREMENT by value you defined, and then return the increased value. CURRVAL always returns the value of the current SEQUENCE, but CURRVAL cannot be used until after the first NEXTVAL initialization, otherwise an error will occur. NEXTVAL increases the value of SEQUENCE once, so if you use multiple NEXTVAL in the same statement, the value is different. Understand?

-if you specify a cache value, ORACLE can pre-place some sequence in memory so that it can be accessed faster. 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.

2 、 Alter Sequence

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 emp_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE-- start from scratch after 10000

NOCACHE

Initialization parameters that affect Sequence:

SEQUENCE_CACHE_ENTRIES = sets the number of sequence that can be cache simultaneously.

It can be very simple, Drop Sequence.

DROP SEQUENCE order_seq

The above is how to carry out the sequence analysis in oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.