ORACLE实验指导书

ORACLE数据库技术实验指导书 ORACLE数据库技术实验指导书 目 录 实验一 SQL PLUS用法及SQL语言中的简单查询语句 实验二 SQL语言中的分组查询和连接查询语句 实验三 SQL语言中的子查询及函数的使用 实验四 SQL语言中的数据操纵 事务控制和数据定义语句 实验五 SQL PL块中SQL命令的使用 实验六 SQL PL块中流程控制语句 游标 例外处理 实验七 数据库触发器 存储过程和存储函数 实验一 SQL PLUS用法及SQL语言中的简单查询语句 一 实验目的 1 熟悉SQL语言交互式使用工具SQL PLUS的使用方法 2 熟悉SQL PLUS常用命令的功能和使用方法 3 掌握SQL语言中简单查询语句的功能和使用方法 二 实验环境 1 硬件设备 计算机局域网 服务器1台 客户机100台 2 软件系统 Windows 2000 Server 网络操作系统 Windows 2000 XP客户机操作系统 Oracle9i服务端数据库系统 客户端工具 三 实验内容 1 SQL PLUS的使用 1 进入 启动 SQL PLUS 在Windows环境下 双击SQL PLUS图标或从程序组找SQL PLUS 出现登录窗口 输入正确的数据库用户名 密码和连接字符串后 若连接数据库成功 则会出现如下提示符 SQL 2 退出 SQL PLUS SQL exit 则退回到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的结构 SQL desc 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 6 执行SQL缓冲区中的命令 SQL 直接执行 SQL run 先显示命令的内容 再执行 7 执行磁盘上的命令文件 a 先调入缓冲区 再运行 SQL get f ora dept sql SQL b 用 或start命令将指定命令文件调入缓冲区并执行 SQL f ora dept sql 或 SQL start 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 实验二 SQL语言中的分组查询和连接查询语句 一 实验目的 1 掌握SQL语言中分组查询命令的功能及其使用方法 2 掌握SQL语言中连接查询命令的功能及其使用方法 二 实验环境 1 硬件设备 计算机局域网 服务器1台 客户机100台 2 软件系统 Windows 2000 Server 网络操作系统 Windows 2000 XP客户机操作系统 Oracle9i服务端数据库系统 客户端工具 三 实验内容 1 分组查询命令的使用 1 不带条件的分组查询 每组只返回一条记录 计算每个部门的工资总和 SQL select deptno sum sal 2 From emp 3 Group by deptno 查每部门每个工种的雇员数 SQL select deptno job count 2 From emp 3 Group by deptno job 2 带条件的分组查询 部门工资总和超过 9000的部门 并按工资总和升序排列 SQL select deptno sum sal 2 From emp 3 Group by deptno 4 Having sum sal 9000 5 Order by sum sal 2 连接查询命令的使用 1 无条件连接 SQL Select Dept Deptno Dname Empno Ename Sal 2 From Dept Emp 2 等值连接 参与连接的表在公共列上有相同值 查看工资高于 3000的雇员及所在部门情况 SQL Select ename sal emp deptno loc dname 2 From emp dept 3 Where Emp deptno Dept deptno and sal 3000 3 自连接 查询每个雇员的经理的名字 SQL select worker ename worker empno manager ename manager manager empno mgr From emp worker emp manager Where worker mgr manager empno 4 外连接 可避免连接时丢失信息 SQL Select ename dept deptno loc From emp dept Where emp deptno dept deptno SQL Select ename dept deptno loc From emp dept Where emp deptno dept deptno SQL Select ename dept deptno loc From emp dept Where dept deptno emp deptno 实验三 SQL语言中的子查询及函数的使用 一 实验目的 1 掌握SQL语言中子查询命令的功能及其使用方法 2 掌握子查询命令中的函数的使用方法 二 实验环境 1 硬件设备 计算机局域网 服务器1台 客户机100台 2 软件系统 Windows 2000 Server 网络操作系统 Windows 2000 XP客户机操作系统 Oracle9i服务端数据库系统 客户端工具 三 实验内容 1 子查询命令的使用 1 单行值子查询 子查询返回一条纪录 查询工资高于7698号雇员 并且工种与他相同的雇员情况 SQL select ename sal job empno From emp Where sal Select sal From emp Where empno 7698 and job Select job From emp Where mpno 7698 查询工资高于或等于20号部门工资额最高的雇员情况 SQL Select ename sal deptno From emp Where sal Select max sal From emp Where deptno 20 2 多行值子查询 子查询返回一组纪录 查工资在 2000到 2900之间的雇员所在部门的雇员 SQL Select ename sal job deptno From emp Where deptno IN Select deptno From emp Where sal between 2000 and 2900 查工资在 2000到 2900之间的雇员所在部门的雇员 SQL Select ename sal job deptno From emp Where deptno ANY Select deptno From emp Where sal between 2000 and 2900 Order by sal desc 3 多列值子查询 查询工资 奖金与20号部门雇员匹配 相同 的雇员情况 SQL Select ename sal comm deptno From emp Where NVL comm 0 sal IN Select NVL comm 0 sal From emp Where deptno 20 2 子查询命令中函数的使用 将所有雇员的名字用大小写形式显示 SQL Select empno Upper ename Initcap ename From emp 计算所有雇员的日工资 设一个月为25个工作日 SQL Select ename round sal 25 0 round sal 25 1 From emp 显示当前日期 dual 是系统提供的一张空表 SQL Select sysdate From dual 显示当前日期 SQL Select to char sysdate yyyy mm dd From dual 分部门统计所有雇员的中收入 SQL Select deptno sum sal comm sum sal NVL comm 0 From emp Group by deptno 根据输入的值将雇员信息排序 若输入1 则按ename排序 若输入2 则按SAL排序 缺省时 按ename 排序 SQL Select from emp order by decode 向dept表中一次插入多个纪录 所有字段可用 代替 SQL insert into dept deptno dname loc Values 将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 事务提交命令Commit SQL 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 6 数据库链操作 创建一个数据库链Link xsdb 用来连接远程销售数据库xsdb 其用户名为scott 口令为tiger SQL create Database Link Link xsdb connect to scott identified by tiger Using xsdb 实验五 SQL PL块中SQL命令的使用 一 实验目的 1 了解SQL PL块的基本结构与功能 2 掌握SQL PL块中各种SQL命令的使用方法 二 实验环境 1 硬件设备 计算机局域网 服务器1台 客户机100台 2 软件系统 Windows 2000 Server 网络操作系统 Windows 2000 XP客户机操作系统 Oracle9i服务端数据库系统 客户端工具 三 实验内容 1 SQL PL块中查询命令的使用 将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 SQL PL块中数据操纵命令的使用 向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 实验六 SQL PL块中流程控制语句 游标 例外处理 一 实验目的 1 掌握SQL PL块中流程控制语句的使用方法 2 掌握SQL PL块中游标的使用方法 3 掌握SQL PL块中例外处理的使用方法 二 实验环境 1 硬件设备 计算机局域网 服务器1台 客户机100台 2 软件系统 Windows 2000 Server 网络操作系统 Windows 2000 XP客户机操作系统 Oracle9i服务端数据库系统 客户端工具 三 实验内容 1 SQL PL块中流程控制语句的使用 1 条件控制语句的使用 将EMP表中名为SMITH的雇员的工资进行修改 若原工资大于 2000 则加 500 否则加 1000 SQL DECLARE 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 sal 20OO 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号部门增加新雇员 只确定雇员号 其它信息忽略 SQL DECLARE 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号部门增加新雇员 只确定雇员号 其它信息忽略 SQL DECLRE 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中 SQL DECLARE 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 3 SQL PL块中例外处理的使用 从EMP表中删去SMITH的信息 SQL DECLARE 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台 客户机100台 2 软件系统 Windows 2000 Server 网络操作系统 Windows 2000 XP客户机操作系统 Oracle9i服务端数据库系统 客户端工具 三 实验内容 1 数据库触发器的使用 创建一个数据库触发器 当任何时候某个部门从dept表中删除时 该触发器将从emp表中删除该部 门的所有雇员 SQL Create trigger del emp deptno trig Before delete ON dept For each row BEGIN dele