实验三 Oracle对象管理及使用

实验三 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）表结构