實(shí)驗(yàn)四oracle對(duì)象管理及使用

1、實(shí)驗(yàn)四 oracle對(duì)象管理及使用一、實(shí)驗(yàn)?zāi)康募耙笳莆誒racle的常用對(duì)象的操作方法。會(huì)使用常用對(duì)象解決一些實(shí)際問(wèn)題。二、實(shí)驗(yàn)主要內(nèi)容(1)表結(jié)構(gòu)的建立、修改、查看、刪除操作。(2)索引的建立、修改、查看、刪除操作。(3)視圖的建立、查詢、修改、刪除操作。(4)同義詞的建立、查詢、修改、刪除操作，比較對(duì)同義詞的操作與對(duì)原數(shù)據(jù)庫(kù)對(duì)象的操作是否一致。(5)掌握序列的建立、查詢、修改、刪除操作，利用序列向數(shù)據(jù)庫(kù)表中插入數(shù)據(jù)。三、實(shí)驗(yàn)儀器設(shè)備在局域網(wǎng)環(huán)境下，有一臺(tái)服務(wù)器和若干臺(tái)客戶機(jī)。服務(wù)器成功安裝Oracle 11g數(shù)據(jù)庫(kù)服務(wù)器（企業(yè)版），客戶機(jī)成功安裝Oracle 11g客戶端軟件，網(wǎng)絡(luò)服務(wù)配

2、置正確，數(shù)據(jù)庫(kù)和客戶端正常工作。四、實(shí)驗(yàn)步驟表的操作1創(chuàng)建表結(jié)構(gòu)利用命令行方式將下列各表建立到員工醫(yī)療保險(xiǎn)系統(tǒng)數(shù)據(jù)庫(kù)中。表結(jié)構(gòu)如附錄員工醫(yī)療保險(xiǎn)系統(tǒng)表1-表7所示。SQL> /* =創(chuàng)建企業(yè)(business)表=*/SQL> create table business ( 2 bno char(10) primary key, 3 bname char(50) not null, 4 btype char(4) constraint CK_1 check(btype in('企業(yè)','事業(yè)','私有'), 5 baddress ch

3、ar(20), 6 btel char(13) 7 );表已創(chuàng)建。SQL> /* =創(chuàng)建醫(yī)院(hospital)表=*/SQL> create table hospital( 2 hno char(5) primary key, 3 hname char(40) not null, 4 haddress char(60) 5 );表已創(chuàng)建。SQL> /* =創(chuàng)建醫(yī)?？?card)表=*/SQL> create table card( 2 cno char(15) primary key, 3 ctype char(4) constraint CK_2 check(cty

4、pe in('企業(yè)','事業(yè)','靈活就業(yè)'), 4 cmoney number(7,2) not null 5 );表已創(chuàng)建。SQL> /* =創(chuàng)建員工(staff)表=*/SQL> create table staff( 2 sno char(5) primary key, 3 sname char(20) not null, 4 ssex char(2) check (ssex in('男','女'), 5 sbirthday date, 6 saddress char(20), 7 stel c

5、har(15) unique, 8 cno char(15) references card(cno), 9 bno char(10) references business(bno) 10 );表已創(chuàng)建。SQL> /* =創(chuàng)建就診表(see)=*/SQL> create table see ( 2 sno char(5) references staff(sno), 3 hno char(5) references hospital(hno), 4 sdate date, 5 constraint S_PK primary key(sno,hno,sdate) 6 );表已創(chuàng)建。

6、SQL> /* =創(chuàng)建消費(fèi)表(consume)=*/SQL> /* =創(chuàng)建消費(fèi)表(insurance)=*/SQL> create table insurance ( 2 idate date, 3 cno char(15) references card(cno), 4 imoney number(5,2) not null, 5 bno char(10) references business(bno), 6 constraint I_PK primary key (idate,cno) 7 );表已創(chuàng)建。SQL> /* =創(chuàng)建消費(fèi)表(consume)=*/SQL&

7、gt; create table consume( 2 cno char(15) references card(cno), 3 hno char(5) references hospital(hno), 4 csdate date not null, 5 mname char(20), 6 mnum int not null, 7 csmoney number(7,2) not null, 8 constraint C_PK primary key(cno,hno,csdate) 9 );表已創(chuàng)建。2查看表結(jié)構(gòu)利用SQL*Plus或iSQL*Plus從數(shù)據(jù)字典DBA_TAB _COLUMNS

8、查看員工醫(yī)療保險(xiǎn)系統(tǒng)所有表的字段信息。SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME IN('BUSINESS','CARD','STAFF','CONSUME','HOSPITAL','SEE','INSURANCE');利用SQL*Plus或iSQL*Plus從數(shù)據(jù)字典DBA_ CONSTRAINTS查看員工醫(yī)療保險(xiǎn)系統(tǒng)所有表的約束信息。SELECT CONSTRAINT_

9、NAME,CONSTRAINT_TYPE,TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME IN('BUSINESS','CARD','STAFF','CONSUME','HOSPITAL','SEE','INSURANCE');3刪除表結(jié)構(gòu)利用SQL*Plus或iSQL*Plus刪除員工表3，看能否成功。從原理上解釋原因，同時(shí)記錄外鍵約束表刪除順序的影響。DROP TABLE STAFF;索引操作1.創(chuàng)建索引利用SQL*Plus或iS

10、QL*Plus為醫(yī)院表的醫(yī)院名稱創(chuàng)建索引，并以降序排列，索引名為“hospital_name_index”。create index hospital_name_index on hospital(hname desc);2查看索引利用SQL*Plus或iSQL*Plus從DBA_INDEXES數(shù)據(jù)字典中查看員工醫(yī)療保險(xiǎn)系統(tǒng)所有索引的信息。select index_name,index_type,table_name from DBA_INDEXES where table_name in('BUSINESS','CARD','SEE','

11、;STAFF','CONSUME','HOSPITAL','INSURANCE');3刪除索引利用SQL*Plus或iSQL*Plus將“hospital_name_index”索引刪除。DROP INDEX hospital_name_index;視圖操作1創(chuàng)建視圖利用SQL*Plus或iSQL*Plus為實(shí)現(xiàn)顯示醫(yī)保卡信息創(chuàng)建視圖，該視圖中包括醫(yī)?？ㄐ畔ⅰ⑨t(yī)?？ㄋ鶎偃诵畔⒑退鶎偃藛挝恍畔ⅲ晥D名為“ygbx_card_view”。create or replace view ygbx_card_view as select o,c.c

12、type,c.cmoney,b.bno,b.bname,s.sno,s.sname,s.ssex,s.saddress,s.stel from card c,staff s,business b where o = o and s.bno = b.bno2查看視圖利用SQL*Plus或iSQL*Plus查看“ygbx_card_view”視圖的信息。select * from ygbx_card_view;3刪除視圖利用SQL*Plus或iSQL*Plus刪除“ygbx_card_view”視圖。drop view ygbx_card_view;同義詞操作1創(chuàng)建同義詞利用SQL*Plus或iS

13、QL*Plus創(chuàng)建企業(yè)表同義詞，名為“qyb”。CREATE SYNONYM qyb FOR BUSINESS;2查詢同義詞利用SQL*Plus或iSQL*Plus查看同義詞“qyb”。SELECT * FROM QYB; 3刪除同義詞利用SQL*Plus或iSQL*Plus刪除同義詞“qyb”。DROP SYNONYM QYB;序列操作1創(chuàng)建序列利用SQL*Plus或iSQL*Plus創(chuàng)建序列，該序列最大值為“28000”，最小值為“60”，步長(zhǎng)為“1”，可循環(huán)，序列名為“ygbx_seq1”。create sequence ygbx_seql maxvalue 28000 minvalue

14、 60 increment by 1 cycle;2查詢序列利用SQL*Plus或iSQL*Plus查看序列“ygbx_seq1”。select ygbx_seql.currval from dual;3修改序列利用SQL*Plus或iSQL*Plus修改序列“ygbx_seq1”，將該序列最大值設(shè)為“82000”，最小值設(shè)為“100”，步長(zhǎng)設(shè)為“5”。alter sequence ygbx_seql maxvalue 82000 minvalue 59 increment by 5;4刪除序列利用SQL*Plus或iSQL*Plus刪除序列“ygbx_seq1”。drop sequence

15、ygbx_seql;主要算法和程序清單SQL> /* =創(chuàng)建企業(yè)(business)表=*/SQL> create table business ( 2 bno char(10) primary key, 3 bname char(50) not null, 4 btype char(4) constraint CK_1 check(btype in('企業(yè)','事業(yè)','私有'), 5 baddress char(20), 6 btel char(13) 7 );表已創(chuàng)建。SQL> /* =創(chuàng)建醫(yī)院(hospital)表=*/

16、SQL> create table hospital( 2 hno char(5) primary key, 3 hname char(40) not null, 4 haddress char(60) 5 );表已創(chuàng)建。SQL> /* =創(chuàng)建醫(yī)?？?card)表=*/SQL> create table card( 2 cno char(15) primary key, 3 ctype char(4) constraint CK_2 check(ctype in('企業(yè)','事業(yè)','靈活就業(yè)'), 4 cmoney numbe

17、r(7,2) not null 5 );表已創(chuàng)建。SQL> /* =創(chuàng)建員工(staff)表=*/SQL> create table staff( 2 sno char(5) primary key, 3 sname char(20) not null, 4 ssex char(2) check (ssex in('男','女'), 5 sbirthday date, 6 saddress char(20), 7 stel char(15) unique, 8 cno char(15) references card(cno), 9 bno char

18、(10) references business(bno) 10 );表已創(chuàng)建。SQL> /* =創(chuàng)建就診表(see)=*/SQL> create table see ( 2 sno char(5) references staff(sno), 3 hno char(5) references hospital(hno), 4 sdate date, 5 constraint S_PK primary key(sno,hno,sdate) 6 );表已創(chuàng)建。SQL> /* =創(chuàng)建消費(fèi)表(consume)=*/SQL> /* =創(chuàng)建消費(fèi)表(insurance)=*/SQL

19、> create table insurance ( 2 idate date, 3 cno char(15) references card(cno), 4 imoney number(5,2) not null, 5 bno char(10) references business(bno), 6 constraint I_PK primary key (idate,cno) 7 );表已創(chuàng)建。SQL> /* =創(chuàng)建消費(fèi)表(consume)=*/SQL> create table consume( 2 cno char(15) references card(cno),

20、3 hno char(5) references hospital(hno), 4 csdate date not null, 5 mname char(20), 6 mnum int not null, 7 csmoney number(7,2) not null, 8 constraint C_PK primary key(cno,hno,csdate) 9 );表已創(chuàng)建。SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME IN('BUSINESS','CARD&

21、#39;,'STAFF','CONSUME','HOSPITAL','SEE','INSURANCE');SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME IN('BUSINESS','CARD','STAFF','CONSUME','HOSPITAL','SEE','INSURANCE');DROP TABLE STAFF;create index hospital_name_index on hospital(hname desc);select index_name,index_type,table_name from DBA_INDEXES where table_name in('BUSINESS','CARD','SEE','STAFF','CONSUME','HOSPITAL','INSURANC