Oracle实验报告.doc

Oracle数据库实验报告

实验一：Oracle 10g安装卸载及相关工具配置
一、实验目标：
安装Oracle 10g，了解OEM，通过DBCA安装数据库，通过DBCA删除数据库，sqldeveloper连接数据库，卸载oracle 10g。
二、实验学时数
2学时
三、实验步骤和内容：
1、安装Oracle10g（默认安装数据库）
双击setup.exe，
选择基本安装，安装目录D：盘，标准版，默认数据库orcl，口令bhbh。
进入先决条件检查界面时：网络配置需求选项不用打勾，直接下一步，是。
直到安装成功。
2、登陆和了解OEM
主要是已网页的形式来对数据库进行管理。
http://主机IP:1158/em
用户名：sys
口令：bhbh
身份：sysdba
或者
用户名：system
口令：bhbh
身份：normal
3、通过DBCA删除已安装的默认数据库orcl
程序->Oracle - OraDb10g_home1->配置和移植工具->Database Configuration Assistant->删除数据库->……
4、通过DBCA安装数据库xscj
程序->Oracle - OraDb10g_home1->配置和移植工具->Database Configuration Assistant->创建数据库->……
5、sqldeveloper连接数据库
打开sqldeveloper，新建连接
连接名：system_ora
用户名：system
口令：bhbh
主机名：本机计算机名
SID:xscj
测试，显示成功，连接，保存。
6、卸载oracle 10g
Windows下
1>停止所有Oracle服务，点Universal Installer卸载
2>删除注册表中的所有关于Oracle项
在HKEY_LOCAL_MACHINE\SOFTWARE下，删除Oracle目录
3>删除硬盘上所有Oracle文件。
（1）Oracle安装文件
（2）系统目录下，在Program files文件夹中的Oracle文件
四、上机作业
根据实验步骤完成逐个实验目标中的任务。
五、心得体会
通过这次的实验，我了解了oracle数据库的情况。懂得了数据库就是把数据存储在一个类似与仓库的地方，需要用时才从数据库里调出来。通过上机实践，知道了装数据库和卸载数据库，并且学会了怎样连数据库。

实验二：Oracle 10g手工建数据库
一、实验目标：
安装Oracle 10g数据库环境，手工建立数据库；通过Net Configuration Assistant建立监听，使用sqldeveloper连接数据库测试。
二、实验学时数
2学时
三、实验步骤和内容：
先安装好Oracle 10g数据库环境（不安装默认数据库）。
1. 创建好相关的目录
假设oracle安装在D盘，打开命令行，运行->cmd->
md D:\oracle\product\10.2.0\admin\mydb\pfile
md D:\oracle\product\10.2.0\admin\mydb\bdump
md D:\oracle\product\10.2.0\admin\mydb\cdump
md D:\oracle\product\10.2.0\admin\mydb\udump
md D:\oracle\product\10.2.0\oradata\mydb
md D:\oracle\product\10.2.0\flash_recovery_area\mydb
2.创建数据库实例
（1）设置环境变量SID（注：以后每打开一次CMD都要运行此句）
set ORACLE_SID=mydb
（2）创建口令文件
orapwd file=D:\oracle\product\10.2.0\oradata\mydb\PWDmydb.ora Password=bhbh entries=5
（3）使用oradim命令创建数据库实例mydb
oradim -new -sid mydb -startmode manual -pfile "D:\oracle\product\10.2.\admin\mydb\pfile\initmydb.ora"
3.用sys账户以sysdba的身份登录
输入sqlplus sys/bhbh as sysdba
4.创建实例初始化参数文件initmydb.ora，并将其放到D:\oracle\product\10.2.\admin\mydb\pfile\路径下。
initmydb.ora
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
job_queue_processes=10
# Job Queues
# Shared Server
dispatchers="(PROTOCOL=TCP) (SERVICE=mydbXDB)"
# Miscellaneous
compatible=10.2.0.1.0
# Security and Auditing
remote_login_passwordfile=EXCLUSIVE
# Sort, Hash Joins, Bitmap Indexes
pga_aggregate_target=25165824
sort_area_size=65536
# Database Identification
db_domain=""
db_name=mydb
# File Configuration
control_files=("D:\oracle\product\10.2.0\oradata\mydb\control01.ctl", "D:\oracle\product\10.2.0\oradata\mydb\control02.ctl","D:\oracle\product\10.2.0\oradata\mydb\control03.ctl")
db_recovery_file_dest=D:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size=2147483648
# Pools
java_pool_size=50331648
large_pool_size=8388608
shared_pool_size=83886080
# Cursors and Library Cache
open_cursors=300
# System Managed Undo and Rollback Segments
undo_management=AUTO
undo_tablespace=UNDOTBS1
# Diagnostics and Statistics
background_dump_dest=D:\oracle\product\10.2.0\admin\mydb\bdump
core_dump_dest=D:\oracle\product\10.2.0\admin\mydb\cdump
user_dump_dest=D:\oracle\product\10.2.0\admin\mydb\udump
# Processes and Sessions
processes=150
# Cache and I/O
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

5.运行命令实例初始化
STARTUP NOMOUNT pfile="D:\oracle\product\10.2.0\admin\mydb\pfile\initmydb.ora";
6.运行创建数据库SQL命令
CREATE DATABASE mydb
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE 'D:\oracle\product\10.2.0\oradata\mydb\system01.dbf'
SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE 'D:\oracle\product\10.2.0\oradata\mydb\sysaux01.dbf' size 20M
UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:\oracle\product\10.2.0\oradata\mydb\undotbs01.dbf'
SIZE 150M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP1 TEMPFILE 'D:\oracle\product\10.2.0\oradata\mydb\temp01.dbf' SIZE 100M REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 'D:\oracle\product\10.2.0\oradata\mydb\redo01.log' SIZE 50M,
'D:\oracle\product\10.2.0\oradata\mydb\redo02.log' SIZE 50M,
'D:\oracle\product\10.2.0\oradata\mydb\redo03.log' SIZE 50M;
7.创建用户表空间USERS
CREATE TABLESPACE USERS
DATAFILE 'D:\oracle\product\10.2.0\oradata\mydb\users01.dbf' SIZE 128M REUSE
AUTOEXTEND ON NEXT 1280K
MAXSIZE UNLIMITED;
8.创建数据字典（需要几分钟，报错不要紧）
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql;
9. 创建内部包（需要几分钟，报错不要紧）
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql;
10.创建自启动文件，以便以后不用每次打开数据库实例都要运行一次第5步。
create spfile FROM pfile='D:\oracle\product\10.2.0\admin\mydb\pfile\initmydb.ora';
11.重启数据库，即关闭再打开。
shutdown ;
connect sys/bhbh as sysdba;
startup;
12.授权用户system为dba，置密码为bhbh
grant dba to system identified by bhbh;
13.使用system用户登录
conn system/bhbh;
14．为system用户创建一张KC表
CREATE TABLE KC
( KCH CHAR(3) NOT NULL,
KCM CHAR(16) NOT NULL,
KKXQ NUMBER(1) NOT NULL,
XS NUMBER(2) NOT NULL,
XF NUMBER(2),
CONSTRAINT CH_KKXQ CHECK(kkxq between 1 and 8),
CONSTRAINT "PK_KCH" PRIMARY KEY("KCH"))
TABLESPACE USERS ;
15.给KC表插入一条数据，并提交。

INSERT INTO KC VALUES('001','数据库',7,68,4);
commit;
16.测试查询
Select * from kc;
17．使用Net Configuration Assistant添加监听器，并重启监听器（运行cmd->netca也一样）。
18. 创建Em资料档案库(这一步可以省略，只为创建OEM，我们以后学习用不到)。
 cmd->emca -config dbcontrol db -repos create
19．打开SQLDeveloper，并新建数据库连接并测试是否成功。
20.若之前手工安装数据库出现差错，可使用以下命令删除数据库实例：
cmd->oradim -delete -sid mydb
然后删除第1步所建目录即可。
四、上机作业
根据实验步骤完成逐个实验目标中的任务。
五、心得体会：
通过这次的实验，明白了手动建数据库，建表空间等。还知道了运行数据库的命令，授权命令插表等等。这次学习让我对数据库的了解和运用又新的体会。

实验三：数据库修改
一、实验目标：
学习简化实验二里的实验步骤，学习修改数据库某些属性，完成数据库作业1：手工快速创建数据库实例XSCJ和数据库XSCJ。
二、实验学时数
2学时
三、实验步骤和内容：
1.安装好ORACLE 10g，（不需要安装数据库）。
2.快速安装数据库实例mydb和数据库mydb需要3个文件：init.bat，initmydb.ora，ctreatedb.sql。
（1）双击运行init.bat文件；
（2）将initmydb.ora放到正确的路径下（参考实验二）；
（3）在sqlplus环境下运行ctreatedb.sql
3.了解startup nomount，startup mount和startup三种状态的使用情况的区别，即打开实例不装载数据库、打开实例装载数据库但不打开数据库和打开实例装载数据库同时打开数据库。
（1）运行P45页例子2-7修改数据库（提示：startup;）
ALTER DATABASE mydb
DATAFILE 'D:\oracle\product\10.2.0\oradata\mydb\users01.dbf' RESIZE 200M;
（2）运行P45页例子2-9删除数据库（提示：startup mount;）
取消数据库的受限制状态：
alter system enable restricted session;
删除数据库：
DROP DATABASE;
（3）重建数据库
sys重新登录，运行ctreatedb.sql。
4.删除数据库实例mydb和数据库mydb。使用命令 oradim -delete -sid mydb 以及删除相关文件夹。
四、上机作业
适当地修改建库所用到的3个文件里的参数，手工安装数据库实例xscj和数据库xscj （快速安装）,用户表空间myts。

五、心得体会
这次学习，让我明白了怎样修改数据库某些属性，手工快速创建数据库实例XSCJ和数据库XSCJ。DROP DATABASE命令可以删除数据库…

实验四：表的创建、修改和删除
以及表数据的插入、修改和删除
一、实验目标：
学习表的创建、插入、修改和删除以及表数据的插入、修改和删除。
二、实验学时数
4学时
三、实验步骤和内容：
1．手工创建好数据库XSCJ。
2.为用户system创建3个表XSB、KCB和CJB（P66-67 例子3.7-3.9， create table用法，自己照书完成）。
CREATE TABLE XSB
( XH char(6) NOT NULL PRIMARY KEY,
XM char(8) NOT NULL,
XB char(2) DEFAULT '1' NOT NULL,
CSSJ date NOT NULL,
ZY char(12) NULL,
ZXF number(2) NULL,
BZ varchar2(200) NULL
)TABLESPACE myts ;
CREATE TABLE KCB
( KCH char(3) NOT NULL PRIMARY KEY,
KCM char(16) NOT NULL,
KKXQ number(1) NULL,
XS number(2) NULL,
XF number(1) NOT NULL
)TABLESPACE myts ;
CREATE TABLE CJB
( XH CHAR(6) NOT NULL,
KCH CHAR(3) NOT NULL,
CJ NUMBER(2),
XF NUMBER(1),
PRIMARY KEY(XH,KCH)
)TABLESPACE myts ;
3.学习修改表结构（P70 例子3.11 （1）-（3）alter table用法）。
（1）在表XSB中增加两列：JXJ（奖学金等级）、DJSM（奖学金等级说明）。
ALTER TABLE XSB
ADD ( JXJ number(1),
DJSM varchar2(40) DEFAULT '奖金1000元');
（2）在表XSB中修改名为DJSM的列的默认值。
ALTER TABLE XSB
MODIFY ( DJSM DEFAULT '奖金800元' );
（3）在表XSB中删除名为JXJ和DJSM的列。
ALTER TABLE XSB
DROP COLUMN JXJ;
ALTER TABLE XSB
DROP COLUMN DJSM;
修改好可用命令：describe table_name; 查看。
4.删除表（P71 drop table用法）。
DROP TABLE table_name;
5. 向XSCJ数据库的表XSB中插入一行（P75 例子3.12 insert用法）。
INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF)
VALUES('101101', '王林', '男',TO_DATE('19900210','YYYYMMDD'), '计算机', 50);
提交数据：
Commit；
插入后可用命令：select * from table_name;查看。
6.学习删除表数据（P78 DELETE和TRUNCATE的用法）。
将XSCJ数据库的XSB表中总学分小于50的行删除：
DELETE FROM XSB WHERE ZXF<50;
使用TRUNCATE TABLE语句删除表中的所有数据，且不能恢复。
TRUNCATE TABLE table_name;
插入后可用命令：select * from table_name;查看。
7. 为3个表XSB、KCB和CJB插入数据（使用insert.sql）
8.学习修改表数据（P78-79 例子3.17-3.20 UPDATE 用法）。
UPDATE XSB
SET BZ='三好'
WHERE XH='101110';
UPDATE XSB
SET ZXF=ZXF+10;

UPDATE XSB
SET ZY='通信工程',
BZ='转专业学习',
XH='101241'
WHERE XM= '罗林琳';

UPDATE XS
SET ZXF=ZXF+ 4,
BZ= '提前修完《数据结构》，并获得学分'
WHERE XM='李明';

插入后可用命令：select * from table_name where （条件）;查看。
9.将数据恢复到刚插入时的状态（即把修改过的表清空再插入）。
10.了解一下同义词，知道同义词的作用（不作重点）。
四、上机作业
手工安装数据库实例xscj和数据库xscj （快速安装）,用户表空间myts后，为用户system创建3个表XSB、KCB和CJB，并插入所有数据。

五、心得体会
这次学习的表创建，删除和修改的学习使我对数据库的表的认识更进一步。通过这次的学习，学会对表的创建、修改、删除。创建表就是按格式写些代码；修改（UPDATE+表名+需修改的内容）和删除（DROP TABLE + 表名）就是使用指定的指令对表进行操作，使之完成想要的目的。

实验五：数据库查询练习
一、实验目标：
学习数据库数据查询语句的基本方法以及视图的使用方法。
二、实验学时数
6学时
三、实验步骤和内容：
课本例子：4.5，4.6，4.8，4.9，4.12（2），4.14，4.15（2），4.16，4.17
4.18，4.19，4.28，4.29，4.37，4.39（1），（3），4.42，4.43，4.46
（一）学习和掌握课本查询语句的基本语法和使用方法，完成下面练习（*为必做，@为选做）。
*1、查询所有计算机系学生的信息 。
Select * from xsb;
*2、查询王同学所修的学号
Select kch from xsb where xm like '王%';
*3、查询出生于85和86年的女同学的学号和姓名。
Select xh,xm from xsb where cssj between to_date('19900101','yyyymmdd') and to_date('19911231','yyyymmdd')and xb='女';
*4、查询学号为101104所选修的全部课程成绩。
Select cj from cjb where xh='101104';
*5、检索每个学生的姓名和年龄。
Select xm,to_char(sysdate,'yyyy')-to_char(cssj,'yyyy') as 年龄 from xsb;
*6、在XS表中检索学生的姓名和出生年份，输出的列名为姓名和生年
Select xm,to_char(cssj,'yyyy') as 出生时间 from xsb;
*7、在XS_KC表中，求选修课程206的学生的学号和得分，并将结果按分数降序排序。
Select xh,cj from cjb where kch=’206’ order by cj desc;
*8、把课程名为软件工程的成绩从学生选课数据表XS_KC中删除。
Delete from kcb where kcm=(select kch from kcb where kcm=‘软件工程’);
*9、把女同学的成绩提高10%。
Update cjb set cj=cj*1.1 where xh in (select xh from xsb where xb='女') and cj<=90;
*10、如果学号为061106的学生的成绩少于90，则加上10分。
Update cjb set cj=cj+10 where xh=’061106’and cj<90;
*11、查询学号为061104的学生选修的课程号和课程名。
Select xh,xm from xsb where xh in (select xh from cjb where kcb=’061104’;
*12、检索学习课程号为206的学生学号与姓名。
Select xm,xh from xsb where kch=’206’;
*13、检索‘李明’所选修的全部课程名称。
Select kcm from kch where xm=(select xm from xsb where xm=’李明’);
*14、查找每个同学的学号及选修课程的平均成绩情况。
Select xh,avg as 课程号 平均成绩 from cjb;
*15、求选修了各课程的学生的人数。
Select count(distinct xh) as 选修课程总人数 from cjb;
Select kch as课程号 ,count (xh) as 选修人数 group by kch；

*16、查询平均成绩都在80分以上的学生学号及平均成绩。
Select xh,cj from cjb group by xh having avg(cj)>80;
Select xh,cj from cjb group by xh having avg(cj)>80 order by avg(cj);
Select xh,cj from cjb group by xh having avg(cj)>80 order by avg(cj) desc;
*17、查询至少有6人选修的课程号。
Select kch as 课程号 from cjb group by having count(*)>=6;
*18、检索选修课程号为101或102的学生学号，姓名和所在系
Select xh,xm,zy from kch=(select kch from kcb where kch=’101’and kch= ‘102’;
*19、统计被学生选修的课程门数。
Select cjb
每个学生情况以及选修课程情况
Select * from xsb inner join cjb on xsb.xh=cjb.xh
Select xsb .*,cjb.* from xsb,cjb where xsb.xh=cjb.xh *20、查询没有选修数据库的学生学号，姓名和专业名。
@21、取出选修‘操作系统’课程的学生姓名。
@22、检索至少选修课程“数据结构”和“计算机基础”的学生学号。
@23、检索至少选修课程号为101和206的学生姓名。
@24、列出学生所有可能的选课情况。
@25、查询101号课程得最高分的学生的学号
@26、‘计算机基础’课程得最高分的学生的姓名、性别、所在系。
@27、将成绩最低的学生的成绩加上10分。
@28、所有成绩都在70分以上的学生姓名及所在系。
@29、列出选修课程超过3门的学生姓名及选修门数。
@30、至少选修了两门课及以上的学生的姓名和性别
@31、检索至少有两名男生选修的课程名。
@32、统计每门课程的学生选修人数(超过10人的课程才统计)。
@33、计算机基础成绩比离散数学成绩好的学生
@34、列出每个同学的学号、姓名及选修课程的平均成绩情况，没有选修的同学也列出。
@35、列出每个同学的学号及选修课程号，没有选修的同学也列出
（二）视图
1、创建一个名为zy_view的视图，该视图可以用来查看每个系名称和学生人
Create or replace view zy_view(专业,专业人数) as select zy,count(xh) from xsb group by zy;
2、创建一名为xs_view的视图，该视图可以用来查看每个学生的姓名、选修的课程名和成绩。查看视图。
Create or replace view xs_view(姓名,课程名,成绩) as select xm kcm cj from xsb,kcb,cjb where xsb.xh=cjb.xh and cjb.kch=kcb.kch;
3、创建一个名为kc_view的视图，该视图可以用来查看每门课的课程号，课程名，选修人数和平均成绩。
Create or replace view kc_view(课程号,课程名,选修人数,平均成绩) as select kcb.kch,kcm,count(xh),avg(cj) from cjb,kcb where cjb.kch=kcb.kch group by kcb.kch,kcm;
4、利用kc_view视图，查看平均成绩最高的课程名。
Select 课程名 from kc_view where 平均成绩=(select max(平均成绩) from kc_view);
5、修改xs_view视图，将视图重定义为每个学生的姓名、选课门数和平均成绩。
Create or replace view xs_view(姓名,选课门数,平均成绩) as select xm,count(kch),avg(cj) from cjb,xsb where xsb.xh=cjb.xh group by xsb.xh,xm;
6、删除zy_view视图。
Drop view zy_view;

四、上机作业
完成实验内容中的任务，并把语句记录在每道题的下面。

五、心得体会
这次学习，我知道了对表的内容的查询，修改的操作，并通过实践对这次操作命令进一步的熟悉，运用和掌握。还学会了怎么建立视图，视图是可以快速的查询多个表的不同的信息的图表，它使我们更方便。

实验六：索引和约束
一、实验目标：
（1）掌握索引的使用方法。
（2）掌握各种约束即数据完整性的实现方法。
二、实验学时数
2学时
三、实验步骤和内容：
1.关于索引
建立索引的最大好处是快速存取数据。
假设有10个学生，10门课，则成绩表会有100条记录，对于成绩表中某人某门课成绩的查找，最坏情况是逐行查找100次，即最后一行。而如果对成绩表中的学号和课程号创建索引，则最坏情况只需查找20次（先查找学号，再查找课程号）。
建立索引的原则：
（1）先插数据再建索引。
（2）如果经常检索的内容仅为包含大量数据的表中少于15%的行，就需要创建索引。
（3）建立索引的先后顺序会影响查询的性能。
2.创建索引表空间
CREATE TABLESPACE INDX
DATAFILE 'D:\oracle\product\10.2.0\oradata\xscj\indx01.dbf' SIZE 32M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE
(INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
2.创建索引
CREATE INDEX kc_name_idx
ON KCB(KCM)
TABLESPACE "INDX";
3.维护索引
ALTER INDEX kc_name_idx
RENAME TO kc_idx;

4.删除索引
DROP INDEX kc_idx;
5.数据完整性
生活中有些数据除了数据类型和宽度，还必须要定义其他规则的，比如学生学号必须是唯一的，不能重复的；性别必须是男或女，成绩必须是0-100的范围等。因此必须使用一些方法对数据进行约束，才得以保持数据完整性。
6.约束
（1）建表同时添加约束：
XSB表：
主键为学号。
KCB表：
课程号为主键，
开课学期默认值为1，且只能为1-8。
CJB表
主键为学号和课程号，
存在外键XH参照XSB表的XH，
存在外键KCH参照KCB表的KCH。
（2）建表后时添加约束
XSB表：
性别必须为“男”或者“女”。
CJB表：
成绩必须>=0且<=100或者为空。
7.插入数据验证。
8.思考题
（1）建KCB表时添加约束，使课程号必须为三位0-9数字
（2）建XSB表时添加约束，使学号为101开头，后三位为0-9数字
四、上机作业
完成实验内容中的任务，并把语句记录在每道题的下面。
五、心得体会
通过这次的学习，我知道了索引的最大好处是快速存取数据。1：先插数据再建索引。2：如果经常检索的内容仅为包含大量数据的表中少于15%的行，就需要创建索引。3：建立索引的先后顺序会影响查询的性能。这3条是建立索引的原则。
我还知道了可以增加约束，就是制约范围。

实验七：PL/SQL编程
一、实验目标：
（1）掌握变量、运算符以及控制语句的使用。
（2）掌握系统函数和自定义函数的使用。
二、实验学时数
4学时
三、实验步骤和内容：
SYS登录，先导入数据字典：
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql;
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql;
（1）定义变量
例子1：定义一个长度为10的变量count，其初始值为1，是varchar2类型。
count varchar2(10) := '1';
例子2：在表XSB中包含XH列，为了声明一个变量my_xh与XH列具有相同的数据类型，声明时可使用点和%TYPE属性，格式如下：
my_xh XSB.XH%TYPE;
例子3：可以使用%ROWTYPE属性声明描述表的行数据的记录。
如：声明一个记录名为cj_rec，它与CJB表具有相同的名称和数据类型，格式如下：
DECLARE
cj_rec CJB%ROWTYPE;
对于用户定义的记录，必须声明自己的域：
DECLARE
TYPE TimeRec IS RECORD(HH number(2),MM number(2));
（2）程序块
例子6.6：查询总学分大于50的学生人数
set serveroutput on
DECLARE
v_num number(3);
BEGIN