All About View in Oracle SQL Database
What is a View in Oracle SQL Database?
A view is a logical table based on table or another view which is used to present the combinations of data present in the database. A view as itself doesn’t contain any data but it is more like a window through which the data from the tables can be viewed or changed. The table on which view is based is known as the base table and view is stored as a select statement in the data dictionary.
Advantages of Creating a View:-
- It restricts the data access to predefined tables and columns so that a database user can see only information relevant for him.
- It makes the complex queries easy.
- Provides the data independence.
- Can be used to present the different views of the same data.
There are majorly two types of views:-
1. Simple Views
2. Complex Views
The major difference b/w the two lies in the DML operations.
| Simple View | Complex View |
| Derives the data from only one table. | Derives the data from many tables. |
| Contains no functions or group of data | Contains functions or group of data |
| Can perform the DML operations through the view. | Does not always allow the DMLoperations through the view. |
Syntax for creating a view:-
| CREATE [ OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]…)] As subquery [WITH CHECK OPTION [CONSTRAINT constraint ] ] [WITH READ ONLY [CONSTRAINT constraint ] ] |
- Force creates the view regardless of whether the base table exists or not.
- OR REPLACE re-creates the view if already exists.
- NOFORCE creates the view only if the base table exist this is by default.
- WITH CHECK OPTION specifies that only rows accessible to the view can be inserted or updated.
- WITH READ ONLY ensures that no DML operations can be performed on this view.
Example:-
- Creating a view of employees in department 80
| CREATE VIEW emp80
AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; |
- You can add a column alias in the subquery.
| CREATE VIEW sal80
AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 80; |
RETRIEVING DATA FROM A VIEW:-
- It is same as retrieving a data from a table
|
You can also refer to USER_VIEWS to see the name and definition of a view.
MODIFYING A VIEW:-
You can modify a view via CREATE OR REPLACE VIEW clause.
|
RULES FOR PERFORMING DML OPERATIONS ON A VIEW:-
Data may not be added through a view if the view includes:
- Group functions
- A GROUP BY clause.
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyword
- Columns defined by expressions.
- NOT NULL columns in the base tables that are not selected by the view.
REMOVING A VIEW:-
You can remove a view without the loss of data because view is based on underlying tables in the database which is not removed by removing a view
- DROP VIEW view;
| DROP VIEW emp80; |
Instant Tony Tormenta
[...] All About View in Oracle SQL Database | Technofunction [...]
Are you out of the Loop about Tony Tormenta?
[...] All About View in Oracle SQL Database | Technofunction [...]