Setting the self-increment primary key in Oracle

First of all, you have to have a table!
CREATE TABLE Example (
ID Number (4) Not NULL PRIMARY KEY,
NAME VARCHAR (25),
PHONE VARCHAR (10),
ADDRESS VARCHAR (50));
If you have any questions about the above statement, it is recommended that you do not continue! There are some times you might as well go to see Jin Yong read Qiong Yao!
Then, you need a custom sequence
CREATE SEQUENCE emp_sequence
INCREMENT by 1--add a few each time
Start with 1-counting starting from 1
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
NOCACHE--Do not build buffers
The above code completes the establishment of a sequence (sequence), the name is Emp_sequence, the range is from 1 to infinity (infinitely large degree is determined by your machine), Nocycle is decided not to loop, if you set the maximum then you can use cycle Causes the SEQ to loop after the maximum. For NoCache, by the way, if you give the cache value, the system will automatically read your cache value by a seq.
, which speeds up the operation as it is repeated, but if an unexpected situation such as a machine or Oracle dies, the next fetch will be inconsistent with the last time. (If the connection is not consistent, it is not recommended to use the cache, because time is money!) )
The book connected to the above, you have only the table and the sequence is not enough, but also need a trigger to execute it! The code is as follows:
CREATE TRIGGER "trigger name" before
INSERT on example for each ROW if (new.id is null)
Begin
Select Emp_sequence.nextval into:new.id from dual;
End
Finish the call! Let's try inserting the data below!
INSERT into Example (name,phone,address) Values (' Cao ', ' 56498543 ', ' Heibei ');


=============================================================
Simple Introduction to ORACLE sequence (self-growing field)--


From:http://ub1010.51.net/bbs/user_file/2002-04-10/1018438701.htm

In Oracle, sequence is the so-called serial number, which is automatically incremented each time it is taken, and is typically used where serial numbers need to be sorted.
1, CreateSequence
You first have to have CreateSequence or createanysequence permissions,
Createsequenceemp_sequence
incrementby1--add a few each time
startwith1--counting starting from 1
nomaxvalue--do not set the maximum value
nocycle--always accumulates, does not circulate
CACHE10;
Once you've defined emp_sequence, you can use Currval,nextval
Currval= returns the current value of the sequence
Nextval= increases the value of sequence and then returns the sequence value
Like what:
Emp_sequence. Currval
Emp_sequence. Nextval
Places where you can use sequence:
-SELECT statements that do not contain subqueries, snapshot, and view
-insert in a subquery for a statement
In the values of the-nsert statement
In the set of-update
You can see the following example:
Insertintoempvalues
(Empseq.nextval, ' LEWIS ', ' clerk ', 7902,sysdate,1200,null,20);
selectempseq.currvalfromdual;
However, it is important to note that:
-The first nextval returns the initial value, and the subsequent nextval automatically increments the IncrementBy value that you defined, and then returns the incremented value. Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs. A nextval will increment the value of sequence once, so if you use multiple nextval in the same statement, the value is different. Got it?
-If you specify the cache value, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdownabort), the cache sequence will be lost. So you can use NoCache to prevent this when createsequence.
2, Altersequence
You or the owner of the sequence, Or you have alteranysequence permission to change sequence. You can alter all sequence parameters except start to. If you want to change the start value, you must dropsequence the re-create again.
Examples of altersequence
Altersequenceemp_sequence
INCREMENTBY10
MAXVALUE10000
cycle--to 10000 and start from the beginning.
NOCACHE;

Initialization parameters that affect sequence:
Sequence_cache_entries= sets the number of SEQUENCE that can be simultaneously CACHE.
Can be very simple dropsequence
Dropsequenceorder_seq;

All right, here we go.


-------------------------------------------------------------
Self-growth and triggers:
How the
Oracle
Do you realize a feature like auto-increment ID in?
Finishing editing: chinaasp
We often use a system-assigned ID as our primary key when designing a database, but there is no such thing in Oracle
function, we can automatically add the function of ID by taking the following function
1. First create sequence
Createsequenceseqmaxincrementby1
2. How to use
Selectseqmax.nextvalidfromdual
You get an ID.
If you put this statement in the trigger, you can implement and MSSQL automatically add the same ID function!

-------------------------------------------------------------------------
# # #建表 # #
CreateTable "SPORTS". " Lineup "(" ID "Numbernotnull," TYPE "
Number (3) notnull, "BODY" VARCHAR2 (notnull), "HITS" number (
Default0notnull,primarykey ("ID"))
Tablespace "Ts_sports"
# # #建序列 # #
CreateSequence "SPORTS". " Sports_lineup_id_seq "INCREMENTBY1
Startwith1maxvalue1.0e28minvalue1nocycle
Cache50noorder
# # #建自动更新的触发器 # #
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;
# # #建保护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;

There is no self-increment field in Oracle, which can be implemented indirectly via sequence + trigger, Sqlplus login in cmd and run directly. It usually takes a few steps:

1 Creating a data sheet

CREATE TABLE Test_increase (
UserID Number PRIMARY KEY,/* primary key, auto increment */
Username VARCHAR2 (20)
);

2 Creating an auto-grow sequence

CREATE SEQUENCE testincrease_sequence
INCREMENT by 1--add a few each time
Start with 1-counting starting from 1
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
CACHE 10;

3 Creating a Trigger

CREATE TRIGGER Test_increase before
Insert on Test_increase for each ROW
Begin
Select Testincrease_sequence.nextval Into:New.userid from dual;

End

4 Submit

Commit

5 Testing

Execute the following statement repeatedly:

Insert into Test_increase (Username) VALUES (' Test ')

Setting the self-increment primary key in Oracle