Correct use of Oracle sequence

We all know that the Oracle database does not have fields related to the auto-increment type, therefore, we usually need to define a data about Oracle sequence as its Automatically increasing Type field. So I recorded some information about Oracle sequence, which is available for future reference!

Sequence in Oracle)

1: how to define a sequence

Only forward numeric variables (and automatic numbers in SQL are a bit like identity (1, 2 ))

Format:

Create sequence <sequence Name>

Start with <start number>

Increment by <growth volume>

[Maxvalue value]

[Minvalue value]

[When cycle reaches the maximum value, it will start from scratch]

[Nocycle -- always accumulate without repeating]

[Cache]

Note:

The first value returned by nextval is the initial value. The subsequent NEXTVAL will automatically increase the value of your defined increment by and then return the added value. CURRVAL always returns the value of the current Oracle SEQUENCE, but CURRVAL can be used only after the first NEXTVAL initialization; otherwise, an error occurs. NEXTVAL increases the value of SEQUENCE once.

If the CACHE value is specified, Oracle can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, Oracle automatically retrieves another group to the cache. If the cache is used, it may jump. For example, if the database suddenly fails to shut down abort), the Oracle sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation during the create sequence operation.

 
 

  
  
example:  
  
  
create sequence mySeq  
  
  
start with 1  
  
  
increment by 2  
  
  
maxvalue 40  
  
  
minvalue 1  
  
  
cycle  
 
 

2: how to retrieve sequence data:

 
 

  
  
currval--->curenvalue  
  
  
nextVal-->NextVlaue  
  
  
example:  
  
  
select mySeq.nextVal from dual  
 
 

Note: currval can be used only after nextVal is executed once.

Sequence can be used when creating a table.

Example:

Create table basic info table

 
 

  
  
(  
  
  
ComPID int,  
  
  
CompName varchar2(20)  
  
  
)  
 
 

Insert into company basic info table values (mySeq. nextVal, 'A ')