Creating and managing views

A view is a stored query. You write a SQL query and save it in the database as a view. A view can reference a single table/view, or multiple tables/views.

The following is an example of the CREATE VIEW statement:

-- Create a new view
CREATE [OR REPLACE] VIEW salary_gr_1000 AS
select emp_no, emp_name, salary
FROM emp
WHERE salary > 1000;

In the preceding statement, we created a salary_gr_1000 view, which fetches data from the emp table for all employees whose salary is greater than 1000. We can use the OR REPLACE clause to change the query in the view.

We will use the ALTER VIEW statement to compile and modify/drop constraints. The following is an example:

-- Compiling view
ALTER VIEW salary_gr_1000 COMPILE;

To change the query of a view, we use the CREATE or REPLACE VIEW statement.

Removing a view from the database is achieved using the DROP VIEW statement, as shown in the following code snippet:

-- Permanently remove the view from the schema
DROP VIEW salary_gr_1000...