创建第一个view;
4.由于cheap_product_view没有使用with check option，所以可以插入、删除、修改那些子查询不能检索的行；例如：cheap_product_view只能查询到价格小于15，插入价格大于15试试；
6. 如果插入视图不包含的基表列，则该列会被设为空值；
SQL> create view cheap_product_view as
2 select * from store.products where price < 15;
View created
SQL> select * from calvin.cheap_product_view where rownum < 4;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
4 2 Tank War Action movie about a future war 13.95
6 2 2412: The Return Aliens return 14.95
7 3 Space Force 9 Adventures of heroes 13.49
SQL> desc store.products;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
PRODUCT_ID INTEGER
PRODUCT_TYPE_ID INTEGER Y
NAME VARCHAR2(30)
DESCRIPTION VARCHAR2(50) Y
PRICE NUMBER(5,2) Y
SQL> desc calvin.cheap_product_view;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
PRODUCT_ID INTEGER
PRODUCT_TYPE_ID INTEGER Y
NAME VARCHAR2(30)
DESCRIPTION VARCHAR2(50) Y
PRICE NUMBER(5,2) Y
创建第二个view;
SQL> create view employees_view as
2 select employee_id, manager_id, first_name, last_name, title
3 from store.employees;
View created
SQL> desc store.employees;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
EMPLOYEE_ID INTEGER
MANAGER_ID INTEGER Y
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
TITLE VARCHAR2(20) Y
SALARY NUMBER(6) Y
SQL> desc calvin.employees_view;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
EMPLOYEE_ID INTEGER
MANAGER_ID INTEGER Y
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
TITLE VARCHAR2(20) Y 3.往简单视图中插入输入，复杂视图不支持DML操作；中间没有权限，需要添加基础表中的insert权限；
SQL> insert into calvin.cheap_product_view ( product_id, product_type_id, name, price ) values (
2 13,1, 'Westerrn Front',13.50);
insert into calvin.cheap_product_view ( product_id, product_type_id, name, price ) values (
13,1, 'Westerrn Front',13.50)
ORA-01031: insufficient privileges
SQL> conn /@test as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as [calvin]@TEST AS SYSDBA
SQL> grant insert on store.products to calvin;
Grant succeeded
SQL> conn calvin/123456@test
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as calvin@TEST
SQL> insert into calvin.cheap_product_view ( product_id, product_type_id, name, price ) values (
2 13,1, 'Westerrn Front',13.50);
1 row inserted
SQL> select * from calvin.cheap_product_view where product_id = 13;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
13 1 Westerrn Front 13.50
SQL> select * from store.products where product_id = 13;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
13 1 Westerrn Front 13.50
SQL> commit;
Commit complete4.由于cheap_product_view没有使用with check option，所以可以插入、删除、修改那些子查询不能检索的行；例如：cheap_product_view只能查询到价格小于15，插入价格大于15试试；
SQL> insert into calvin.cheap_product_view ( product_id, product_type_id, name, price ) values (
2 14,1, 'Eastern Front', 16.50 );
1 row inserted
SQL> select * from store.products where product_id = 14;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
14 1 Eastern Front 16.50
SQL> select * from calvin.cheap_product_view where product_id = 14;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
SQL> commit;
Commit complete5.创建具有check option约束的视图；
SQL> create view calvin.cheap_product_view2 as
2 select * from store.products where price < 15 with check option constraint cheap_product_view2_price;
View created
SQL> insert into calvin.cheap_product_view2 ( product_id, product_type_id, name, price ) values (
2 15,1, 'Eastern Front', 16.50 );
insert into calvin.cheap_product_view2 ( product_id, product_type_id, name, price ) values (
15,1, 'Eastern Front', 16.50 )
ORA-01402: view WITH CHECK OPTION where-clause violation
6. 如果插入视图不包含的基表列，则该列会被设为空值；
SQL> create view employees_view as 2 select employee_id, manager_id, first_name, last_name, title 3 from store.employees; View createdSQL> desc store.employees;Name Type Nullable Default Comments ----------- ------------ -------- ------- -------- EMPLOYEE_ID INTEGER MANAGER_ID INTEGER Y FIRST_NAME VARCHAR2(10) LAST_NAME VARCHAR2(10) TITLE VARCHAR2(20) Y SALARY NUMBER(6) Y SQL> desc calvin.employees_view;Name Type Nullable Default Comments ----------- ------------ -------- ------- -------- EMPLOYEE_ID INTEGER MANAGER_ID INTEGER Y FIRST_NAME VARCHAR2(10) LAST_NAME VARCHAR2(10) TITLE VARCHAR2(20) YSQL> insert into calvin.employees_view ( 2 employee_id, manager_id, first_name, last_name, title 3 ) values ( 4 5,1,'Jeff','Jones','CTO') 5 / 1 row inserted SQL> select * from store.employees where employee_id = 5; EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY --------------------------------------- --------------------------------------- ---------- ---------- -------------------- ------- 5 1 Jeff Jones CTO7.创建只读read only 视图；
SQL> create view calvin.cheap_product_view3 as 2 select * from store.products where price < 15 3 with read only constraint cheap_product_view3_read_only; View created SQL> insert into calvin.cheap_product_view3( 2 product_id, PRODUCT_TYPE_ID, name, price ) values ( 16,1,'N Front', 19.50); insert into calvin.cheap_product_view3( product_id, PRODUCT_TYPE_ID, name, price ) values ( 16,1,'N Front', 19.50) ORA-42399: cannot perform a DML operation on a read-only view
8,，关于视图的信息；SQL> select * from user_views; VIEW_NAME TEXT_LENGTH TEXT TYPE_TEXT_LENGTH TYPE_TEXT OID_TEXT_LENGTH OID_TEXT VIEW_TYPE_OWNER VIEW_TYPE SUPERVIEW_NAME EDITIONING_VIEW READ_ONLY ------------------------------ ----------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- --------- CHEAP_PRODUCT_VIEW 104 select "PRODUCT_ID","PRODUCT_TYPE_ID","NAME","DESCRIPTION","PRICE" from store.pr N N CHEAP_PRODUCT_VIEW2 122 select "PRODUCT_ID","PRODUCT_TYPE_ID","NAME","DESCRIPTION","PRICE" from store.pr N N CHEAP_PRODUCT_VIEW3 119 select "PRODUCT_ID","PRODUCT_TYPE_ID","NAME","DESCRIPTION","PRICE" from store.pr N Y EMPLOYEES_VIEW 82 select employee_id, manager_id, first_name, last_name, title N N from store.employe
9.create or reaplace 可以彻底替换视图，alter view可以修改视图的约束；drop view 删除视图；
SQL> create view product_average_view as 2 select product_type_id, avg(price) average_price 3 from store.products 4 where price < 15 5 group by product_type_id 6 having avg(price) > 13; View created SQL> select * from calvin.product_average_view; PRODUCT_TYPE_ID AVERAGE_PRICE --------------------------------------- ------------- 13.49 1 13.5 2 14.45 3 13.24
SQL> alter view calvin.cheap_product_view2 drop constraint cheap_product_view2_price; View altered
SQL> drop view calvin.cheap_product_view2; View dropped
2139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
