SQL VIEW

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

 A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

For example: suppose that in the EMF table we want that ‘Manager’ should have access to the detail of employee’s names, job and department. But he should not have any access to the salaries of all the employees. This can be done with the help of Views. We can create View according to the individual’s need. We will create the view for the manager under which we do not include salary column. This helps the database administrator to limit the access to different kinds of users.

Uses of Views

The reasons for using views in applications can be many like;
• Reducing complexity.
• Security is increased – sensitive information can be excluded from a view
• Renaming the table columns- by giving the different names to columns while creating views.
• Views can represent a subset of the data contained in a table.
• Views can join and simplify multiple tables into a single virtual table.
• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.
• Different views can be created on the same base table for different users.

Syntax:
 
CREATE [OR REPLACE] VIEW view_name 
AS 
SELECT column_list FROM table_name [WHERE condition] 
[WITH CHECK OPTION]
[CONSTRAINT constrain_name] 
[WITH READ ONLY]; 

In the Syntax, View _name specifies the name of the view and must follow the rules for identifiers. Column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement. Select_statement specifies the SELECT Statement that defines a view.

The view may use the data contained in other views and tables. WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent. WITH READ ONLY restrict the DML operations to be performed on the view.

Creating Views 

To create a view, you must meet the following requirements:

To create a view in your schema, you must have the CREATE VIEW privilege. To create a view in another user’s schema, you must have the CREATE ANY VIEW system privilege. You can acquire these privileges explicitly or through a role.

The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view depends on the privileges of the view owner. For example, if the owner of the view has only the INSERT privilege for Scott’s EMP table, then the view can be used only to insert new rows into the EMP table, not to SELECT, UPDATE, or DELETE rows.

Examples:

Consider the EMPLOYEES table and create a View for the ‘RAMAN’; she cannot access the Salary of all the employees.

SQL > DESC EMPLOYEES; 
SQL > CREATE OR REPLACE VIEW RAMAN AS SELECT EID, NAME FROM EMPLOYEES; 

On Execution the view with Name ‘Raman’ is created. Once you create a view now it is assumed to be a Table. You can now perform the various DML operations on the View. But make sure if you impose the CHECK OPTIONS, then you cannot perform any operation. Remember that it will also copy the Records contained in the base table. Display all the records in the view.

SQL> SELECT * FROM RAMAN; 

After execution this command will display all the records of EMP table as when view created all the records automatically copied into the view. It will display all the records.

Read Only Option

If you use read only option in the view while creation then you cannot perform any DML operation on the view. If you try to do this then error message will encounter. WITH READ ONLY restrict the DML operations to be performed on the view

Consider the EMPLOYEES table and create a view for the ‘RAMANDEEP’; she cannot access the Salary of all the employees. Use the constraint ‘WITH READ ONLY.

SQL> create view ramandeep as select eid, name from employees with read only; 

After execution a view with the name ‘ramandeep’ will be created. In the view because we use the option WITH READ ONLY so it restricts the DML operations to be executed.

Modifying Views 

You can use the OR REPLACE option to modify the view. If the view exists it will be replaced with the new definition or a new view will be created. We can use Create or Replace option to create views instead of dropping the view and recreating it as with this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.

The view will become invalid whenever the base table is altered. Vie can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed. On recompiling the dependent objects become invalid.

SQL> ALTER VIEW View Name; 
Restrictions on DML Statements 

There are certain rules that have to be followed to perform the DML operations. They are as under:

You cannot insert the rows into view if the base table has certain columns with NOT NULL constraint that does not appear in the view.

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

If a view is defined with WITH CHECK OPTION, a row cannot be inserted into. or updated in, the base table (using the view), if the view cannot select the row from the base table.

If view contains WITH READ ONLY Constraint then you cannot perform any DML operations.

Renaming Columns

Views can be used to rename the columns without effecting the base tables provided that the number of columns in a view must match the number of columns specified in the select statement.

Create a view RAMAN from the table employees with different column names,

SQL> CREATE OR REPLACE UIEW RAMAN (ENO, ENAME) AS SELECT EID, NAME FROM EMPLOYEES;

View Created

After execution the view RAMAN will be created with the different column names.