Creating a Simple view:
The Views are created using the Create command. The defintion of the view will hold the column a name provides in the query. An Syntax for Creating a View query is as given below:
Syntax:
CREATE OR REPLACE VIEW <viewname> AS <query>;
Example:
This creates an easy view with records selected from the Employee Table
CREATE OR REPLACE VIEW empview AS SELECT ename, deptno, sal FROM emp;# query for create simple view of employee table#
This displays the feedback
View created.
From now on, querying required not is done from the EMP table. A records can be queried using the View as given below:
SELECT * FROM empview;
Displays,
ENAME SAL DEPTNO
--------- -------- -------
KING 5000 10
BLAKE 2850 30
CLARK 2450 10
JONES 2975 20
MARTIN 1250 30
ALLEN 1600 30
TURNER 1500 30
JAMES 950 30
WARD 1250 30
FORD 3000 20
SMITH 800 20
SCOTT 3000 20
ADAMS 1100 20
MILLER 1300 10
Any DML operations can be building to this view. The Records inserted by the view will be affected in the base table and vice versa. But not all columns can be inserted by the view. Only the column names which appear in the select list of the query can be updated or inserted.
There are a few Restrictions on performing DML operations. They are as given below:
If a view is defined through a query which holds SET or DISTINCT operators, a GROUP BY clause, or a group function, rows cannot be inserted into, deleted from the base tables, or updated in using the view.
If a view is described with the 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.
A row cannot be inserted into the base table using the view if a NOT NULL column that does not have a DEFAULT clause is omitted from the view.
If the view was created through using an expression, such as DECODE (deptno, 10, 'SALES',), rows cannot be inserted into or updated in the base table using the view.
A View creation cannot hold an ORDER BY clause.