6.4. View, sequence, synonym--oracle mode object

views View

is a virtual table that does not store data values.

Create or Replace View Patient Basic Information  as Select  from patient information table;

When a view is created, only the definition of the view is stored in the database dictionary, and the select query is not executed, only when the user queries the view, the system fetches the data from the base table as defined by the views.

Insert, update, and delete operations are available on the view.
In one view, you can include both updatable and non-updatable fields .
For example:

Create or Replace View Patient basic information (patient ID, hospitalization number, name, gender, patient age)  as Select Patient ID, hospitalization number, name, gender, age +1 from patient information table;

The age field is calculated from the source table "age" +1 , so the patient age field cannot be updated, and the other fields can be updated.
You can query which fields can be updated and which cannot be updated through the data dictionary user_updatable_columns view.

With CHECK option

Create or Replace View Stu_age_view (student number, student name, student age)  as Select Sid,sname,sage  from Student where name=' small white ';

-At this point, the view is inserted into the data, and the following actions are available:

Insert into Stu_age_view (student number, student name, student age)
Values (3, ' Little Red ', 20);

--query table student results as follows:

sid SNAME SSEX SAGE sclass
----------- ------------------
1     small white       men     &NBS p;22      1
2     xiaoming       men   &NBSP ;  21      1
3     Little Red         &N Bsp    

When updating a view, the "Where name= ' small white '" restriction is violated, inserting data into the table.
You can use the WITH CHECK option when creating a table, and when you insert a similar record with this clause,Oracle will prompt an error ora-01402: View with CHECK OPTION clause violation.

To delete a view:
Drop View Stu_age_view;

Sequence sequence:
A database object used to produce a series of unique numbers. does not occupy the actual storage space , just saves the definition in the dictionary.
Use sequences to automate the generation of primary key values.

Create sequence or create any sequence to have the permissions for creating a sequence

syntax for creating sequences:
create sequence  sequence_name  --Create sequence name
[increment by  INCREMENT ]  --increments the sequence value is N if n is a positive number increment, if the negative is decremented by default is 1
[start with  START ] 
[maxvalue n |  Nomaxvalue] --maximum
[minvalue  N | nominvalue] --minimum
[ cache n | Nocache]--pre-allocated and deposited into memory
[cycle |  NOCYCLE]; --loop/no loop

Two pseudo-columns:
Nextval --Returns the next value generated by the sequence
Currval --Returns the current value of the sequence
Nextval should be specified before Currval, both should be valid at the same time

To modify a sequence:
Alter sequence stu_seq increment by 2;
After the sequence is modified, the saved sequence values in the cache are all lost.

To delete a sequence:
Drop sequence stu_seq;

Synonym synonym:
Is an alias for a schema object such as a table, index, view, and so on. does not occupy any actual storage space.
divided into two categories: public synonyms, private synonyms

To create a synonym:
Create [public] synonym synonym_name for Schema_object;

To delete a synonym:
Drop public sysnonym Pub_sysnonym_name;

----Perform a lookup for all the synonyms that are not valid, generate the delete script as follows:

Select 'Drop'||Decode (S.owner,' Public','Public synonym','synonym' ||S.owner|| '.')||S.synonym_name|| ';'  as"Dropping invalid synonyms:" fromdba_synonyms SwhereTable_owner not inch('SYSTEM','SYS') andDb_link is NULL and  not exists(Select NULL  fromdba_objects owhereS.table_owner=O.owner andS.table_name=O.object_name);

----Query the current user, what are the objects that do not have synonyms created?

Select * fromdba_objects SwhereOwnerinch('Zlhis') andObject_typeinch('TABLE','PROCEDURE','FUNCTIONS') and  not exists(Select NULL  fromdba_synonyms oWhereO.table_owner=S.owner andO.table_name=S.object_name  andO.db_link is NULL)

6.4. View, sequence, synonym--oracle mode object