ORACLE数据库对象——同义词、序列、视图

ORACLE数据库对象

——同义词、序列、视图

同义词：同义词是现有对象的别名

简化SQL语句

隐藏对象的名称和所有者

提供对对象的公共访问

 

同义词分为私有同义词和公有同义词

私有同义词只能在其模式内访问，且不能与当前模式的对象同名。

公有同义词可被所有的数据库用户访问。

 

以 SYS 用户登录

SQL> CONNECT SYS/SYS AS SYSDBA;

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

Connected as SYS

 

创建私有同义词

SQL> CREATE SYNONYM synemp FOR SCOTT.emp;

 

Synonym created

 

SQL> select * from synemp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

 7839 KING       PRESIDENT       1981-11-17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20

 7900 JAMES      CLERK      7698 1981-12-3      950.00               30

 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20

 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

 

14 rows selected

 

查询创建的同义词

SQL> select * from user_synonyms;   SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- DEF$_AQCALL                    SYSTEM                         DEF$_AQCALL                    DEF$_CALLDEST                  SYSTEM                         DEF$_CALLDEST                  DEF$_DEFAULTDEST               SYSTEM                         DEF$_DEFAULTDEST               DEF$_ERROR                     SYSTEM                         DEF$_ERROR                     DEF$_LOB                       SYSTEM                         DEF$_LOB                        DEF$_SCHEDULE                  SYSTEM                         DEF$_SCHEDULE                  EMP                            SCOTT                          EMP                              7 rows selected

SQL> select OBJECT_NAME,OBJECT_TYPE from user_objects where object_type='SYNONYM';   OBJECT_NAME                                                                     OBJECT_TYPE ----------------------------------------------------------------                ------------ DEF$_AQCALL                                                                      SYNONYM DEF$_CALLDEST                                                                    SYNONYM DEF$_DEFAULTDEST                                                                 SYNONYM DEF$_ERROR                                                                       SYNONYM DEF$_LOB                                                                         SYNONYM DEF$_SCHEDULE                                                                    SYNONYM EMP                                                                              SYNONYM   7 rows selected

SQL> select * from dict where instr(comments,'SYNONYM')>0;   TABLE_NAME                     COMMENTS -----------------     ---------------------------------------------------------------------- SYN                            Synonym for USER_SYNONYMS   SQL> select * from SYN;   SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- DEF$_AQCALL                    SYSTEM                         DEF$_AQCALL                    DEF$_CALLDEST                  SYSTEM                         DEF$_CALLDEST                  DEF$_DEFAULTDEST               SYSTEM                         DEF$_DEFAULTDEST               DEF$_ERROR                     SYSTEM                         DEF$_ERROR                     DEF$_LOB                       SYSTEM                         DEF$_LOB                       DEF$_SCHEDULE                  SYSTEM                         DEF$_SCHEDULE                  EMP                            SCOTT                          EMP                              7 rows selected

SQL> select * from dict where instr(comments,'synonym')>0;   TABLE_NAME                     COMMENTS -----------------     ---------------------------------------------------------------------- ALL_CATALOG                    All tables, views, synonyms, sequences accessible to the user ALL_POLICY_CONTEXTS            All policy driving context defined for all synonyms, tables, or views accessable ALL_POLICY_GROUPS              All policy groups defined for any synonym, table or view accessable to the user ALL_SYNONYMS                   All synonyms accessible to the user DBA_AUDIT_OBJECT               Audit trail records for statements concerning objects, specifically: table, clus DBA_POLICY_CONTEXTS            All policy driving context defined for any synonym, table, or view in the databa DBA_POLICY_GROUPS              All policy groups defined for any synonym, table, view in the database DBA_SYNONYMS                   All synonyms in the database USER_AUDIT_OBJECT              Audit trail records for statements concerning objects, specifically: table, clus USER_POLICIES                  All row level security policies for synonyms, tables, or views owned by the user USER_POLICY_CONTEXTS           All policy driving context defined for synonyms, tables, or views in current sch USER_POLICY_GROUPS             All policy groups defined for any synonym, table, or view USER_SYNONYMS                  The user's private synonyms   13 rows selected   SQL> select * from USER_SYNONYMS;   SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- DEF$_AQCALL                    SYSTEM                         DEF$_AQCALL                    DEF$_CALLDEST                  SYSTEM                         DEF$_CALLDEST                  DEF$_DEFAULTDEST               SYSTEM                         DEF$_DEFAULTDEST               DEF$_ERROR                     SYSTEM                         DEF$_ERROR                     DEF$_LOB                       SYSTEM                         DEF$_LOB                        DEF$_SCHEDULE                  SYSTEM                         DEF$_SCHEDULE                  EMP                            SCOTT                          EMP                              7 rows selected

 

 

创建公有同义词

SQL> CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;

 

Synonym created

 

查询公有同义词

 

SQL> select * from ALL_SYNONYMS where SYNONYM_NAME='emp_syn';

 

OWNER        SYNONYM_NAME        TABLE_OWNER          TABLE_NAME           DB_LINK

----------  -------------------  -------------------  -------------------  --------

 

SQL> select * from ALL_SYNONYMS where SYNONYM_NAME='EMP_SYN';

 

OWNER        SYNONYM_NAME        TABLE_OWNER          TABLE_NAME           DB_LINK

----------   ------------------  -------------------  -------------------  --------

PUBLIC       EMP_SYN             SCOTT                EMP                          

 

SQL> select * from all_objects where OBJECT_NAME='emp_syn';

 

OWNER    OBJECT_NAME   SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE

------   -----------   --------------   ---------   --------------   -----------

 

CREATED       LAST_DDL_TIME   TIMESTAMP            STATUS   TEMPORARY   GENERATED

-----------   -------------   ------------------   ------   ---------   ---------

 

SQL> select * from all_objects where OBJECT_NAME='EMP_SYN';

 

OWNER    OBJECT_NAME   SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE

------   -----------   --------------   ---------   --------------   -----------

PUBLIC   EMP_SYN                        30308                        SYNONYM

 

CREATED       LAST_DDL_TIME   TIMESTAMP            STATUS   TEMPORARY   GENERATED

-----------   -------------   ------------------   ------   ---------   ---------

2008-8-19 0   2008-8-19 0:0   2008-8-19:00:09:07   VALID    N           N

 

创建或替换现有的同义词

CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;

删除同义词

DROP SYNONYM emp;

DROP PUBLIC SYNONYM emp_syn;

 

 

序列：用于生成唯一、连续序号的对象；序列可以是升序的，也可以是降序的

CREATE SEQUENCE emp_seq

   START WITH 10       指定第一个序号从10开始

   INCREMENT BY 10     指定序号之间的间隔为10

   MAXVALUE 2000       表示序列的最大值为2000

   MINVALUE 10         表示序列的最小值为10

   NOCYCLE             达到最大值后停止生成下一个值

   CACHE 10;           指定内存中预先分配的序号数

START WITH 默认值1

INCREMENT BY 默认值1

CACHE 默认值20

   如果指定循环CYCLE，必须指定MINVALUE 与START WITH 一致 且必须指定MAXVALUE；一般情况MAXVALUE  、MINVALUE、CYCLE同时使用才具有意义，否则无意义。

 

 

 

CREATE SEQUENCE emp_seq      START WITH 8000      INCREMENT BY 100      MAXVALUE 8200      MINVALUE 8000      CYCLE      CACHE 10   ORA-04013: CACHE 值必须小于 CYCLE 值

CACHE值必须小于CYCLE 值

CREATE SEQUENCE emp_seq      START WITH 8000      INCREMENT BY 100      MAXVALUE 8200      MINVALUE 8000      CYCLE      CACHE 10   Sequence created

通过序列的伪列来访问序列的值

SQL> SELECT emp_seq.CURRVAL FROM dual;   SELECT emp_seq.CURRVAL FROM dual   ORA-08002: 序列EMP_SEQ.CURRVAL 尚未在此进程中定义

   CURRVAL必须在执行过最少一次NEXTVAL后调用

SQL> SELECT emp_seq.NEXTVAL FROM dual;     第一次执行NEXTVAL      NEXTVAL ----------       8000   SQL> SELECT emp_seq.CURRVAL FROM dual;      CURRVAL ----------       8000

 

 

SQL> INSERT INTO EMP(EMPNO,ENAME) VALUES(emp_seq.NEXTVAL,'HUANGPei');   1 row inserted

SQL> SELECT emp_seq.CURRVAL FROM dual;      CURRVAL ----------       8100   SQL> SELECT emp_seq.NEXTVAL FROM dual;      NEXTVAL ----------       8200

 

 

 

SQL> SELECT * FROM EMP;   EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  8100 HUANGPei                                                     15 rows selected

 

 

 

 

使用ALTER SEQUENCE语句修改序列， 不能更改序列的START WITH参数 ALTER SEQUENCE emp_seq MAXVALUE 5000 CYCLE; 使用DROP SEQUENCE语句删除序列 DROP SEQUENCE emp_seq;

 

 

视图：以经过定制的方式显示来自一个或多个表的数据

视图可以视为“虚拟表”或“存储的查询”

创建视图所依据的表称为“基表”

视图的优点有：

提供了另外一种级别的表安全性

隐藏的数据的复杂性

简化的用户的SQL命令

隔离基表结构的改变

通过重命名列，从另一个角度提供数据

 

 

SQL> CREATE VIEW emp_view   2  AS   3  SELECT * FROM EMP;   View created   SQL> SELECT * FROM emp_view;   EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  8100 HUANGPei                                                     15 rows selected

SQL> CREATE OR REPLACE VIEW emp_view   2  AS   3  SELECT EMPNO,ENAME,SAL FROM EMP;   View created   SQL> SELECT * FROM emp_view;   EMPNO ENAME            SAL ----- ---------- ---------  7369 SMITH         800.00  7499 ALLEN        1600.00  7521 WARD         1250.00  7566 JONES        2975.00  7654 MARTIN       1250.00  7698 BLAKE        2850.00  7782 CLARK        2450.00  7788 SCOTT        3000.00  7839 KING         5000.00  7844 TURNER       1500.00  7876 ADAMS        1100.00  7900 JAMES         950.00  7902 FORD         3000.00  7934 MILLER       1300.00  8100 HUANGPei     15 rows selected

 

 

 

SQL> CREATE VIEW error_view   2  AS   3  SELECT * FROM HP;   CREATE VIEW error_view AS SELECT * FROM HP   ORA-00942: 表或视图不存在

SQL> CREATE FORCE VIEW error_view   2  AS   3  SELECT * FROM HP;   Warning: View created with compilation errors   SQL> CREATE TABLE HP   2  AS   3  SELECT EMPNO,ENAME FROM EMP;   Table created   SQL> SELECT * FROM error_view;   EMPNO ENAME ----- ----------  7369 SMITH  7499 ALLEN  7521 WARD  7566 JONES  7654 MARTIN  7698 BLAKE  7782 CLARK  7788 SCOTT  7839 KING  7844 TURNER  7876 ADAMS  7900 JAMES  7902 FORD  7934 MILLER  8100 HUANGPei   15 rows selected

SQL> CREATE VIEW emp_sal_view   2  AS   3  SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=3000;   View created   SQL> SELECT * FROM emp_sal_view;   EMPNO ENAME            SAL ----- ---------- ---------  7788 SCOTT        3000.00  7839 KING         5000.00  7902 FORD         3000.00   SQL> UPDATE emp_sal_view SET SAL=2000 WHERE EMPNO=7788;   1 row updated   SQL> SELECT * FROM emp_sal_view;   EMPNO ENAME            SAL ----- ---------- ---------  7839 KING         5000.00  7902 FORD         3000.00

SQL>  CREATE OR REPLACE VIEW emp_sal_view   2   AS   3   SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=3000   4   WITH CHECK OPTION;   View created SQL> SELECT * FROM emp_sal_view;   EMPNO ENAME            SAL ----- ---------- ---------  7788 SCOTT        3000.00  7839 KING         5000.00  7902 FORD         3000.00   SQL> UPDATE emp_sal_view SET SAL=2000 WHERE EMPNO=7788;   UPDATE emp_sal_view SET SAL=2000 WHERE EMPNO=7788   ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句

SQL> CREATE OR REPLACE VIEW emp_sal_view   2  AS   3  SELECT * FROM EMP;   View created   SQL> SELECT * FROM emp_sal_view;   EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  8100 HUANGPei                                                     15 rows selected   SQL> UPDATE emp_sal_view SET SAL=20000 WHERE EMPNO=8100;   1 row updated   SQL> SELECT * FROM emp_sal_view;   EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  8100 HUANGPei                                20000.00             15 rows selected

SQL> CREATE OR REPLACE VIEW emp_sal_view   2  AS   3  SELECT * FROM EMP   4  WITH READ ONLY;   View created   SQL> SELECT * FROM emp_sal_view;   EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20  7839 KING       PRESIDENT       1981-11-17    5000.00               10  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  8100 HUANGPei                                20000.00                    15 rows selected   SQL> UPDATE emp_sal_view SET DEPTNO=10 WHERE EMPNO=8100;   UPDATE emp_sal_view SET DEPTNO=10 WHERE EMPNO=8100   ORA-01733: 此处不允许虚拟列

SQL>  CREATE OR REPLACE VIEW emp_dept_view   2   AS   3   SELECT E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME FROM EMP E,DEPT D   4   WHERE E.DEPTNO=D.DEPTNO;   View created   SQL> SELECT * FROM emp_dept_view;   EMPNO ENAME            SAL DEPTNO DNAME ----- ---------- --------- ------ --------------  7369 SMITH         800.00     20 RESEARCH  7499 ALLEN        1600.00     30 SALES  7521 WARD         1250.00     30 SALES  7566 JONES        2975.00     20 RESEARCH  7654 MARTIN       1250.00     30 SALES  7698 BLAKE        2850.00     30 SALES  7782 CLARK        2450.00     10 ACCOUNTING  7788 SCOTT        3000.00     20 RESEARCH  7839 KING         5000.00     10 ACCOUNTING  7844 TURNER       1500.00     30 SALES  7876 ADAMS        1100.00     20 RESEARCH  7900 JAMES         950.00     30 SALES  7902 FORD         3000.00     20 RESEARCH  7934 MILLER       1300.00     10 ACCOUNTING   14 rows selected   SQL> UPDATE emp_dept_view SET ENAME='HUANGPei' WHERE EMPNO=7369;   1 row updated   SQL> UPDATE emp_dept_view SET DNAME='ACCOUNTING' WHERE EMPNO=7369;   UPDATE emp_dept_view SET DNAME='ACCOUNTING' WHERE EMPNO=7369   ORA-01779: 无法修改与非键值保存表对应的列   SQL> SELECT * FROM emp_dept_view;   EMPNO ENAME            SAL DEPTNO DNAME ----- ---------- --------- ------ --------------  7369 HUANGPei      800.00     20 RESEARCH  7499 ALLEN        1600.00     30 SALES  7521 WARD         1250.00     30 SALES  7566 JONES        2975.00     20 RESEARCH  7654 MARTIN       1250.00     30 SALES  7698 BLAKE        2850.00     30 SALES  7782 CLARK        2450.00     10 ACCOUNTING  7788 SCOTT        3000.00     20 RESEARCH  7839 KING         5000.00     10 ACCOUNTING  7844 TURNER       1500.00     30 SALES  7876 ADAMS        1100.00     20 RESEARCH  7900 JAMES         950.00     30 SALES  7902 FORD         3000.00     20 RESEARCH  7934 MILLER       1300.00     10 ACCOUNTING   14 rows selected

 

在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE 视图上的DML语句有如下限制： 只能修改一个底层的基表 如果修改违反了基表的约束条件，则无法更新视图 如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图 如果视图包含伪列或表达式，则将无法更新视图