7 Views

How do I…

7.1 Create a view?

7.2 Determine all of the views owned by a particular user?

7.3 Determine if a view can be updated?

7.4 Create an updatable join view?

7.5 Simulate a cross-tab query using a view?

7.6 Re-create CREATE VIEW statements?

7.7 Implement record level security using views?

A view is a logical table based on a query. Views themselves do not store data, but present data from other sources, such as tables and other views. To an application, a view looks and acts like a table. Data can be queried from a view, and in many cases, data can be inserted, updated, and deleted through a view. Views can be used to present data in a different format than how it is actually stored in the underlying tables. Views simplify the presentation of data by hiding the actual structure of the base tables, and can secure data at the row level by presenting a restricted subset of the underlying table to the user.

7.1 Create a View

A view can be created for several reasons. Views can be used to represent queries on tables or other views in a virtual table. Views are used mostly to make a subset of data, or to join several tables, all invisible to the user accounts. This is useful for increased data security and simplifying code. This How-To shows the method for creating views.

7.2 Determine All of the Views Owned by a Particular User

The views available to a user can be queried from the data dictionary through the USER_VIEWS and ALL_VIEWS data dictionary views. Because it is possible to have many views available, an easy method to determine the views available is presented in this How-To.

7.3 Determine if a View Can Be Updated

A view’s data can be manipulated if the structure of the view meets certain criteria. A view cannot be updated if it joins tables, uses a DISTINCT operator, or contains a GROUP BY clause or group functions. This How-To presents SQL and PL/SQL statements to quickly determine if a view is updatable, without analyzing the structure of the view.

7.4 Create an Updatable Join View

Having the ability to update a view based upon more than one table allows for great flexibility in developing applications. If a table in a join view is key-preserved, it may be updated, depending on additional criteria. The ability to update object views are also available. This How-To describes the conditions that join views may be updatable, and steps through creating an updatable join view.

7.5 Simulate a Cross-Tab Query Using a View

A cross-tab query in Microsoft Access presents data from multiple rows on a single line. This type of query is useful for presenting data, such as monthly summary data, in a spreadsheet type format. This How-To presents a method to create a view that displays multiple records as a single record.

7.6 Re-Create CREATE VIEW Statements

It is often necessary to re-create the CREATE VIEW statements that were used to create views. Throughout the book, SQL*Plus and the data dictionary are used to re-create DDL statements with a complex query of the data dictionary. This How-To presents the queries used to rebuild CREATE VIEW statements from the data dictionary.

7.7 Implement Record Level Security Using Views

One of the most common uses of views is to restrict access to data from users. Users can have different views of the same data. A subset of the data in the base table can be shown by including a WHERE clause in the view, which restricts data that is based on the user account’s name or privileges. This How-To presents methods for restricting access to data on the record level using views.