Oracle view and other object operations

View and other object operations

Experimental purpose

(1) Grasp the establishment of the view, query, modify, delete operations.

(2) to master the establishment of synonyms, query, modify, delete operations, compare the operation of the synonym to the original database object is consistent with the operation.

(3) Mastering sequence Establishment, query, modification, delete operation, using the sequence to insert data into the database table.

　　

Experimental content

1. Create a View

(1) Using Enterprise Manager to realize the display of health insurance card information to create a view, which includes health insurance card information, health Insurance card owner information and the owner's unit information, the view is named "Ygbx_card_view".

(1)

(2) Use Sql*plus or isql*plus to create a view of the employee's health insurance card to hospital consumption, which includes information about employees, Medicare card information, Medicare information, and consumption, and the view is named "Consume_view".

Sql> CREATE View Consume_view as select Staff.sno,sname,ssex,sbirthday,

2 Saddress,stel,staff.cno,staff.bno,ctype,cmoney,idate,imoney,see.hno,csdate,mname,mnum,csmoney

3 from Staff,insurance,card,consume,see

4 where Staff.sno=see.sno and See.hno=consume.cno and Consume.cno=card.cno and Card.cno=insurance.cno;

(3) using Sql*plus or Isql*plus to create a view of the enterprise health Insurance payment information function, which includes information about the enterprise information of medical insurance payment, Medicare card information and Enterprise health insurance payment fee, the view is named "Insurnce_view".

Sql> CREATE View Insurance_view as select Insurance.bno,bname,btype,baddress,btel,card.cno,ctype,cmoney,

2 Hno,csdate,mname,mnum,csmoney

3 from Insurance,business,card,consume

4 where Insurance.bno=business.bno and Insurance.cno=card.cno

5;

(4) Create a view with Sql*plus or Isql*plus for the enterprise table, with the view named "Business_view".

Sql> CREATE View Business_view as

2 Select Bno,bname,btype,baddress,btel

3 from business;

2. View View

(1) Use Enterprise Manager to view views under the "Ygbx_user" scenario.

(2) Use Enterprise Manager to view information for the Consume_view view.

(3) Use Sql*plus or isql*plus to view the subquery statement for the Card_view view.

Sql> Select text from user_views where view_name= ' Ygbx_card_uiew '

2;

(4) Use Sql*plus or isql*plus to display the information for the "Insurance_view" view.

Sql> Select View_name,view_type,read_only,text,text_length from User_views

2 where view_name= ' Insurance_view '

3;

3. Update of view data

(1) The use of Sql*plus or Isql*plus to "Business_view" insert a record, the enterprise number is "B1997010287", the Enterprise name is "Green pharmaceutical", the Enterprise type is "Enterprise", the Enterprise address is "Yalu River Street 98th", contact telephone for " 84692315 ".

Sql> ALTER TABLE Business Modify (bno char (20));

The table has changed.

sql> INSERT into Business_view values (' B1997010287 ', ' Green Pharma ', ' business ', ' Yalu River Street 98 number ', ' 84692315 ');

1 rows have been created .

(2) Use Sql*plus or isql*plus to see if the "Business" table is changed.

Sql> SELECT * from business;

4. Delete a view

(1) Use Enterprise Manager to delete the "Business_view" view.

(2) Use Enterprise Manager to delete the "Card_view" view.

(3) Delete the "Consume_view" view using Sql*plus or Isql*plus.

sql> drop View Consume_view;

(4) Delete the "Insurnce_view" view using Sql*plus or Isql*plus.

sql> drop View Insurance_view;

5. Create synonyms

(1) Use Enterprise Manager to create an enterprise table synonym, named "Qyb".

Sql> Grant create public synonym to Scott;

Authorization is successful.

(2) Use Sql*plus or isql*plus to create a synonym for the Medicare card table, called "YBK".

sql> create synonym YBK for card;

The synonym has been created.

6. Query synonyms

(1) Use Enterprise Manager to view the synonym "Qyb".

(2) Use Sql*plus or isql*plus to view synonyms "YBK".

Sql> SELECT * from user_synonyms where synonym_name= ' YBK ';

7. Delete Synonyms

(1) Use Enterprise Manager to delete the synonym "Qyb".

(2) Delete the synonym "YBK" using Sql*plus or Isql*plus.

sql> drop synonym ybk;

The synonym has been deleted.

8. Create a sequence

(1) Use Enterprise Manager to create a sequence with a maximum value of "28000", a minimum value of "60", a step of "1", a loop, and a sequence named "Ygbx_seq1".

(2) using Sql*plus or Isql*plus to create a sequence, the maximum value of the sequence is unlimited, the minimum value is "1", the Step is "10", and the sequence is named "YGBX_SEQ2".

sql> Create sequence ygbx_seq2 nocycle noorder nomaxvalue minvalue 1

2 increment by ten start with 1;

The sequence has been created.

9. Query sequence

(1) Use Enterprise Manager to view the sequence "ygbx_seq1".

(2) Use Sql*plus or isql*plus to view synonyms "YGBX_SEQ2".

Sql> SELECT * from user_sequences where sequence_name= ' ygbx_seq2 ';

. Modifying a sequence

(1) Using Enterprise Manager to modify the sequence "ygbx_seq1", the maximum value of the series is set to "82000", the minimum value is set to "100", the step is set to "5".

The minimum value cannot be greater than the current value, so the minimum value is

(2) using Sql*plus or Isql*plus to modify the sequence "YGBX_SEQ2", the maximum value of the sequence is set to "1000".

sql> alter sequence YGBX_SEQ2 MaxValue 1000;

The sequence has changed.

One by one. Delete a sequence

(1) Use Enterprise Manager to delete the sequence "ygbx_seq1".

(2) Delete the sequence "ygbx_seq2" using Sql*plus or Isql*plus.

sql> drop sequence ygbx_seq2;

The sequence has been deleted.

Appendix: Employee Health Insurance system table

Table 1 Enterprise (business) Table Structure

Table 2 Hospital (Hospital) Table structure

Table 3 Health Insurance card table structure

Table 4 Employee (staff) Table structure

Table 5 consultation form (see) structure

Table 6 consumption (consume) Table structure

Table 7 Health insurance (insurance) Table structure

Experiment Summary:

1. You can modify the view by using the CREATE OR REPLACE View statement, essentially deleting the original view and rebuilding the view, but preserving the various permissions granted on that view.

2. The sequence is used to generate a unique ordinal of a database object, which is used to sequentially produce a contiguous integer that is not duplicated for multiple database users. You typically use sequences to automatically generate primary key values in a table. The sequence does not occupy the actual storage space, only the definition description of the sequence is stored in the data dictionary.

3. A view is a representation of the data extracted from one or more tables or views. The view is a "virtual" table because there is only a definition of the view in the database, and no actual corresponding table exists. When you operate on a view, the system generates data temporarily based on its definition.

4. You can delete a view using the drop view statement. When you delete a view, the definition of the view is removed from the data dictionary, and permissions on the view are recycled, but there is no effect on the database table.

5. A synonym is an alias for a table, index, view, or other schema object in a database. Using synonyms, on the one hand, provides some security assurance for database objects, such as the ability to hide the actual name and owner information of an object, or to hide the location information of remote objects in a distributed database, and the other is to simplify object access.

6. Once the view is created, you can manipulate it, including data queries,DML operations, and so on. Because views are virtual tables, the operation of the view is ultimately converted to the operation of the base table.

Oracle view and other object operations