Views:Basic Concepts

Views
1.A view is a predefined query on one or more tables.
2.Retrieving information from a view is done in the same manner as retrieving from a table.
3.With some views you can also perform DML operations (delete, insert, update) on the base tables.
4.Views don’t store data, they only access rows in the base tables.
5.user_tables, user_sequences, and user_indexes are all views.
6.View Only allows a user to retrieve data.
7.view can hide the underlying base tables.
8.By writing complex queries as a view, we can hide complexity from an end user.
9.View only allows a user to access certain rows in the base tables.

Advantages of Views

• To restrict data access
• To make complex queries easy
• To provide data independence
• To present different views of the same data

Creating and Using a View

You create a view using CREATE VIEW , which has the following simplified syntax:

    CREATE [OR REPLACE][{FORCE | NOFORCE}] VIEW view_name
    [(alias_name[, alias_name...])] AS subquery
    [WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];

where

1.OR REPLACE specifies the view is to replace an existing view if present.
2.FORCE specifies the view is to be created even if the base tables don’t exist.
3.NOFORCE specifies the view is not to be created if the base tables don’t exist; NOFORCE is the default.
4.alias_name specifies the name of an alias for an expression in the subquery.
5.There must be the same number of aliases as there are expressions in the subquery.
6.subquery specifies the subquery that retrieves from the base tables.
7.If you’ve supplied aliases, you can use those aliases in the list after the SELECT clause.
8.WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
9.By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
10.constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
11.WITH READ ONLY specifies that rows may only read from the base tables.

There are two basic types of views:

Simple views:

*)Derives data from only one table
*)Contains no functions or groups of data
*)Can perform DML operations through the view

Complex views:
*)Derives data from many tables
*)Contains functions or groups of data
*)Does not always allow DML operations through the view

Creating a View with a CHECK OPTION Constraint

You can specify that DML operations on a view must satisfy the subquery by adding a CHECK OPTION constraint to the view.
    CREATE VIEW myView AS
       SELECT *
       FROM employee
       WHERE id  INSERT INTO myView (id) VALUES (0);
   
    1 row created.
   
    INSERT INTO myView (id) VALUES (7);
    INSERT INTO myView (id) VALUES (7)
                *
    ERROR at line 1:
    ORA-01402: view WITH CHECK OPTION where-clause violation

Creating a View with a READ ONLY Constraint

You can make a view read only by adding a READ ONLY constraint to the view.

    CREATE VIEW myView AS
      SELECT *
      FROM employee
      WITH READ ONLY CONSTRAINT my_view_read_only;
   
    View created.
   
    INSERT INTO myView (id) VALUES (1);
    INSERT INTO myView (id) VALUES (1)
                        *
    ERROR at line 1:
    ORA-01733: virtual column not allowed here

Performing an INSERT Using a View

You can only perform DML operations with simple views.Complex views don’t support DML.

    CREATE VIEW employee_view AS
      SELECT id, first_name, last_name
      FROM employee;
   
    View created.
   
    INSERT INTO employee_view (id, first_name, last_name) VALUES (
                                 13, 'New','Western');
   
    1 row created.
   
    1 row is created in the employee table.

Modifying a View

You can completely replace a view using CREATE OR REPLACE. You can alter the constraints on a view using ALTER VIEW.

    CREATE VIEW myView AS
      SELECT *
      FROM employee
      WITH READ ONLY CONSTRAINT my_view_read_only;
   
    View created.
   
    ALTER VIEW myview
    DROP CONSTRAINT my_view_read_only;
   
    View altered.

Create a complex view with group by and having clause

    CREATE VIEW myview AS
      SELECT city, AVG(salary) average_salary
      FROM employee
      GROUP BY city
      HAVING AVG(salary)>50000;

Removing a View

You can remove a view without losing data because a view is based on underlying tables in the database.

DROP VIEW myview;

Inline Views

• An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement.
• A named subquery in the FROM clause of the main query is an example of an inline view.

Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.