Question

怎么判断 view table synonym

Answer 1

区别是什么呢？

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、