实验三 Oracle对象管理及使用
一、实验目的及要求
掌握Oracle的常用对象的操作方法。会使用常用对象解决一些实际问题。
二、实验主要内容
(1) 表结构的建立、修改、查看、删除操作。
(2) 索引的建立、修改、查看、删除操作。
(3) 视图的建立、查询、修改、删除操作。
(4) 同义词的建立、查询、修改、删除操作,比较对同义词的操作与对原数据库 对象的操作是否一致。
(5) 掌握序列的建立、查询、修改、删除操作,利用序列向数据库表中插入数据。
三、实验仪器设备
在局域网环境下,有一台服务器和若干台客户机。服务器成功安装Oracle 10g数据库服务器(企业版),客户机成功安装Oracle 10g客户端软件,网络服务配置正确,数据库和客户端正常工作。
四、实验步骤
1.创建表结构
利用命令行方式将下列各表建立到员工医疗保险系统数据库中。表结构如附录员工医疗保险系统表1-表7所示。
(1)
create table business(
bno char(10) primary key,
bname char(50) not null,
btype char(4) constraint ck_btype check(btype in(‘企业’,'事业’,‘私有’)),
baddress char(20),
btel char(13)
);
(2)
create table hospital(
hno char(5) primary key,
hname char(40) not null,
haddress char(60)
);
(3)
create table card(
cno char(15) primary key,
ctype char(4) constraint ck_ctype check(ctype in(‘企业’,‘事业’,‘灵活就业’)),
cmoney number(7,2) not null
);
(4)
create table staff(
sno char(5) primary key,
sname char(20) not null,
ssex char(2) check (ssex in('男’,‘女’)),
sbirthday date,
stel char(15) unique,
cno char(15) references card(cno),
bno char(10) references business(bno)
);
(5)
create table see(
sno char(5) references staff(sno),
hno char(5) references hospital(hno),
sdate date,
constraint s_pk primary key(sno,hno,sdate)
);
(6)
create table consume(
cno char(15) references card(cno),
hno char(15) references hospital(hno),
csdate date not null,
csmoney number(7,2) not null,
constraint c_pk primary key(cno,hno,csdate)
);
(6)
create table insurance(
idate date,
cno char(15) references card(cno),
imoney number(5,2) not null,
bno char(10) references business(bno),
constraint i_pk primary key(idate,cno)
);
2.查看表结构
(1) 利用数据字典查看员工医疗保险系统所有表的字段信息和约束信息。
从数据字典DBA_TAB _COLUMNS查看员工医疗保险系统所有表的字段信息。
从数据字典DBA_ CONSTRAINTS查看员工医疗保险系统所有表的约束信
息。
(1)
select table_name,column_name,data_type
from dba_tab_columnS
in(‘BUSINESS’,‘CARD’,‘STAFF’,‘CONSUME’,‘HOSPITAL’,‘INSURANCE’);
select constraint_name,constraint_type,table_name
from dba_constraints
where table_name
in(‘BUSINESS’,‘CARD’,‘STAFF’,‘CONSUME’,‘HOSPITAL’,‘SEE’,‘INSURANCE’);
3.修改表结构
(1) 将表“staff_sql”重新命名为“staff_sql0”。
alter table staff rename to staff_sql0;
(2) 为“staff_sql0”表添加“age ”字段,利用DESC命令查看“staff_sql0”表的字段信息。(3) 为“staff_sql0”表添加“salary NUMBER(5,2)”、“salary_add NUMBER(3,1)”两个字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 add age int;
(3) 为“staff_sql0”表添加“salary NUMBER(5,2)”、“salary_add NUMBER(3,1)”两个字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 add(salary number(5,2),salary_add number(3,1));
(4) 向“staff_sql0”表添加“sname”字段惟一性约束,从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。
alter table staff_sql0
add constraint
sname unique(SNAME);
(5) 删除“staff_sql0”表上“sname”字段惟一性约束,从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。
alter talbe staff_sql0
drop constraint
sname;
(6) 从“staff_sql0”表删除“age”字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 drop column age;
(7) 从“staff_sql0”表删除“salary”、“salary_add”两个字段,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 drop(salary,salary_add);
(8) 将“staff_sql0”表“sname”字段长度修改为30,利用DESC命令查看“staff_sql0”表的字段信息。
alter table staff_sql0 modify(sname char(30));
4. 删除表结构
(1) 删除员工表3,看能否成功。从原理上解释原因,同时记录外键约束表删除顺序的影响。
drop table card;
(2) 删除员工表2,看能否成功。从原理上解释原因
drop table hospital;
索引、视图、同义词及序列操作
1.创建索引
(1) 为医院表的医院名称创建索引,并以降序排列,索引名为“hospital_name_index”。
create index hospital_name_index on hospital(hname desc);
(2) 为员工表的员工姓名、员工性别、出生年月排序,以员工姓名升序、员工性别降序、出生年月降序排列,索引名为“staff_info_index”。
create index staff_info_index on satff_sql0(sname,ssex,desc,sbirthday);
2.查看索引
(1) 从DBA_INDEXES数据字典中查看员工医疗保险系统所有索引的信息。
select index_name,index_type,table_name
from DBA_INDEXES
where table_name in(‘BUSINESS’,‘CARD’,‘STAFF’,‘CONSUME’,‘HOSPITAL’);
(2) 从DBA_INDEXES数据字典中查看“staff_info_index”索引的信息,并查看该索引列的顺序及状态。
select index_name,index_type,status,uniqueness,num_rows,blevel
from dba_indexes
where index_name=‘STAFF_INFO_INDEX’;
3.删除索引
(1) 删除“hospital_name_index”索引。
drop index hospital_name_index;
4.创建视图
(1) 创建视图,该视图中包括医保卡信息、医保卡所属人信息和所属人单位信 息,视图名为“ygbx_card_view
create view ygbx_card_view as
select c.cno,c.ctype,c.cmoney,b.bno,b.bname,s.sno,s.sname,s.ssex,s.address,s.stel
from card c.staff_sql0 s.business b
where c.cno=s.cno and s.bno=b.bno;
5.创建同义词
(1) 创建企业表同义词,名为“qyb”。
create synonym qyb for business;
(2) 创建医保卡表的同义词,名为“ybk”。
create synonym ybk for card;
6.查询同义词
(1) 查看同义词“qyb”。
select * from qyb;
(2) 查看同义词“ybk”。
select * from ybk;
7.删除同义词
(1) 删除同义词“qyb”。
drop synonym qyb;
(2) 删除同义词“ybk”。
drop synonym ybk;
8.创建序列
(1) 创建序列,该序列最大值为“28000”,最小值为“60”,步长为“1”,可循环,序列名为“ygbx_seq1”。
create sequence ygbx_seq1
maxvalue 28000
minvalue 60
increment by 1
cycle;
(2) 创建序列,该序列最大值无限制,最小值为“1”,步长为“10”,序列名为“ygbx_seq2”。
create sequence ygbx_seq2
minvalue 1
increment by 10;
9.查询序列
(1) 查看序列“ygbx_seq1”。
(2) 查看同义词“ygbx_seq2”。
10.修改序列
(1) 修改序列“ygbx_seq1”,将该序列最大值设为“82000”,最小值设为“100”,步长设为“5”。
alter sequence ygbx_seq1
maxvalue 82000
minvalue 100
increment by 5;
11.删除序列
(1) 删除序列“ygbx_seq1”。
drop sequence ygbx_seq1;
常见问题分析
1.“名称已由现有对象使用”错误
数据库中已存在同名对象,修改数据库对象名称即可。
2.查看数据字典信息时,SELECT命令正确,却查不到数据
虽然Oracle的命令中是不区分大小写的,但查看Oracle系统数据字典信息时所有的字母均需大写,即便是用户定义的表名。例如,正确的命令是:SELECT * FROM DBA_TABLES WHERE TABLE_NAME= ‘STAFF’;错误的命令是:SELECT * FROM DBA_TABLES WHERE TABLE_NAME=‘staff’;
3.插入数据时,出现“值过多”、“没有足够的值”或“插入的值对于列过大”的错误
插入数据时,要求字段列表和值列表是按顺序一一对应的,即字段的个数、类型、长度与值的个数、类型、长度必须一致。如果字段的个数多,则出现“没有足够的值”的错误;如果字段的个数少,则出现“值过多”的错误;如果字段的类型与值列表中值的类型不一致,或值的长度超出了字段的长度,则出现“插入的值对于列过大”的错误。
4.插入数据时,出现“未找到父项关键字”错误
外键关联的两个表输入的数据必须满足参照完整性,即子表中外键字段的值或为空,或为父表中外键关联的主键值中的一个。出现“未找到父项关键字”错误可能有多种情况:
(1) 在子表中输入的外键字段的值在父表中外键关联的主键值中不存在,应修改为父表中主键已经存在的值。
(2) 可能是外键关联的父表和子表中字段的类型、长度不一致,父表中主键的长度大于子表中外键的长度,应修改子表,使子表中外键的类型、长度与父表一致。
(3) 可能是外键关联的父表中未创建主键,应在父表中定义外键关联的字段为主键。
(4) 外键关联的表的数据插入顺序是先插入父表数据,再插入子表数据。例如,先在“business”表插入某企业记录,才能在“staff”表中插入该企业的员工记录。
5.删除数据时,出现“已找到相关子记录”错误
删除表中数据时,外键关联的父表与子表,在没有指定数据删除方式为级联删除时,数据删除顺序是先删除子表数据,再删除父表数据。如果在子表中仍存在父表中要删除记录相关的记录时,出现“已找到相关子记录”错误,那么解决的方法为先删除子表中相关记录,再删除父表中记录。
6.创建外键约束时,出现“此列列表的惟一或主键不匹配”错误
在创建外键约束时,由于参照完整性要求子表的外键与父表的主键建立外键关联,而当父表上外键关联的字段不是主键时,出现“此列列表的惟一或主键不匹配”错误,解决的方法是在父表外键关联的字段上创建主键约束。另外,主键约束相当于惟一性和非空两个约束的作用,但主键约束不能定义成惟一性和非空约束,即就诊表中员工编号、医院编号和就诊日期联合作主键,正确的定义方法为“PRIMARY KEY (sno,hno,sdata)”,但如果定义成“sno UNIQUE NOT NULL,hno UNIQUE NOT NULL”,要求“sno”的值不能重复,“hno”的值也不能重复,这样就起不到联合作主键的作用了,所以在定义表的约束时,要注意主键约束的正确使用。
7.表创建存在先后顺序
创建表时,如果存在外键约束,则应先创建无外键关联的父表,再创建子表,因为外键约束是建立在子表上的。如果先创建子表,那么建立外键约束时选择不到外键关联的父表。
8.外键建立在子表上
在Oracle数据库中外键约束建立在子表上。
9.名称已被一现有约束条件占用
在Oracle数据库中,约束的名称在一个方案中必须是惟一的,所以一般情况下用户不自己定义约束的名称,而由系统给定约束名称,用户自己定义时要注意在同一方案中约束的名称不要相同。
10.表只能具有一个主键
在Oracle数据库中,一个表的主键约束是惟一的,但这并不表明一个表的主键字段是惟一的,一个表的主键可以是一个字段,也可以多个字段联合作主键。例如,员工表主键是员工编号,医院主键是医院编号,但消费表主键则是员工编号、医院编号和消费日期3个字段。如果是多个字段联合作主键,则定义成表级约束。
11.无法减小列长度, 因为一些值过大
在Oracle数据库中,要改变表中字段的类型或缩小字段长度,该字段的所有记录值必须为空,如果该字段存在记录值,则字段长度只能扩大,不能缩小。
12.表删除存在先后顺序
删除存在外键关联的表的顺序与创建表的顺序正好相反,应先删除子表,再删除父表,否则会出现“表中的惟一/主键被外部关键字引用”错误,所以本书案例中应先删除“staff”表,再删除“business”表。
13.插入或修改数据时,违反主键等各种约束错误
在插入数据或修改数据时,插入的数据或修改后的数据应满足创建表时的约束条件。解决方法视违反约束的具体情况而定。首先查看违反了什么约束,再查看约束的具体内容,如果是主键约束,则主键的值不能为空,不能重复;如果是检查约束,则应符合检查条件;如果是外键约束,则应符合参照完整性;如果是惟一性约束,则字段的值不能重复;如果是非空约束,则字段不能不赋值。
14.分组查询时,出现“不是GROUP BY表达式”错误
分组查询时,显示列表项中只能出现分组字段和利用聚集函数得到的统计结果,例如:SELECT bno,sno, COUNT(sno) FROM staff GROUP BY bno;在显示列表项中出现非分组信息“sno”,这时就会提示“不是GROUP BY表达式”的错误信息,解决方法为在显示列表项中删除非分组、非聚集信息,正确的命令为:SELECT bno, COUNT(sno) FROM staff GROUP BY bno;
15.多表查询时出现结果不正确
多表查询时,必须带WHERE子句,因为多表查询时必须带表间连接条件,即外键关联条件。如果没有外键关联条件,那么查询的结果为多表间的笛卡儿积运算,而关系是笛卡儿积的子集,因此要想得到正确的多表查询结果,必须写明表间的外键关联条件。
16.为表主键创建索引发生错误
创建数据表时,系统自动为表的主键创建升序索引,如果用户再为该表的主键创建升序索引,那么系统就会出错。如果用户为该表的主键创建降序索引,那么该索引可以被创建。创建一个视图之后,查看视图发现视图中有重复出现的列值,这时,请查看视图连接中WHERE条件是否正确
附录:员工医疗保险系统表表
表1 企业(business)表结构
表2医院(hospital)表结构
表3医保卡(card)表结构
表4员工(staff)表结构
表5就诊表(see)结构
表6消费(consume)表结构
表7医保(insurance)表结构