Oracle view and other object operations

Oracle view and other object operations
View and other object operations

Lab Purpose

(1) master the creation, query, modification, and deletion operations of views.

(2) establish, query, modify, and delete synonyms, and compare the operations on synonyms with those on the original database objects.

(3) Establish, query, modify, and delete sequences, and insert data into database tables using sequences.

Lab content

1. Create a view

(1) The Enterprise Manager is used to create a view for displaying the information about the medical insurance card. The view includes the information about the medical insurance card, the information about the person who owns the medical insurance card, and the information about the person's organization. The view is named "ygbx_card_view ".

(2) use SQL * Plus or iSQL * Plus to create a view of the functions of employees holding medical insurance cards to hospital for consumption, this view includes employee information, medical insurance card information, medical insurance information, and consumption information. The view name is "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) use SQL * Plus or iSQL * Plus to create a view of the enterprise's medical insurance payment information function. This view includes the enterprise information, medical insurance card information, and enterprise medical insurance payment, the view name is "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) use SQL * Plus or iSQL * Plus to create a view for an enterprise table named "business_view ".

SQL> create view business_view

2 select bno, bname, btype, baddress, btel

3 from business;

2. view the view

(1) Use the Enterprise Manager to view the view under the "ygbx_user" solution.

(2) Use the Enterprise Manager to view the information in the "consume_view" view.

(3) use SQL * Plus or iSQL * Plus to view subquery statements in 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 "insurance_view" view information.

SQL> select view_name, view_type, read_only, text, text_length from user_views

2 where view_name = 'Insurance _ view'

3;

3. View data update

(1) use SQL * Plus or iSQL * Plus to insert a record into "business_view". The enterprise number is "B1997010287". The enterprise name is "Green Pharmaceuticals" and the enterprise type is "enterprise ", the company address is "98 Yalu River Street" and the contact number is "84692315 ".

SQL> alter table business modify (bno char (20 ));

The table has been changed.

SQL> insert into business_view values ('b1997010287 ', 'green pharmaceuticals', 'business', '98 Yalu Street ', '123 ');

One row has been created.

(2) use SQL * Plus or iSQL * Plus to check whether the "business" table has changed.

SQL> select * from business;

4. delete a view

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

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

(3) use SQL * Plus or iSQL * Plus to delete the "consume_view" view.

SQL> drop view consume_view;

(4) use SQL * Plus or iSQL * Plus to delete the "insurnce_view" view.

SQL> drop view insurance_view;

5. Create a synonym

(1) Create an enterprise table synonym named "qyb" using the Enterprise Manager ".

SQL> grant create public synonym to scott;

Authorization successful.

(2) use SQL * Plus or iSQL * Plus to create the synonym for the medical insurance card table, called "ybk ".

SQL> create synonym ybk for card;

You have created a synonym.

6. query Synonyms

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

(2) use SQL * Plus or iSQL * Plus to view the synonym "ybk ".

SQL> select * from user_synonyms where synonym_name = 'ybk ';

7. Delete Synonyms

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

(2) use SQL * Plus or iSQL * Plus to delete the synonym "ybk ".

SQL> drop synonym ybk;

The synonym has been deleted.

8. Create a sequence

(1) create a sequence using the Enterprise Manager. The maximum value of the sequence is "28000", the minimum value is "60", the step is "1", and the sequence name is "ygbx_seq1 ".

(2) create a sequence using SQL * Plus or iSQL * Plus. the maximum value of the sequence is unlimited. The minimum value is "1", the step size is "10", and the sequence name is "ygbx_seq2 ".

SQL> create sequence ygbx_seq2 nocycle noorder nomaxvalue minvalue 1

2 increment by 10 start with 1;

The sequence has been created.

9. query Sequence

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

(2) use SQL * Plus or iSQL * Plus to view the synonym "ygbx_seq2 ".

SQL> select * from user_sequences where sequence_name = 'ygbx _ seq2 ';

10. Modify the sequence

(1) Use the Enterprise Manager to modify the sequence "ygbx_seq1", set the maximum value of the sequence to "82000", the minimum value to "100", and the step size to "5 ".

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

(2) use SQL * Plus or iSQL * Plus to modify the sequence ygbx_seq2 and set the maximum value of the sequence to 1000 ".

SQL> alter sequence ygbx_seq2 maxvalue 1000;

The sequence has been changed.

11. Delete Sequence

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

(2) use SQL * Plus or iSQL * Plus to delete the sequence "ygbx_seq2 ".

SQL> drop sequence ygbx_seq2;

The sequence has been deleted.

Appendix: Employee Medical Insurance System Table

Table 1 Enterprise table structure

Table 2 hospital table structure

Table 3 medical insurance card (card) Table Structure

Table 4 staff table structure

Table 5 see Structure

Table 6 consume table structure

Table 7 insurance table structure

Experiment summary:

1. You can use the create or replace view statement to modify a VIEW. In essence, you can delete the original VIEW and recreate it. However, all the permissions granted to the VIEW are retained.

2. sequences are used to generate database objects with unique sequence numbers. They are used to generate continuous integers that are not repeated in sequence for multiple database users. Generally, the sequence is used to automatically generate the primary key value in the table. The sequence does not occupy the actual storage space. In the data dictionary, only the definition description of the sequence is stored.

3. A view is a form of data extracted from one or more tables or views. In the database, there is only a view definition, but there is no actual corresponding "table". Therefore, the view is a "virtual" table. When you operate a view, the system temporarily generates data according to the view definition.

4. You can use the drop view statement to delete a VIEW. After a view is deleted, its definition is deleted from the data dictionary. permissions on the view are revoked, but the database table is not affected.

5. synonyms are aliases of tables, indexes, views, and Other schema objects in the database. Synonyms can be used to ensure the security of database objects. For example, the actual names and owner information of objects can be hidden or the location information of remote objects in distributed databases can be hidden; another aspect is to simplify object access.

6. After creating a view, you can perform operations on the view, including data query and DML operations. Because the view is a "virtual table", operations on the view are eventually converted to operations on the basic table.