SQL Bible [Electronic resources] نسخه متنی

Views

The most common view
definition describes it as a virtual table. Database users can select rows and
columns from a view, join it with other views and tables, limit, sort, group
the query results, and so on. Actually, in most cases, users wouldn't even know
if they were selecting values from a view or from a table. The main difference
is that, unlike tables, views do not take physical disk space. View definitions
are stored in RDBMS as compiled queries that dynamically populate data to be
used as virtual tables for users' requests.

The details are implementation-specific —
RDBMS can create a temporary table behind the scene, populate it with actual
rows, and use it to return results from a user's query. The database engine may
also combine a user's query with an internal view definition (which is, as you
already know, also a query) and execute the resulting query to return data, and
so on — from a user's viewpoint, it does not matter at all.

Views are used in many different ways. For
example, they can combine data from multiple tables in a more user-friendly
form or enforce security rules by making available for users certain horizontal
or vertical slices of data (more about security in
Chapter
12). In this chapter, you'll learn how to create different types of
views.

CREATE VIEW statement

This section explains the
CREATE
VIEW statement for different RDBMS
implementations.

SQL99

Here is the SQL99 syntax for a
CREATE
VIEW statement:

CREATE VIEW <view_name>
[(<column_name>,...)] AS <select_statement> [WITH [CASCADED |
LOCAL] CHECK OPTION]

Column names

The
column_name list is optional in most
cases — if it's skipped, the view columns will be named based on the column
names in the
SELECT statement; it becomes
mandatory though if at least one of the following conditions is true:

Any two columns would otherwise have the same name
(ambiguity problem).

Any column contains a computed value (including
concatenated strings) and the column is not aliased.

SELECT statement and updatable
views

The
select_statement can be virtually any
valid
SELECT statement with some minimal
restrictions. For example, the
ORDER
BY clause cannot be included in view
definition, but
GROUP
BY can be used instead; the view
definition cannot be circular; thus, view cannot be referenced in its own
select_statement clause, and so
on.

Views can be updatable or not
updatable. If a view is updatable, that means you can use its name in DML
statements to actually update, insert, and delete the underlying table's rows.
A view can be updatable only if all these rules hold:

The
select_statement does not contain
any table joins; that is, the view is based on one and only one table or view.
(In the latter case, the underlying view must also be updatable.)

All underlying table's mandatory (NOT
NULL) columns are present in the
view definition.

The underlying query does not contain set operations like
UNION,
EXCEPT, or
INTERSECT; the
DISTINCT keyword is also not
allowed.

No aggregate functions or expressions can be specified in
the
select_statement clause.

The underlying query cannot have a
GROUP
BY clause.

View constraints

SQL99 does not allow creating
explicit constraints on views, but the
CHECK
OPTION can be viewed as some kind of
a constraint. This clause can only be specified for updatable views and
prohibits you from using DML statements on any underlying table's rows that are
not visible through the view. The
CASCADED option (default) means that
if a view is based on another view(s), the underlying view(s) are also checked.
The
LOCAL keyword would only enforce
checking at the level of the view created with this option.

Oracle
9i

Oracle has the following syntax to
create a view:

CREATE [OR
REPLACE] [FORCE] VIEW [<schema>.]<view_name> [(<column_name>
<column_constrnt>,... [<view_level_constrnt>])] AS
<select_statement> [WITH {[READ ONLY | CHECK OPTION [<constrnt
name>]]}];

The pseudocode for
CREATE
OR
REPLACE
view_name is:

IF EXISTS (view_name) THEN
DROP VIEW view_name CREATE VIEW view_name view_definition ... END
IF

For example, this statement creates
view
V_CUSTOMER_STATUS if it does not exist,
or replaces the old definition for
V_CUSTOMER_STATUS if it is present.

CREATE OR REPLACE VIEW
v_customer_status ( name, status ) AS SELECT cust_name_s, cust_status_s FROM
customer;

The view columns are

Name Null? Type
-------------------- -------- ------------ NAME NOT NULL VARCHAR2(50) STATUS
NOT NULL VARCHAR2(1)

In the latter case, you would get an
error if the
OR
REPLACE clause is skipped:

CREATE VIEW v_customer_status
( name, status ) AS SELECT cust_name_s, cust_status_s FROM customer; ORA-00955:
name is already used by an existing object

The column names / constraints clause
is optional:

CREATE OR REPLACE VIEW
v_customer_status AS SELECT cust_name_s, cust_status_s FROM
customer;

Note that in this case Oracle gives
view columns the same names as in underlying table:

Name Null? Type
------------------------- -------- --------------- CUST_NAME_S NOT NULL
VARCHAR2(50) CUST_STATUS_S NOT NULL VARCHAR2(1)

The
WITH
READ
ONLY clause makes the view nonupdatable
even if it satisfies all conditions for updatable views listed previously:

CREATE OR REPLACE VIEW
v_phone_number ( phone_id, phone_number ) AS SELECT phone_id_n,
phone_phonenum_s FROM phone WHERE phone_type_s = 'PHONE' WITH READ ONLY;

UPDATE v_phone_number SET
phone_number = NULL WHERE phone_id = 1; ORA-01733: virtual column not allowed
here

The
WITH
CHECK option is basically the same as
described for SQL99 except
CASCADE/LOCAL keywords are not available (the default behavior is
always
CASCADE):

CREATE OR REPLACE VIEW
v_fax_number ( fax_id, fax_number ) AS SELECT phone_id_n, phone_phonenum_s FROM
phone WHERE phone_type_s = 'FAX' WITH CHECK OPTION;

DB2 UDB 8.1

To create a view in DB2, use this
syntax:

CREATE VIEW
[<schema>.]<view_name> [(<column_name>,...)] AS
{<select_statement> | <values_statement>} [WITH [CASCADED | LOCAL]
CHECK OPTION]

The only clause in DB2's
CREATE
VIEW statement that does not look
familiar from the SQL99 standards point of view is the
values_statement, which allows for
creating a view that does not refer to an actual table, but rather contains its
own list of values:

CREATE
VIEW v_exchange_rate ( currency_name, exchange_rate, converted_price ) AS
VALUES ('Canadian Dollars', CAST (0.6331458594 AS DECIMAL(20,10)), NULL),
('Euro', CAST (0.9761179317 AS DECIMAL(20,10)), NULL), ('Japanese Yen', CAST
(0.0083339039 AS DECIMAL(20,10)), NULL) 

db2 => SELECT * FROM
v_exchange_rate CURRENCY_NAME EXCHANGE_RATE CONVERTED_PRICE ----------------
-------------- --------------- Canadian Dollars 0.6331458594 0 Euro
0.9761179317 0 Japanese Yen 0.0083339039 0 3 record(s)
selected.

The view created in the foregoing
example can be used in a way similar to how temporary tables are used — for
example, the
CONVERTED_PRICE column can be populated
dynamically based on data from other tables or views.

MS SQL Server 2000

MS SQL 2000 syntax is

CREATE VIEW
[[<database_name>.]<owner>.]<view_name>
[(<column_name>,...)] [WITH {ENCRYPTION | SCHEMABINDING |
VIEW_METADATA,...}] AS select_statement [WITH CHECK OPTION]

MS SQL provides some additional
options with the
CREATE
VIEW statement.

The
WITH
ENCRYPTION clause gives you the ability
to encrypt the system table columns containing the text of the
CREATE
VIEW statement. The feature can be
used, for example, to hide proprietary code:

CREATE VIEW v_phone_number (
phone_id, phone_number ) WITH ENCRYPTION AS SELECT phone_id_n, phone_phonenum_s
FROM phone WHERE phone_type_s = 'PHONE' WITH CHECK OPTION

The
WITH
SCHEMABINDING clause binds the view to
the schema (more about schemas later in this chapter):

CREATE VIEW
dbo.v_phone_number ( phone_id, phone_number ) WITH SCHEMABINDING AS SELECT
phone_id_n, phone_phonenum_s FROM dbo.phone WHERE phone_type_s =
'PHONE'

The
WITH
VIEW_METADATA clause specifies that SQL
Server returns to the calling application that uses OLE DB, ODBC, or DBLIB
information about the view rather than about underlying tables.

Creating complex views

We already
mentioned that you can create a view based on practically any
SELECT statement (with some insignificant
limitations). The
SELECT statement itself is one of the
most difficult SQL topics and will be covered in detail in later chapters.
Examples below are to illustrate the main concepts used when creating a complex
view.

We mentioned before that Oracle's
OR
REPLACE clause can be a very useful
feature. MS SQL Server does not have it, but it can easily be simulated using
this syntax:

IF EXISTS ( SELECT
table_name FROM information_schema.views WHERE table_name = 'V_CUSTOMER_STATUS'
) DROP VIEW V_CUSTOMER_STATUS GO CREATE VIEW v_customer_status ( name, status )
AS SELECT cust_name_s, cust_status_s FROM customer

This example uses the MS SQL Server
built-in procedural language Transact SQL that is widely used by MS SQL Server
developers but is not a part of standard SQL (which is nonprocedural by
definition).

Another option (for all "big three"
databases) is to use
ALTER
VIEW statement described in
Chapter
5.

Join view with GROUP BY clause
and aggregate function

V_CUSTOMER_TOTALS displays the total calculated order
price grouped by the
CUSTOMER_NAME and then by
ORDER_NUMBER fields:

CREATE
VIEW v_customer_totals ( customer_name, order_number, total_price ) AS ( SELECT
customer.cust_name_s, order_header.ordhdr_nbr_s, sum(product.prod_price_n *
order_line.ordline_ordqty_n) FROM customer, order_header, order_line,
product WHERE customer.cust_id_n = order_header.ordhdr_custid_fn AND
order_header.ordhdr_id_n = order_line.ordline_ordhdrid_fn AND product.prod_id_n
= order_line.ordline_prodid_fn AND order_line.ordline_ordqty_n IS NOT NULL
GROUP BY customer.cust_name_s, order_header.ordhdr_nbr_s )

View based on another view
example

The
V_CUSTOMER_TOTALS_OVER_15000 view
displays the same data as its underlying view
V_CUSTOMER_TOTALS but only for orders
with a total price over $15,000:

CREATE VIEW
v_customer_totals_over_15000 AS SELECT * FROM v_customer_totals WHERE
total_price > 15000

View with UNION example

The
V_CONTACT_LIST view displays the
combined list of customers and salesmen with their phone numbers and contact
types (customer or salesman):

CREATE VIEW v_contact_list (
name, phone_number, contact_type ) AS SELECT cust_name_s, phone_phonenum_s,
'customer' FROM customer, phone WHERE cust_id_n = phone_custid_fn AND
phone_type_s = 'PHONE' UNION SELECT salesman_name_s, phone_phonenum_s,
'salesperson' FROM salesman, phone WHERE salesman_id_n = phone_salesmanid_fn
AND phone_type_s = 'PHONE'

View with subquery

V_WILE_BESS_ORDERS displays orders for customer
WILE
BESS
COMPANY:

CREATE VIEW
v_wile_bess_orders ( order_number, order_date ) AS SELECT ordhdr_nbr_s,
ordhdr_orderdate_d FROM order_header WHERE ordhdr_custid_fn IN ( SELECT
cust_id_n FROM customer WHERE cust_name_s = 'WILE BESS COMPANY'
)

The foregoing examples will work in
all our three RDBMS. Some of the
SELECT statements used to create the
views will be covered in
Chapters
8 and
9.