view和synonym都是数据库对象名称,都不对应实际的数据存储,并且都依赖其它的数据库对象,比如table。
但它们的区别是什么呢?
1、view可以对应一张或多张表,synonym只能对应一张表
2、view可以设置其它约束条件
3、可以在view上创建synonym,也可以在synonym上创建view
4、view和synonym都可以屏蔽用户访问其它用户拥有的表
SQL> create or replace view v_test as select * from test;
视图已创建。
SQL> create or replace synonym s_test for test;
同义词已创建。
SQL> select view_name, text from user_views;
VIEW_NAME
------------------------------
TEXT
--------------------------------------------------
V_TEST
select "OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID",
"DATA_OBJECT_ID","OBJECT_TYPE"
SQL> set longchunksize 255;
SQL> select text from user_views;
TEXT
--------------------------------------------------
select "OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID",
"DATA_OBJECT_ID","OBJECT_TYPE"
SQL> set long 2000000000;
SQL> select text from user_views;
TEXT
--------------------------------------------------
select "OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID",
"DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL
_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED
","SECONDARY" from test
SQL> create or replace view v_s_test as select * from s_test;
视图已创建。
SQL> create or replace synonym s_v_test for v_test;
同义词已创建。
--它们的查询结果是一样的,如下:
SQL> select object_name from test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
SQL> select object_name from s_test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
SQL> select object_name from v_test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
SQL> select object_name from s_v_test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
SQL> select object_name from v_s_test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
SQL> desc test;
名称 是否为空? 类型
----------------------- -------- ----------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select object_name, object_type from v_s_test;
OBJECT_NAME OBJECT_TYPE
--------------- -------------------
TEST TABLE
T_PART TABLE PARTITION
IDX_T_PART INDEX
T_PART TABLE
SQL> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------- -------------------
TEST TABLE
T_PART TABLE PARTITION
IDX_T_PART INDEX
T_PART TABLE
V_TEST VIEW
S_TEST SYNONYM
V_S_TEST VIEW
S_V_TEST SYNONYM
已选择8行。
--接下来再看DML操作
SQL> insert into test(object_name) values('T1');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select object_name from test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
T1
SQL> insert into v_test(object_name) values('T2');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into s_test(object_name) values('T3');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select object_name from test;
OBJECT_NAME
---------------
TEST
T_PART
IDX_T_PART
T_PART
T1
T2
T3
已选择7行。
--对单表而言,view和synonym可以进行所有的dml操作。
SQL> create table test2 as select object_name, object_type from test where objec
t_name in ('T1', 'T2', 'T3', 'TEST');
表已创建。
SQL> select * from test2;
OBJECT_NAME OBJECT_TYPE
--------------- -------------------
TEST TABLE
T1
T2
T3
SQL> create or replace view v_test2 as select t.object_name from test t, test2 t
2 where t.object_name = t2.object_name;
视图已创建。
SQL> select * from v_test2;
OBJECT_NAME
---------------
TEST
T1
T2
T3
SQL> insert into v_test2 values('T4');
insert into v_test2 values('T4')
*
第 1 行出现错误:
ORA-01779: 无法修改与非键值保存表对应的列
---无法对多表以上的view做insert操作
--综上:可以对synonym做任何dml操作,包括insert,delete,alter等,而对与多表以上的view只能进行delete、alter,无法insert