Explain oracle sequence, oraclesequence

Explain oracle sequence, oraclesequence

1. About Sequences (About Sequences)

A sequence is a type of database object. Multiple users can generate consecutive numbers in sequence to automatically and uniquely increase primary key fields. A sequence can be used for multiple columns and multiple tables at the same time.

Sequences eliminate serialization and improve application consistency. (Imagine what if there is no sequence of days ?)

2. Creating Sequences (create sequence)

Prerequisites:Prerequisites

To create a sequence inyour own schema, you must haveCREATE SEQUENCESystem privilege. The create sequence permission is required to create a sequence in the current mode.

To create a sequence inanother user's schema, you must haveCREATE ANY SEQUENCESystem privilege. The create any sequence permission is required to create a sequence in other user modes.

Syntax:Syntax

If no conditional statement is added, the default sequence format is as follows:

-- Create sequence

Create sequence SEQ_T

Minvalue 1

Max value 999999999999999999999999999

Start with 1

Increment by 1

Cache 20;

 

SemanticsSemantics:

INCREMENT:Specify the sequence growth step. It can be positive (ascending) or negative integer (descending), but cannot be 0. The highest precision is 28.

START:Specify the start number of a sequence. The default value is the minimum value of the sequence.

MAXVALUE: Specifies the maximum Sequence Value. A maximum of 28 BITs are supported. Must be greater than or equal to the starting value and greater than or equal to the minimum value of the sequence.

NOMAXVALUE: No maximum value (actually 10 ^ 27 or-1 ). Default

MINVALUE: Specifies the minimum value of the sequence.

NOMINVALUE : No minimum value (actually 1 or-10 ^ 26 ). Default

CYCLE : The sequence generation starts from scratch after it reaches the maximum or minimum value.

NOCYCLE: Generate without loops. Default.

CACHE: Specifies the number of pre-allocated sequence values in the database memory for quick acquisition. The minimum cache value is 2.

The Maximum Cache parameter value is:

(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

Note 1: If the system fails, All cached sequence values that are not used by DML statements and submitted will be lost. The number of potential missing values equals the number of caches.

NOCACHE : No cache quantity is specified. The default cache is 20.

ORDER: Specify the order condition to ensure that the sequence is generated in the Request order. This condition applies to the RAC environment.

NOORDER: The sequence is not necessarily generated in the Request order.

 

Example:

CREATE SEQUENCE customers_seq

 START WITH     1000

 INCREMENT BY   1

 NOCACHE

 NOCYCLE;

 

NOTE 2: When the sequence with cycle conditions reaches the maximum value, the next value starts to cycle from the minimum value minvalue!

Create sequence seq1

Start with 200

Increment by 10

Max value 200

CYCLE

NOCACHE;

 

SELECT seq1.nextval FROM dual;

Result: 1

3. alter sequence (modify SEQUENCE)

 

Prerequisites:

The sequence must be in your own schema, or youmust haveALTERObject privilege on

The sequence, or you must haveALTER ANY SEQUENCESystemprivilege.

The alter object permission is required to modify the mode sequence. The alter any sequence permission is required to modify the mode sequence.

 

Syntax:

 

Semantics:

 

1) If you want to re-start the sequence with different numbers, you must delete and recreate the sequence.

SQL> alter sequence seq_t start with 2;

Alter sequence seq_t start with 2

*

ERROR at line 1:

ORA-02283: cannot alter starting sequencenumber.

2) The modified maxvalue must be greater than the current value of the sequence.

SQL> alter sequence seq_t maxvalue 1;

Alter sequence seq_t maxvalue 1

*

ERROR at line 1:

ORA-04004: MINVALUE must be less than MAXVALUE

 

 

Example:

ALTER SEQUENCE customers_seq 

   MAXVALUE 1500;

 

ALTER SEQUENCE customers_seq 

   CYCLE

   CACHE 5; 

 

4. drop sequence (delete SEQUENCE)

Prerequisites:

Thesequence must be in your own schema or you must have the drop any sequence system privilege.

 

You must have the drop any sequence permission to delete a sequence.

 

Syntax:

 

Example:

DROP SEQUENCE oe.customers_seq; 

 

5. NEXTVAL and CURRVAL restrictions

CURRVALAndNEXTVALCan be used in the following places:

·VALUESClauseINSERTStatements

·SELECTList ofSELECTStatement

·SETClause ofUPDATEStatement

CURRVALAndNEXTVALCannot be used in these places: cannot be used in the following scenarios

· A subquery

· A view query or materialized view query view or materialized view query

·SELECTStatement withDISTINCTOperator query with distinct keywords

·SELECTStatement withGROUP BYOrORDER BYClause query statement with order

·SELECTStatement that is combined with anotherSELECTStatement withUNION, INTERSECT, OrMINUSSet operator includes union, interest, and minus Operators

·WHEREClause ofSELECTStatement is used in the where Condition

·DEFAULTValue of a column inCREATE TABLEOrALTER TABLEDefault Value of the statement Column

· The condition ofCHECKConstraint check Constraints

 

 

 

 

 

 

 

 

--------------------------------------

Dylan Presents.

What is oracle sequence used?

Oracle provides a sequence object that is provided by the system for auto-incrementing serial numbers. It is usually used to generate auto-incrementing primary keys or serial numbers for database data records.

The following describes common operations such as sequence generation, modification, and deletion:

1. Create a Sequence

Run the following command to CREATE a sequence ):

Create sequence test_sequence increment by 1 -- start with 1 for each added data -- count NOMAXVALUE from 1 -- do not set the maximum value NOCYCLE -- accumulate all the time, and CACHE 10 is not cyclic; [note] If the CACHE value is set, ORACLE will pre-place some sequence in the memory to make access faster. After the cache is obtained, oracle automatically retrieves another group to the cache. However, using the cache may jump. When a database suddenly goes down abnormally (shutdown abort), the sequence in the cache will be lost. Therefore, we recommend that you use the nocache option when creating the sequence.

2. Use sequence:

Sequence. CURRVAL -- returns the current sequence Value of sequence. NEXTVAL -- increases the sequence value, and then returns the sequence value.

[Note] the first value returned by nextval is the initial value. The subsequent NEXTVAL will automatically add your defined increment by value, 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 SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different.

Sequence is stored in the data dictionary and stored in the user_sequences table LAST_NUMBER as the final serial number, that is, the current position of the sequence cursor.

// Get sequence last_number

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = TEST_SEQNAME

// NEXTVAL points the cursor to the next position (add or subtract one)

Select seqname. nextval from USER_SEQUENCES to get the value of the next cursor.

3. Modify Sequence

You must have the alter any sequence permission to modify the sequence. You can alter all sequence parameters except start. To change the start value, you must drop the sequence and re-create.

The command format is as follows:

Alter sequence test_sequence increment by 10 MAXVALUE 10000 CYCLE -- start NOCACHE from the beginning after 10000;

4. Delete Sequence drop sequence order_seq;
References:... the remaining full text>

How to write the sequence of the oracle database?

To set field auto-increment in Oracle, use the sequence and trigger in combination. For example:
Sequence:
Create sequence SEQ_XLH
Minvalue 1
Start with 1
Increment by 1
Cache 20
Order;
First, add an ID column in the emp table with 20 characters: alter table emp add ID char (20 );
Trigger:
Create or replace trigger trig_emp_id
Before insert on emp
For each row
Begin
Select to_char (sysdate, 'yyyymmdd') |
Substr (to_char (1000000000000 + seq_xlh.nextval)
Into: new. ID from dual;
End;