Views.

Unlike tables a view are logical representation of data from one ore more tables. We can think of a view as a query stored in a database.

Creating views.

`CREATE VIEW` statement.

To create a view the CREATE VIEW statement is used. The general syntax of the command is

CREATE [OR REPLACE] [ [NO] FORCE] VIEW [schema.]view_name
  [ ( list_of_column_descriptions ) ]
  AS
  select_statement
  [ WITH { READ ONLY | CHECK OPTION } ];

The simplest example of a view creation is

CREATE VIEW emp_list AS
 SELECT sss, fname, lname
   FROM employees;

Join views.

We can also use more than one table as base tables. For example, we can create a view based on several tables:

CREATE OR REPLACE VIEW dep_max_salaries AS
 SELECT dep, name, max(salary) max_salary
   FROM departments, employees
  WHERE id = dep
  GROUP BY dep, name
  ORDER BY 3 DESC;

Defined column names.

When we create a view without specifying the names of the columns, then the names of the columns is the view will be the same as the names of the columns in the SELECT statement. For example, the previously created view dep_max_salaries will have the columns

SQL> desc dep_max_salaries
 Name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            Null?    Type
 ----------
 DEP                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      CHAR(4)
 NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            NOT NULL CHAR(16)
 MAX_SALARY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               NUMBER

That is, the fields dep and name kept their names, and the view field MAX(salary) got the name of its alias. If we need to specify different names we can use the extended syntax by specifying those names in the parentheses before the keyword AS. For example

CREATE OR REPLACE VIEW dep_max_salaries (dep_id, dep_name, dep_max_salary) 
 AS
 SELECT dep, name, max(salary) max_salary
   FROM departments, employees
  WHERE id = dep
  GROUP BY dep, name
  ORDER BY 3 DESC;

SQL> desc dep_max_salaries
 Name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            Null?    Type
 --------------
 DEP_ID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   CHAR(4)
 DEP_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        NOT NULL CHAR(16)
 DEP_MAX_SALARY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           NUMBER

Constraint on views.

In the same parentheses we can define constraints on views. In Oracle9i these view constraints are not enforced - they are declarative constraints. Because of that we must define view constraints as DISABLE NOVALIDATE. To actually enforce constraints, we have to define them on the base table.

CREATE VIEW emp_phones
 (dep_id,
  emp_ssn CONSTRAINT emp_phones_fk REFERENCES employees DISABLE NOVALIDATE,
  manager_ssn,
  phone_num CONSTRAINT emp_phones_uq UNIQUE DISABLE NOVALIDATE,
  CONSTRAINT emp_phones_pk PRIMARY KEY (dep_id, emp_ssn) DISABLE NOVALIDATE)
 AS
 SELECT e.dep, e.ssn, m.ssn, e.phone
   FROM employees e, departments d, employees m
  WHERE e.dep = d.id 
        AND
        d.head = m.ssn
  ORDER BY e.dep;

As we can see from this example we can specify both column-level and view-level constraints. Another way to set a constraint on a view (a VALID one) is to use WITH CHECK OPTION.

Invalid views.

When we are trying to create a view based on a non-existing table, Oracle produces an error message:

SQL> CREATE VIEW bad_view
 2    AS SELECT * FROM some_unknown_table;

   FROM some_unknown_table
        *
ERROR at line 4:
ORA-00942: table or view does not exist

However, we can force Oracle to create such a view by using the FORCE keyword (NO FORCE is the default option). If the FORCE keyword is specified, the view will be created even the base table does not exist. In this situation Oracle will mark the view as invalid, and each time we try access the view it will try to compile it again and if the table is still does not exist it will produce an error message.

SQL> CREATE FORCE VIEW bad_view
 2    AS SELECT * FROM some_unknown_table;

Warning: View created with compilation errors.

SQL> SELECT * FROM bad_view;

SELECT * FROM bad_view
              *
ERROR at line 1:
ORA-04063: view "schema.BAD_VIEW" has errors

Although, if we create the needed table, the view will work just fine.

Modifying views.

Dropping views.

To drop a view we need to use the DROP VIEW command. The syntax of the command is simple

DROP VIEW [schema.]view_name [ CASCADE CONSTRAINTS ];

Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the view to be dropped. If you omit this clause, and such constraints exist, then the DROP VIEW statement will fail. If the command is successful, the view will be removed from the dictionary and the privileges granted on the view will also be dropped. Other views and stored programs that refer to the dropped view will become invalid.

Changing view.

One way to redefine a view is to DROP and re-CREATE it again. ALthough, this method will drop all the privileges granted on the view, so we'll have to re-grant them. Another way to change a view's definition is to use the OR REPLACE option in the CREATE VIEW command. When this option is used, if the view exists, it will be replaced, with the new definition; otherwise, a new view will be created. This methods preserves all the privileges granted on the view. The dependent views and stored programs still become invalid, though.

Recompiling views.

If a view became invalid, then Oracle needs to recompile it before using it again. Thus, next time we will use a currently invalid view Oracle will implicitly compile it. However, we can explicitly recompile a view by using the ALTER VIEW command with the COMPILE clause. The syntax of the command is

ALTER VIEW [schema.]view_name COMPILE;

Status of a view we can look up in the Oracle dictionary table USER_OBJECTS. Please execute the following commands:

CREATE TABLE students (
  login VARCHAR2(30) CONSTRAINT students_pk PRIMARY KEY,
  fname VARCHAR2(16),
  lname VARCHAR2(24) NOT NULL,
  prj_1 NUMBER(3) CONSTRAINT students_check_1 CHECK (prj_1<=100 and prj_1>=0),
  prj_2 NUMBER(3) CONSTRAINT students_check_2 CHECK (prj_2<=100 and prj_2>=0),
  prj_3 NUMBER(3) CONSTRAINT students_check_3 CHECK (prj_3<=100 and prj_3>=0),
  prj_4 NUMBER(3) CONSTRAINT students_check_4 CHECK (prj_4<=100 and prj_4>=0)
);

CREATE VIEW my_grades (student_name, Project_1, Project_2, Project_3, Project_4, Total)
 AS
 SELECT lname || ', ' || fname, prj_1, prj_2, prj_3, prj_4, (prj_1+prj_2+prj_3+prj_4)/4
   FROM students
  WHERE login = USER;
  
SELECT object_name, object_type, created, status
  FROM user_objects;
  
ALTER TABLE students
  ADD prj_5 NUMBER(3) CONSTRAINT students_check_5 CHECK (prj_5<=100 and prj_5>=0); 

SELECT object_name, object_type, created, status
  FROM user_objects;
  
ALTER VIEW my_grades COMPILE;

SELECT object_name, object_type, created, status
  FROM user_objects;

Altering view.

Using the ALTER VIEW statement we can also add and drop constraints on a view. The syntax for adding and dropping on a view is similar to to that for modifying the constraints on a table.

Using views.

The following are the some common uses of views:

To represent a subset of data. Using privileges we can prevent users from seeing some columns of a table, but we cannot prevent him/her from seeing particular rows. However, we can achieve that by creating a view that represents only the rows a particular user is allowed to read and grant the SELECT privilege on this view.
To represent a superset of data. We can use views to represent some information from several joined tables (including aggregate functions).
To hide a complex join. We can have complex queries stored as views. Thus, the end user need not worry about the relationship between the tables.
To provide more meaningful names for columns.
To minimize application and data source changes. We may develop our applications referring to views, and if the data source changes or the data is derived in a different manner, only the views needs to be changed.

Selecting from views.

In the SELECT statements we use views as we do tables.

Inserting, updating, and deleting through views.

If certain conditions are met, most single-table views and many join views can be used to insert, update, and delete data from the base table. We can perform DML statements on a view only if the view definition does not have the following

WITH READ ONLY option
DISTINCT clause
GROUP BY clause
START WITH clause
CONNECT BY clause
ROWNUM pseudo-column
set operations UNION, UNION ALL, MINUS, or INTERSECT
subqueries in the SELECT clause

All DML operations will be performed on the base table. If a view has more than one base table, then such a view is called a join view. Any INSERT, UPDATE, or DELETE operation on a join view can modify data from only one base table in any single SQL operation.

For example, let's consider a view that produces all employees of the department dp01:

CREATE OR REPLACE VIEW employees_from_dp01 AS
  SELECT *
    FROM employees
   WHERE dep = 'dp01';

Since this view satisfies all the requirements, we can perform DML operations on the view. For example, we can insert a new employee by executng

INSERT INTO employees_from_dp01 VALUES
 (98760, 'Test', 'Employee', '17-SEP-1973', SYSDATE,
  45000, 'M', 3046961234, 'testing', 'sd01', NULL, 'single');

As you can notice, despite the fact that the view produces only the employees from the department dp01, we can insert an employee that works for another department. If we want Oracle to enforce the constraint we specified in the WHERE clause; that is, we want the DML statements through the view to conform to the view definition, we need to use the WITH CHECK OPTION clause of the CREATE VIEW statement. The general syntax if the clause is

WITH CHECK OPTION [ CONSTRAINT constraint_name ];

For example, the previous view employees_from_dp01 can be created as

CREATE OR REPLACE VIEW employees_from_dp01 AS
  SELECT *
    FROM employees
   WHERE dep = 'dp01'
    WITH CHECK OPTION CONSTRAINT employees_from_dp01_check;

Now the previous INSERT command will generate an error message

INSERT INTO employees_from_dp01 VALUES
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

A table in a join view is key-preserved, if the primary key and unique keys of the table are unique on the view's result set. We can update only key-preserved join tables through a view. If the view created with the WITH CHECK OPTION clause, we cannot update the columns that join the base tables. INSERT statement cannot refer to any columns of the non-key-preserved table. If the join view was created with the WITH CHECK OPTION clause, no INSERT operation is permitted on the view.

Oracle dictionary about views.

Oracle dictionary maintains several tables that contain information about views created in the database. These tables are:

USER_VIEWS in particular contains the query with which the views were created. For example, the following query retrieves the create statement for the view employees_from_dp01
```
SELECT text
  FROM user_views
 WHERE view_name = 'EMPLOYEES_FROM_DP01';
```
ALL_VIEWS this tables contains information about all views in the DB, not only those belonging to the current user
USER_OBJECTS - contains information about all objects that belong to the current user
ALL_OBJECTS contains information about all objects in the DB
USER_CONSTRAINTS contains information on all constraints defined by the current user. In particular, we can see the constraints on a view enforced by the WITH CHECK OPTION clause. For example, we can see the constraints on the employees_from_dp01
```
SELECT table_name, constraint_name, constraint_type, status
  FROM user_constraints
 WHERE table_name = 'EMPLOYEES_FROM_DP01';
```
USER_UPDATABLE_COLUMNS contains information about allowable DML operations on the columns of the user's views and tables. For example, we can see which columns of the view employees_from_dp01 allow the update, insert, and delete operations:
```
SELECT column_name, updatable, insertable, deletable
  FROM user_updatable_columns
 WHERE table_name = 'EMPLOYEES_FROM_DP01';
```

Inline views.

Inline views are the SELECT statements that appear in the FROM clause of other SELECT statements. These inline views are to be included in parentheses and may be given an alias name. The columns selected in the parentheses can be referenced in the parent query. Inline views can be considered as temporary views. Example of an inline view

SELECT fname, lname, salary, dep_avg_salary
  FROM employees,
       (SELECT dep, AVG(salary) dep_avg_salary
          FROM employees
         GROUP BY dep) dept
 WHERE employees.dep = dept.dep
       AND 
       lname LIKE 'M%';

Performing top-N analysis.

Using views or inline views we can perform top-'n' analysis. That is, print first several rows from a particular view. For example, if we need to print the top 5 highest paid employees, we cannot write a query like

SELECT fname, lname, salary
  FROM employees
 WHERE ROWNUM < 6
 ORDER BY salary DESC;

Or, more exactly, this query doesn't produce the result we need. Since the pseudo-column ROWNUM is assigned only when each row is returned, the result set is actually the first 5 rows of the table that were sorted by salary. We actually need to sort the whole table and only then print the first five rows. We can achieve this by using an inline query:

SELECT *
  FROM ( SELECT fname, lname, 
                TO_CHAR(salary, '$999,999.00') salary
           FROM employees 
          ORDER BY salary DESC)
 WHERE ROWNUM < 6;