Learning Visually with Examples [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Learning Visually with Examples [Electronic resources] - نسخه متنی

Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










7.11. Views


A view is a virtual table derived from one or more tables or other views. It is virtual because it does not contain any data, but a definition of a table based on the result of a

SELECT statement. Figure 7.21 illustrates view

view1 derived from table

table1 .

Figure 7.21. A view derived from a table

A view does not need to contain all the columns of the base table. Its columns do not need to have the same names as the base table, either. This is illustrated in Figure 7.21, where the view consists of only two columns, and the first column of the view has a different name than the corresponding column in the base table. This is particularly useful for hiding confidential information from users.

You can create a view using the

CREATE VIEW statement. For example, to create the view

view1 shown in Figure 7.21, issue this statement.


CREATE VIEW view1 (id, name)

AS SELECT employee_id, name FROM table1

To display the contents of

view1 , use the following statement.


SELECT * FROM view1

You can also create views based on multiple tables. Figure 7.22 shows a view created from two tables.

Figure 7.22. A view derived from two tables

Here is the corresponding

CREATE VIEW statement for Figure 7.22.


CREATE VIEW view2 (id, name, region)

AS SELECT table1.employee_id, table1.name, table2.region

FROM table1,table2

WHERE table1.employee_id = table2.employee_id

With this statement we have combined the information of

table1 and

table2 into

view2 , while limiting access to the salary information.

When you create a view, its definition is stored in the system catalog table SYSCAT.VIEWS. This table contains information about each view such as its name, schema, whether or not it is read-only, and the SQL statement used to create the view. For example, in Figure 7.23 we show part of the information for views

view1 and

view2 in SYSCAT.VIEWS.

Figure 7.23. View definitions stored in the SYSCAT.VIEWS table

[View full size image]

When a view is referenced in a query, DB2 reads and executes the view definition from the SYSCAT.VIEWS table, pulls the data from the base table, and presents it to the users.

To remove a view, use the

DROP VIEW statement. For example, to remove the view

view1 use:


DROP VIEW view1

If any of the base tables or views is dropped, the views that are dependent on them will be marked invalid and the value in the VALID column shown in Figure 7.23 will be set to X instead of Y. When this happens, you will not be able to use these views. This is true even if you have recreated the base table or view afterward.

7.11.1. View Classification


Views are classified by the operations they allow. There are four classes of views:

  • Deleteable views

  • Updatable views

  • Insertable views

  • Read-only views


In the SYSCAT.VIEWS catalog table, when the value of the column READ-ONLY is Y, this indicates that the view is read-only; otherwise, it is either a deleteable, updatable, or insertable view. Figure 7.23 shows

view2 is a read-only view, but

view1 is not.

Figure 7.24 illustrates the relationship between the different types of views. The views are discussed further in the next sections.

Figure 7.24. View classifications and relationships

7.11.1.1 Deleteable Views

A

deleteable view allows you to execute the

DELETE statement against it. All of the following must be true.

  • Each

    FROM clause of the outer fullselect identifies only one base table (with no

    OUTER clause), a deleteable view (with no

    OUTER clause), a deleteable nested table expression, or a deleteable common table expression (cannot identify a NICKNAME used with federated support).

  • The outer fullselect does not include a

    VALUES clause.

  • The outer fullselect does not include a

    GROUP BY clause or a

    HAVING clause.

  • The outer fullselect does not include column functions in the

    SELECT list.

  • The outer fullselect does not include

    SET operations (

    UNION, EXCEPT , or

    INTERSECT ), with the exception of

    UNION ALL .

  • The base tables in the operands of a

    UNION ALL must not be the same table, and each operand must be deleteable.

  • The select list of the outer fullselect does not include

    DISTINCT .


For further detail, refer to the

DB2 UDB SQL Reference manual. In our previous example,

view1 is a deleteable view. However,

view2 is not because it does not follow the first rule. In

view2 's definition, the

SELECT statement contains two base tables in the

FROM clause.

7.11.1.2 Updatable Views

An

updatable view is a special case of a deleteable view. A view is updatable when at least one of its columns is updatable. All of the following must be true.

  • The view is deleteable.

  • The column resolves to a column of a base table (not using a dereference operation), and the

    READ ONLY option is not specified

  • All the corresponding columns of the operands of a

    UNION ALL have exactly matching data types (including length or precision and scale) and matching default values if the fullselect of the view includes a

    UNION ALL .


In our previous example,

view1 is an updatable view. However,

view2 is not because it is not deleteable.

You can update

view1 using the

UPDATE statement, and the changes will be applied to its base table. For example, the following statement changes the value of column

employee_id to 100 for records with the

name value of Mary in

table1 .


UPDATE view1 SET id='100' WHERE name = 'Mary';

7.11.1.3 Insertable Views

An

insertable view allows you to execute the

INSERT statement against it. A view is insertable when all of its columns are updatable. For example,

view1 fits this rule. The following statement will insert a row into

table1 , which is the base table of

view1 .


INSERT INTO view1 VALUES ('200', 'Ben');

Figure 7.25 displays the contents of

table1 after executing the

INSERT statement on

view1 . Note that the

salary and

deptno columns for Ben contain NULL values because these two columns are not contained in

view1 .

Figure 7.25. Contents of

table1 after inserting a row into

view1


[View full size image]

If

table1 were defined such that NULL values were not allowed in one of the

salary or

deptno columns, the

INSERT statement would fail, and

view1 would not be an insertable view.

7.11.1.4 Read-Only Views

A

read-only view is not deleteable. In Figure 7.22, shown earlier,

view2 is a read-only view. Its read-only property is also stored in the SYSCAT.VIEWS table, which is shown in Figure 7.23.

NOTE


Even if a view is read-only,

INSERT, UPDATE , and

DELETE operations are still possible by using an

INSTEAD OF trigger. For more information, see section 7.13, Triggers.

7.11.2. Using the WITH CHECK OPTION


You can define a view to selectively display a subset of rows of its base table by using the

WHERE clause in the

CREATE VIEW statement. To ensure that all the

INSERT and

UPDATE operations conform to the criteria specified in the

WHERE clause of the view, you can use the

WITH CHECK OPTION clause. For example, let's create the view

view3 derived from table

table1 (see Figure 7.22) as follows.


CREATE VIEW view3 (id, name,deptno)

AS SELECT employee_id, name, deptno

FROM table1

WHERE deptno = 101

WITH CHECK OPTION

If you issue a

SELECT * FROM view3 statement, you will obtain the following result:


ID NAME DEPTNO
--- -------------------- -----------
001 John 101
002 Mary 101

Only two rows are retrieved because these are the only rows that satisfy the

WHERE clause. What happens if you issue the following statement?


INSERT INTO view3 VALUES ('007','Shawn',201)

This statement fails because 201 does not conform to the criteria of the

WHERE clause used in the

CREATE VIEW definition, which is enforced because of

WITH CHECK OPTION . If

view3 had not been defined with this clause, the

INSERT statement would have succeeded.

7.11.3. Nested Views


Nested views are ones based on other views, for example:


CREATE VIEW view4

AS SELECT * FROM view3

In this example,

view4 has been created based on

view3 , which was used in earlier examples. The

WITH CHECK OPTION clause specified in

view3 is still in effect for

view4 ; therefore, the following

INSERT statement fails for the same reason it fails when inserting into

view3 .


INSERT INTO view4 VALUES ('007','Shawn',201)

When a view is defined with the

WITH CHECK OPTION clause, the search condition is propagated through all the views that depend on it.


/ 312