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.

  Cross-References 

  The SELECT statement is covered in detail in Chapters 8 and 9.

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>]]}];

Note 

The OR REPLACE clause (often used when creating many Oracle objects — stored procedures, functions, packages, etc.) basically tells RDBMS to drop the view if it already exists, and then re-create it with the new syntax. This is a very practical feature, but it has to be used with care — if you already have a view with the exact same name, Oracle will just replace the old object definition with the new one without giving you any warning.

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)

Note 

Oracle 9i allows you to specify integrity constraints on the view column or view as a whole, but does not enforce those constraints, so they are declarative only.

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.

Note 

DB2 does not perform implicit data type conversion, so we have to use functions CAST and INTEGER in the foregoing example. More about conversion functions in Chapter 10.

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.

Cross-References 

OLE DB, ODBC, DBLIB programming interfaces (API) are covered in more detail in Chapter 16.

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.

Simulating OR REPLACE Clause in MS SQL Server

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' 

Cross-References 

UNION is one of the set operators used to combine the results of two or more SQL queries. The theoretical aspect of the set operators is covered in detail in Appendix L; the practical part is discussed in Chapter 7.

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.