學習Join View-Key-Preserved Tables以及DML Statements on a join view

Previous Section   < Day Day Up >   Next Section  

3.5 DML Statements on a Join View
A join view is a view based on a join. Special considerations apply when you issue a DML (INSERT, UPDATE, or DELETE) statement against a join view. Ever thought about what happens when you insert a row into a join view—which table does the row go into? And what happens when you delete a row from a join view—from which table is it deleted? This section deals with these questions.

To be modifiable (also referred to as updatable), a join view must not contain any of the following:

Hierarchical query clauses, such as START WITH or CONNECT BY

GROUP BY or ORDER BY clauses

MODEL query

Set operations, such as UNION, UNION ALL, INTERSECT, MINUS

Aggregate functions, such as AVG, COUNT, MAX, MIN, SUM, and so on

Analytical functions, such as CUME_DIST, and so on

A subquery or a collection expression in the SELECT list

The DISTINCT operator

WITH READ ONLY option

The ROWNUM pseudocolumn

A DML statement on a join view can modify only one base table of the view. Thus, to be modifiable, a join view must also preserve a key from at least one of its tables.

3.5.1 Key-Preserved Tables
A key-preserved table is the most important requirement for a join view to be modifiable. In a join, a table is called a key-preserved table if its keys are preserved through the join—every key of the table can also be a key of the resultant join result set. Every primary key or unique key value in the base table must also be unique in the result set of the join. Here's an example that better demonstrates the concept of key preserved tables:

DESC employee

Name                                      Null?    Type

----------------------------------------- -------- -------------

EMP_ID                                    NOT NULL NUMBER(5)

FNAME                                              VARCHAR2(20)

LNAME                                              VARCHAR2(20)

DEPT_ID                                   NOT NULL NUMBER(5)

MANAGER_EMP_ID                                     NUMBER(5)

SALARY                                             NUMBER(5)

HIRE_DATE                                          DATE

JOB_ID                                             NUMBER(3)



DESC retailer

Name                                      Null?    Type

----------------------------------------- -------- --------------

RTLR_NBR                                  NOT NULL NUMBER(6)

NAME                                               VARCHAR2(45)

ADDRESS                                            VARCHAR2(40)

CITY                                               VARCHAR2(30)

STATE                                              VARCHAR2(2)

ZIP_CODE                                           VARCHAR2(9)

AREA_CODE                                          NUMBER(3)

PHONE_NUMBER                                       NUMBER(7)

SALESPERSON_ID                                     NUMBER(4)

CREDIT_LIMIT                                       NUMBER(9,2)

COMMENTS                                           LONG



CREATE OR REPLACE VIEW v_rtlr_emp AS

SELECT c.rtlr_nbr, c.name, c.city, e.emp_id,

       c.salesperson_id, e.lname sales_rep

FROM retailer c JOIN employee e

ON c.salesperson_id = e.emp_id;



View created.



SELECT * FROM v_rtlr_emp;



RTLR_NBR NAME                            CITY            EMP_ID SALES_REP

-------- ------------------------------- --------------- ------ -

     104 EVERY MOUNTAIN                  CUPERTINO         7499 ALLEN

     107 WOMENS SPORTS                   SUNNYVALE         7499 ALLEN

     201 STADIUM SPORTS                  NEW YORK          7499 ALLEN

     203 REBOUND SPORTS                  NEW YORK          7499 ALLEN

     207 FAST BREAK                      CONCORD           7499 ALLEN

     216 THE ALL AMERICAN                CHELSEA           7499 ALLEN

     223 VELO SPORTS                     MALDEN            7499 ALLEN

     227 THE TOUR                        SOMERVILLE        7499 ALLEN

     218 THE OUTFIELD                    FLUSHING          7499 ALLEN

     211 AT BAT                          BROOKLINE         7499 ALLEN

     206 THE COLISEUM                    SCARSDALE         7499 ALLEN

     205 POINT GUARD                     YONKERS           7499 ALLEN

     202 HOOPS                           LEICESTER         7499 ALLEN

     101 TKB SPORT SHOP                  REDWOOD CITY      7521 WARD

     228 FITNESS FIRST                   JACKSON HEIGHTS   7521 WARD

     226 CENTURY SHOP                    HUNTINGTON        7521 WARD

     106 SHAPE UP                        PALO ALTO         7521 WARD

     103 JUST TENNIS                     BURLINGAME        7521 WARD

     102 VOLLYRITE                       BURLINGAME        7654 MARTIN

     208 AL AND BOB'S SPORTS             AUSTIN            7654 MARTIN

     204 THE POWER FORWARD               DALLAS            7654 MARTIN

     215 BOB'S FAMILY SPORTS             HOUSTON           7654 MARTIN

     217 HIT name, THROW addr, AND RUN   GRAPEVINE         7654 MARTIN

     214 AL'S PRO SHOP                   SPRING            7654 MARTIN

     100 JOCKSPORTS                      BELMONT           7844 TURNER

     212 ALL SPORT                       BROOKLYN          7844 TURNER

     221 WHEELS AND DEALS                HOUSTON           7844 TURNER

     224 JOE'S BIKE SHOP                 GRAND PRAIRIE     7844 TURNER

     225 BOB'S SWIM, CYCLE AND RUN       IRVING            7844 TURNER

     222 JUST BIKES                      DALLAS            7844 TURNER

     213 GOOD SPORT                      SUNNYSIDE         7844 TURNER

     105 K + T SPORTS                    SANTA CLARA       7844 TURNER





32 rows selected.


The view v_rtlr_emp is a join of retailer and employee tables on the retailer.salesperson_id and employee.emp_id columns. Is there a key-preserved table in this join view? Which one—or is it both? If you observe the relationship between the two tables and the join query, you will notice that rtlr_nbr is the key of the retailer table, as well as the key of the result of the join. This is because there is only one row in the retailer table for every row in the join view v_rtlr_emp, and every row in the view has a unique rtlr_nbr. Therefore, the table retailer is a key-preserved table in this join view. How about the employee table? The key of the employee table is not preserved through the join because emp_id is not unique in the view, consequently emp_id can't be a key for the result of the join. Therefore, the table employee is not a key-preserved table in this view.

You must remember the following important points regarding key-preserved tables:

Key-preservation is a property of the table inside the join view, not the table itself independently. A table may be key-preserved in one join view, and may not be key-preserved in another join view. For example, if we create a join view by joining the employee table with the department table on the dept_id column, then in the resulting view the employee table will be key-preserved, but the department table will not be a key-preserved table.

It is not necessary for the key column(s) of a table to be SELECTed in the join view for the table to be key-preserved. For example, in the v_rtlr_emp view discussed previously, the retailer table would have been the key-preserved table even if we had not included the rtlr_nbr column in the SELECT list.

On the other hand, if you select the key column(s) of a table in the view definition, your doing so doesn't make that table key-preserved. In the v_rtlr_emp view, even though we have included emp_id in the SELECT list, the employee table is not key-preserved.

The key-preserved property of a table in a join view doesn't depend on the data inside the table. It depends on the schema design and the relationship between the tables.

A join view may SELECT data from many tables. However, any DML operation can modify the data from only one underlying table. The following sections discuss how you can use INSERT, UPDATE, and DELETE statements on a join view.

3.5.2 INSERT Statements on a Join View
Let's issue an INSERT statement against the join view v_rtlr_emp, that attempts to insert a record into the retailer table:

INSERT INTO v_rtlr_emp (rtlr_nbr, name, salesperson_id)

VALUES (345, 'X-MART STORES', 7820);



1 row created.


That worked. Now let's try the following INSERT statement, which also supplies a value for a column from the employee table:

INSERT INTO v_rtlr_emp (rtlr_nbr, name, salesperson_id, sales_rep)

VALUES (456, 'LEE PARK RECREATION CENTER', 7599, 'JAMES');

INSERT INTO v_rtlr_emp (rtlr_nbr, name, salesperson_id, sales_rep)

                                                        *

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view


This INSERT statement attempts to insert values into two tables (retailer and employee), which is not allowed. You can't refer to the columns of a non-key-preserved table in an INSERT statement.

3.5.3 DELETE Statements on a Join View
DELETE operations can be performed on a join view if the join view has one and only one key-preserved table. The view v_rtlr_emp discussed previously has only one key-preserved table, retailer; therefore, you can delete from this join view as in the following example:

DELETE FROM v_rtlr_emp

WHERE rtlr_nbr = 214;



1 row deleted.


But wait! The view joined two tables? What row then, did we just delete? The answer is that we deleted a row from the key-preserved table, in this case from the retailer table.

Let's take another example where there is more than one key-preserved table. We will create a join view that involves two key-preserved tables, and then attempt to delete from that view.

CREATE VIEW v_cust_disputed_ord AS

SELECT d.order_nbr, d.cust_nbr, c.cancelled_dt

FROM disputed_orders d JOIN cust_order c

ON d.order_nbr = c.order_nbr;



View created.


In the view v_cust_disputed_ord both the tables are key-preserved, because the key of each of the tables is also a key of the result set of the join. Now try deleting a row from this view:

DELETE FROM v_cust_disputed_ord

WHERE order_nbr = 1003;



1 row deleted.


Since there are two key-preserved tables, which table did the row get deleted from? After querying the individual tables, you will find that a row has been deleted from the disputed_orders table. Why? This is a bit tricky. The rule is that if you attempt to delete a row from a join view having more than one key-preserved table, the row will be deleted from the first table in the join. If you reverse the order of the tables in the join, and then issue a delete, you will find that the row will be deleted from the cust_order table. This is strange, but it's the rule. Keep this unexpected behavior in mind when you write applications that need to delete from a join view with multiple key-preserved tables.

3.5.4 UPDATE Statements on a Join View
An UPDATE operation can be performed on a join view if it attempts to update a column in the key-preserved table. For example:

UPDATE v_rtlr_emp

SET name = 'PRO SPORTS'

WHERE rtlr_nbr = 215;



1 row updated.


This UPDATE is successful since it updated the name column of the retailer table, which is key-preserved. However, the following UPDATE statement will fail because it attempts to modify the sales_rep column that maps to the employee table, which is non-key-preserved:

UPDATE v_rtlr_emp

SET sales_rep = 'ANDREW'

WHERE rtlr_nbr = 214;

SET sales_rep = 'ANDREW'

    *

ERROR at line 2:

ORA-01779: cannot modify a column which maps to a non-key-preserved table


3.5.5 Data Dictionary Views to Find Updatable Columns
Oracle provides the data dictionary view USER_UPDATABLE_COLUMNS that shows all modifiable columns in all tables and views in a user's schema. This can be helpful if you have a view that you wish to update, but aren't sure whether it's updatable. USER_UPDATABLE_COLUMNS has the following definition:

DESC USER_UPDATABLE_COLUMNS



 Name           Null?    Type

 -------------- -------- -------------

 OWNER          NOT NULL VARCHAR2(30)

 TABLE_NAME     NOT NULL VARCHAR2(30)

 COLUMN_NAME    NOT NULL VARCHAR2(30)

 UPDATABLE               VARCHAR2(3)

 INSERTABLE              VARCHAR2(3)

 DELETABLE               VARCHAR2(3)


 ALL_UPDATABLE_COLUMNS shows modifiable columns from all the views you can access (as opposed to just those you own), and DBA_UPDATABLE_COLUMNS (for DBAs only) shows such columns for all the views in the database.
 
 




The following example shows USER_UPDATABLE_COLUMNS being queried for a list of updatable columns in the v_rtlr_emp view:

SELECT * FROM USER_UPDATABLE_COLUMNS

WHERE TABLE_NAME = 'V_RTLR_EMP';



OWNER   TABLE_NAME    COLUMN_NAME      UPD INS DEL

------- ------------- ---------------- --- ---

DEMO    V_RTLR_EMP    RTLR_NBR         YES YES YES

DEMO    V_RTLR_EMP    NAME             YES YES YES

DEMO    V_RTLR_EMP    CITY             YES YES YES

DEMO    V_RTLR_EMP    EMP_ID           NO  NO  NO

DEMO    V_RTLR_EMP    SALESPERSON_ID   YES YES YES

DEMO    V_RTLR_EMP    SALES_REP        NO  NO  NO


3.5.6 Impact of WITH CHECK OPTION
WITH CHECK OPTION is an optional clause in the CREATE VIEW statement that prevents any changes to the data in the view that could cause rows to be not included in the view. For example, you have a view with the following definition:

CREATE VIEW emp_20 AS

SELECT * FROM employee

WHERE dept_id = 20

WITH CHECK OPTION;


Using this view you can't insert a row that has dept_id = 30, or update the existing rows to have dept_id = 30, as shown in the following example:

INSERT INTO emp_20 VALUES

(8765, 'SANJAY','MISHRA', 30, 7656, 4000, '01-JAN-88', 765);

INSERT INTO emp_20 VALUES

            *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation



UPDATE emp_20 SET dept_id = 30;

UPDATE emp_20 SET dept_id = 30

       *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation


Since the WHERE clause of the view definition restricts the data in the view to dept_id = 20, and the view is defined with the clause WITH CHECK OPTION, you are not allowed to insert or update rows that could cause the rows not to be included in this view.

The purpose of WITH CHECK OPTION is to prevent DML operations as shown in the preceding example. However, this clause has some side effects on the updatability of join views in general.

If a join view is created using the WITH CHECK OPTION clause, INSERT statements are not allowed on the view at all, even if you are attempting to insert into the key-preserved table only. For example:

CREATE VIEW v_rtlr_emp_wco AS

SELECT c.rtlr_nbr, c.name, c.city, c.salesperson_id, e.lname sales_rep

FROM retailer c JOIN employee e

ON c.salesperson_id = e.emp_id

WITH CHECK OPTION;



View created.



INSERT INTO v_rtlr_emp_wco (rtlr_nbr, name, salesperson_id)

VALUES (345, 'X-MART STORES', 7820);

INSERT INTO v_rtlr_emp_wco (rtlr_nbr, name, salesperson_id)

                            *

ERROR at line 1:

ORA-01733: virtual column not allowed here


The error message "ORA-01733: virtual column not allowed here" may not be very comprehensible, but it indicates that you are not allowed to insert into this join view.

WITH CHECK OPTION as such doesn't prevent you from deleting rows from a join view, as shown in the following example:

DELETE FROM v_rtlr_emp_wco

WHERE rtlr_nbr = 215;



1 row deleted.


However, WITH CHECK OPTION prevents deletion if the join view involves a self join of the key-preserved table. For example, the view emp_mgr_wco involves a self join of the table employee (which is the key-preserved table in this view definition).

CREATE VIEW emp_mgr_wco AS

SELECT e.lname employee, e.salary salary, m.lname manager

FROM employee e, employee m

WHERE e.manager_emp_id = m.emp_id

WITH CHECK OPTION;



View created.



DELETE FROM emp_mgr_wco WHERE employee = 'JONES';

DELETE FROM emp_mgr_wco WHERE employee = 'JONES'

            *

ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table


You get an error while trying to delete a row from the view emp_mgr_wco, as it involves a self join of the key-preserved table.

Furthermore, the WITH CHECK OPTION restricts your ability to modify a join view. If a join view is created using the WITH CHECK OPTION clause, you can't modify any of the join columns, nor any of the columns from the tables involved in a self join. The following example illustrates the error you get when trying to update the join column of such a view.

UPDATE v_rtlr_emp_wco

SET salesperson_id = 7784

WHERE rtlr_nbr = 215;

SET salesperson_id = 7784

    *

ERROR at line 2:

ORA-01733: virtual column not allowed here


The error message "ORA-01733: virtual column not allowed here" indicates that you are not allowed to update the indicated column. Since the view is created with the WITH CHECK OPTION clause, and the column salesperson_id is a join column, you are not allowed to update it. You will get a similar error if you try to update a column of a table involved in a self join, as illustrated in the following example:

UPDATE emp_mgr_wco

SET salary = 4800

WHERE employee = 'JONES';

SET salary = 4800

    *

ERROR at line 2:

ORA-01733: virtual column not allowed here


In this example, since the view definition involves a self join of the employee table, and the view is created with the WITH CHECK OPTION clause, you are not allowed to update any columns of the employee table.
 
Previous Section   < Day Day Up >   Next Section  

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