DML Views

1  view of insertable,updable,deletable

SQL> CREATE VIEW V4(or_no, or_date, cust_id)
  2  AS SELECT order_id, order_date, customer_id
  3  FROM orders
  4  WHERE order_date < '30-mar-2007' WITH CHECK OPTION;

檢視已建立。

SQL> select text from user_views where view_name='V4';

TEXT
------------------------------------------------------------
SELECT order_id, order_date, customer_id
FROM orders
WHERE order_date < '30-mar-

SQL>  select column_name,insertable,updatable,deletable
  2   from user_updatable_columns
  3   where column_name in ('OR_NO','OR_DATE','CUST_ID')
  4  ;

COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
OR_NO                          YES YES YES
OR_DATE                        YES YES YES
CUST_ID                        YES YES YES

SQL> select column_name,insertable,updatable,deletable
  2  from user_updatable_columns
  3  where table_name IN('ORDERS','ORDER_ITEMS')
  4  AND column_name in ('ORDER_ID','LINE_ITEM_ID')
  5  /

COLUMN_NAME                    INS UPD DEL
------------------------------ --- --- ---
ORDER_ID                       YES YES YES
ORDER_ID                       YES YES YES
LINE_ITEM_ID                   YES YES YES

----

USER_UPDATABLE_COLUMNS describes columns in a join view that can be updated by the current user, subject to appropriate privileges. Its columns are the same as those in "ALL_UPDATABLE_COLUMNS".

ALL_UPDATABLE_COLUMNS describes all columns in a join view that are updatable by the current user, subject to appropriate privileges.

DBA_UPDATABLE_COLUMNS describes all columns in a join view that are updatable by the database administrator, subject to appropriate privileges.

2  Dml of Object Views

A view is not updatable if its view query contains joins, set operators, rownumthe.columns defined by  expression,aggregate functions, GROUP BY, or DISTINCT. If a view query contains pseudocolumns or expressions, the corresponding view columns are not updatable. Object views often involve joins.

---end---

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-718882/，如需轉載，請註明出處，否則將追究法律責任。