《Oracle数据库管理系统》实验指导书.doc

oracle数据库管理系统实验指导书 编写 主审计算机与信息技术学院信息科学系2011.1目 录实验一 熟悉sql命令与sql*plus命令的应用1实验二 sql语言中的数据操纵、事务控制和数据定义语句4实验三 pl/sql编程7实验四 数据库触发器、存储过程和存储函数11实验五 数据表的管理14实验六 索引的管理17实验七 安全管理19实验八 触发器和游标21前 言oracle数据库是目前最为流行和成熟的几种大型关系数据库之一。oracle数据库管理系统课程以oracle9i为主，在学习数据库系统概论的基础上，进一步理解关系数据库的概念、原理。通过对oracle数据库系统的学习，使学生掌握数据库的基本理论和oracle数据库操作的基本方法，熟悉sql基本命令的运用,了解运用oracle数据库知识处理复杂问题的方法。使学生具有运用oracle进行数据库服务器端的程序开发的基本能力。为了加强学生对oracle数据库系统的操作能力，特编写该实验指导书，希望给学生提供一定的指导。本指导书由李学贵编写，冯亚丽教授主审，由于时间仓促，作者的水平有限，书中难免有不足之处，恳请广大师生批评指正。编者2011年1月实验一 熟悉sql命令与sql*plus命令的应用一、实验目的 1、熟悉sql语言交互式使用工具sql*plus的使用方法2、熟悉sql*plus常用命令的功能和使用方法3、掌握sql语言中简单查询语句的功能和使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、sql*plus的使用(1) 进入（启动）sql*plus在windows环境下,双击sql*plus图标或从程序组找sql*plus ，出现登录窗口输入正确的数据库用户名、密码和连接字符串后，若连接数据库成功，则会出现如下提示符sql(2) 退出 sql*plus sqlexit 则退回到windows桌面。 (3) 创建表结构：create table 创建部门登记表dept和雇员登记表emp的结构 sql create table dept ( deptno number(2) not null， dname char(14)， loc char(13)；sql create table emp ( empno number(4) not null， ename char(9)， job char(10), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) not null )； (4) 显示表结构 显示部门登记表dept和雇员登记表emp的结构 sqldesc dept sql desc emp (5) 向表中插入记录 向部门登记表dept中插入一条记录 sql insert into dept values (60，computer，beijing)； sql select * from dept; 向部门登记表dept中连续插入多条记录sql insert into dept (deptno，dname，loc) values ( &deptno，&dname，&loc); (6) 执行sql缓冲区中的命令 sql/ (直接执行) sqlrun (先显示命令的内容，再执行 ) (7) 执行磁盘上的命令文件 a. 先调入缓冲区，再运行： sqlget f:oradept.sql sql/ b. 用 或start命令将指定命令文件调入缓冲区并执行。 sqlf:ora dept.sql 或 sqlstart f:ora dept.sql 2、简单查询语句(1) 无条件简单查询：查表中所有记录sql select dname, deptno 2 from dept；对查询结果进行计算和统计sql select count(*) all_emp， sum(sal) all_sal 2 from emp；(2) 有条件简单查询：查表中部分记录 查在20号部门工作的雇员姓名和工资sql select ename，sal，deptno 2 from emp 3 where deptno=20； 查找工种是职员或分析员的雇员姓名和工种sql select ename，job 2 from emp 3 where job in (clerk，analyst)； 查找以“s”开头的雇员姓名和所在部门 sql select ename，deptno 2 from emp 3 where ename like s%；按工资升序排列20号部门的雇员sql select ename，sal，deptno 2 from emp 3 where deptno=20 4 order by sal asc ； 查所有部门中工资大于2800美元的雇员 sql select ename，sal from account where sal 2800 union select ename，sal from research where sal 2800 union select ename，sal from sales where sal 2800 ； 查在所有部门中都存在的工种sql select job from account intersect select job from research intersect select job from sales； 查在account部门中有哪些职业，是sales部门中所没有的 sql select job from account minus select job from sales； 23实验二 sql语言中的数据操纵、事务控制和数据定义语句一、实验目的1、掌握sql语言中数据操纵命令的功能及其使用方法2、掌握sql语言中事务控制命令的功能及其使用方法3、掌握sql语言中数据定义命令的功能及其使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、数据操纵命令的使用(1) 数据插入 向dept 表插入一行数据(插入所有列时可省略列名) 。 sqlinsert into dept values (60，computer，beijing); 向dept表中一次插入多个纪录(所有字段可用*代替) 。 sql insert into dept (deptno，dname，loc) values ( &deptno，&dname，&loc); 将dept表中所有记录插入到空表department中。 sql insert into department select * from dept;向emp表中插入一条与smith 内容相同的记录，但姓名改为richard，雇员号改为9999。 sql insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) select 9999,richard,job,mgr,hiredate,sal,comm,deptno from emp where ename=smith; (2) 数据修改将 martin 提升为经理，工资加￥1000 。 sql update emp set job = manager，sal=sal+1000 where ename= martin; 将30号部门所有雇员工资和奖金，变为20号部门雇员的最高工资和奖金。 sql update emp set (sal，comm)=( select max(sal)，max(nvl(comm，0) from emp where deptno =20) where deptno=30；(3) 数据删除将 martin从公司删除 。 sql delete from emp where ename= martin；2、事务控制命令的使用(1) 事务提交命令commitsql commit；(2) 事务回退命令rollback sql rollback ；3、数据定义命令的使用(1) 表操作手工创建表dept结构。 sql create table dept ( deptno number(2) not null， dname char(14)， loc char(13)； 通过复制创建表deptpartment结构，其结构与dept相同。sql create table deptpartment as select * from dept where 1=2； 将dept 表中dname 列宽度加大到20个字符。 sql alter table dept modify dname char (20) ； 为表dept增加一列all_emp 用来存放部门人数 。 sql alter table dept add all_emp number(3)； 删除dept表。 sql drop table dept；(2) 视图操作为20号部门的雇员创建一个视图，包括姓名,工资,部门号 。 sql create view emp_view(name ，salary ，no) as select ename ，sal ，deptno from emp where deptno=20 ；(3) 索引操作为emp表建立empno列上的唯一索引。 sql create unique index empno_index on emp(empno); (4) 同义词操作为gx1用户的dept表建立同义词new_dept 。 sql create synonym new_dept for gx1.dept; (5) 序列生成器操作创建一个序列生成器deptno_seq，起始值为50,间隔为10 。 sql create sequence deptno_seq start with 50 increment by 10； 列出deptno_seq的下次值(第一次使用时为50 ) sql select deptno_seq.nextval next_val from dual；实验三 pl/sql编程 一、实验目的1、了解pl/sql块的基本结构与功能2、掌握pl/sql块中各种sql命令的使用方法3、掌握pl/sql块中流程控制语句的使用方法4、掌握pl/sql块中游标的使用方法5、掌握pl/sql块中异常处理的使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、pl/sql块中查询命令的使用将7788号雇员的工资和奖金作为smith的工资和奖金。 sql declare v_empno emp.empno%type:=7788； v_ename emp.ename%type:=smith； v_sal emp.sal%type； v_comm m%type； begin select sal，comm into v_sal，v_comm from emp where empno=v_empno； update emp set sal=v_sal，comm=v_comm where ename= v_ename ； commit; end；查询smith的情况 。 sql declare emp_rec emp%rowtype； v_ename emp.ename%type：=smith； begin select * into emp_rec from emp where ename=v_ename； - end； 2、pl/sql块中数据操纵命令的使用向emp表插入一新雇员 。 sql declare v_deptno dept.deptno%type not null:=50； v_dname dept.dname%type:=computer； v_loc dept.loc%type:=beijing； begin insert into dept(deptno，dname，loc) values(v_deptno，v_dname，v_loc)； commit work; end；修改7788号雇员的工资 。 sql declare v_empno emp.empno%type:=7788； v_addsal emp.sal%type； begin v_addsal:=1000； update emp set sal=sal+v_addsal where empno=v_empno； commit； end； 从emp表中删除7788号雇员 。 sql declare begin delete from emp where empno=7788； commit； end； 3、pl/sql块中流程控制语句的使用(1) 条件控制语句的使用将emp表中名为smith的雇员的工资进行修改，若原工资大于$2000，则加$500，否则加$1000 。 sqldeclare v_ename emp.ename%type:=smith； v_addsal emp.sal%type； v_sal emp.sal%type； begin select sal into v_sal from emp where ename=v_ename； if v_sal20oo then v_addsal:=500； else v_addsal:=1000； end if； update emp set sall=sal+v_addsal where ename=v_ename； commit； end；(2) 循环控制语句的使用给10号部门增加新雇员，只确定雇员号，其它信息忽略 。 sqldeclare v_empno emp.empno%type:=8000； begin loop insert into emp(deptno,empno) values( 10，v_empno)； v_empno:=v_empno+l00； exit when v_empno=9000； end loop； end； (3) goto控制语句的使用给10号部门增加新雇员，只确定雇员号，其它信息忽略 。 sqldeclre v_empno emp.empno%type:=8000； begin lab1 insert into emp(deptno,empno) values(l0，v_empno)； v_empno:=v_empno+100； if v_empnodeclare v_deptno emp.deptno%type； v_ename emp.ename%type； v_sal emp.sal%type； cursor c1 is select ename，sal from emp where deptno=v_deptno； begin v_deptno:=10； open cl； fetch c1 into v_ename，v_sal； close c1 ； end；查询10号部门所有雇员姓名、工资，并插入到一临时表tmp中 。 sqldeclare v_deptno emp.deptno%type:=10； cursor c1 is select ename，sal from emp where deptno=v_deptno； emp_ec c1%rowtype； begin for emp_rec in c1 loop insert into tmp(ename，sal) values(emp_rec.ename，emp_rec.sal)； end loop； gommit work； end；5、pl/sql块中异常处理的使用从emp表中删去smith的信息 。 sqldeclare v_ename emp.ename%type:=smith； begin delete from emp where ename=v_ename； commit work； exception when no_data_found then rollback work； insert into temp(message) values(smith is not found)； commit work； when too_many_rows then rollback work； insert into temp(message) values(found too many rows in emp)； commit work； when others then rollback work； insert into temp(message) values(other error occurred)； commit work； end； 实验四 数据库触发器、存储过程和存储函数一、实验目的1、掌握数据库触发器的功能与使用方法2、掌握存储过程的功能与使用方法3、掌握存储函数的功能与使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、数据库触发器的使用创建一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。 sqlcreate trigger del_emp_deptno_trig before delete on dept for each row begin delete from emp where deptno=:old.deptno; end del_emp_deptno_trig；2、存储过程的使用创建一个统计各部门雇员人数的据库存储过程。sqlcreate procedure count_proc(in_deptno in emp.deptno%type， out_num out number) as begin if in_deptno=10 then select count(deptno) into out_num from emp where deptno=in_deptno； elsif in_deptno=20 then select count(deptno) into out_num from emp where deptno= in_deptno ； elsif in_deptno=30 then select count(deptno) into out_num from emp where deptno= in_deptno ； else select count(deptno) into out_num from emp where deptno=40； end if； end count_proc ；3、存储函数的使用创建一个统计不同部门雇员人数的据库存储函数sqlcreate function count _func( in_deptno emp.deptno%type) return number as out_num number; begin if in_deptno=10 then select count(deptno) into out_num from emp where deptno=in_deptno； elsif in_deptno=20 then select count(deptno) into out_num from emp where deptno= in_deptno ； elsif in_deptno=30 then select count(deptno) into out_num from emp where deptno= in_deptno ； else select count(deptno) into out_num from emp where deptno=40； end if； return(out_num )； end count_func； 用实际参数代替形式参数。 sql execute empdnum:= count_proc (10)；实验五 数据表的管理一、实验目的1、利用sql语句（2种）创建表；2、掌握在oem中创建表；3、掌握定义表的约束、添加、删除和激活约束；4、掌握修改表的各种操作。二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容一、 实验内容和步骤1、创建数据表 create table (列级完整性约束条件 ， 列级完整性约束条件 ，) 参数设置;2、创建带约束的数据表create table (列级完整性约束条件 ， 列级完整性约束条件 ，) 参数设置as 子查询 3 定义约束（1）列级约束语法格式：constraint constraint_name constraint_type condition（2）表级约束语法格式：constraint constraint_name constraint_type column1_name,column2_name,|condition 3、表约束的修改alter table add modify enable disable drop 4、字段的的添加、删除、修改alter table add 完整性约束定义modify rename column oldname to newnameset unused column column /single columnset unused columns（column1，column2）drop column /single columndrop /multi columndrop unused columns 5、利用oem创建表、删除表、修改表和查询表实验六 索引的管理一、实验目的 1、熟悉利用sql语句创建索引2、掌握在oem中创建索引3、掌握修改索引的各种操作二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、使用命令创建索引 create unique bitmapindex on ( 次序 , 次序)reverseparameter_list 2、使用命令修改索引alter indexcoalescealter index rebuilder alter index monitoring usage alter indexrename to 3、删除索引drop index 4、使用oem创建索引、删除索引、修改索引和查询索引实验七 安全管理一、实验目的 1、熟悉利用sql语句对用户、权限和角色进行管理2、掌握在oem中对用户、权限和角色进行管理二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、create user user_name identified by password | externally | globally as external_name default tablespace tablespace_name temporary tablespace temp_tablespace_name quota n k|m|unlimited on tablespace_name profile profile_name password expire account lock | unlock 2、alter user user_name identified by password | externally | globally as external_name default tablespace tablespace_name temporary tablespace temp_tablespace_name quota n k | m | unlimited on tablespace_name profile profile_name default role role_list | all except role_list | none password expire account lock | unlock 3、drop user user_name cascade ;4、create role role_name not identified identified by password 5、alter role role_name not identified identified by password ；6、drop role role_name;7、grant sys_priv_list to user_list|role_list public with admin option ;8、revoke sys_priv_list from user_list | role_list;9、grant obj_priv_list | all on schema.object to user_list | role_list with grant option;10、revoke obj_priv_list | all on schema.object from user_list|role_list;11、利用oem对用户、权限和角色进行管理。实验八 触发器和游标一、实验目的1、了解触发器的概念。2、熟悉触发器的基本用法。3、了解游标的概念。4、熟悉游标的基本用法。二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机若干台2、软件系统：windows 2003 server 网络操作系统，windows 2003/xp客户机操作系统； oracle9i服务端数据库系统，客户端工具。三、实验内容1、声明显式游标声明一个游标用来读取基表emp中部门号是20且工作为分析员的职工：declare cursor c1 is select ename, sal, hiredate from emp where deptno = 20 and job = analyst;v_ename varchar2(10);v_sal number(7,2);v_hiredate date; begin open c1; fetch c1 into v_ename, v_sal, v_hiredate; close c1;end;2、使用游标使用游标属性判断游标是否打开：if c1%open then fetch c1 into v_ename, v_sal, v_hiredate;else open c1;end if;利用循环读取数据：loopfetch c1 into v_ename, v_sal, v_hiredate