Oracle view, oracle view Creation

Oracle view, oracle view Creation
Oracle ViewSimple View

View a virtual table that contains a series of columns and row data with names, just like a real table.
However, the view does not store the actual value in the database. The database only stores the view definition in the data dictionary.

You can perform INSERT, UPDATE, and DELETE operations in the view. When you modify data through a view, you actually modify the data stored in the basic table.

A view can be created on a relational table, other views, or both. Create a view to combine the data frequently used in one or more tables to facilitate data operations.

A simple view can only retrieve data from a basic table, but cannot modify the data in the basic table. The basic syntax for creating a view is as follows:

CRAETE [or replace] VIEW view_nameAS <SELECT statement>; [with check option]

The with check option is used to define a specification.

Example 1:
Create a view for the student table:

CREATE OR REPLACE VIEW stu_viewAS SELECT SID,SNAME,SAGE,SSEX FROM student WHERE SSEX='F'

Or

Create or replace view stu_view (number, name, age, gender) as select sid, SNAME, SAGE, ssex from student where ssex = 'F'

You can view information through data dictionary:

SELECT TEXT FROM user_views WHERE view_name=UPPER('stu_view')

The result is as follows:

Select sid, SNAME, SAGE, ssex from student where ssex = 'F'

Updatable View

Whether a view can be updated depends on the statement used to create the view. Generally, the more complex the view is, the less likely it is to be updated.

Create the following view:

CREATE OR REPLACE VIEW stu_age_view(SID,SNAME,SAGE,SSEX)AS SELECT SID,SNAME,SAGE+1,SSEX FROM student 

Update it:

UPDATE stu_age_view SET sname='SIEGE' WHERE sname='SAM'

Modified successfully.

Then, change the age column:

UPDATE stu_age_view SET sage=10 WHERE sname='SIEGE'

This times ORA-01733 error (Virtual Columns cannot be modified)

We can view the columns in the view based on the data dictionary and modify them:

SELECT column_name,UPDATABLE,insertable,deletable FROMuser_updatable_columns WHERE table_name=UPPER('stu_age_view')

The result is as follows:

  COLUMN_NAME UPDATABLE   INSERTABLE  DELETABLE      SID                         YES           YES                 YES      SNAME                  YES            YES                 YES      SAGE                      NO            NO                 NO      SSEX                       YES            YES               YES

The name column cannot be changed.

If with check option is specified:

CREATE OR REPLACE VIEW stu_check_view(SID,SNAME,SAGE,SSEX)AS SELECT SID,SNAME,SAGE,SSEX FROM student  WHERE sname='SIEGE'WITH CHECK OPTION 

If you modify the View:

UPDATE  stu_check_view SET sname='kate'

A ORA-01402 error is reported, which violates the constraints of the with check option clause ).