一、实验目的及要求
掌握Oracle的常用对象的操作方法。会使用常用对象解决一些实际问题。
二、实验主要内容
(1)表结构的建立、修改、查看、删除操作。 (2)索引的建立、修改、查看、删除操作。 (3)视图的建立、查询、修改、删除操作。
(4)同义词的建立、查询、修改、删除操作，比较对同义词的操作与对原数据库对象的操作是否一致。
(5)掌握序列的建立、查询、修改、删除操作，利用序列向数据库表中插入数据。
三、实验仪器设备
在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器（企业版），客户机成功安装Oracle 11g客户端软件，网络服务配置正确，数据库和客户端正常工作。
四、实验步骤 表的操作
1．创建表结构
利用命令行方式将下列各表建立到员工医疗保险系统数据库中。表结构如附录员工医疗保险系统表1-表7所示。
SQL> /* ======创建企业(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('企业','事业','私有')), 5 baddress char(20), 6 btel char(13) 7 );
表已创建。
SQL> /* ======创建医院(hospital)表======*/ SQL> create table hospital(
2 hno char(5) primary key, 3 hname char(40) not null, 4 haddress char(60) 5 );
表已创建。
SQL> /* ======创建医保卡(card)表======*/
SQL> create table card(
2 cno char(15) primary key,
3 ctype char(4) constraint CK_2 check(ctype in('企业','事业','灵活就业')), 4 cmoney number(7,2) not null 5 );
表已创建。
SQL> /* ======创建员工(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(10) references business(bno) 10 );
表已创建。
SQL> /* ======创建就诊表(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 );
表已创建。
SQL> /* ======创建消费表(consume)======*/ SQL> /* ======创建消费表(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 );
表已创建。
SQL> /* ======创建消费表(consume)======*/ SQL> 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 );
表已创建。
2．查看表结构
利用SQL*Plus或iSQL*Plus从数据字典DBA_TAB _COLUMNS查看员工医疗保险系
统所有表的字段信息。
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从数据字典DBA_ CONSTRAINTS查看员工医疗保险系统所有表的约束信息。
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME
IN('BUSINESS','CARD','STAFF','CONSUME','HOSPITAL','SEE','INSURANCE');
3．删除表结构
利用SQL*Plus或iSQL*Plus删除员工表3，看能否成功。从原理上解释原因，同时记录外键约束表删除顺序的影响。
DROP TABLE STAFF;
索引操作
1.创建索引
利用SQL*Plus或iSQL*Plus为医院表的医院名称创建索引，并以降序排列，索引名为“hospital_name_index”。
create index hospital_name_index on hospital(hname desc);
2．查看索引
利用SQL*Plus或iSQL*Plus从DBA_INDEXES数据字典中查看员工医疗保险系统所有索引的信息。
select index_name,index_type,table_name from DBA_INDEXES where table_name
in('BUSINESS','CARD','SEE','STAFF','CONSUME','HOSPITAL','INSURANCE');
3．删除索引
利用SQL*Plus或iSQL*Plus将“hospital_name_index”索引删除。 DROP INDEX hospital_name_index;
视图操作
1．创建视图
利用SQL*Plus或iSQL*Plus为实现显示医保卡信息创建视图，该视图中包括医保卡信息、医保卡所属人信息和所属人单位信息，视图名为“ygbx_card_view”。
create or replace view ygbx_card_view as select
c.cno,c.ctype,c.cmoney,b.bno,b.bname,s.sno,s.sname,s.ssex,s.saddress,s.stel
from card c,staff s,business b
where c.cno = s.cno and s.bno = b.bno
2．查看视图
利用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．创建同义词
利用SQL*Plus或iSQL*Plus创建企业表同义词，名为“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．创建序列
利用SQL*Plus或iSQL*Plus创建序列，该序列最大值为“28000”，最小值为“60”，步长为“1”，可循环，序列名为“ygbx_seq1”。
create sequence ygbx_seql maxvalue 28000 minvalue 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”，将该序列最大值设为“82000”，最小值设为“100”，步长设为“5”。
alter sequence ygbx_seql maxvalue 82000 minvalue 59 increment by 5;
4．删除序列
利用SQL*Plus或iSQL*Plus删除序列“ygbx_seq1”。 drop sequence ygbx_seql;
主要算法和程序清单
SQL> /* ======创建企业(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('企业','事业','私有')), 5 baddress char(20), 6 btel char(13) 7 );
表已创建。
SQL> /* ======创建医院(hospital)表======*/ SQL> create table hospital(
2 hno char(5) primary key, 3 hname char(40) not null, 4 haddress char(60) 5 );
表已创建。
SQL> /* ======创建医保卡(card)表======*/ SQL> create table card(
2 cno char(15) primary key,
3 ctype char(4) constraint CK_2 check(ctype in('企业','事业','灵活就业')), 4 cmoney number(7,2) not null 5 );
表已创建。
SQL> /* ======创建员工(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(10) references business(bno) 10 );
表已创建。
SQL> /* ======创建就诊表(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 );
表已创建。
SQL> /* ======创建消费表(consume)======*/ SQL> /* ======创建消费表(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 );
表已创建。
SQL> /* ======创建消费表(consume)======*/ SQL> 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 );
表已创建。
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS WHERE TABLE_NAME
IN('BUSINESS','CARD','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','INSURANCE');
DROP INDEX hospital_name_index;
create or replace view ygbx_card_view as select
c.cno,c.ctype,c.cmoney,b.bno,b.bname,s.sno,s.sname,s.ssex,s.saddress,s.stel
from card c,staff s,business b
where c.cno = s.cno and s.bno = b.bno select * from ygbx_card_view; drop view ygbx_card_view;
CREATE SYNONYM qyb FOR BUSINESS; SELECT * FROM QYB; DROP SYNONYM QYB;
create sequence ygbx_seql maxvalue 28000 minvalue 60 increment by 1 cycle;
select ygbx_seql.currval from dual; alter sequence ygbx_seql maxvalue 82000 minvalue 59 increment by 5; drop sequence ygbx_seql
疑难小结：
本次实验中只要多锻炼，记住语法就太大问题，需要注意的是在建表的时候主键/外键的相互引用，另外关键字必须记清楚，这就要求要经常锻炼。
附录：员工医疗保险系统表
表1 企业（business）表结构
表2-6 企业 (business)表结构 字段名 bno bname btype baddress btel 含 义 企业编号 企业名称 企业类型 企业地址 联系电话 字段类型 char(10) char(50) char(4) char(20) char(13) 主键 非空 其值只能取“企业”、“事业”、“私有” — —
表2医院（hospital）表结构 表2-7 医院(hospital)表结构约 束 字段名 hno hname haddress 含 义 医院编号 医院名称 医院地址 字段类型 char(5) char(40) char(60) 主键 非空 — 约 束 字段名 cno ctype cmoney 含 义 医保卡号 医保类型 医保余额
表2-8 医保卡 (card)表结构 字段类型 char(15) char(8) number(7，2) 主键 其值只能取“企业”、“事业”、“灵活就业” 非空 约 束 表3医保卡（card）表结构
表4员工（staff）表结构
表2-9 员工(staff)表结构 字段名 sno sname ssex sbirthday saddress stel cno bno 含 义 员工编号 员工姓名 员工性别 出生日期 员工住址 联系电话 医保卡号 企业编号 字段类型 char(5) char(20) char(2) date char(20) char(15) char(15) char(10) 主键 非空 其值只能取“男”和“女” — — 要求每个员工的联系电话不能相同 与card表中的cno外键关联 与business表中的bno外键关联 约 束 字段名 sno hno sdate 含 义 员工编号 医院编号 就诊日期
表2-10 就诊(see)表结构 字段类型 char(5) char(5) date 约 束 (sno，hno，sdate)联合主键，与staff表中的sno外键关联，且级联删除 与hospital表中的hno外键关联 —
表5就诊表（see）结构
字段名 cno hno csdate mname mnum csmoney 含 义 表2-11 消费(consume)表结构 字段类型 char(15) char(5) date char(20) int number(7，2) 约 束 (cno，hno，csdate)联合主键，与card表中的cno外键关联，且级联删除 与hospital表中的hno外键关联 非空 — 非空 非空
7医保（insurance）表结构 表表2-12 医保(insurance)表结构 表6消费（consume）表结构
医保卡号 医院编号 消费日期 药品名称 药品数量 消费金额 字段名 idate cno imoney bno 含 义 存款日期 医保卡号 存款金额 企业编号 字段类型 date char(15) number(5，2) char(10) 约 束 (idate，cno)联合主键 与card表中的cno外键关联 非空 与business表中的bno外键关联
因篇幅问题不能全部显示，请点此查看更多更全内容