Oracle 自动加一主键字段生成

来源说明:本文系转贴,原文网址http://www.cnblogs.com/leup/archive/2007/11/18/962952.html

转贴原因:Oracle自增字段和SQL不同,但是调用很有意思,多个人同时调用,不会有唯一的ID生成.

在access中有自动编号的数据类型，MSSQL和MYSQL也都有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值，而oracle没有自动增长的数据类型，我们需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段，可以预见的是，有此功能，我们可以把数据从ACCESS、MSSQL或MYSQL迁移到oracle了！
create sequence type_id increment by 1 start with 1;
这句中，type_id为序列号的名称，每次增长为1，起始序号为1。
如果要删除序列，用drop sequence 序列名就可以了！！

序列可以保证多个用户对同一张表进行操作时生成唯一的整数,利用序列可以自动生成主关键字,序列只存在于数据字典中.

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE |NOCYCLE}]
[{CACHE n|NOCACHE}];
INCREMENT BY--指定步长
START WITH--指定初始值
MAXVALUE--定义序列生成的最大编号.默认的MAXVALUE就是NOMAXVALUE,对于递增序列为10^27,对于递减序列为-1
MINVALUE--定义序列的最小编号,默认的MINVALUE为NOMINVALUE,对于递增序列为1,递减序列为-10^26.
CYCLE--配置序列在达到界限值时重复编号
NOCYCLE--达到界限值时不重复编号,这是默认值,当你试图生成MAXVALUE+1时将返回异常.
CACHE--定义在内存中保留的序列编号块的大小,默认值为20.
NOCACHE--强制数据词典对于生成的每个序列编号进行更新,保证在生成的编号中没有空缺,但这样会降低性能.

生成一个序列
CREATE SEQUENCE dept_deptid_seq
INCREAMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
//如果是用来生成主键值的话,不要用CYCLE选项,而且命名序列时最好能体现它的潜在用途以便于理解.

确认序列
SELECT sequence_name,min_value,max_value,increament_by,last_number
FROM user_sequences;
//如果你指定了NOCACHE选项,那么LAST_NUMBER列将显示下一可用的序列号.

使用NEXTVAL可以访问序列中的下一个编号,但问题常常出现在会话初始序列之前查询其当前序列号CURRVAL
CREATE SEQUENCE emp_seq
NOMAXVALUE
NOCYCLE;
然后查询
SELECT emp_seq.currval
FROM dual;
将返回错误,问题就在于你视图引用CURRVAL之前,在你的会话中并没有使用NEXTVAL先初始化此序列.
SELECT emp_seq.nextval
FROM dual;
这样再查询CURRVAL就不会出错了.

使用序列
INSERT INTO departments(department_id,department_name,location_id)
VALUES (dept_deptid_seq.NEXTVAL,'Support',2500);

对序列进行缓冲存储可以提高性能,因为这样就不必对每个生成的编号都更新数据字典表,只需要对每一组编号进行更新即可.这样,在我们查询NEXTVAL时就直接从缓冲中提取,速度将快很多,但是进行序列缓冲带来的负面影响就是当数据库被回滚时,比如说系统崩溃,手动ROLLBACK数据时,在缓冲中存储的序列值将会丢失,这也就是为什么会出现空缺(GAPS),如果生成序列时指定的是NOCACHE,那么可以在USER_SEQUENCES表里查询下一个可用的序列号值,这个查询并不会产生增加序列值的动作.

修改序列
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
规则:
>必须为序列的所有者或者拥有ALTER特权
>修改对于以后的序列号生效
>序列必须是被删除然后重新生成(使所有相关的对象失效,并且失去相应的关联)
>修改时还要满足些其他的验证条件,比如说新的MAXVALUE不可以比现在的序列号低

删除序列
DROP SEQUENCE dept_deptid_seq;
>必须要时序列的所有者或者有DROP ANY SEQUENCE的权限

索引
索引是通过获取特定的行信息而与默认的全表扫描相比大大提高系统性能的数据结构.可是显式的手动建立,也可一由ORACLE自动生成,它们是与之索引的表相独立的,就是说,可以在任何时候建立或者删除索引而对基表或者其他索引无任何影响(当你删除表时,相关的索引将会被删除).
有两种索引
>唯一索引--当你定义了一列含有主键或者唯一键约束时将自动生成一个唯一索引(可以手动建立,但是推荐由ORACLE自动建立)
>非唯一索引--当你手动为一个查询中的连接建立一个外键索引来加速查询速度时

建立索引
CREATE INDEX index
ON table (column[,column]...);
e.g.
CREATE INDEX emp_last_name_idx
ON employees(last_name);

何时使用索引
>当一个列包含的数值范围较大时
>当一列包含大量空值时
>一个或者多个列经常一起在WHERE子句或者JOIN子句中使用时
>表非常大,但是大多数的查询只要求检索少于2-4个百分点的行记录

何时不该使用索引
>表很小时
>列并不经常作为查询中的条件使用时
>大多数查询都检索多于4个百分点的行记录
>表经常被更新
>索引列被作为表达式的一部分引用

索引虽然可以很大程度提高检索性能,但是越多的索引,意味着在DML操作之后ORACLE就将花越多的功夫去更新索引.所以,一定要适时使用,

确认索引
查询数据字典视图USER_INDEXES和USER_IND_COLUMNS可以检索到索引的相关信息
SELECT ic.index_name,ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix,user_ind_columns ic
WHERE ic.inde_name=ix.index_name
AND ic.table_name='EMPLOYEES';

基于函数的索引
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
//但是如果想保证ORACLE使用索引而不是全表扫描就必须保证函数值非空,就是需要加个WHERE子句指定非空如
SELECT *
FROM employees
WHERE UPPER(last_name_ IS NOT NULL
ORDER BY UPPER(last_name);
如果没有WHERE子句,则将会进行全表扫描非使用索引了.

删除索引
DROP INDEX index;
//当你删除一个表时,索引和约束将会自动删除,但是视图和序列将会保留.

同义词
同义词经常用来通过为一个本地或者远程对象给定一个通用的名字来简化SQL.同义词可以指向一个表,视图,序列,过程,函数或者本地数据库中的包,或者通过一个数据库连接指向另一个数据库中的对象.公共同义词可供所有用户使用,而专用同义词则只能供其所有者或者获得了相关授权的帐户所有者使用.
比如说SCOTT拥有表EMP,所有用户都使用自己的用户名登陆数据库,并且必须引用该表,即SCOTT.EMP,如果我们为其生成一个同义词EMP,每个对该表具有相关特权的人都可以简单地在自己的SQL或者PL/SQL语句中以EMP的形式来引用它,不需要再指出所有者了.

CREATE [PUBLIC] SYNONYM synonym
FOR object;

e.g.
CREATE SYNONYM d_sum
FOR dept_sum_vu;

DROP SYNONYM d_sum;

CREATE PUBLIC SYNONYM dept
FOR alice.departments;

DROP PUBLIC SYNONYM dept;
//仅仅数据库管理员可以删除公共同义词

---

关于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

注意：在使用序列的时候，有时需要有用户名，就像这样：

insert into system.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values('JG','123456','000',0, system.CONID.nextval);