VIEW in Oracle

CREATE VIEW

The view is a virtual table in Oracle with no physical existence as such and thus it does not store any data. The view is saved in the data dictionary of the Oracle and when called, it can be simply executed.

Syntax:

CREATE VIEW name AS  
SELECT column_1, column_2, column_3,......  
FROM table 
WHERE view_conditions;

Parameters:

name: It is used to specify the name of the Oracle VIEW to be created.

Example:

Students table:

CREATE TABLE  "STUDENTS"  
   (    "STUDENT_ID" NUMBER,   
    "STUDENT_NAME" VARCHAR2(100),   
    "STUDENT_AGE" VARCHAR2(50))

Teachers table:

CREATE TABLE  "TEACHERS"   
   (    "TEACHER_ID" NUMBER,   
    "TEACHER_NAME" VARCHAR2(100),   
    "TEACHER_AGE" VARCHAR2(50))

Create View Query:

CREATE VIEW stu_teach AS  
SELECT students.student_id, students.student_age, teachers.teacher_name  
FROM students  
INNER JOIN teachers  
ON students.student_id = student_id  
WHERE  students.student_name = 'VOJO';

Output:

View created.
0.21 seconds

Explanation:
A query joining one or more than one table is used to create a view. Here we are considering two tables, namely, students and teachers. The above query is then executed to create a view called stu_teach. To check the Oracle VIEW stu_teach, another query needs to be executed.

SELECT * FROM stu_teach;

Output:

STUDENT_ID	STUDENT_AGE	TEACHER_NAME
1	10	SIMRAN
2	12	SMITA
3	15	SHWETA
4	9	SHAURYA

Oracle Update VIEW:

The CREATE OR REPLACE VIEW statement is facilitated by the Oracle to modify the definition without dropping of an Oracle VIEW.

Syntax:

CREATE OR REPLACE VIEW name AS  
SELECT column_1, column_2, column_3,......  
FROM table 
WHERE view_conditions;

Parameters:

name: It is used to specify the name of the Oracle VIEW to be modified.

Example:

CREATE or REPLACE VIEW sup_orders AS  
CREATE VIEW stu_teach AS  
SELECT students.student_id, students.student_age, teachers.teacher_name  
FROM students  
INNER JOIN teachers  
ON students.student_id = student_id  
WHERE  students.student_name = 'HCL';

Explanation:
A query joining one or more than one table is used to update the definition of a view. Here we are considering two tables, namely, students and teachers. The above query is then executed to update the definition of VIEW called stu_teach. To check the Oracle VIEW stu_teach, another query needs to be executed.

SELECT * FROM stu_teach;

Output:

STUDENT_ID	STUDENT_AGE	TEACHER_NAME
1	10	SIMRAN
2	12	SMITA
3	15	SHWETA
4	9	SHAURYA

Oracle DROP VIEW

To entirely remove or delete an Oracle VIEW, the DROP VIEW statement is used.

Syntax:

DROP VIEW name;

Parameters:
name:
It is used to specify the name of the Oracle VIEW to be deleted.

Example:

DROP VIEW stu_teach;

Explanation:
To delete or remove the Oracle VIEW stu_teach, the above query needs to be executed. After the execution, the Oracle VIEW stu_teach, will be completely deleted from the data dictionary of the Oracle.

Alex Martin