一、实验目的及要求
掌握Oracle的常用对象的操作方法。会使用常用对象解决一些实际问题。
二、实验主要内容
- 表结构的建立、修改、查看、删除操作。
- 索引的建立、修改、查看、删除操作。
- 视图的建立、查询、修改、删除操作。
- 同义词的建立、查询、修改、删除操作,比较对同义词的操作与对原数据库对象的操作是否一致。
- 掌握序列的建立、查询、修改、删除操作,利用序列向数据库表中插入数据。
三、实验仪器设备
在局域网环境下,有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器(企业版),客户机成功安装Oracle 11g客户端软件,网络服务配置正确,数据库和客户端正常工作。
四、实验步骤
1. 创建表结构
利用命令行方式将下列各表建立到员工医疗保险系统数据库中。表结构如附录员工医疗保险系统表1-表7所示。
create table business(
Bno char(10) primary key ,
Bname char(50) not null,
Btype char(4) constraint OK_1 check(btype in (‘企业’,’事业’,’私有’)),
Baddress char(20),
Btel char(13)
);
Create table card(
Hno char(5) primary key ,
Ctype char(8) constraint OK_2 check(ctype in(‘企业’,‘事业’,’灵活就业’)),
Cmoney number (7,2) not null
);
Create table staff (
Sno char(5) primary key ,
Sname char(20) not null ,
Ssex char(2) check (ssex in (‘男’,’女’)),
Sbirthday date,
Saddress char(20) ,
Stel char(26) unique,
Cno char(15) references card(cno),
Bno char(10) references business(bno)
);
Create table see(
Sno char (5) references staff(sno) ,
Hno char(5) references hospital(hno) ON DELETE CASCADE,
Sdate date ,
constraint S_PK primary key (sno , hno, sdate)
);
Create table consume (
Con char (15) references card(cno) ON DELETE CASCADE,
Hno char(5) references hosital (hno),
Csdate date not null,
Mname char(20),
Mnum int not null,
Csmoney number(7,2) not null ,
Constraint C_PK primary key (cno ,hno ,csdate)
);
Create table insurance(
Idate date,
Cnop char(15) references card(cno),
Imoney number(5,2) not null ,
Bon char(10) references business (bno) ,
Constraint I_PK primary key(idate ,cno)
);
Create table hospital(
Hno char (5) primary key ,
Hname char(40) not null ,
Haddress char (60)
);
2.查看表结构
(1) 利用OEM查看员工医疗保险系统所有表的字段信息和约束信息。
SELECT TABLE_NAME,COLUMN_NAME
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME
IN ('BUSINESS','CARD','CONSUME','HOSPITAL','SEE','INSURANCE','STAFF');
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME
IN ('BUSINESS','CARD','CONSUME','HOSPITAL','SEE','INSURANCE','STAFF');
(2) 利用SQL*Plus或PL/SQL Developer从数据字典DBA_TAB _COLUMNS查看员工医疗保险系统所有表的字段信息。
SELECT TABLE_NAME,COLUMN_NAME
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME
IN ('BUSINESS','CARD','CONSUME','HOSPITAL','SEE','INSURAINCE','STAFF');
(3) 利用SQL*Plus或PL/SQL Developer从数据字典DBA_ CONSTRAINTS查看员工医疗保险系统所有表的约束信息。
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME
IN ('BUSINESS','CARD','CONSUME','HOSPITAL','SEE','INSURAINCE','STAFF');
3.修改表结构
(1) 利用SQL*Plus或PL/SQL Developer将表“staff_sql”重新命名为“staff_sql0”。
ALTER TABLE STAFF_SQL RENAME TO STAFF_SQL0 ;
(2) 利用企业管理器为“staff_sql0”表添加“age INT”字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 add age int;
desc staff_sql0;
(3) 利用SQL*Plus或PL/SQL Developer为“staff_sql0”表添加“salary NUMBER(5,2)”、“salary_add NUMBER(3,1)”两个字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 add salary NUMBER(5,2);
alter table staff_sql0 add salary_add NUMBER(3,1);
desc staff_sql0;
(4) 利用SQL*Plus或PL/SQL Developer向“staff_sql0”表添加“sname”字段惟一性约束,从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。
ALTER TABLE STAFF_SQL0
ADD CONSTRAINT SAF_SNAM_UNIQUE
UNIQUE (SNAME);
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME
IN ('STAFF_SQL0');
(5) 利用SQL*Plus或PL/SQL Developer删除“staff_sql0”表上“sname”字段惟一性约束,从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。
alter table STAFF_SQL0 drop constraint SAF_SNAM_UNIQUE;
commit;
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME
IN ('STAFF_SQL0');
(6) 利用企业管理器从“staff_sql0”表删除“age”字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table STAFF_SQL0 drop (AGE);
DESC STAFF_SQL0;
(7) 利用SQL*Plus或PL/SQL Developer从“staff_sql0”表删除“salary”、“salary_add”两个字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table STAFF_SQL0 drop (salary,salary_add);
DESC STAFF_SQL0;
(8) 利用SQL*Plus或PL/SQL Developer将“staff_sql0”表“sname”字段长度修改为30,利用DESC命令查看“staff_sql0”表的字段信息。
alter table STAFF_SQL0 modify sname varchar2(30);
DESC STAFF_SQL0;
4. 删除表结构
(1) 利用SQL*Plus或PL/SQL Developer删除员工表3,看能否成功。从原理上解释原因,同时记录外键约束表删除顺序的影响。
drop table staff-sql2;
(2) 利用企业管理器删除员工表2,看能否成功。从原理上解释原因。
不能删除成功,Staff表中的cno和bno分别和card表中的cno、business表中的bno外建约束。
索引、视图、同义词及序列操作
1.创建索引
(1) 利用企业管理器为医院表的医院名称创建索引,并以降序排列,索引名为“hospital_name_index”。
CREATE INDEX hospital_name_index ON hospital(HNAME desc);
(2) 利用SQL*Plus或PL/SQL Developer为员工表的员工姓名、员工性别、出生年月排序,以员工姓名升序、员工性别降序、出生年月降序排列,索引名为“staff_info_index”。
CREATE INDEX staff_info_index ON STAFF(SNAME ,SSEX desc ,SBIRTHDAY desc);
2.查看索引
(1) 利用企业管理器查看“ygbx_user”方案下有几个隶属于该方案的索引,有几个系统创建的索引,有几个用户创建的索引。
(2) 利用SQL*Plus或PL/SQL Developer从DBA_INDEXES数据字典中查看员工医疗保险系统所有索引的信息。
SELECT *
FROM DBA_INDEXES
WHERE TABLE_NAME
IN ('BUSINESS','CARD','CONSUME','HOSPITAL','SEE','INSURANCE','STAFF')
(3) 利用SQL*Plus或PL/SQL Developer从DBA_INDEXES数据字典中查看“staff_info_index”索引的信息,并查看该索引列的顺序及状态。
SELECT *
FROM DBA_INDEXES
WHERE INDEX_NAME
IN ('STAFF_INFO_INDEX')
3.删除索引
(1) 利用企业管理器删除“hospital_name_index”索引。
(2) 利用SQL*Plus或PL/SQL Developer将“staff_info_index”索引删除。
drop index staff_info_index;
select * from user_ind_columns where index_name='staff_info_index';
4.创建视图
(1) 利用企业管理器为实现显示医保卡信息创建视图,该视图中包括医保卡信息、医保卡所属人信息和所属人单位信息,视图名为“ygbx_card_view”。
(2) 利用SQL*Plus或PL/SQL Developer为实现员工持医保卡到医院消费的功能创建视图,该视图中包括员工的信息、医保卡信息、医保信息和消费等信息,视图名为“consume_view”。
CREATE OR REPLACE VIEW consume_view
AS
SELECT staff.sno,staff.sname,staff.ssex,staff.saddress,staff.stel
,card.cno,card.ctype,card.cmoney
FROM card,staff WHERE card.cno=staff.cno
(3) 利用SQL*Plus或PL/SQL Developer为企业医保缴费信息功能创建视图,该视图中包括医保缴费的企业信息,医保卡信息和企业医保缴费费用等信息,视图名为“insurnce_view”。
“insurnce_view”。
CREATE OR REPLACE VIEW insurnce_view
AS
SELECT business.bname,insurance.imoney
,card.cno,card.ctype,card.cmoney
FROM card,staff,business,insurance WHERE card.cno=staff.cno
and staff.cno=insurance.cno
and staff.bno=business.bno
(4) 利用SQL*Plus或PL/SQL Developer为企业表创建视图,视图名为“business_view”。
CREATE OR REPLACE VIEW insurnce_view
AS
SELECT *
FROM business
5.查看视图
(1) 利用企业管理器查看“ygbx_user”方案下的视图。
(2) 利用企业管理器查看“consume_view”视图的信息。
(3) 利用SQL*Plus或PL/SQL Developer查看“card_view”视图的子查询语句。
(4) 利用SQL*Plus或PL/SQL Developer显示“insurance_view”视图的信息。
Select * from insurance_vew ;
6.视图数据的更新
(1) 利用SQL*Plus或PL/SQL Developer向“business_view”插入一个记录,企业编号为“B1997010287”,企业名称为“格林制药”,企业类型为“企业”,企业地址为“鸭绿江街98号”,联系电话为“84692315”。
insert into business_vew values( B1997010287,’格林制药’,’企业’,’鸭绿江街98号’,84692315);
(2) 利用SQL*Plus或PL/SQL Developer查看“business”表是否有变化。
Select * from business ;
7.删除视图
(1) 利用企业管理器删除“business_view”视图。
(2) 利用企业管理器删除“card_view”视图。
(3) 利用SQL*Plus或PL/SQL Developer删除“consume_view”视图。
drop view consume_view;
(4) 利用SQL*Plus或PL/SQL Developer删除“insurnce_view”视图。
drop view insurnce_view;
8.创建同义词
(1) 利用企业管理器创建企业表同义词,名为“qyb”。
(2) 利用SQL*Plus或PL/SQL Developer创建医保卡表的同义词,名为“ybk”。
create synonym ybk for business;
9.查询同义词
(1) 利用企业管理器查看同义词“qyb”。
(2) 利用SQL*Plus或PL/SQL Developer查看同义词“ybk”。
select * from dba_synonyms ;
10.删除同义词
(1) 利用企业管理器删除同义词“qyb”。
(2) 利用SQL*Plus或PL/SQL Developer删除同义词“ybk”。
drop synonym ybk;
11.创建序列
(1) 利用企业管理器创建序列,该序列最大值为“28000”,最小值为“60”,步长为“1”,可循环,序列名为“ygbx_seq1”。
(2) 利用SQL*Plus或PL/SQL Developer创建序列,该序列最大值无限制,最小值为“1”,步长为“10”,序列名为“ygbx_seq2”。
create sequence ygbx_seq2 minvalue 1 nomaxvalue increment by 10 ;
12.查询序列
(1) 利用企业管理器查看序列“ygbx_seq1”。
(2) 利用SQL*Plus或PL/SQL Developer查看同义词“ygbx_seq2”。
select * from user_sequences;
13.修改序列
(1) 利用企业管理器修改序列“ygbx_seq1”,将该序列最大值设为“82000”,最小值设为“100”,步长设为“5”。
(2) 利用SQL*Plus或PL/SQL Developer修改序列“ygbx_seq2”,将该序列最大值设为“1000”。
alter sequence ygbx_seq2 maxvalue 1000 ;
14.删除序列
(1) 利用企业管理器删除序列“ygbx_seq1”。
(2) 利用SQL*Plus或PL/SQL Developer删除序列“ygbx_seq2”。
drop sequence scott.ygbx_seq2;
附录:员工医疗保险系统表
表1 企业(business)表结构
表2医院(hospital)表结构
表3医保卡(card)表结构
表4员工(staff)表结构
表5就诊表(see)结构
表6消费(consume)表结构
表7医保(insurance)表结构