In this article, we will discuss the functions and types of Views in Oracle Database in detail.<\/p>\n
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.<\/p>\n
They are used to simplify complex SQL data and limit the data that users can access.<\/p>\n
Information can be obtained by querying DBA\/ALL\/USER_VIEWS views about views.<\/p>\n
Views can be created on a table or another view.<\/p>\n
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.<\/p>\n
If a view is created with one of the following structures, delete and update operations cannot be performed from the view.<\/p>\n
A Set Operator (Union, union all, intersect, minus)
\nA DISTINCT Operator
\nAn aggregate or analytic function
\nA Group by, Order by, Model, Connect by, Start with.<\/p>\n
Views are created with “CREATE VIEW” or “CREATE OR REPLACE VIEW” sentences.<\/p>\n
Views are divided into 2 groups.<\/p>\n
Allows DML operations.<\/p>\n
Data entry made via view is recorded in the view’s table.<\/p>\n
They are views created on a single table. They do not contain any functions.<\/p>\n
Data cannot be deleted from a view created with DISTINCT.<\/p>\n
For example,<\/p>\n
\tSQL> create or replace view v2 as select distinct packageid from iptvmwc.account_to_package;\r\n\t\r\n\tView created.\r\n\t\r\n\tSQL> delete from v2 where packageid = '1000112';\r\n\tdelete from v2 where packageid = '1000112'\r\n\t *\r\n\tERROR at line 1:\r\n\tORA-01732: data manipulation operation not legal on this view\r\n<\/pre>\nCOMPLEX VIEWS<\/h3>\n
They are views that contain functions and tables that are related to each other.<\/p>\n
No DML operations are performed.<\/p>\n
SQL> CREATE OR REPLACE VIEW CALISAN\r\nAS\r\nSELECT SICIL_NO, ADI, SOYADI, ISE_GIRIS_TARIHI\r\nFROM CALISANLAR;\r\n\r\nView created.\r\n<\/pre>\nWe use the WITH CHECK OPTION clause to prevent insertion of data other than the data we have the authority to see.<\/p>\n
When the With Check Option is used, the expression specified in the WHERE clause must be in the Select list.<\/p>\n
CREATE OR REPLACE VIEW CALISAN\r\nAS\r\nSELECT SICIL_NO, ADI, SOYADI, ISE_GIRIS_TARIHI\r\nFROM CALISANLAR;\r\nWITH CHECK OPTION;<\/pre>\nWhen 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.<\/p>\n
CREATE OR REPLACE FORCE VIEW CALISAN\r\nAS\r\nSELECT SICIL_NO, ADI, SOYADI, SUBE_ID\r\nFROM CALISANLARRR\r\nWHERE SUBE_ID=5;\r\nWITH CHECK OPTION;<\/pre>\nIf our view is a simple view but we don’t allow DML operations on it, we make the view read-only.<\/p>\n
CREATE OR REPLACE FORCE VIEW CALISAN\r\nAS\r\nSELECT SICIL_NO, ADI, SOYADI, SUBE_ID\r\nFROM CALISANLAR\r\nWITH READ ONLY;<\/pre>\nAn invalid view can be recompiled with the “ALTER VIEW” statement.<\/p>\n
SQL> ALTER VIEW CALISAN COMPILE;\r\n\r\nView altered.<\/pre>\nThe name of the View is changed as follows.<\/p>\n
SQL> RENAME CALISAN TO CALISAN_VW;\r\n\r\nTable renamed.<\/pre>\nThe 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.<\/p>\n
SQL> DROP VIEW CALISAN_VW;\r\n\r\nView dropped.<\/pre>\nDEPENDENCE CONTROL<\/h3>\n
Dependency check is done as follows.<\/p>\n
In the DBA_DEPENDENCIES view, you can see which object is dependent on which object.<\/p>\n
The sample query is as follows.<\/p>\n
SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = 'ACCOUNT';<\/pre>\nIn the query above, we see which objects the ACCOUNT object is dependent on.<\/p>\n
If we delete the ACCOUNT object, the resulting objects will also become invalid.<\/p>\n
<\/div>\n