--=============================================
--SQL 基础 --> 序列 (SEQUENCE) 、同义词 (SYNONYM)
--=============================================
一、序列
是一个 Oracle 对象，提供唯一的数字，在需要时根据指定的增量值来递增，通常用于产生主键值
类似于 SQL server 中的 IDENTITY(int,1,1) 或者列为 IDENTITY 列。 SQL server 可以直接将列指定
为 IDENTITY 列，在使用的时候可以不需要理会 IDENTITY 列，系统会自动递增，这样看来 SQL
server 中主键的产生更为简便。
1. 序列的特性：
自动提供唯一的数值
共享对象
主要用于提供主键值
代替应用代码
将序列值装入内存可以提高访问效率
2.CREATE SEQUENCE 语句定义序列 :
CREATE SEQUENCE sequencename
[INCREMENT BY n] 定义序列增长步长，省略为 1
[START WITH m] 序列起始值，省略为 1
[{MAXVALUE n | NOMAXVALUE}] 序列最大值， NOMAXVALUE 升序时，序列最大值的次方
降序时为－（默认也是为此）
[{MINVALUE n | NOMINVALUE}] 序列最小值， NOMINVALUE 升序时，最小值为，降序时序列
最小值为－的次方（默认也是为此）
[{CYCLE | NOCYCLE}] 到达最大值或最小值后，继续产生序列（默认 NOCYCLE ）
[{CACHE n | NOCACHE}]; 序列缓存与否（默认 NOCACHE ）
关于创建序列，使用序列所需要的权限请参考： Oracle 用 户 、 对 象 权 限 、 系 统 权 限
-- 创建一个序列
SQL > CREATE SEQUENCE my_seq
2 INCREMENT BY 10
3 START WITH 100
4 MAXVALUE 150
5 NOCACHE
6 NOCYCLE ;
Sequence created .
3. 使用序列
NEXTVAL 和 CURRVAL 伪列
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
第一次使用时 CURRVAL 不能用
使用时需要指定序列的对象名
将序列值装入内存可提高访问效率
序列在下列情况下出现裂缝 :
– 回滚
– 系统异常
– 多个表同时使用同一序列
如果不将序列的值装入内存 ( NOCACHE ), 可使用表 USER_SEQUENCES 查看序列当前的有效值
-- 当第一次使用序列时指定了 currval 列，结果出现如下错误提示
SQL > SELECT my_seq . currval FROM dual ;
SELECT my_seq . currval FROM dual
*
ERROR at line 1 :
ORA - 08002 : sequence MY_SEQ . CURRVAL is not yet defined in this session
-- 使用 nextval 列，则成功执行，这就是 NEXTVAL 必须在 CURRVAL 之前指定，及第一次使用必须是 NEXTVAL
SQL > SELECT my_seq . nextval FROM dual ;
NEXTVAL
----------
100
SQL > SELECT my_seq . currval FROM dual ;
CURRVAL
----------
100
4. 查询序列
USER_SEQUENCES
DBA_SEQUENCES
ALL_SEQUENCES
查询数据字典视图 USER_SEQUENCES 获取序列定义信息
如果指定 NOCACHE 选项，则列 LAST_NUMBER 显示序列中下一个有效的值
SQL > SELECT sequence_name , min_value , max_value
2 increment_by , cycle_flag , order_flag ,
3 cache_size , last_number
4 FROM user_sequences
5 WHERE sequence_name IN ( 'SEQ1' , 'SEQ2' , 'MY_SEQ' );
SEQUENCE_NAME MIN_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ - - ---------- -----------
MY_SEQ 1 150 N N 0 110
SEQ1 1 200 Y N 0 110
SEQ2 1 200 Y N 0 31
5. 序列应用举例 :
SQL > conn robinson / lion
Connected .
SQL > ALTER TABLE robinson . dept MODIFY ( deptno NUMBER );
Table altered .
-- 出现了下面的错误，怀疑是授权的问题
SQL > INSERT INTO robinson . dept ( deptno , dname , loc )
2 VALUES ( my_seq . currval , 'Customers' , 'HongKong' );
VALUES ( my_seq . currval , 'Customers' , 'HongKong' )
*
ERROR at line 2 :
ORA - 02289 : sequence does not exist
-- 使用 sys 帐户授权成功
SQL > conn sys as sysdba
Enter password :
Connected .
SQL > GRANT ALL ON my_seq TO robinson ;
Grant succeeded .
-- 查看权限已被成功授予
SQL > select grantee , owner , table_name , grantor , privilege from user_tab_privs ;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
---------- ---------- ------------- -------------- --------------
ROBINSON SCOTT EMP SCOTT SELECT
ROBINSON SYS MY_SEQ SYS SELECT
ROBINSON SYS MY_SEQ SYS ALTER
-- 成功授权之后还是出现同样的提示
SQL > INSERT INTO robinson . dept ( deptno , dname , loc )
2 VALUES ( my_seq . currval , 'Customers' , 'HongKong' );
VALUES ( my_seq . currval , 'Customers' , 'HongKong' )
*
ERROR at line 2 :
ORA - 02289 : sequence does not exist
-- 在 sequence 名字前增加 schema 为 sys ，出现了如下提示
SQL > INSERT INTO robinson . dept ( deptno , dname , loc )
2 VALUES ( sys.my_seq . currval , 'Customers' , 'HongKong' );
VALUES ( sys.my_seq . currval , 'Customers' , 'HongKong' )
*
ERROR at line 2 :
ORA - 08002 : sequence MY_SEQ . CURRVAL is not yet defined in this session
-- 修改 currval 为 nextval, 操作成功
SQL > INSERT INTO robinson . dept ( deptno , dname , loc )
2 VALUES ( sys.my_seq . nextval , 'Customers' , 'HongKong' );
1 row created .
SQL > SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
110 Customers HongKong
-- 第二次使用 currval 可以成功执行，因该表未设置主键，故未提示冲突
SQL > INSERT INTO robinson . dept ( deptno , dname , loc )
2 VALUES ( sys.my_seq . currval , 'Customers' , 'HongKong' );
1 row created .
SQL > SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
110 Customers HongKong
110 Customers HongKong
6. 修改序列
修改序列的增量 , 最大值 , 最小值 , 循环选项 , 或是否装入内存
修改序列的注意事项
必须是序列的拥有者或对序列有 ALTER 权限
只有将来的序列值会被改变
改变序列的初始值只能通过删除序列之后重建序列的方法实现
其它的一些限制
SQL > ALTER SEQUENCE my_seq
2 INCREMENT BY 20
3 MAXVALUE 160
4 CYCLE ;
Sequence altered
-- 注意当序列值达到最大值后，其初始值变成了，但增量值不会发生变，如下面的例子
SQL > select sys.my_seq . nextval from dual ;
NEXTVAL
----------
130
SQL > /
NEXTVAL
----------
150
SQL > / -- 此时序列值变成了
NEXTVAL
----------
1
SQL > / -- 此时序列值按作为增量值，所以结果为
NEXTVAL
----------
21
7. 删除序列
使用 DROP SEQUENCE 语句删除序列
删除之后，序列不能再次被引用
SQL > DROP SEQUENCE sys.my_seq ;
DROP SEQUENCE sys.my_seq
*
ERROR at line 1 :
ORA - 01031 : insufficient privileges
SQL > CONN sys as sysdba
Enter password :
Connected .
SQL > DROP SEQUENCE my_seq ;
Sequence dropped
8. 创建序列的详细语法： CREATE SEQUENCE
二、同义词
是 Oracle 对象的别名，使用同义词访问相同的对象
可以为表、视图、存储过程、函数或另一同义词等对象创建同义词
方便访问其它用户的对象，隐藏了对象的身份
缩短对象名字的长度
1. 创建同义词的权限
CREATE ANY SYNONYM
CREATE PUBLIC SYNONYM
2. 创建同义词
CREATE [PUBLIC] SYNONYM synonym_name FOR object ;
3. 查看同义词
DBA_OBJECTS
DBA_SYNONYMS
USER_SYNONYMS
-- 查看系统同义词
SQL > SELECT object_name , object_type , created , status FROM dba_objects
2 WHERE object_name = 'S' ;
SQL > SELECT OWNER , SYNONYM_NAME , TABLE_OWNER , TABLE_NAME
2 FROM dba_synonyms ;
4. 删除同义词
DROP SYNONYM synonymname
所需权限
DROP PUBLIC SYNONYM
DROP ANY SYNONYM
5. 同义词应用举例
-- 演示使用 scoot 帐户创建公共同义词
SQL > SHOW USER ;
USER is "SCOTT"
SQL > CREATE PUBLIC SYNONYM DEPARTMENT FOR scott . dept ;
Synonym created .
--robinson 帐户访问同义词 DEPARTMENT
SQL > CONN robinson / lion ; -- 注意此处 robinson 必须对 scott.dept 具有 select 权限，否则访问 DEPARTMENT 不成功
Connected .
SQL > SELECT * FROM DEPARTMENT ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL > CONN scott / tiger ;
Connected .
-- 对于创建的公共同义词没有出现在 user_synonyms 视图中
SQL > SELECT synonym_name , table_owner , table_name FROM user_synonyms ;
no rows selected
--scott 再次为同一个对象创建一个私有同义词，且与公共同义词同名，并没有报错
SQL > CREATE SYNONYM DEPARTMENT FOR scott . dept ;
Synonym created .
--scott 再次查看同义词视图中有一条记录，则该记录为私有同义词
SQL > SELECT synonym_name , table_owner , table_name FROM user_synonyms ;
SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
DEPARTMENT SCOTT DEPT
-- 使用 sys 帐户查看为条记录，一条为私有，一条为公共，哪条为公，哪条为私，不太好区分
SQL > CONN sys as sysdba ;
Enter password :
Connected .
SQL > SELECT synonym_name , table_owner , table_name FROM dba_synonyms
2 WHERE table_name = 'DEPT' ;
SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
DEPARTMENT SCOTT DEPT
DEPARTMENT SCOTT DEPT
--scott 用户登陆后删除公共同义词，提示权限不够
SQL > DROP PUBLIC SYNONYM DEPARTMENT ;
DROP PUBLIC SYNONYM DEPARTMENT
*
ERROR at line 1 :
ORA - 01031 : insufficient privileges
-- 为 scott 授予删除同义词的权限
SQL > CONN sys as sysdba ;
Enter password :
Connected .
SQL > GRANT DROP ANY SYNONYM , DROP PUBLIC SYNONYM TO scott ;
Grant succeeded .
--scott 成功删除同义词
SQL > CONN scott / tiger ;
Connected .
SQL > DROP PUBLIC SYNONYM DEPARTMENT ;
Synonym dropped .
-- 删除公共同义词后 robinson 不可访问
SQL > CONN robinson / lion ;
Connected .
SQL > SELECT * FROM DEPARTMENT ;
SELECT * FROM DEPARTMENT
*
ERROR at line 1 :
ORA - 00942 : table or view does not exist
--scott 对于私有的同义词仍然可以使用
SQL > conn scott / tiger ;
Connected .
SQL > SELECT * FROM DEPARTMENT ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
-- 最后删掉私有同义词
SQL > DROP SYNONYM DEPARTMENT ;
Synonym dropped .
6. 创建同义词的详细语法： CREATE SYNONYM
三、总结：
序列
主要用于产生主键值
创建删除时所需的权限
关于 sequence_name . nextval 与 sequence_name . currval 的使用， nextval 优先于 currval 使用
对于不同用户创建的序列，使用时需要带上 schema ，如 scott . seq1 . nextval
对于循环使用序列，当达到最大值后，初始值为
同义词
是 Oracle 对象中的一个同名对象
可以分为公共同义词和私有同义词，两者可同名
创建和删除时所需的权限
对于同义词的访问，需要对原始对象具有适当的权限，否则同义词不可用
四、更多
SQL 基础 --> ROLLUP 与 CUBE 运算符实现数据汇总
SQL 基础 --> 层次化查询 (START BY ... CONNECT BY PRIOR)
五、如转载，请注明出处。
