View in Oracle
Hola Tech_Bunnies! I am back with another blog on View in Oracle Database continuing in the series on Concepts of SQL. I consider that you are familiar with the basic concepts of SQL for any database.
What are the views?
The view is nothing but SQL queries that can be stored as database objects. These are, essentially, the virtual tables based on the result set obtained from the queries. A view is like a table, containing rows and columns with a simple trick that these fields are from one or more existing tables but it presents data as if it was being retrieved from one single table. A view can contain:
1. SQL functions
2. WHERE clause
3. JOIN statements
Creating a view:
CREATE VIEW view_name AS
SELECT column_name1, column_name2, …, column_nameN
FROM table_name
WHERE condition;
Retrieving the result set:
SELECT * FROM view_name;
Updating the view definition:
CREATE [OR REPLACE] VIEW view_name AS
SELECT column_names
FROM table_name
WHERE condition;
Dropping the views:
DROP VIEW view_name;
The definition of view requires it to function as if an actual physical table is created when invoked from another query. It is stored in the schema tables to be invoked by the name whenever a table could be referenced.
Note:- A view always presents up-to-date data as is recreated every time user queries the view.
The properties of view a different from other database objects considering temporary tables, derived tables, and base tables. A view can not contain constraints like temporary and base tables. There is no existence of a view in the database until it is invoked. It is interesting to know that you cannot reference a view from another view definition, as it does not persist in the database.
Views can either UPDATABLE or READ-ONLY. Updatable views and their base tables allow INSERT, UPDATE and DELETE operation to be performed on them. On the other hand, read-only views don’t allow INSERT, UPDATE, and DELETE operations, but you can modify their base tables, as expected!
An updatable view is the one in which each row is associated with precisely a row in the underlying base table. Any modification made to the views is passed to the underlying base table through the view.
The criteria for a view for being considered as the updatable view are:
1. They are built on ONLY ONE table
2. They have no GROUP BY clause
3. They have no HAVING clause
4. They have no aggregated functions
5. They have no calculated columns
6. They have no UNION, INTERSECT, or EXCEPT
7. They have no SELECT DISTINCT clause
8. Columns that are not included in the view shouldn’t contain NOT NULL constraint or have a DEFAULT in the base table.
Although the definition of updatable views is quite limited but very safe.
Here, in the above examples, the view DEMO_EMP is an updatable view as it is built on a single table. The view EMP_DETAILS is fetching the data from the join of two tables henceforth, it is regarded as a read-only view!
Let’s take a quick recap, in this blog we walked through the theoretical understanding about the views in Oracle SQL, learned how to define views and finally, covered about updatable and read-only views.
P.s The code snippets that are uploaded in this blog can be found in my GitHub repository.
