Views are the named queries, acting like a table. When a complex query is used multiple times, part of the query can be kept to use it in further queries. But in order to use it again in some other query, it should have some name. Such named queries are known as view. It will be compiled whenever it is used in the query. It can be created as follows: This view is created on BOOK_LOAN table to get books borrowed by the borrowers in June 2015.
CREATE VIEW vw_book_loan_Jun AS SELECT * FROM BOOK_LOAN WHERE TO_CHAR (LOAN_DATE, 'MMYYYY') = '062015';
We can see the records in view, like we query tables. Here each time we query view, the query underlying the view is executed. That means when we say select * from view, actually it queries BOOK_LOAN table to get the data.
SELECT * FROM vw_book_loan_Jun;
Views can be dropped like we drop the tables using DROP statement.
DROP VIEW vw_book_loan_Jun;
We can modify the existing view. But first we have to DROP the view and then we have to recreate it. This can also be done by using CREATE OR REPLACE statements like below. It creates a view, if it does not exist; it replaces the view with new query, if it already exists.
CREATE OR REPLACE VIEW vw_book_loan_Jun AS SELECT * FROM BOOK_LOAN WHERE TO_CHAR (LOAN_DATE, 'MMYYYY') >= '062015';
There are restrictions on the view while using DMLs like insert, update or delete. Views cannot have all the columns or records from the table. It is the subset of the table. It does not have constraints too. Hence insert/update/delete on views will not have complete information. Even though we are modifying view records, it is directly modifying the table records. If we use ‘WITH CHECK OPTION’ while creating a view, then it will verify the tables while manipulating the records. If it finds any insert/delete/update which violates the constraints of table, then it will not allow to perform the transaction on view/table.
CREATE OR REPLACE VIEW vw_book_loan_Jun AS SELECT * FROM BOOK_LOAN WHERE TO_CHAR (LOAN_DATE, 'MMYYYY') >= '062015' WITH CHECK OPTION;
