Oracle sequence SEQUENCE analysis

This article brings you relevant knowledge about Oracle, and explains the sequence SEQUENCE in Oracle in detail. The article introduces it in detail through sample code. It has certain reference value for everyone's study or work, and I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

1. Sequence introduction

Oracle's sequence is a A database object whose main function is to generate unique values. After the sequence is created, the sequence object can be found through the data dictionary, so the sequence can be shared by multiple objects.

2. Create a sequence

The sequence is created using the CREATE SEQUENCE syntax:

CREATE SEQUENCE sequence 
[INCREMENT BY n]
 [START WITH n]
 [{MAXVALUE n | NOMAXVALUE}]
 [{MINVALUE n | NOMINVALUE}]
 [{CYCLE | NOCYCLE}]
 [{CACHE n | NOCACHE}];

• NCREMENT BY: used to define the step size of the sequence, if omitted, it defaults Is 1. If it is a negative value, it means that the value of the sequence is decreasing.
• START WITH: Defines the initial value of the sequence (that is, the first value generated), the default is 1.
• MAXVALUE: Defines the maximum value that the sequence can generate. NOMAXVALUE is the default option, which means there is no maximum value. At this time, for an increasing sequence, the maximum value the system can generate is 10 to the 27th power; for a decreasing sequence, the maximum value is -1.
• MINVALUE: Defines the minimum value that the sequence can generate. NOMINVALUE is the default option, which means there is no minimum value. At this time, for the descending sequence, the minimum value the system can generate is negative 10 to the 26th power; for the descending sequence, the minimum value is 1.
• CYCLE and NOCYCLE: Indicate whether to loop when the value of the sequence generator reaches the limit. If looping, when the increasing sequence reaches the maximum value, it loops to the minimum value; for the descending sequence, when it reaches the minimum value, it loops to the maximum value. If you do not loop, after reaching the limit value, an error will occur if you continue to generate new values.
• CACHE: Defines the size of the memory block to store the sequence, the default is 20. NOCACHE means no memory buffering of the sequence. Memory buffering of sequences can improve sequence performance.

For example:

CREATE SEQUENCE invoice_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999
NOCYCLE NOCACHE;

3. Query sequence

Once the sequence is created, the sequence creation code is textualized in the data dictionary and can be found in the user_objects data See it in the dictionary, such as:

SELECT object_name,object_id,object_type  FROM user_objects WHERE object_name = 'INVOICE_SEQ';

The sequence details are saved in the user_sequences table:

SELECT sequence_name, min_value, max_value, increment_by, last_number  FROM user_sequences;

4. Use the sequence

NEXTVAL and CURRVAL pseudo columns

• NEXTVAL: Returns the next available sequence value. It returns a unique referenced value each time, and this is actually true for different users. When using sequence.NEXTVAL, a new sequence number is generated and the current sequence number is put into CURRVAL.
• CURRVAL: Get the current sequence value. If you use CURRVAL before using NEXTVAL for the first time, an error will be reported.

is used as follows:

SELECT invoice_seq.CURRVAL,invoice_seq.NEXTVAL FROM DUAL;

INSERT INTO invoice  (invoice_id, vendor_id, invoice_number, invoice_total  )
      VALUES (invoice_seq.NEXTVAL, 10, 'INV' || invoice_seq.CURRVAL, 100  );

NEXTVAL and CURRVAL can be used in the following context:

• is not part of a subquery The field list of the SELECT statement.
• The SELECT list of the subquery in the INSERT statement.
• VALUES clause in the INSERT statement.
• SET clause in UPDATE statement.

NEXTVAL and CURRVAL cannot be used in the following context:

• The SELECT list of a view.
• SELECT statement with DISTINCT.
• SELECT statement with GROUP BY, HAVING or ORDER BY clause.
• Clauses in SELECT, DELETE or UPDATE statements.
• DEFAULT expression in CREATE TABLE or ALTER TABLE statement.

Also note that ROLLBACK does not roll back the sequence value.

5. Modify the sequence

For example:

ALTER SEQUENCE invoice_seq     INCREMENT BY 2 MAXVALUE 10   NOCACHE  NOCYCLE;

When modifying the sequence, there are the following restrictions:

• The table sequence cannot be started initial value.
• The minimum value cannot be greater than the current value.
• The maximum value cannot be less than the current value.
• The modified sequence rules will not affect previous sequence values, only future sequence values ​​will be affected.
• Users must have ALTER SEQUENCE permissions.

6. Delete sequence

DROP SEQUENCE invoice_seq;

7. Create auto-increment sequence

1. Create a sequence

create sequence sq_recid 
minvalue 1 maxvalue 999999  increment by 1   start with 1 noCYCLE;

2. Create a trigger

create or replace trigger  trg_test
  before  insert on test for each row
begin
  select sq_recid.nextval into :new.ID from dual;
end;

alter  trigger trg_test  enable;

3. In C#, you can also manually insert sequences into the table

string sql="insert into test(ID,otherCol)value (Sql_recid.nextval,***）
    retuing ID into :ID"

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Oracle sequence SEQUENCE analysis. For more information, please follow other related articles on the PHP Chinese website!