CREATE OR REPLACE VIEW

Views are queries stored in Oracle that dynamically assemble data into a virtual table. To the person using the view, manipulating the data from the view is just like manipulating the data from a table. In some cases, it is even possible for the user to change data in a view as though the view were a table. Let’s now explore the topic of creating, using, and managing views in more detail.

Creating a VIEW
The syntax for creating a VIEW is:
    CREATE VIEW view_name AS
    SELECT columns
    FROM table
    WHERE predicates;A view will not be created if the base table you specify does not exist. However, you can overcome this restriction by using the force keyword in the create view command. This keyword forces Oracle to create the view anyway. However, the view will be invalid because no underlying table data is available to draw from.
For example:
Create VIEW XX_PO_DETAILS_v AS
Select a.PO_ID, a.PO_NUMBER, b.ITEM_NAME, b.NEED_DATE
From XX_PO_HEADERS_ALL a, XX_PO_LINES_ALL b
Where a.PO_ID=b.PO_ID
This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
    SELECT *
    FROM  XX_PO_DETAILS_vs;

Creating Views That Enforce Constraints
Tables that underlie views often have constraints that limit the data that can be added to those tables. As I said earlier, views cannot add data to the underlying table that would violate the table’s constraints. However, you can also define a view to restrict the user’s ability to change underlying table data even further, effectively placing a special constraint for data manipulation through the view. This additional constraint says that insert or update statements issued against the view are cannot create rows that the view cannot subsequently select. In other words, if after the change is made, the view will not be able to select the row you changed, the view will not let you make the change. This viewability constraint is configured when the view is defined by adding the with check option to the create view statement. Let’s look at an example to clarify my point:
 create or replace view emp_view as
 (select empno, ename, job, deptno
   from emp
   where deptno = 10)
  with check option constraint emp_view_constraint;
Updating a VIEW
You can update a VIEW without dropping it by using the following syntax:
    CREATE OR REPLACE VIEW view_name AS
    SELECT columns
    FROM table
    WHERE predicates;For example:
    CREATE or REPLACE VIEW sup_orders AS
    SELECT suppliers.supplier_id, orders.quantity, orders.price
    FROM suppliers, orders
    WHERE suppliers.supplier_id = orders.supplier_id
    and suppliers.supplier_name = ‘Microsoft’;
Dropping a VIEW
The syntax for dropping a VIEW is:
    DROP VIEW view_name;
For example:
    DROP VIEW sup_orders;
Creating Simple Views That Can’t Change Underlying Table Data
In some cases, you may find that you want to create views that don’t let your users change data in the underlying table. In this case, you can use the with read only clause. This clause will prevent any user of the view from making changes to the base table. Let’s say that after reprimanding SCOTT severely for calling him a fool, KING wants to prevent all employees from ever changing data in EMP via the EMP_VIEW again. The following shows how he would do it:
create or replace view emp_view
 as (select * from emp)
 with read only;
Frequently Asked Questions
Question:  Can you update the data in a view?
Answer:  A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view. So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.
Question: Does the view exist if the table is dropped from the database?
Answer: Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.If you recreate the table (that you had dropped), the view will again be fine.