Oracle Learning Article 11: Views

A view is a stored query definition.

1. The syntax for creating a view is as follows:
Create [OR REPLACE] [Force | Noforce] VIEW view_name[(Alias,alias,...)] As
select_statement [with CHECK OPTION [CONSTRAINT CONSTRAINT]] [with READ only];

Where: OR REPLACE means that the view will be recreated if the view exists.
Force uses this keyword to create a view regardless of whether the base table exists.
Noforce This is the default value. If you use this keyword, the view is created only if the base table exists.
View_name represents the name of the view to create.
alias specifies the name of the column in the view, and the number of names must match the number of expressions selected by the view.
Select_statement represents the SELECT statement.
With CHECK option This option specifies that only rows that are accessible by the view can be inserted or updated. The term constraint represents the name specified for the CHECK option constraint. The conditions for creating the view cannot be updated.
With READ only this option ensures that no modifications can be performed on this view.

Example 7: Demo creates a view named Ven_view that has the same structure as the Vendor_master table.
Create VIEW Ven_view as SELECT * from Vendor_master;

The ORDER BY clause in the

View: The result set arranges rows in the specified order, even if the ORDER BY clause is not used when querying the view.
Example 8:create OR REPLACE VIEW ven_view (number, date) as select Orderno,odate from Vendor_master order by Venname;

2. Create a view with errors: Use the force option
Oracle also creates views in the following cases:
1 The Vision definition query refers to a nonexistent table;
2 view defines a query that references an invalid column in an existing table; The owner of the
3 view does not have the required permissions.

Example 9: Create a venmast-based view below. However, a table named "Venmast" does not exist in the database.
Create Force VIEW ven as SELECT * from Venmast;
If you later create a table named Venmast, you can use the Alter VIEW VEN COMPILE;

3. Join View:
Example: show how to create a join view
Create OR REPLACE view Ven_ord_view as Select Vm.vencode,venname,orderno,odate,os Tatus
from Vendor_master vm,order_master om where vm.vencode=om.vencode;

Key retention table: In a join view, if the view contains a primary key for a table and is also the primary key for the view, the key is preserved, and the table is called the key reservation table.
The Ven_ord_view view is the base table for both tables Vendor_master and Order_master. The Order_master table is treated as a key-preserving table because OrderNo is both a order_master table
Primary key, which is also the primary key of the view. The Vendor_master table is not considered a key-preserving table because the primary key vencode of the Vendor_master table is the join field, not the primary key of the view.
Example 10: This example successfully modifies a record because the Odate column belongs to the key-preserving table Order_master. It is not allowed to update the Venname column for non-key reserved table Vendor_master.
Update Wen_ord_view odate=odate+1 where vencode= ' V003 '; Note: When you run this example, you must ensure that the primary key for the base table is created correctly.

You can query the dictionary view User_updateble_columns to see the columns that can be updated in the join view.
You can query the dictionary view User_views to see the view information created by the user.

Functions in Views: Views can use a single-line function (consisting of numbers, characters, dates), grouping functions, and expressions.
Example 11: This example creates a view with the upper function, noting that when a function or expression is used, the column should be given an alias.
Create VIEW Vendor_master_view as Select Vencode,upper (venname) vendor_name from Vendor_master;

4. Delete View syntax: Drop view view_name;

Oracle Learning Article 11: Views