<4D F736F F D204F C6520CAFDBEDDBFE2C9E8BCC6D3EBBFAAB7A22DCAB5D1E9D6B8B5BCCAE92DC8EDBCFED7A8C9FDB1BE2D61736D E646F63>

Size: px

Start display at page:

Download "<4D F736F F D204F C6520CAFDBEDDBFE2C9E8BCC6D3EBBFAAB7A22DCAB5D1E9D6B8B5BCCAE92DC8EDBCFED7A8C9FDB1BE2D61736D E646F63>"

弋电双
5 years ago
Views:

1 Oracle 数据库设计与开发实验指导书 ( 软件专升本 ) 第 1/17 页 1

2 Oracle 数据库设计与开发课程实验一本实验课在培养实验能力中的地位及作用 Oracle 数据库设计是软件工程专业的一门专业课 Oracle 数据库是目前最流行的大型数据库平台之一, 是一种极具前景的大型数据库该课程侧重于 Oracle 数据库维护和管理知识的掌握以及实际应用 Oracle 数据库的能力的培养本课程设置的目的是使学生通过本课程的学习逐渐全面了解 Oracle 数据库, 并具有对大型数据库数据库进行安全维护等的管理技能, 同时能应用 Oracle 数据库进行数据库结构的设计和数据库应用系统的开发二应达到的实验能力标准本实验的教学目标是使学生掌握如何使用 Oracle 数据库开发系统, 了解数据库设计及相关操作的基本概念与方法, 进而学会建立与组织操作数据库上机实验的主要目标如下 : (1) 通过上机操作, 加深对数据库系统理论知识的理解 (2) 通过使用具体的 DBMS, 了解一种实际的数据库管理系统并掌握其操作技术 (3) 通过上机实验, 提高动手能力, 提高分析问题和解决问题的能力 (4) 通过上机实验, 提高动手能力, 掌握大型数据库实际应用与开发技巧三实验要求学生在实验课前认真做好预习, 实验结束及时提交电子版实验报告三实验成绩考核方法实验成绩在课程总成绩中占 30%, 包括平时每次实验考核与最后一次实验考试第 2/17 页 2

3 实验内容目录实验 1 Oracle 环境及开发工具使用实验 2 数据库物理存储和逻辑存储设置与管理实验 3 数据库对象的创建与管理 (1) 实验 4 数据库对象的创建与管理 (2) 实验 5 数据操纵 DML 和数据查询语言 DQL(1) 实验 6 数据操纵 DML 和数据查询语言 DQL(2) 实验 7 PL/SQL 程序设计基础 (1) 实验 8 PL/SQL 程序设计基础 (2) 实验 9 存储过程函数触发器的程序设计与开发实验 10 Oracle 数据库安全恢复与备份第 3/17 页 3

$熟悉开启计算机服务到 D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN, 修改文件 listener.ora 和 tnsnames.ora, 把其中的 HOST = 中的改为你自己的机器名, 保存这两个文件修改环境变量 : 我的电脑, 右键, 属性, 高级系统设置环境变量修改系统变量 path 变量 : 将 D:\oracle\product\10.$

4 实验 1 Oracle 环境及开发工具使用 1. 能熟练开启 oracle 的服务, 熟悉 oracle 的环境, 以及常用的工具, 主要包括 sql*plus 和 oem; 2. 通过对数据库的物理文件以及内存参数的查看, 结合课件, 加深对 oracle 实例以及整个体系结构的了解 ; 3. 能通过查询数据字典视图, 了解数据库里的信息 ; 实验内容 : 1. 熟悉开启计算机服务到 D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN, 修改文件 listener.ora 和 tnsnames.ora, 把其中的 HOST = 中的改为你自己的机器名, 保存这两个文件修改环境变量 : 我的电脑, 右键, 属性, 高级系统设置环境变量修改系统变量 path 变量 : 将 D:\oracle\product\10.2.0\client_1 修改为 D:\oracle\product\10.2.0\db_1\ 开启服务 : 我的电脑, 右键, 管理, 服务, 开启 OracleServiceORCL 和 OracleOraDb10g_home1TNSListener 服务这样保证了服务器端监听器能正确监听, 用服务器端的 sql*plus 能正确登录. 2. 用 SQL*Plus 连接到 Oracle 数据库, 开始程序 Oracle-OraDb10g_Home1 应用程序开发 SQL*Plus 用户名 :sys 口令 orcl 主机字符串 :orcl as sysdba 如图所示 : 第 4/17 页 4

5 练习下面命令 : (1)set pause on // 参数信息可能很多, 设置分屏显示内容 Select * from V$parameter; // 查询数据库的参数信息 : 或者 show parameter (2)Show parameter sga // 用以下命令查看实例相应内存的大小 (3)set pause on // 后台进程信息可能很多, 设置分屏显示内容 Select * from v$bgprocess; // 查看后台进程 : (4) 练习把 scott 用户加锁或解锁 :alter user scott account unlock/lock; (5)Conn scott/tiger@orcl // 改用 scott 登陆 : Select table_name from user_tables; Desc emp Desc dept // 查看 emp 表结构 // 查看 dept 表结构 (6) 修改 scott 用户密码为 mytiger: alter user scott identified by mytiger 3. 练习在命令行下运行 SQL*PLUS 的方法, 并查看 emp 表中数据信息 4. 掌握 Sys,sytem,scott 用户登陆以及几个用户之间切换, 并用 show user 查询当前用户 5. 用 sys 登陆后, 在 SQL> 提示符后面输入下面的 SELECT 语句, 可以查看所有 Oracle 数据库的名称和创建日期 6. 查看 scott.emp 的表结构所及所有记录 7. 练习用 edit 命令编辑 SQL 命令的方法 8. 查询 emp 表中 sal>1200 的记录信息, 用脚本保存到 D:\test1.sql, 并运行该脚本 ( 用 save 与 start 完成 ) 9. 用 spool 命令把 emp 表中 sal>1200 信息输出到 d:\ex1.txt 文件中 10. 使用追加缓冲区命令, 查询 Emp 表中 sal 大于 1200, 且 job 为 CLERK 的记录信息 11. 查询 scott.emp 表中员工号与员工工资, 要求在员工工资数值前加上本地货币符号 12. 可以用 LIST 命令来列出当前 SQL 缓冲区中的第 1 行或 2 行到第 3 行命令语句第 5/17 页 5

6 13. 查询 scott.emp 表中员工工资在 1000~2000 记录信息, 使用命令行方式 SQL 缓冲区方式脚本文件三种方式运行 SQL 语句 14. 把 Select sal,sal*100 from scott.emp 语句中乘号 ( * ) 改为加号 ( + ) 15. 在当前行 select sal,sal+100 from emp 后加 where sal>=2000, 显示运行结果 16. 设置一行可容纳的 120 个字符, 显示表 emp 的信息 17. 设置每页显示的 15 行, 显示表 emp 的信息 18. 设置列名员工号员工姓名工资, 显示 emp 表中信息第 6/17 页 6

7 实验 2 数据库物理存储和逻辑存储设置与管理 1. 掌握 Oracle 数据库与实例概念 ; 2. 掌握 Oracle 数据库的物理存储 ; 3. 掌握 Oracle 数据库的逻辑存储实验内容 : 1. 使用 SQL 命令创建一个本地管理方式下的自动分区管理的表空间 USERTBS1, 其对应的数据文件大小为 20MB 2. 修改 USERTBS1 表空间的大小, 将该表空间的数据文件修改为自动扩展方式, 最大值为 100MB 3. 为 USERTBS1 表空间添加一个 10M 数据文件, 以改变该表空间的大小 4. 删除表空间 USERTBS1, 同时删除该表空间的内容以及对应的操作系统文件 5. 查询当前数据库中所有的表空间及其对应的数据文件信息 6. 为 USERS 表空间增加一个数据文件, 文件名为 userdata03.dbf, 大小为 20M 7. 修改 USERS 表空间中的 userdata03.dbf 为自动扩展方式, 每次扩展 5MB, 最大为 50MB 8. 将 USERS 表空间中的 userdata03.dbf 更名为 userdata04.dbf 9. 为数据库添加一个重做日志文件组, 组内包含两个成员文件, 分别为 redo4a.log 和 redo4b.log, 大小为分别为 5MB. 10. 为新建的重做日志文件组添加一个成员文件, 名称为 redo4c.log 11. 将数据库设置为归档模式, 在切换回非归档模式第 7/17 页 7

8 实验 3 数据库对象的创建与管理 (1) 1. 掌握 Oracle 表的创建与基本操作 ; 2. 掌握表的约束类别及各个约束的应用 ; 实验内容 : 一完成教材案例数据库的创建 1. 创建 REGIONS 表 2. 创建 COUNTRIES 表 3. 创建 LOCATIONS 表 4. 创建 DEPARTMENTS 表 5. 创建 JOBS 表 6. 创建 EMPLOYEES 表 7. 创建 JOB_HISTORY 表 8. 创建 SAL_GRADES 表 9. 创建 USERS 表二完成下列各个题目 : 1. 在 orcl 数据库中创建一个名为 student 的表, 要求 :(sno char(6) not null, sname char(10) not null, ssex char(2) not null, birthday date not null, polity char(20)), 其中表中字段满足 :sno 设置为主键,sname 字段设置惟一性约束 ; 2. 将 student 表的 ssex 设置检查性约束, 要求 ssex 只能为男或女,polity 字段设置默认约束, 值为团员 3. 在 orcl 数据库中创建一个名为 course 的表 (cno, cname, teacher, class) 4. 创建一个学生选课表 sc(sno char(6) not null,cno char(10) not null,grade number(3) ). 将 sc 表创建外键约束, 把 sc 表的 sno 和 student 表的 sno 关联起来, 在这两个表之间创建一种制约关系第 8/17 页 8

9 实验 4 数据库对象的创建与管理 (2) 1. 掌握 Oracle 表的创建与基本操作 ; 2. 掌握表的约束类别及各个约束的应用 ; 3. 掌握索引索引化表分区视图序列功能实验内容 : 1. 按下列表结构利用 SQL 语句创建 exer_class exer_student 两个表 Exer_class 列明数据类型约束备注 CNO NUMBER(2) 主键班号 CNAME VARCHAR2(20) 班名 NUM NUMBER(3) 人数 Exer_student 列明数据类型约束备注 SNO NUMBER(4) 主键学号 SNAME VARCHAR2(10) 姓名 SAGE NUMBER 年龄 SEX CHAR(2) 性别 CNO NUMBER(2) 班级号 2. 为 exer_student 表的 SAG 列添加一个检查约束, 保证该列取值在之间 3. 为 exer_student 表的 SEX 列添加一个检查约束, 保证该列取值为 M 或者 F, 且默认值为 M. 4. 在 exer_class 表的 CNAME 列上创建一个唯一性索引 5. 创建一个视图, 包含学生及其班级信息 6. 创建一个序列, 起始值为 , 作为学生的学号 7. 创建一个 exer_student_rang 表 ( 列类型与 exer_student 表的列类型相同 ), 按学生年龄分为 3 个区, 低于 20 岁的学生信息放到 part1 区, 存储在 example 表空间中 ;20-20 岁的学生信息放在 part2 区, 存放在 ORCLTBS1 表空间中 ; 其他数据放在 part3 区, 存放在 ORCLTBS2 表空间中 8. 创建一个 exer_student_list 表 ( 列类型与 exer_student 表的列类型相同 ), 按学生性别分为两个区 9. 为 exer_student_range 表创建本地分区索引第 9/17 页 9

10 实验 5 数据操纵 DML 和数据查询语言 DQL(1) 1. 掌握对表数据的插入更新删除操作语句语法 2. 熟练利用 insert update delete 语句完成对表视图的数据操作 3. 了解事务的基本概念和使用实验内容 : 一在实验 (3) 第二题的基础上, 完成下列各题 : 1. 利用 insert into 语句向 student 表中插入以下新的记录 : ( 0001, 张三, 男, , 团员 ) ( 0002, 李思, 女, , 团员 ) ( 0003, 王五, 女, , 团员 ) ( 0004, 赵六, 男, , 团员 ) ( 0005, 张琪, 女, , 团员 ) ( 0006, 吴一, 女, , 团员 ) ( 0007, 郑久, 男, , 团员 ) 2. 用 insert into 语句向 course 表插入以下新记录 : ( , 数据库, 王力, A101 ) ( , 大学英语, 张美, B201 ) ( , 程序设计, 张强, C502 ) 3. 用 insert into 语句向 sc 表插入以下新记录 : ( 0001, ,65) ( 0001, ,85) ( 0002, ,78) ( 0002, ,90) ( 0003, ,90) ( 0004, ,65) ( 0005, ,60) ( 0006, ,50) 第 10/17 页 10

11 4. 利用 update 语句将编号为 0004 的学生 polity 改为党员 5. 利用 delete 语句将大学英语成绩小于 60 的同学记录删除二依据以下三个表的内容完成各题 : BOOK No Title Author Publish PUB_DA TE Oracle9i 数据库系李代平冶金工业出版社统管理 Oracle9i 中文版入赵松涛人民邮电出版社门与提高 Oracle9i 开发指南 : Joan 电子工业出版社 PL/SQL 程序设计 Casteel 数据库原理辅助与盛定宇电子工业出版社提高 Oracle9i 中文版实赵伯山电子工业出版社用培训教程 Oracle8 实用教程翁正科电子工业出版社等 8 READER RNO RNAME 张三李凤孟欣谢非刘英 BORROW NO RNO BORROW_DATE :06: :06: :06: :06: :06:58 Price 根据下面 BOOK READER BORROW 表结构创建表, 并插入数据 2. 插入一条图书信息 : 编号 : , 书名 :java 网络编程, 作者 : 李程等, 出版社 : 电子工业出版社, 出版日期 : , 价格 :35 3. 将图书标号为的图书价格改为 29 元 4. 删除图书编号为的图书信息第 11/17 页 11

12 实验 6 数据操纵 DML 和数据查询语言 DQL(2) 1. 掌握 SELECT 语句基本语法 ; 2. 掌握利用 SELECT 基本查询查询分组统计排序连接查询嵌套子查询实验内容 : 依据教材案例数据库完成下列各题 : 1. 查询 100 号部门的所有员工信息 2. 查询所有职位编号为 SA_MAN 的员工的员工号员工名和部门号 3. 查询每个员工的员工号工资奖金以及工资与奖金的和 4. 查询 40 号部门中职位编号为 AD_ASST 和 20 号部门中职位编号为 SA_REP 的员工的信息 5. 查询所有职位名称不是 Stock Manager 和 Purchasing Manager, 且工资大于或等于 2000 的员工的详细信息 6. 查询有奖金的员工的不同职位编号和名称 7. 查询没有奖金或奖金低于 100 元的员工信息 8. 查询员工名 (first_name) 中不包含字母 S 的员工 9. 查询员工的姓名和入职日期, 并按入职日期从先到后进行排序 10. 查询所有员工的姓名及其直接上级的姓名 11. 查询入职日期早于其直接上级领导的所有员工信息 12. 查询各个部门号部门名称部门所在地以及部门领导的姓名 13. 查询所有部门及其员工信息, 包括那些没有员工的部门 14. 查询所有员工及其部门信息, 包括那些还不属于任何部门的员工 15. 查询所有员工的员工号员工名部门名称职位名称工资和奖金 16. 查询至少有一个员工的部门信息 17. 查询工资比 100 号员工工资高的所有员工信息 18. 查询工资高于公司平均工资的所有员工信息第 12/17 页 12

13 19. 查询各个部门中不同职位的最高工资 20. 查询各个部门的人数及平均工资 21. 统计各个职位的员工人数与平均工资 22. 统计每个部门中各职位的人数与平均工资 23. 查询最低工资大于 5000 元的各种工作 24. 查询平均工资低于 6000 元的部门及其员工信息 25. 查询在 Sales 部门工作的员工的姓名信息 26. 查询与 140 号员工从事相同工作的所有员工信息 27. 查询工资高于 30 号部门中所有员工的工资的员工姓名和工资 28. 查询每个部门中的员工数量平均工资和平均工作年限 29. 查询工资为某个部门平均工资的员工的信息 30. 查询工资高于本部门平均工资的员工的信息 31. 查询工资高于本部门平均工资的员工的信息及其部门的平均工资 32. 查询工资高于 50 号部门某个员工工资的员工的信息 33. 查询工资奖金与 10 号部门某员工工资奖金都相同的员工的信息 34. 查询部门人数大于 10 的部门的员工信息 35. 查询所有员工工资都大于元的部门的信息 36. 查询所有员工工资都大于 5000 元的部门的信息及其员工信息 37. 查询所有员工工资都在 4000 元 ~8000 元之间的部门的信息 38. 查询人数最多的部门信息第 13/17 页 13

14 实验 7 PL/SQL 程序设计基础 (1) 1. 掌握 PL/SQL 语言的程序结构和基本语法知识 ; 2. 掌握 PL/SQL 语言流程控制结构 ; 实验内容 : 注意 : 在完成下列题目时, 首先要有 employees 表, 提示大家可以利用子查询建表的方式先建立 employees 表, 并将数据输入如 :create table employees AS select * from hr.employees ; 1. 编写一个 PL/SQL 块, 输出所有员工的员工名员工号工资和部门号 2. 定义 PL/SQL 程序块, 查询 FIRST_NAME 为 'William',LAST_NAME 为 'Smith' 的姓名工资和入职日期, 显示到屏幕上, 然后再把 William Smith 的工资增加 100 元, 之后重新显示她的姓名和工资 3. 输入一个员工号, 如果此员工的工资大于 3000 元, 则工资加 500 元 ; 如果小于 3000 元, 则工资加 300 元, 如果没有此员工给出异常的提示信息 (NO_DATA_FOUND) 为此员工不存在! 提示: 用 IF 语句 4. 编写一个 PL/SQL 块, 输出所有比本部门平均工资高的员工信息 5. 利用 FOR 循环向 employees 表中插入 50 条记录. 6. 输入一个员工号, 修改该员工的工资, 如果该员工为 10 号部门, 工资增加 100; 若为 20 号部门, 工资增加 150; 若为 30 号部门, 工资增加 200; 否则增加 300 用 if 语句完成注意 : 编写完每个程序块后, 在 sqlplus 下利用 / 运行程序块第 14/17 页 14

15 实验 8 PL/SQL 程序设计基础 (2) 1. 掌握显式游标隐式游标的使用方法 ; 2. 掌握游标变量的使用方法 3. 掌握建立存储过程的方法实验内容 : 1. 根据输入的部门号查询某个部门的员工信息, 部门号在程序运行时指定 ( 用游标 ) 2. 利用 WHILE 循环统计并输出各个部门的平均工资 3. 修改员工的工资, 如果员工的部门号为 10, 工资提高 100; 部门号为 20, 工资提高 150; 部门号为 30, 工资提高 200; 否则工资提高 250 ( 用游标完成 p334) 4. 使用游标提取部门 30 员工的姓名和工资 5. 创建一个存储过程, 以部门号为参数, 返回该部门的人数和最低工资 6. 创建存储过程, 以工资为参数, 如果工资小于 2000 则输出低, 如果工资大于 2000 并且小于 3000, 则输出中, 如果工资大于 3000 则输出高 7. 创建一个存储过程, 以部门号为参数, 查询该部门的平均工资, 并输出该部门中比平均工资高的员工号员工名第 15/17 页 15

16 实验 9 存储过程函数触发器的程序设计与开发 1. 掌握 Oracle 的存储过程与函数的使用方法 2. 掌握 Oracle 的触发器的使用方法实验内容 : 1. 创建一个存储过程, 以 EMPLOYEES 表中职位 JOB_ID 为参数, 返回该职位的人数与最低工资然后以职位为 ST_CLERK 为例, 调用该存储过程, 并输出该职位的人数与最低工资 2. 针对 EMPLOYEES 表, 创建一个存储过程, 以部门号为参数, 返回该部门的人数最高工资和最低工资然后以部门号为 100 为例, 调用该存储过程, 并输出该部门的人数最高工资与最低工资 3. 创建一个以部门号为参数, 返回该部门平均工资的函数调用该函数, 并输出 EMPLOYEES 表中各个部门的平均工资 4. 针对 EMPLOYEES 表, 创建一个以职工号为参数, 返回该职工工资的函数调用该函数, 输出 EMPLOYEES 表中每个职工的工资及 EMPLOYEES 表中所有员工工资总和 5. 为 EMPLOYEES 表创建一个触发器, 当执行插入更新与删除时, 统计操作后各个部门的人数及平均工资关于触发器使用提示 : 错误提示 :ORA-O4O89: 无法对 sys 拥有的对象创建触发器出现上面这种情况一定是触发器操作的表的 owner 为 sys Sys 用户的对象不允许利用触发器修改 ( 大多涉及系统内容 ) 解决方法 : 用 sys 系统用户登录, 创建名称为该表名称相同的表, 数据从 sys 的表里面拷贝如 : CREATE TABLE scott.employees AS SELECT * FROM SYS.employees; 然后删除掉 sys 底下的表 DROP TABLE SYS.employees; 再执行创建触发器语句第 16/17 页 16

17 实验 10 Oracle 的数据库安全恢复与备份 1. 掌握 Oracle 数据库的完整性约束 ; 2. 掌握 Oracle 的恢复与备份, 数据的导入与导出 ; 3. 掌握 Oracle 的安全与权限实验内容 : 1. 创建一个口令认证的数据库用户 usera_exer, 口令为 usera 默认表空间为 USERS, 配额为 10MB, 初始帐号为锁定状态 2. 创建一个口令认证的数据库用户 userb_exer, 口令为 userb 3. 为 usera_exer 用户授予 CREATE SESSION 权限 SCOTT.emp 的 SELECT 权限和 UPDATE 权限同时允许该用户将获得的权限授予其他用户 4. 用 usera_exer 登陆数据库, 查询和更新 scott.emp 中的数据同时, 将 scott.emp 的 SELECT 权限和 UPDATE 权限授予 userb_exer 5. 禁止用户 usera_exer 将获得的 scott.emp 的 SELECT 权限和 UPDATE 权限授予其他人 6. 创建角色 rolea 和 roleb, 将 CREATE TABLE 权限 SCOTT.emp 的 SELECT 权限和 UPDATE 权限授予 rolea; 将 CONNECT, RESOURCE 角色授予 roleb 7. 将角色 rolea roleb 授予用户 usera_exer 8. 使用冷物理备份对数据库进行完全备份 9. 假定丢失了一个数据文件 example01.dbf, 试使用前面 ( 第 8 题 ) 做过的完全备份对数据库进行恢复, 并验证恢复是否成功 10. 使用热物理备份对表空间 users 的数据文件 user01.dbf 进行备份 11. 使用 EXP 命令导出 SCOTT 用户下的所有数据库对象 12. 创建一个用户 JOHN, 并使用 IMP 命令将 SCOTT 用户下的所有数据库对象导入第 17/17 页 17

sql> startup mount 改变数据库的归档模式 sql> alter database archivelog # 打开数据库 sql> alter database open 禁止归档模式 sql> shutdown immediate sql>startup mount sql> al

sql> startup mount 改变数据库的归档模式 sql> alter database archivelog # 打开数据库 sql> alter database open 禁止归档模式 sql> shutdown immediate sql>startup mount sql> al RMAN sql> sqlplus / as sysdba 查看数据库版本 sql> select * from v$version; 查看数据库名称 sql> show parameter db_name; 一使用 RMAN 时, 需要将数据库设置成归档模式 sql> conn / as sysdba; sql> show user 查看数据库是否为归档模式 sql> archive log list