In this article, we will discuss the functions and types of Views in Oracle Database in detail.
View is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.
They are used to simplify complex SQL data and limit the data that users can access.
Information can be obtained by querying DBA/ALL/USER_VIEWS views about views.
Views can be created on a table or another view.
In order to create a view in our own schema, it is necessary to have “CREATE VIEW” system authority, and to create a view in another user’s schema, it is necessary to have “CREATE ANY VIEW” system authority.
If a view is created with one of the following structures, delete and update operations cannot be performed from the view.
A Set Operator (Union, union all, intersect, minus)
A DISTINCT Operator
An aggregate or analytic function
A Group by, Order by, Model, Connect by, Start with.
Views are created with “CREATE VIEW” or “CREATE OR REPLACE VIEW” sentences.
Views are divided into 2 groups.
SIMPLE VIEWS
Allows DML operations.
Data entry made via view is recorded in the view’s table.
They are views created on a single table. They do not contain any functions.
Data cannot be deleted from a view created with DISTINCT.
For example,
|
1 2 3 4 5 6 7 8 9 |
SQL> create or replace view v2 as select distinct packageid from iptvmwc.account_to_package; View created. SQL> delete from v2 where packageid = '1000112'; delete from v2 where packageid = '1000112' * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view |
COMPLEX VIEWS
They are views that contain functions and tables that are related to each other.
No DML operations are performed.
|
1 2 3 4 5 6 |
SQL> CREATE OR REPLACE VIEW CALISAN AS SELECT SICIL_NO, ADI, SOYADI, ISE_GIRIS_TARIHI FROM CALISANLAR; View created. |
We use the WITH CHECK OPTION clause to prevent insertion of data other than the data we have the authority to see.
When the With Check Option is used, the expression specified in the WHERE clause must be in the Select list.
|
1 2 3 4 5 |
CREATE OR REPLACE VIEW CALISAN AS SELECT SICIL_NO, ADI, SOYADI, ISE_GIRIS_TARIHI FROM CALISANLAR; WITH CHECK OPTION; |
When creating views, the existence of tables is first checked. If the table does not exist, an error is thrown. In this case, the view is not created. If we still want to create it, then we add the FORCE word.
|
1 2 3 4 5 6 |
CREATE OR REPLACE FORCE VIEW CALISAN AS SELECT SICIL_NO, ADI, SOYADI, SUBE_ID FROM CALISANLARRR WHERE SUBE_ID=5; WITH CHECK OPTION; |
If our view is a simple view but we don’t allow DML operations on it, we make the view read-only.
|
1 2 3 4 5 |
CREATE OR REPLACE FORCE VIEW CALISAN AS SELECT SICIL_NO, ADI, SOYADI, SUBE_ID FROM CALISANLAR WITH READ ONLY; |
An invalid view can be recompiled with the “ALTER VIEW” statement.
|
1 2 3 |
SQL> ALTER VIEW CALISAN COMPILE; View altered. |
The name of the View is changed as follows.
|
1 2 3 |
SQL> RENAME CALISAN TO CALISAN_VW; Table renamed. |
The view is DROP as follows. However, before dropping the view, a dependency check must be performed. Because when the view is dropped, the objects it depends on (procedure, function, ..) become invalid.
|
1 2 3 |
SQL> DROP VIEW CALISAN_VW; View dropped. |
DEPENDENCE CONTROL
Dependency check is done as follows.
In the DBA_DEPENDENCIES view, you can see which object is dependent on which object.
The sample query is as follows.
|
1 |
SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = 'ACCOUNT'; |
In the query above, we see which objects the ACCOUNT object is dependent on.
If we delete the ACCOUNT object, the resulting objects will also become invalid.
