Relationship between Oracle database view and base table

Relationship between Oracle database view and base table

I. What is a view first? A view is actually a query SQL statement used to display relevant data in one or more tables or other views. A view uses the results of a query as a table. Therefore, a view can be considered as a stored query or a virtual table. Unlike a real table, a view does not require storage space to be allocated, the view does not contain actual data. The view only defines a query. The data in the view is obtained from the base table, which is dynamically generated when the view is referenced. Because a view is based on other objects in the database, a view only needs to occupy the space defined by the data dictionary, without additional storage space, in addition, changes in the base table will lead to changes in the view.

Ii. View creation:
 
CREATE [or replace] [FORCE | NOFORCE] VIEW view_name [(alias [, alias]...)]
AS subquery
[With check option [CONSTRAINT constraint]
[With read only]
Where:
Or replace: If the created attempt already exists, Oracle automatically recreates the view;
FORCE: this view is automatically created no matter whether the base table exists in ORACLE;
NOFORCE: this view is created only when the base table exists in ORACLE:
Alias: the alias defined for the columns generated by the view;
Subquery: a complete SELECT statement. You can define an alias in this statement to SELECT the attributes you need for a table;
With check option: the inserted or modified data rows must meet the view-defined constraints;
With read only: No DML operations can be performed on this view.
3. View modification: directly use the or replaece when the front edge is created to recreate the view.

4. DML operations on the View:

1. A simple view, that is, a base table has only one view. You can modify the view to modify the base table,
Oracle can modify the Base table through the view. Base table is the table used to build the view, that is, the data source table of the view. However, such modifications are conditional. For example:
Create view v_emp as select empno, ename, job, deptno from emp where deptno = 10 with check option constraint emp_cnst;
If this restriction exists, the value of the deptno field that inserts data through the view v_emp must be 10, otherwise an exception "ORA-01402: view with check optidn violates the where clause" is reported.
2. For a complex view, that is, a base table has multiple tables. You can only modify the key_preserved table through the view created through the internal join query,
What is the Key-Preserved Table? Oracle defines it:
A table is key preserved if every key of the table can also be a key of the result of the join.
It is not necessary that the key or keys of a table be selected for it to be key preserved.
 
It is sufficient that if the key or keys were selected, then they wowould also be key (s) of the result of the join.

If the primary key of a table can be used as the primary key of the join result (view is usually the join result of several tables), the table is the key preserved table.

The primary key of this table does not necessarily appear in the select result set (in the select list), but if it appears in the result set, it must meet the requirements of the primary key used as the result set.
 
The following example is used to explain:

Create view liuwenhe as select e. ename, e. empno, e. job, d. deptno, d. dname from emp e, dept d where e. empno in (10, 30) and e. deptno = d. deptno; the primary key of the emp table is empno, and that of the dept table is deptno,
It can be seen that the empno value of the primary key of the emp table is unique and non-empty. Therefore, it can be used as the primary key of the query result set, however, the value of dempno In the result set is not necessarily unique. Therefore, the key_preserved table in this view is emp,
5. View functions: reduce complexity and enhance security.

1. view can hide the complexity of your query, for example:

SELECT d. dname, count (*) as NUM_EMPS
FROM emp e, dept d
WHERE e. deptno = d. deptno
Group by dname;

You do not need to enter such a complex query (according to today's standards, it is not complex). I can query the following view:

SELECT dname, num_emps FROM my_view; (provided that you have created a view based on the preceding query)

You can also store a lot of complicated queries as a view, which greatly simplifies the query. In this way, a view is a macro. It can do many things behind the scenes and make the process very simple for end users or applications.
2. Views can also be used to enhance security. Let's assume that I only want user BOB to see the ENAME and DEPNO columns in the EMP table. I can use the following:
GRANT select ON emp TO bob;

However, the above command will show BOB all the contents of the table. I can write down the following view so that BOB can only see the columns that he can see:

Create view bob_emp as select ename, deptno FROM emp; GRANT select ON bob_emp TO bob;

With these two commands, BOBO can only see two columns in the table in the view.

Let's further discuss the concept of security. Suppose we want everyone to query the EMP table, but they only have their own records. I can write the following view:

Create view my_emp as select * FROM emp WHERE ename = USER; GRANT select ON my_emp TO public;

When a user queries MY_EMP, the preceding view only returns the values of the ENAME columns as their usernames.

3. One of the most powerful features of the Oracle view is that it can create a view with errors. For example, if the field in the view does not exist in the base table, the view can still be created successfully, but it is invalid and cannot be executed. When this field is added to the base table, or a field is changed to the field name in the view, the view can become legal immediately.