A basic view is really a SQL statement stored in the database as an object. Conceptually, when you select from a view, Oracle looks up the view definition in the data dictionary, executes the query the view is based on, and returns the results.

In addition to selecting from a view, in some scenarios it is possible to execute INSERT, UPDATE, and DELETE statements against the view, which results in modifications to the underlying table data. So, in this sense, instead of simply describing a view as a stored SQL statement, it is more accurate to conceptualize a view as a logical table built on other tables or views, or both.

Here are some common uses for views:

•     Create an efficient method of storing a SQL query for reuse

•     Provide an interface layer between an application and physical tables

•     Hide the complexity of a SQL query from an application

•     Report to a user only a subset of columns or rows, or both

You should be able to start to see how useful views are for application APIs and how they can provide the needed data sets to applications and users.

Creating a View

You can create views on tables, materialized views, or other views. To create a view, your user account must have the CREATE VIEW system privilege. If you want to create a view in another user’s schema, then you must have the CREATE ANY VIEW privilege.

For reference, the view creation example in this section depends on the following base table:

SQL> create table sales ( sales_id number primary key , amnt number, state varchar2(2), sales_person_id number); Also assume that the table has the following data initially inserted into it:

SQL> insert into sales values(1, 222, ‘CO’, 8773), (20,827, ‘FL’, 9222); The CREATE VIEW statement is used to create a view.

The following code creates a view (or replaces it if the view already exists) that selects a subset of columns and rows from the SALES table:

SQL> create or replace view sales_rockies as select sales_id, amnt, state from sales where state in (‘CO’,’UT’,’WY’,’ID’,’AZ’); CREATE OR REPLACE VIEW is useful to modify a view if it exists or create a new view without having to verify if it already exists. If you don’t want to overwrite existing views, then use CREATE VIEW statements.

When you select from SALES_ROCKIES, it executes the view query and returns data from the SALES table as appropriate:

SQL> select * from sales_rockies; Given the view query, it is intuitive that the output shows only the following columns and one row:

SALES_ID    AMNT                    ST ———- ———- –1              222                    CO

What is not as apparent is that you can also issue UPDATE, INSERT, and DELETE statements against a view, which results in modification of the underlying table data. For example, the following insert statement against the view results in the insertion of a record in the SALES table:

SQL> insert into sales_rockies ( sales_id, amnt, state) Values (2, 100, ‘CO’);

Additionally, as the owner of the table and view (or as a DBA), you can grant DML privileges to other users on the view.

For instance, you can grant SELECT, INSERT, UPDATE, and DELETE privileges on the view to another user, which will allow the user to select and modify data referencing the view to another user, which will allow the user to select and modify data referencing the view. However, having privileges on the view does not give the user direct SQL access to the underlying tables.

Any users granted privileges on the view will be able to manipulate data through the view but not issue SQL against the object the view is based on.

If you create the view using the WITH READ ONLY clause, users cannot perform INSERT, UPDATE, or DELETE operations on the view.

This is useful if you use views for reporting and never intend for the view to be used as a mechanism for modifying the underlying table’s data; then you should always create the views with the WITH READ ONLY clause. Doing so prevents accidental modifications to the underlying tables through a view that was never intended to be used to modify data.