Oracle Create View Syntax: 9 Different Examples

advertisements

_____________________________________________________________________________________________________________________

1. Create Simple View with Selected columns of a table

SQL> create view vw_emp as

select empno, ename from emp;

View created.

2. Create a View with all columns of a table

SQL> create view vw_emp as select * from emp;

View created.

3. Create view with columns from multiple tables

SQL> create view vw_emp_dept as

select empno, ename, dname

from   emp, dept

where  emp.deptno=dept.deptno;

View created.

4. Modify the View definition - create or replace

SQL> create or replace view vw_emp_dept as

select empno, ename, dname

from   emp, dept

where  emp.deptno=dept.deptno

and    sal>5000;

View created.

5. Update view rows - 

It requires the update privilege on the underlying tables.

SQL> update vw_emp_dept set empno=9999;

14 rows updated.

6. Non Updatable views – A view with distinct, group by, aggregate functions cannot be updated. Row manipulation is not possible for the underling tables for these tables.

create view vw_emp as select distinct ename from emp;

SQL> update vw_emp set ename ='xyz';

update vw_emp set ename ='xyz'

       *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

7. Create View without base table using FORCE clause. The view will be created with compilation errors.

SQL> create force view VW_Force_Invalid

as select * from bonus;

Warning: View created with compilation errors.

SQL>  select text from user_errors where name='VW_FORCE_INVALID';

TEXT

---------------------------------------------------------------

ORA-00942: table or view does not exist

8. To describe the structure of the view

SQL> desc vw_emp_dept

 Name                                      Null?    Type

 ----------------------------------------- -------- ------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 DNAME                                              VARCHAR2(14)

9. To drop a view

SQL> drop view vw_emp;

View dropped.

_____________________________________________________________________________________________________________________

Website Stats