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. In order to check the Oracle VIEW stu_teach, another query needs to be executed.

SELECT * FROM stu_teach;

Output:

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. In order to check the Oracle VIEW stu_teach, another query needs to be executed. SELECT * FROM stu_teach;

Output:

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: In order 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.

Please follow and like us: