A sequence is a database object used to create an integer. You can create a sequence and use it to generate numbers. Sequence has the following features: • A Unique id can be automatically generated • It is a shared object • It can be used to create a primary key value • Replace the application code • If the sequence is cached in memory, the efficiency of the access sequence value will be improved. The sequence is the database object created by the user and can be generated by multiple users. You can define a sequence to generate unique values, or reclaim numbers and reuse the same numbers. The common purpose of a sequence is to create a primary key value. The primary key value of each row must be unique. The sequence is generated by the internal Oracle routine in ascending (or descending) mode. Because you can reduce the amount of application code required to write a routine that generates sequences, using this object can save some time. The storage and generation of serial numbers are irrelevant to the table. Therefore, the same sequence can be used for multiple tables.
- Create sequence statement: syntax
Define a sequence that can automatically generate sequence numbers: create sequence sequence [increment by n] [start with n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; you can use the create sequence statement to automatically generate SEQUENCE numbers. In this syntax: sequence: Is the sequence generator name increment by n: Specifies the interval between serial numbers, where n is an integer (if this clause is omitted, the sequence increments BY 1) start with n: Specifies the first serial number to be generated (if this clause is omitted, the sequence starts from 1) MAXVALUE n: specify the maximum value NOMAXVALUE that can be generated by the sequence. Specify 10 ^ 27 as the maximum value of the ascending sequence, and-1 as the maximum value of the descending sequence (this is the default option) MINVALUE n: specify the minimum Sequence Value NOMINVALUE: specify 1 as the minimum value of the ascending sequence, and-(10 ^ 26) as the minimum value of the descending sequence (this is the default option)
• Create a sequence named DEPT_DEPTID_SEQ and use it as the primary key of the orders table. • Do not use the CYCLE option.Sys @ TEST0924> create sequence dept_deptid_seq2 increment by 103 start with 1204 MAXVALUE 99995 NOCACHE6 NOCYCLE7; Sequence created.CYCLE | NOCYCLE: Specifies whether the sequence continues to generate a value after the maximum or minimum value is reached (NOCYCLE is the default option) CACHE n | NOCACHE: specify the number of values pre-allocated and retained by the Oracle Server in the memory (20 values are cached by Oracle Server by default). In this example, a sequence named DEPT_DEPTID_SEQ is created, used as the DEPARTMENT_ID column of the orders table. This sequence starts from 120 and does not allow high-speed caching or loops. Do not use the CYCLE option when the sequence is used to generate the primary key value, unless there is a reliable mechanism to clear the old rows faster than the sequence loop. Note: The sequence is irrelevant to the table. Generally, the sequence should be named based on the intended use of the sequence. However, sequences can be used anywhere, regardless of their names.
- NEXTVAL and CURRVAL pseudo Columns
• NEXTVAL returns the next available Sequence Value. NEXTVAL returns a unique value each time it is referenced, even for different users. • CURRVAL obtains the current sequence value. • CURRVAL can contain values only after NEXTVAL is sent to the sequence. After creating a sequence, it generates sequence numbers that can be used in the table. You can use the NEXTVAL and CURRVAL pseudo columns to reference sequence values. The NEXTVAL pseudo column is used to extract consecutive serial numbers from the specified sequence. NEXTVAL must be specified by the sequence name. When sequence. NEXTVAL is referenced, a new serial number is generated, and the current serial number is placed in CURRVAL. The CURRVAL pseudo column is used to reference the serial number generated by the current user. However, you must first generate a serial number in the current user's session with NEXTVAL before referencing CURRVAL. The sequence name must be used to limit CURRVAL. When you reference sequence. CURRVAL, the last value returned to the user process is displayed. Rules Using NEXTVAL and CURRVAL can be used in the following context: • SELECT list of SELECT statements that are not part of a subquery • SELECT list of subqueries In the INSERT statement • VALUES clause of the INSERT statement • The SET clause of the UPDATE statement cannot use NEXTVAL and CURRVAL in the following context: • SELECT list of views • SELECT statements with DISTINCT keywords • SELECT statements with group by, HAVING, or order by clauses • subqueries in SELECT, DELETE, or UPDATE statements • create table or ALTER DEFAULT expression in TABLE statement
• Insert a new department named "Support" in location ID 2500:Sys @ TEST0924> insert into hr. departments (department_id, department_name, location_id)2 VALUES (dept_deptid_seq.NEXTVAL, 'support', 2500 ); 1 row created.It uses the DEPT_DEPTID_SEQ sequence to generate a new department Number of 280. • Use sequence_name.CURRVAL to view the current value of the DEPT_DEPTID_SEQ sequence:Sys @ TEST0924> SELECT dept_deptid_seq.CURRVAL FROM dual; CURRVAL----------280Assume that you want to hire some employees as staff in the new Department. The INSERT statement to be executed on all new employees can contain the following code: insert into employees (employee_id, department_id ,...) VALUES (employees_seq.NEXTVAL, dept_deptid_seq. CURRVAL ,...); note: The preceding example assumes that a sequence named EMPLOYEE_SEQ has been created to generate a new employee number.
For more details, please continue to read the 2nd page content:
Oracle stored procedure and serialized write demo
Sequence created in Oracle
Oracle query of all sequences JAVA + Oracle Functions
Query sequence values in Oracle stored procedures and receive them with variables
Query all sequences in Oracle
