ORACLE VIEW)

Before talking about the ORACLE view, let's take a look at this requirement first. There is a teacher information table with the following fields: teacher ID, name, marital status, ID card number, and substitute subject. You can view the marital status and ID card number from the academic affairs office, however, it is prohibited for students to view and keep them confidential. The two identities use the same table. How can I view different results?

Before talking about the ORACLE view, let's take a look at this requirement first. There is a teacher information table with the following fields: teacher ID, name, marital status, ID card number, and substitute subject. You can view the marital status and ID card number from the academic affairs office, however, it is prohibited for students to view and keep them confidential. The two identities use the same table. How can I view different results?

Before talking about the ORACLE view, let's take a look at this requirement first.

There is a teacher information table with the following fields: teacher ID, name, marital status, ID card number, and substitute subject. You can view the marital status and ID card number from the academic affairs office, however, it is prohibited for students to view and keep them confidential. The two identities use the same table. How can I view different results? The view is used to restrict the field information to be kept confidential.

A view is displayed based on a real table. It is just a logical concept. The data in the view comes from the base table (real table), which hardly occupies physical storage space. The view can be added, deleted, modified, and operated like a common table; the view ensures the security of database data. Of course, it is convenient and convenient (for example, complicated SQL condition queries can be defined in a view and directly queried ). First, let's learn how to define a view:

1. Complete view definition Syntax:

CREATE [or replace] [FORCE] [NOFORCE] VIEW view_name

[(Column_name )[,.... N]

AS

Select_statement

[With check option [CONSTRAINT constraint_name]

[With read only]

OR REPLACE-- If the view is replaced, an attempt is made.

FORCE] [NOFORCE-- The base table does not exist. FORCE is forcibly created; NOFORCE is not created (default)

With check option [CONSTRAINT constraint_name]-- Whether to verify the WHERE condition for defining view SQL when you manipulate view DML.

WITH READ ONLY-- Define read-only view

2. Create a view. Use base table classes as an example.

SQL> SELECT * FROM classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

Create a default view classes_view statement for the classes table

SQL> CREATE OR REPLACE NOFORCE VIEW classes_view
2
3 SELECT * FROM classes
4;
View created

The view is successfully created. As mentioned above, the view can be added, deleted, or modified, just like a common table. The following example shows how to add data to the view (the modification and deletion are not listed here)

SQL> insert into classes_view values (4,1111 );
1 row inserted

SQL> commit;
Commit complete

SQL> SELECT * FROM classes_view;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 1111

This new data entry also exists in the base table.

SQL> SELECT * FROM classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 1111

Create a classes_view with query conditions for the classes table

SQL> CREATE OR REPLACE NOFORCE VIEW classes_view
2
3 SELECT * FROM classes WHERE cid <= 2
4;
View created

SQL> SELECT * FROM CLASSES_VIEW;
CID CNAME
-------------------------------------------------
1 0901
2 0902

-- Because the WHERE condition cid is included when a view is created <= 2, two messages are contained in all views.

SQL> SELECT * FROM CLASSES;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 1111

Add a message with the cid of 5 in the CLASSES_VIEW view.

SQL> INSERT INTO CLASSES_VIEW VALUES (5, '123 ');
1 row inserted

SQL> COMMIT;
Commit complete

-- Data is successfully added to the view. Query View display result:

SQL> SELECT * FROM CLASSES_VIEW;
CID CNAME
-------------------------------------------------
1 0901
2 0902

??? Why didn't we include the newly added data? Where is the new data? As mentioned above, the Operation View data actually operates on the base table, that is, the data is saved to the base table CLASSES table. Why can't we find the defined view? Let's look at our view definition Syntax:

SQL> CREATE OR REPLACE NOFORCE VIEW classes_view
2
3 SELECT * FROM classes WHERE cid <= 2
4;

Because of the WHERE condition restriction, the conditional view does not display data that does not meet the conditions, so it cannot be seen. Someone may ask, since the view only displays information that meets the WHERE condition, how to control the information that does not meet the WHERE condition of the view into the view (insert, update? Use the view Keyword: with check option. The following describes how with check option controls how to disable access from the view if the conditions are not met.

-- Create a classes_view table WITH query conditions and WITH CHECK OPTION

SQL> CREATE OR REPLACE NOFORCE VIEW classes_view
2
3 SELECT * FROM classes WHERE cid <= 2
4 WITH CHECK OPTION
5;
View created

SQL> select * from classes_view;
CID CNAME
-------------------------------------------------
1 0901
2 0902

The view is successfully created. The view contains only two constraints (cid) <= 2 and four base table data records. If a new cid is 5, an error is returned, the view has the where condition constraint.

SQL> INSERT INTO CLASSES_VIEW VALUES (5, 22 );
Insert into CLASSES_VIEW VALUES (5, 2222)
ORA-01402: view with check optidn where clause violations

Insert data that meets the where condition can be successfully inserted, as shown in the following figure: DATA 0 inserted <= 2:

SQL> INSERT INTO CLASSES_VIEW VALUES (0, '123 ');
1 row inserted

SQL> commit;
Commit complete

SQL> select * from classes_view;
CID CNAME
-------------------------------------------------
0 0001
1 0901
2 0902

Modify view: ORACLE does not provide any statement to modify the view. To modify the view, use the replace keyword.

Create or replace view classes_view
AS
SELECT * FROM classes

View created

Delete View

SQL> DROP VIEW classes_view;
View dropped