SQL基础--> 序列(SEQUENCE)、同义词(SYNONYM)

--=============================================

--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对象中的一个同名对象

可以分为公共同义词和私有同义词，两者可同名

创建和删除时所需的权限

对于同义词的访问，需要对原始对象具有适当的权限，否则同义词不可用

四、更多

Oracle 用户、对象权限、系统权限

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

SQL 基础--> 视图(CREATE VIEW)

Oracle 常用目录结构(10g)

五、如转载，请注明出处。