Nextval usage SEQUENCE and SYS_GUID () in Oracle ()

Nextval usage SEQUENCE and SYS_GUID () in Oracle () nameCREATE SEQUENCE -- create a new SEQUENCE generator SynopsisCREATE [TEMPORARY | TEMP] SEQUENCE name [INCREMENT [BY] increment] [MINVALUE minvalue | no minvalue] [MAXVALUE maxvalue | no maxvalue] [START [WITH] start] [CACHE cache] [[NO] CYCLE] Description create sequence adds a new serial number generator to the current database. This includes creating and initializing a new single-row table named name. The generator will be owned by the user who uses this command. If www.2cto.com provides a mode name, the sequence is created in the specified mode. Otherwise, it creates a temporary sequence in the current mode and exists in a special mode. Therefore, if a temporary sequence is created, the mode name cannot be given. The sequence name must be different from other sequences, tables, indexes, or views in the same mode. After the sequence is created, you can use functions nextval, currval, and nextval to operate the sequence. These functions are detailed in Section 9.12. Although you cannot directly update a sequence, you can use SELECT * FROM name; to check the parameters and current status of a sequence. In particular, the last_value field of the sequence shows the final value allocated by any backend process. (Of course, these values may be out of date when printed-if other processes are actively using nextval .) If the modifier is declared by the TEMPORARY or TEMP parameter, the sequence object is only created for this session and is automatically deleted at the end of the session. When temporary sequences exist, permanent sequences with the same name are invisible (in the same session), unless they are referenced by Schema-modified names. The name of the serial number to be created by www.2cto.com name (which can be modified in mode ). Increment: the Optional Clause increment by increment creates a new value, which should be added to the current sequence value. A positive number generates an ascending sequence, and a negative number generates a descending sequence. The default value is 1 ). MINVALUE, an optional sub-Statement of minvalueNO MINVALUE, determines the minimum value that can be generated in a sequence. If this clause is not declared or no minvalue is declared, the default value is used. By default, the ascending sequence is 1 and the descending sequence is-263-1. MaxvalueNO MAXVALUE uses the Optional Clause MAXVALUE to determine the maximum value of a sequence. If this clause is not declared or no maxvalue is declared, the default value is used. The default values are-263-1 and-1 respectively. The optional start with start clause enables the sequence to start from any position. The default initial value is that the incremental sequence is minvalue. The descending sequence is maxvalue. www.2cto.com cacheCACHE. The cache option preallocates the serial number and stores the quick access in the memory. The minimum value (also the default value) is 1 (only one value can be generated at a time, that is, no cache), which is also the default value. The optional CYCLE Keywords of sequence Eno CYCLE can be reset and continue when the sequence reaches the maximum (maxvalue) or minimum (minvalue. If the limit is reached, the next data generated will be the minimum value or the maximum value ). If no cycle is declared, an error is returned for any call to nextval after the sequence reaches its maximum value. If neither CYCLE nor no cycle is declared, no cycle is the default. Note that www.2cto.com uses the drop sequence statement to delete sequences. The sequence is based on the bigint operation, so its range cannot exceed the Integer Range (-9223372036854775808 to 9223372036854775807) of the eight-character section ). On some older platforms, the compiler may not support eight-character integers. In this case, the sequence uses a common integer Operation (range:-2147483648 to + 2147483647 ). If the cache is set to greater than one and this sequence object is used for concurrent multi-session scenarios, unexpected results may occur. Each session will allocate and cache subsequent sequence values during an access to the sequence object, and add the last_value of the sequence object accordingly. In this way, the subsequent cache-1 nextval in the same transaction will only return the pre-allocated value, instead of the dynamic sequence object. Therefore, any number allocated in a session but not used will be lost at the end of the session, resulting in "holes" in the sequence ". In addition, although the system ensures that an independent sequence value is assigned to multiple sessions, if all sessions are considered, this value may lose the sequence. For example, if the cache is set to 10, session A retains 1 .. 10 and returned nextval = 1, then session B may keep 11 .. 20 then, nextval = 11 is returned before session A generates nextval = 2. Therefore, when the cache is set to one, we can safely assume that the nextval value is generated sequentially. If you set the cache to a value greater than one, you can only assume that the nextval value is always unique, instead of generating in full order. Similarly, last_value will reflect the last value retained by any session, whether or not it was returned by nextval. Another consideration for www.2cto.com is that setval executed on such sequences will not be noticed by other sessions until they use their cached values. In this example, CREATE an incremental SEQUENCE named serial, starting from 101: create sequence serial START 101; then SELECT the next number from the SEQUENCE: SELECT nextval ('serial '); nextval ------- 114 use this sequence in an INSERT statement: insert into distributors VALUES (nextval ('serial '), 'Nothing'); Update the sequence after a copy from statement: BEGIN; COPY distributors FROM 'input _ file'; SELECT setval ('serial', max (id) FROM distributors; END; compatibility create sequence is declared in SQL: 2003. PostgreSQL follows the standard, except that the standard AS <data type> expression is not supported. The next value is obtained using the nextval () function, while the standard is the next value for expression. Www.2cto.com ---------- SQL Server, Sybase: The system can automatically add 1 create table a (a1 int identity (1, 1), a2 varchar (6 )) then at insert time: insert into a values ('Hello! '); -- No matter whether a1 is used, the system will help you automatically add 1 Oracle: Using SEQUENCE (SEQUENCE) can be used to meet your requirements create table a (a1 int, a2 varchar2 (6); www.2cto.com create SEQUENCE seq_a increment by 1; then at insert time: insert into a values (seq_a.nextval, 'Hello! '); -- Seq_a.nextval is the SEQUENCE of the next value of the sequence. in oracle, SEQUENCE is the so-called serial number, which is automatically added each time it is obtained, it is generally used to sort data by serial number. 1. Create Sequence you must first have the create sequence or create any sequence permission, create sequence emp_sequenceINCREMENT BY 1 -- add a few start with 1 each time -- count NOMAXVALUE from 1 -- do not set the maximum value NOCYCLE -- always accumulate, do not loop CACHE 10; once www.2cto.com defines emp_sequence, you can use CURRVAL, NEXTVALCURRVAL = to return the current sequence Value NEXTVAL = to increase the sequence value, and then return the sequence value, for example, emp_sequence.CURRVALemp_sequence.NEXTVAL, where sequence can be used: -The SELECT statement that does not contain subqueries, snapshot statements, and views-in the INSERT statement subqueries -In the value SET of the NSERT statement-update set, you can see the following example: insert into emp VALUES (em1_q. nextval, 'Lewis ', 'cler', 7902, SYSDATE, 1200, NULL, 20); www.2cto.com SELECT em1_q. currval from dual; but note:-the first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment by and then returns 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. Understand? -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. Www.2cto.com 2. Alter Sequence: you are the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.
Alter sequence example alter sequence emp_sequenceINCREMENT BY 10 MAXVALUE 10000 CYCLE -- To NOCACHE from the beginning after 10000; affects Sequence initialization parameters: SEQUENCE_CACHE_ENTRIES = sets the number of sequence that can be cached at the same time. Www.2cto.com can be a simple Drop SequenceDROP SEQUENCE order_seq; SYS_GUID () Comparison with sequence
The number generated by the column generator sequence can only be unique in a single instance, which is not suitable for using it as the master keyword in parallel or remote environments, the sequence in the environment may generate the same number, resulting in a conflict. SYS_GUID makes sure that the identifier it creates is unique in each database. In addition, the sequence must be part of the DML statement, so it requires a round-trip process to the database (otherwise it cannot guarantee that its value is unique ). SYS_GUID is derived from the timestamp and machine identifier that do not need to access the database, which saves the query consumption. Create table use_seq_table (id integer); create sequence values; insert into use_seq_table values (values); create table use_guid_table (id raw (16); insert into use_guid_table (sys_guid ());
SYS_GUID is useful when an object is generated in different databases of different machines and data needs to be merged. SYS_GUID () and sequence have the same functions and usage. They can be used in bi triggers.
-- Create sequencecreate sequence SEQ_ADDRESSminvalue 1 www.2cto.com maxvalue 999999999999999999999999999 start with 1 increment by 1 nocache; create or replace trigger tri_address_bi before insert on address for each rowbegin if: new. id <0 or: new. id is null then select seq_address.nextval into: new. id from dual; -- select substr (sys_guid (), 1, 32) into: new. id from dual; end if; end;/www.2cto.com
Usage Restrictions: Another notable disadvantage of SYS_GUID generated values is that managing these values is much more difficult. You must (manually) enter them, fill them with scripts, or pass them as Web parameters. For these reasons, it is not a good idea to use SYS_GUID as a primary keyword unless it is in a parallel environment or if you want to avoid using a management sequence generator. However, it is not impossible to use SYS_GUID as the primary key, but it is better to convert it to varchar2 first. It is better to display the conversion during use, such as substr (sys_guid (),). It is obviously inappropriate to directly use RAW. Directly insert raw into the varchar2 field. implicit conversion is not always appropriate. I have seen the database crash due to a large number of implicit conversions. Of course, it is a database bug afterwards.