Sequence sequence usage in Oracle

 1) create a sequence command

Create sequence [user.] sequence_name
[Increment by N]
[Start with N]
[Maxvalue n | nomaxvalue]
[Minvalue n | nominvalue];
Increment by: Specifies the interval between serial numbers. This value can be a positive or negative integer, but cannot be 0. The sequence is ascending. When this clause is ignored, the default value is 1.
Start with: Specifies the first serial number generated. In ascending order, the sequence can start from a value greater than the minimum value. The default value is the minimum value of the sequence. For descending order, the sequence can start from a value smaller than the maximum value. The default value is the maximum value of the sequence.
Maxvalue: specifies the maximum value that can be generated by the sequence.
Nomaxvalue: set the maximum value to 1027 in ascending order and-1 in descending order.
Minvalue: specifies the minimum value of the sequence.
Nominvalue: specify the minimum value as 1 for ascending order. Specify a minimum value of-1026 for descending order.

2) Change sequence commands

Altersequence [user.] sequence_name
[Increment by N]
[Maxvalue n | nomaxvalue]
[Minvalue n | nominvalue];
The sequence can be modified:
? Modify the increment of future sequence values.
? Sets or removes the minimum or maximum value.
? Change the number of buffer sequences.
? Specify whether the serial number is ordered.

3) Delete sequence commands

Drop sequence [user.] sequence_name;
Deletes a sequence from a database.

Create a serial number statement:

   CREATE SEQUENCE EXAM_NO_SEQ      START WITH 1484      MAXVALUE 9999999999      MINVALUE 1      CYCLE      CACHE 20      NOORDER;

Serial number retrieval in Pb:

 String v_exam_no // obtain the application No. Select distinct into: v_exam_no from dual using ghis_database; If ghis_database.sqlcode <> 0 then MessageBox ("", "check no. error") returnend if

 

 

========================================================== ==========================================================

Other Versions

Oracle does not have a field of the auto-increment type. Therefore, a sequence must be defined as the data for fields of the auto-increment type.

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 sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur. 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. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.

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 ')

3: how to modify the sequence

Only the owner of the sequence can be modified if you have the alter any sequence permission. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.

Example of alter sequence:

Alter sequence emp_sequence

Increment by 10

Max value 10000

Cycle -- start from scratch after 10000

Nocache

Initialization parameters that affect sequence:

Sequence_cache_entries = sets the number of sequence that can be simultaneously cached.

You can also

Alter sequence myseq maxvalue 500

Note: you cannot change the start with parameter. You can change the value of minvalue to a value greater than the current value)

4: How to view and delete all sequences of a tablespace

View:

Select * From user_sequences

How to delete:

Drop sequence name

5: What object does sequence belong?

Sequence does not belong to a table or field. Sequence only belongs to a user.

In fact, after a sequence is created, each table can use this sequence, but this will cause a lot of trouble for the application. Therefore, we recommend that you use a sequence for each table.

 

 

Test knife: