亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

oracle sequence的使用

系統 1765 0

ORACLE序列的使用
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE權限,
CREATE SEQUENCE emp_sequence
??? INCREMENT BY 1 -- 每次加幾個
??? START WITH 1??? -- 從1開始計數
??? NOMAXVALUE????? -- 不設置最大值
??? NOCYCLE???????? -- 一直累加,不循環
??? CACHE 10
??? NOORDER;???? --并行時取得序列的順序

一旦定義了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會自動增加你定義的INCREMENT BY值,然后返回增加后的值。CURRVAL 總是返回當前SEQUENCE的值,但是在第一次NEXTVAL初始化之后才能使用CURRVAL,否則會出錯。一次NEXTVAL會增加一次 SEQUENCE的值,所以如果你在同一個語句里面使用多個NEXTVAL,其值就是不一樣的。明白?

- 如果指定CACHE值,ORACLE就可以預先在內存里面放置一些sequence,這樣存取的快些。cache里面的取完后,oracle自動再取一組 到cache。 使用cache或許會跳號, 比如數據庫突然不正常down掉(shutdown abort),cache中的sequence就會丟失. 所以可以在create sequence的時候用nocache防止這種情況。

2、Alter Sequence
你或者是該sequence的owner,或者有ALTER ANY SEQUENCE 權限才能改動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
DROP SEQUENCE order_seq;

關于Oracle的序列(Sequence)使用

序列是一數據庫對象,利用它可生成唯一的整數。一般使用序列自動地生成主鍵值。

1) 建立序列命令

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue];
[NOCYCLE]  --
INCREMENT BY : 指定序列號之間的間隔,該值可為正的或負的整數,但不可為0。序列為升序。忽略該子句時,缺省值為1。
START WITH :指定生成的第一個序列號。在升序時,序列可從比最小值大的值開始,缺省值為序列的最小值。對于降序,序列可由比最大值小的值開始,缺省值為序列的最大值。
MAXVALUE :指定序列可生成的最大值。
NOMAXVALUE :為升序指定最大值為1027,為降序指定最大值為-1。
MINVALUE :指定序列的最小值。
NOMINVALUE :為升序指定最小值為1。為降序指定最小值為-1026。
NOCYCLE :一直累加,不循環

2) 更改序列命令

ALTERSEQUENCE [user.]sequence_name
[INCREMENT BY n]
[MAXVALUE n| NOMAXVALUE ]
[MINVALUE n | NOMINVALUE];
修改序列可以:
?? 修改未來序列值的增量。
設置或撤消最小值或最大值。
改變緩沖序列的數目。
?? 指定序列號是否是有序。

注意:
1,第一次NEXTVAL返回的是初始值
2,可以alter除start至以外的所有sequence參數.如果想要改變start值,必須 drop sequence 再 re-create .

3) 刪除序列命令

DROP SEQUENCE [user.]sequence_name;
用于從數據庫中刪除一序列。

4)牛刀小試

???? 4.1)創建一個序列號的語句:

-- Create sequence
create sequence NCME_QUESTION_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
nocache;

4.2)SQL中取序列號的用法:

SELECT NCME_QUESTION_SEQ.nextval FROM dual
SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual


序:
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;

oracle sequence的使用


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 成人精品视频一区二区在线 | 在线观看一区二区精品视频 | 亚洲国产欧洲精品路线久久 | 伊人网99| 狠狠色丁香婷婷久久综合不卡 | 色的综合| 99国产精品久久久久久久日本 | 在线国产视频一区 | 中文字幕中文字幕在线 | 免费高清成人啪啪网站 | 国产小呦 | 97影院九七理论片男女高清 | 国语毛片 | 性欧美一级毛片欧美片 | 亚洲一区二区高清 | 99视频国产精品 | 亚洲欧美日韩中字综合 | 伊人狠狠丁香婷婷综合色 | 久久大香伊蕉在人线观看热2 | 国产3区| 毛片免费观看久久欧美 | 香蕉爱爱视频 | 天天综合天天看夜夜添狠狠玩 | a v在线男人的天堂观看免费 | 国产精品原创视频 | 亚洲美女在线播放 | 国产大战女模特在线视频 | 一级黄色片毛片 | 国产欧美日韩高清专区ho | 欧美激情_区二区三区 | 中文在线视频 | 国产精品久久久久久网站 | 91精品国产综合久久欧美 | 久久www免费人成精品香蕉 | 99久热在线精品视频观看 | 亚洲成人看片 | 天天色综合5| 不卡无毒免费毛片视频观看 | 国产色啪午夜免费视频 | 精品一区二区三区在线视频 | 午夜激情免费 |