oracle10g上机练习4

SQL> CREATE OR REPLACE VIEW MY VIEW
AS
SELECT * FROM BOOKS;
SQL> INSERT INTO MYVIEW(BOOKS_ID)VALUES('0008');/**其实是插入到了基表中**/

SQL> CREATE OR REPLACE VIEW MY VIEW
AS
SELECT * FROM BOOKS WHERE PRICE > 30;
SQL> INSERT INTO MYVIEW VALUES('0009','abcdef',12,5,'aa');/**可以插入：其中12对应基表的price字段**/
SQL> SELECT * FROM BOOKS；/**但是查不出来*/

--那么怎么限制插入呢？
SQL> CREATE OR REPLACE VIEW MYVIEW
AS
SELECT * FROM BOOKS WHERE PRICE > 30
WITH CHECK OPTION;
SQL> INSERT INTO MYVIEW VALUES('0010','abcdef',25,5,'BB');/**不可以插入：其中25<30**/
SQL> INSERT INTO MYVIEW VALUES('0011','abcdef',35,5,'CC');/**可以插入：其中35>30**/

SQL> CREATE CREATE OR REPLACE VIEW V_EMP_DEPT
AS
SELECT E.EID,http://doc.guandang.net,E.SEX,ED.ID,http://doc.guandang.net FROM EEMP E,DEPTMENT P WHERE E.ID = D.ID;
SQL> INSERT INTO V_EMP_DEPT VALUES('007','ABC','33','TT');/**有错：不能对两个或两个以上进行插入或更新，但是可以用替代触发器实现**/


SQL> CREATE CREATE OR REPLACE VIEW V_READ
AS
SELECT E.EID,http://doc.guandang.net FROM EEMP E
WITH READ ONLY;
SQL> DELETE FROM V_READ WHERE E_ID = '005';/*不能删除**/

SQL> DESC dba_view
SQL> DESC USER_view
SQL> SELECT TEXT FROM USER_VIEW WHERE VIEW_NAME = 'V_READ';/*可以查看视图语法信息*/

--同义词：简化
开发模式
SQL> CREATE SYNONYM DEPT FROM SCOTT.DEPT;/*私有*/
SQL> CREATE PUBLIC SYNONYM DEPT FROM SCOTT.DEPT;/*公有*/
SQL> DESC DBA_SYNONYMS