ORACLE试验参考指导书.doc

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 Server 网络操作系统，Windows /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； 试验二 SQL语言中分组查询和连接查询语句一、试验目标 1、掌握SQL语言中分组查询命令功效及其使用方法2、掌握SQL语言中连接查询命令功效及其使用方法二、试验环境1、硬件设备：计算机局域网，服务器1台，用户机100台2、软件系统：Windows Server 网络操作系统，Windows /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雇员及所在部门情况 。 SQLSelect ename，sal，emp.deptno，loc，dname 2 From emp，dept 3 Where Emp.deptno=Dept.deptno and sal3000 ； (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 Server 网络操作系统，Windows /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) 多行值子查询：子查询返回一组纪录查工资在￥到￥2900之间雇员所在部门雇员。 SQL Select ename，sal ，job，deptno From emp Where deptno IN ( Select deptno From emp Where sal between and 2900); 查工资在￥到￥2900之间雇员所在部门雇员。 SQL Select ename，sal ，job，deptno From emp Where deptno= ANY ( Select deptno From emp Where sal between 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)，salFrom empWhere deptno=20) ； 2、子查询命令中函数使用将全部雇员名字用大小写形式显示 。 SQL Select empno，Upper(ename)，Initcap(ename) From emp；计算全部雇员日工资（设30天为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 ( &ob，1 ，ename，2，sal，ename)； 试验四 SQL语言中数据操纵、事务控制和数据定义语句一、试验目标 1、掌握SQL语言中数据操纵命令功效及其使用方法2、掌握SQL语言中事务控制命令功效及其使用方法3、掌握SQL语言中数据定义命令功效及其使用方法二、试验环境1、硬件设备：计算机局域网，服务器1台，用户机100台2、软件系统：Windows Server 网络操作系统，Windows /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；(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 Server 网络操作系统，Windows /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 Server 网络操作系统，Windows /XP用户机操作系统； Oracle9i服务端数据库系统，用户端工具。三、试验内容1、SQL/PL块中步骤控制语句使用(1) 条件控制语句使用将EMP表中名为SMITH雇员工资进行修改，若原工资大于$，则加$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