Oracle：sequence实现id自增

首先，你要有一张表！

CREATE TABLE example(

ID Number(4) NOT NULL PRIMARY KEY,

NAME VARCHAR(25),

PHONE VARCHAR(10),

ADDRESS VARCHAR(50) );

然后，你需要一个自定义的sequence

CREATE SEQUENCE emp_sequence

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXVALUE -- 不设置最大值

NOCYCLE -- 一直累加，不循环

NOCACHE -- 不建缓冲区

以上代码完成了一个序列(sequence)的建立过程,名称为emp_sequence,范围是从1开始到无限大(无限大的程度是由你机器决定的),nocycle 是决定不循环,如果你设置了最大值那么你可以用cycle 会使seq到最大之后循环.对于nocache顺便说一下如果你给出了cache值那么系统将自动读取你的cache值大小个seq

,这样在反复操作时会加快运行速度,但如果遭遇意外情况如当机了或oracle死了,则下次取出的seq值将和上次的不连贯.(如果连不连贯无所谓建议用cache,因为时间就是金钱呀!跑题了！)

书接上文，你只有了表和序列还不够，还需要一个触发器来执行它！代码如下：

CREATE TRIGGER "触发器名称" BEFORE

INSERT ON example FOR EACH ROW WHEN (new.id is null)

begin

select emp_sequence.nextval into :new.id from dual;

end;

打完收工！下面你就试试插入数据吧！

INSERT INTO example(Name,phone,address) Values('Cao','56498543','Heibei');

==================================================================================

ORACLE SEQUENCE的简单介绍(自增长字段)- -

在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。

1、Create Sequence

你首先要有Create Sequence或者Create AnySequence权限，

Create Sequence emp_sequence

INCREMENT BY 1    --每次加几个

STARTWITH 1    --从1开始计数

NOMAXVALUE    --不设置最大值

NOCYCLE    --一直累加，不循环

CACHE10 ;

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL

CURRVAL=返回sequence的当前值

NEXTVAL=增加sequence的值，然后返回sequence值

比如：

emp_sequence.CURRVAL

emp_sequence.NEXTVAL

可以使用sequence的地方：

-不包含子查询、snapshot、VIEW的SELECT语句

-INSERT语句的子查询中

-INSERT语句的VALUES中

-UPDATE的SET中

可以看如下例子：

INSERT INTO emp VALUES

(empseq.nextval,'LEWIS','CLERK',7902,SYSDATE,1200,NULL,20);

SELECT empseq.currval FROM DUAL;

但是要注意的是：

第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENTBY值，然后返回增加后的值。CURRVAL总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？

如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号，比如数据库突然不正常down掉（shutdownabort),cache中的sequence就会丢失.所以可以在createsequence的时候用nocache防止这种情况。

2、Alter Sequence

你或者是该sequence的owner，或者有ALTER ANYSEQUENCE权限才能改动sequence.可以alter除start至以外的所有sequence参数.如果想要改变start值，必须drop sequence再re-create.

Alter sequence的例子

ALTER SEQUENCE emp_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE--到10000后从头开始

NOCACHE;

影响Sequence的初始化参数：

SEQUENCE_CACHE_ENTRIES=设置能同时被cache的sequence数目。

可以很简单的Drop Sequence

DRO SEQUENCE order_seq;

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

自增长及触发器:

如何在Oracle中实现类似自动增加ID的功能?

我们经常在设计数据库的时候用一个系统自动分配的ID来作为我们的主键，但是在ORACLE中没有这样的功能，我们可以通过采取以下的功能实现自动增加ID的功能

1.首先创建sequence

create sequence seq maxincrement by 1

2.使用方法

select seqmax.nextval ID from dual

就得到了一个ID

如果把这个语句放在触发器中，就可以实现和mssql的自动增加ID相同的功能！

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

###建表###

CREATE TABLE "SPORTS"."LINEUP"("ID" NUMBER NOT NULL,

"TYPE" NUMBER(3) NOT NULL,

"BODY" VARCHAR2(100) NOT NULL,

"HITS" NUMBER(10) DEFAULT 0 NOT NULL,

PRIMARYKEY("ID"))

TABLESPACE "TS_SPORTS"

###建序列###

CREATE SEQUENCE "SPORTS"."SPORTS_LINEUP_ID_SEQ" INCREMENT BY 1

START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE

CACHE 50 NOORDER

###建自动更新的触发器###

CREATE OR REPLACE TRIGGER "SPORTS"."SPORTS_LINEUP_ID_TRIGGER"

BEFORE INSERT ON "SPORTS"."LINEUP" FOR EACH ROW

DECLARE

next_id NUMBER;

BEGIN

--Get the next id number from the sequence

SELECT sports_lineup_id_seq.NEXTVAL INTO next_id FROM dual;

--Use the sequence number as the primarykey

--for there cord being inserted.

:new.id:=next_id;

END;

###建保护PRIMARYKEY的触发器###

CREATE OR REPLACE TRIGGER "SPORTS"."LINEUP_ID_UPDATE_TRIGGER"

BEFORE UPDATE OF "ID" ON "SPORTS"."LINEUP" FOR EACHROW

BEGIN

RAISE_APPLICATION_ERROR(-20000,

'sports_lineup_id_update_trigger:Update sof the ID field'

｜｜'arenotallowed.');

END;

###建删除的触发器###

create   or replace trigger tr_bis_exc_req_del

before delete

on bis_exc_req

referencing old as old new as new

for each row

begin

ifld.check_status = '3' then

raise_application_error (-20001,'*****!');

return;

end if;

end;

/

###建更新的触发器###

create   or replace trigger tr_bis_exc_req_upd

before update

on bis_exc_req

referencing old as old new as new

for each row

begin

ifld.check_status = '3' then

raise_application_error (-20001,'*******!');

return;

end if;

end;

<script type="text/javascript"><!-- google_ad_client = "pub-1076724771190722"; /* JE个人博客468x60 */ google_ad_slot = "5506163105"; google_ad_width = 468; google_ad_height = 60; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script><script src="http://pagead2.googlesyndication.com/pagead/js/r20101117/r20110307/show_ads_impl.js"></script><script src="http://pagead2.googlesyndication.com/pagead/expansion_embed.js"></script><script src="http://googleads.g.doubleclick.net/pagead/test_domain.js"></script><script src="http://pagead2.googlesyndication.com/pagead/render_ads.js"></script><script></script>

 

=========================================================================================================================

 

CREATE SEQUENCE command

PURPOSE:

    To create a sequence. A sequence is a database object from which

    multiple users may generate unique integers. You can use sequences

    to automatically generate primary key values.

SYNTAX:

CREATE SEQUENCE [schema.]sequence

    [INCREMENT BY integer]

    [START WITH integer]

    [MAXVALUE integer | NOMAXVALUE]

    [MINVALUE integer | NOMINVALUE]

    [CYCLE | NOCYCLE]

    [CACHE integer | NOCACHE]

    [ORDER | NOORDER]

where:

schema

    is the schema to contain the sequence. If you omit schema, Oracle

    creates the sequence in your own schema.

sequence

    is the name of the sequence to be created.

INCREMENT BY

    specifies the interval between sequence numbers. This value can be

    any positive or negative Oracle integer, but it cannot be 0. If

    this value is negative, then the sequence descends. If the

    increment is positive, then the sequence ascends. If you omit this

    clause, the interval defaults to 1.

MINVALUE

    specifies the sequence's minimum value.

NOMINVALUE

    specifies a minimum value of 1 for an ascending sequence or -10

    for a descending sequence.

    The default is NOMINVALUE.

MAXVALUE

    specifies the maximum value the sequence can generate.

NOMAXVALUE

    specifies a maximum value of 10

    for a descending sequence.

The default is NOMAXVALUE.

START WITH

    specifies the first sequence number to be generated. You can use

    this option to start an ascending sequence at a value greater than

    its minimum or to start a descending sequence at a value less than

    its maximum. For ascending sequences, the default value is the

    sequence's minimum value. For descending sequences, the default

    value is the sequence's maximum value.

CYCLE

    specifies that the sequence continues to generate values after

    reaching either its maximum or minimum value. After an ascending

    sequence reaches its maximum value, it generates its minimum value.

    After a descending sequence reaches its minimum, it generates its

    maximum.

NOCYCLE

    specifies that the sequence cannot generate more values after

    reaching its maximum or minimum value.

    The default is NOCYCLE.

CACHE

    specifies how many values of the sequence Oracle preallocates and

    keeps in memory for faster access. The minimum value for this

    parameter is 2. For sequences that cycle, this value must be less

    than the number of values in the cycle.

NOCACHE

    specifies that values of the sequence are not preallocated.

    If you omit both the CACHE parameter and the NOCACHE option, Oracle

    caches 20 sequence numbers by default. However, if you are using

    Oracle with the Parallel Server option in parallel mode and you

    specify the ORDER option, sequence values are never cached,

    regardless of whether you specify the CACHE parameter or the NOCACHE

    option.

ORDER

    guarantees that sequence numbers are generated in order of request.

    You may want to use this option if you are using the sequence

    numbers as timestamps. Guaranteeing order is usually not important

    for sequences used to generate primary keys.

NOORDER

    does not guarantee sequence numbers are generated in order of

    request.

    If you omit both the ORDER and NOORDER options, Oracle chooses

    NOORDER by default. Note that the ORDER option is only necessary to

    guarantee ordered generation if you are using Oracle with the

    Parallel Server option in parallel mode. If you are using exclusive

    mode, sequence numbers are always generated in order.

PREREQUISITES:

    To create a sequence in your own schema, you must have CREATE

    SEQUENCE privilege.

    To create a sequence in another user's schema, you must have CREATE

    ANY SEQUENCE privilege. If you are using Trusted Oracle in DBMS MAC

    mode, your DBMS label must dominate the creation label of the owner

    of the schema to contain the sequence.

example

create sequence seqTest

increment by 1

start with 0

maxvalue 10000

minvalue 0

nocache

cycle

noorder;

select seqTest.nextval from dual;

select seqTest.currval from dual;

alter sequence seqTest

increment by 5;

drop sequence seqTest;