Orace sequenced (auto-increment field)

In oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. CreateSequence
You must first have the CREATESEQUENCE or CREATEANYSEQUENCE permissions,
CREATESEQUENCEemp_sequence
INCREMENTBY1 -- add a few
STARTWITH1 -- count from 1
NOMAXVALUE -- do not set the maximum value
NOCYCLE -- always accumulate without repeating
CACHE10;

Once emp_sequence is defined, you can use CURRVAL, NEXTVAL
CURRVAL = returns the current sequence Value
NEXTVAL = increase the sequence value, and then return the sequence Value
For example:
Emp_sequence.CURRVAL
Emp_sequence.NEXTVAL

Where sequence can be used:
-SELECT statements that do not contain subqueries, snapshot, and VIEW
-The INSERT statement is in the subquery.
-In the VALUES of the INSERT statement
-UPDATE in SET

See the following example:
INSERTINTOempVALUES
(Em1_q. nextval, 'Lewis ', 'cler', 7902, SYSDATE, 1200, NULL, 20 );

SELECTempseq. currvalFROMDUAL;

Note that:
-The first value returned by NEXTVAL is the initial value. The subsequent NEXTVAL will automatically add your defined INCREMENTBY 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. 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 (shutdownabort), the sequence in the cache will be lost. Therefore, nocache can be used in createsequence to prevent this situation.

2. AlterSequence
You are either the owner of the sequence, or you have the ALTERANYSEQUENCE permission to modify the sequence. You can alter all sequence parameters except start. To change the start value, you must dropsequence and re-create.
Altersequence example
ALTERSEQUENCEemp_sequence
INCREMENTBY10
MAXVALUE10000
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.

It can be very simple DropSequence
Drow.quenceorder_seq;

Okay, that's it.


-------------------------------------------------------------
Auto-growth and triggers:

In Oracle, how does one implement the function of auto-adding IDs?
Edit: ChinaASP

We often use an ID automatically assigned by the system as our primary key when designing a database, but this is not true in ORACLE.

Feature, we can automatically add the ID by taking the following features
1. First create sequence
Createsequenceseqmaxincrementby1
2. Usage
Selectseqmax. nextvalIDfromdual
You get an ID.
If you put this statement in the trigger, you can automatically add the same ID as mssql!
-------------------------------------------------------------------------

Released by macro at: 2001-04-1314: 44

By MacroZeng

 

### Create a table ###

CREATETABLE "SPORTS". "LINEUP" ("ID" NUMBERNOTNULL, "TYPE"
NUMBER (3) NOTNULL, "BODY" VARCHAR2 (100) NOTNULL, "HITS" NUMBER (
10) DEFAULT0NOTNULL, PRIMARYKEY ("ID "))
TABLESPACE "TS_SPORTS"

### Sequence creation ###

CREATESEQUENCE "SPORTS". "SPORTS_LINEUP_ID_SEQ" INCREMENTBY1
STARTWITH1MAXVALUE1.0E28MINVALUE1NOCYCLE
CACHE50NOORDER

### Create an automatic update trigger ###

CREATEORREPLACETRIGGER "SPORTS". "SPORTS_LINEUP_ID_TRIGGER"
BEFOREINSERT
ON "SPORTS". "LINEUP"
FOREACHROW
DECLARE
Next_idNUMBER;
BEGIN
-- Getthenextidnumberfromthesequence
SELECTsports_lineup_id_seq.NEXTVAL
INTOnext_id
FROMdual;

-- Usethesequencenumberastheprimarykey
-- Fortherecordbeinginserted.
: New. id: = next_id;
END;

### Create a trigger to protect PRIMARYKEY ###

CREATEORREPLACETRIGGER "SPORTS". "LINEUP_ID_UPDATE_TRIGGER"
BEFOREUPDATEOF "ID" ON "SPORTS". "LINEUP"
FOREACHROW
BEGIN
RAISE_APPLICATION_ERROR (-20000,
'Sports _ lineup_id_update_trigger: UpdatesoftheIDfield'
| 'Arenotallowed .');
END;