同义词、序列
(一)序列
在很多数据库系统中多存在一个自动增长的列,如果现在想在ORACLE系统中完成自动增长的功能,则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
序列的创建格式:
CREATE SEQUENCE 序列名
[INCREMENT BY n] [START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
其中:
-
INCREMENT BY 长度 每次的增长幅度, 默认为1,如果长度为负数,则表示序列值按此步长递减
-
START WITH n 每次从哪个值开始,默认为1
-
MAXVALUE 定义序列生成器能产生的最大值。NOMAXVALUES是默认项,代表没有最大值定义,这时对于递增序列系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。
-
MINVALUE 定义序列生成器能产生的最小值。选项NOMINVALUE是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是-10的26次方;对于递增序列,最小值是1。
-
CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
-
CACHE(缓冲)定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。
序列的删除语法:
DROP SEQUENCE 序列名;
序列提供了以下两种操作:
-
nextVal:取得序列的下一个内容
-
currVal:取得序列的当前内容
注:1、在实际应用中序列使用最多的语法是:CREATE SEQUENCE 序列名
2、如果序列还没有通过调用NEXTVAL产生过序列的下一个值,先引用CURRVAL没有意义。第一次调用NEXTVAL产生序列的初始值,第二次调用才产生序列的下个值。
例1:简单序列使用
a:创建一个myseq序列,验证自动增长操作
SQL> create sequence myseq;
Sequence created
b:建立一张表以验证序列
SQL> create table testmyseq
2 (next number,curr number);
Table created
c:使用序列,向表中添加数据
SQL> begin
2 for i in 1..5 loop
3 insert into testmyseq values (myseq.nextval,myseq.currval);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
d:查询以上操作的结果
SQL> select * from testmyseq;
NEXT CURR
---------- ----------
1 1
2 2
3 3
4 4
5 5
从结果可以发现,nextVal的内容始终在进行自动增长的操作,而curr使用取出当前操作的序列结果。
也就是说,现在的这种序列,每次增长的幅度是1
例2:使用INCREMENT BY n重新创建序列
a:删除序列myseq
SQL> drop sequence myseq;
Sequence dropped
b:使用INCREMENT BY n重新创建序列
SQL> create sequence myseq increment by 3;
Sequence created
c:删除testmyseq表,并重新创建
SQL> drop table testmyseq;
Table dropped
SQL> create table testmyseq
2 (next number,curr number);
Table created
d: 使用序列,向表中添加数据
SQL> begin
2 for i in 1..5 loop
3 insert into testmyseq values (myseq.nextval,myseq.currval);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
e:查询以上操作的结果
SQL> select * from testmyseq;
NEXT CURR
---------- ----------
1 1
4 4
7 7
10 10
13 13
从结果可以发现,默认是从1开始的
例3:使用START WITH n改变序列开始值
a:删除序列myseq
SQL> drop sequence myseq;
Sequence dropped
b: 使用INCREMENT BY n、START WITH n重新创建序列
SQL> create sequence myseq increment by 2 start with 10;
Sequence created
c:删除testmyseq表,并重新创建
SQL> drop table testmyseq;
Table dropped
SQL> create table testmyseq
2 (next number,curr number);
Table created
d: 使用序列,向表中添加数据
SQL> begin
2 for i in 1..5 loop
3 insert into testmyseq values (myseq.nextval,myseq.currval);
4 end loop;
5 end;
6 /
e:查询以上操作的结果
SQL> select * from testmyseq;
NEXT CURR
---------- ----------
10 10
12 12
14 14
16 16
18 18
例4:创建一个序列,让其取值固定在1、3、5循环序列
a.删除序列myseq,并重新创建
SQL> drop sequence myseq;
Sequence dropped
SQL> create sequence myseq increment by 2 start with 1 maxvalue 5 cycle CACHE 2;
Sequence created
b:删除testmyseq表,并重新创建
SQL> drop table testmyseq;
Table dropped
SQL> create table testmyseq
2 (next number,curr number);
Table created
c: 使用序列,向表中添加数据
SQL> begin
2 for i in 1..7 loop
3 insert into testmyseq values (myseq.nextval,myseq.currval);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
d:查询以上操作结果
SQL> select * from testmyseq;
NEXT CURR
---------- ----------
1 1
3 3
5 5
1 1
3 3
5 5
1 1
7 rows selected
例5:使用INCREMENT BY n、NOMAXVALUES创建递减序列
a:删除序列myseq,并重新创建
SQL> drop sequence myseq;
Sequence dropped
SQL> create sequence myseq increment by -2 nomaxvalue;
Sequence created
b:删除testmyseq表,并重新创建
SQL> drop table testmyseq;
Table dropped
SQL>
SQL> create table testmyseq
2 (next number,curr number);
Table created
c: 使用序列,向表中添加数据
SQL> begin
2 for i in 1..3 loop
3 insert into testmyseq values (myseq.nextval,myseq.currval);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
d:查询以上操作的结果
SQL> select * from testmyseq;
NEXT CURR
---------- ----------
-1 -1
-3 -3
-5 -5
(二)同义词
1、之前一直存在这样的查询语句:
SQL> select sysdate from dual;
SYSDATE
-----------
2010-5-9 23
之前一直强调DUAL是一张虚拟表,那么虽然是虚拟表,那么此表到底是在哪里定义的呢?
2、如果现在使用SYSTEM连接数据,查询一下此张表是否属于SYSTEM用户
SQL> conn system/******;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as system
SQL> select * from tab where tname=upper('dual');
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SQL>
通过此用户没有查找到dual表,那么sys用户呢?
3、
SQL> conn sys/******as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> select * from tab where tname=upper('dual');
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DUAL TABLE
在sys用户下存在dual这张表,但是在scott用户下却可以直接通过表名称访问,那么正常情况下如果要访问不同用户的表需要使用“用户名.表名称”。
假设要在SYS下访问SCOTT下的DEPT表
SQL> select * from dept;
select * from dept
ORA-00942: 表或视图不存在
SQL> select * from scott.dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING 纽约
20 RESEARCH 达拉斯
30 SALES 芝加哥
40 OPERATIONS 波士顿
那么此时,实际上就是同义词的作用。同义词,可以让其他用户通过一个名称方便的访问“用户名.表名称”。
4、创建同义词的语法
CREATE SYNONYM 同义词名称 FOR 用户名.表名称;
5、删除同义词的语法
DROP SYNONYM 同义词名称
6、 同义词使用1
SQL> show user;
User is "SYS"
SQL> select * from dept;
select * from dept
ORA-00942: 表或视图不存在
SQL> create synonym dept for scott.dept;
Synonym created
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING 纽约
20 RESEARCH 达拉斯
30 SALES 芝加哥
40 OPERATIONS 波士顿
SQL> conn system/******;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as system
SQL> show user;
User is "system"
SQL> select * from dept;
select * from dept
ORA-00942: 表或视图不存在
SQL> create synonym dept for scott.dept;
Synonym created
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING 纽约
20 RESEARCH 达拉斯
30 SALES 芝加哥
40 OPERATIONS 波士顿
可以看出,当前用户下创建的同义词,不能被其他用户使用。
7、 同义词使用2
SQL> connect sys/****** as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> select * from dual;
DUMMY
-----
X
SQL> create synonym dddd for dual;
Synonym created
SQL> select * from dddd;
DUMMY
-----
X
可以看出给同一个用户下的表指定同义词,相当于给名称长的表指定了一个较短的别名。
8、删除同义词
SQL> drop synonym dddd;
Synonym dropped
注:同义词只适用ORACLE数据库