Oracle课程实验指导书

一、Oracle基础

1、SQL语言基础

实验准备

1、提前找班主任拿到机房上机卡、登录服务器的用户名和密码。

2、准时到指定机房，作好实验准备，进入xp64系统；

3、按以下步骤登录数据库服务器：

(1)登录本地数据库服务器：开始->运行，输入cmd进入DOS环境后输入：

sqlplus / as sysdba

(2)查看SCOTT用户状态：

select username,account_status from dba_users where username='SCOTT';

若account_status为‘LOCKED’，修改用户到‘OPEN’状态：

alter user SCOTT account unlock;

(3)以SCOTT用户身份在sqlplus中登陆数据库：

用户名：scott

密码：tiger

4、成功登录后，设置好sqlplus的使用环境;

(1)set pagesize 100

(2)set linesize 100

实验一查询语句练习

1.实验内容与目的

熟练使用select语句进行简单条件和复合条件的查询操作，掌握子查询、分组及函数的应用。

2.实验步骤

(1)通过查数据字典了解SCOTT用户下拥有那些数据库对象，并了解所有表

的结构；

(2)查询emp表中所有信息，并按照职员姓名的字母升序排列；

(3)显示所有部门中地址不以“N”字母打头的部门信息；

(4)查询公司中所有职员所赚工资及所处的工资级别，

(5)统计工资级别为4的总人数；

(6)显示部门30中所有职员的工资总和，并使显示结果以TOTAL30为列头。

(7)查询工资与佣金总和低于1000元的职员名宇、工资、佣金和所在部门号。

(8)查询1981年6月30日之后受雇的职员的最高报酬（包括工资和佣金）。

(9)查询平均工资大于1500元的部门的部门号，职员人数和平均工资。

(10)显示各部门的部门号及所属厂职员的平均工资，并按平均值由大到小的次

序排列。

(11)查询EMP表中在部门10和部门20中工作，且在1981年3月30日以后

雇用的职员的名字、工资、受雇日期、并按照受雇日期的先后顺序排序。

(12)查询部门20中工资大于该部门平均工资的职员信息；

(13)查询能获得红利（即在BONUS表中存在）的推销员的名字，受雇日期及

工资额。

(14)查询在第30号部门工作的所有受雇时间早于他的老板的职员的名字和受

雇日期及其老板的名字及受雇日期。

(15)查询最先受雇的职员姓名及受雇日期。

3.实验答案

(1)select * from cat; desc emp; desc dept; desc salgrade;

表结构如下：

emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

dept(deptno,dname,loc)

salgrade(grade,losal,hisal)

bonus(ename,job,sal,comm);

说明：comm为佣金

注意：日期1981-3-30按格式’yyyy-mon-dd’显示，英文系统表示为

‘1981-March-30’，而中文系统表示为’1981-3月-30’。

(2)select * from emp order by ename asc;

(3)select * from dept where loc not like ‘N%’;

(4)select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between

s.losal and s.hisal;

(5)select count(*) from emp e, salgrade s where s.grade=4 and e.sal between

s.losal and s.hisal;

(6)select deptno, ename, sal+nvl(comm,0) total30 from emp where deptno=30;

(7)select ename, sal, comm, deptno from emp where (sal+nvl(comm,0))<1000;

(8)select max(sal+nvl(comm,0)) from emp where hiredate> to_date('1981-6-30',

'yyyy-mm-dd');

(9)select deptno,count(*),avg(sal) from emp group by deptno having avg(sal)

>1500;

(10)s elect deptno,count(*),avg(sal) from emp group by deptno order by 3;

注意：缺省为升序排列，3表示第三个变量，group by中不能用；

(11)s elect ename,sal,hiredate from emp where hiredate > to_date('1981-3-30',

'yyyy-mm-dd') and (deptno=10 or deptno=20) order by hiredate;

(12)s elect * from emp where deptno=20 and sal > (select avg(sal) from emp where

deptno =20);

(13)s elect ename,hiredate,sal from emp where job=’SALESMAN’ and ename in

(select ename from bonus);

(14)s elect e.ename,e.hiredate,m.ename,m.hiredate from emp e,emp m where

e.deptno=30 and e.hiredate < m.hiredate and e.mgr=m.empno;

(15)s elect ename,hiredate from emp where hiredate=(select min(hiredate) from

emp);

实验二DML/DDL语句练习

1.实验内容与目的

熟练使用insert、update、delete语句对数据进行维护，并掌握创建和删除数据库表和视图的方法。

2.实验步骤

(1)添加一条记录到EMP表中：基本信息如下：

姓名：KELLY，部门号10，员工编号7935，工作为CLERK，经理编号为7782，雇用日期为82年5月23日，工资1300，没有佣金；

(2)修改员工KELLY的信息，将其佣金改为0；

(3)删除名字为KELLY的员工信息；

(4)撤销（3）中的删除操作，还原KELLY的信息；如果（3）中操作用commit

提交是否还可以还原KELLY的信息，请自行实验验证。

(5)用replace函数将job字段中的内容加上引号“”，查看效果，并回滚；

(6)将部门30的所有职员的工资都提升10％。

(7)创建表name(ename varchar2(20), mgrname varchar2(20));

(8)将emp表中职业为CLERK，且比他的上司受雇时间早的职工的名字及其

上司的名字存入新建的表NAME中；查看name表中的内容。

(9)，在system用户下创建表test_system，字段自己定义，然后插入一条数据，

并将表test_system的查看权限授权给SCOTT用户，用SCOTT用户查看

表test_system内容；

(10)在system用户下撤销（9）中授予的权限，看看是否可以撤销该操作，并

解释发生的现象；

(11)在system用户下删除表test_system表，看看是否可以撤销该操作，并解

释发生的现象；

3.实验答案

(1)insert into emp values(7935, 'kelly', 'CLERK', 7782, to_date('23-05-1982',

'dd-mm-yyyy'), 1300, NULL, 10);

(2)update emp set comm=0 where ename=’KELLY’;

(3)delete from emp where ename =’KELLY’;

(4)rollback;

注意：如果用commit提交了是不能回滚的。

(5)update emp set job=replace(job,job,’”’||job||’”’); select * from emp; rollback;

注：job子段长度不够时须修改表结构alter table emp modify(job varchar2(20))。

(6)update emp set sal=sal*1.1 where deptno=30;

(7)create table name(ename varchar2(20), mgrname varchar2(20));

(8)insert into name select e1.ename, e2.ename from emp e1,emp e2 where

e1.job='CLERK' and e1.mgr=e2.empno and e1.hiredate < e2.hiredate;

(9)修改system用户密码为systempass:开始->运行，输入cmd进入DOS环境

后输入：

sqlplus / as sysdba

alter alter user system identified by systempass;

以system用户身份登陆sqlplus后执行：

Create table test_system(ename varchar2(20), mgrname varchar2(20));

Insert into test_system values(‘员工’,’领导’);

select * from test_system; grant select on test_system to SCOTT;

(10)r evoke select on test_system from SCOTT; rollback;

现象：不报错，但回滚没有起作用，revoke操作自动提交；

(11)d rop table test_system; rollback; 现象同上；

2、数据库对象练习

实验一PL/SQL语法训练

1.实验内容与目的

掌握基本的编程语法，编写PL/SQL程序完成简单的任务；

2.实验步骤

(1)用PL/SQL将EMP表中职员'SMITH'的工资增加100元，并提交给数据库;

(2)若MARTIN的佣金不足1500元，则将其工资提为1500元;

(3)查询职业为CLERK，且比他的上司受雇时间早的职工的名字及其上司的

名字，并将存入新建的表NAME中。用两种方法来解答：①用光标WHILE

循环实现；②用光标FOR循环实现。

(4)步骤（3）中，设工作为CLERK的职员不能是公司中最先受聘的员工，

若发生这种情况，将该职员提升为经理（MANAGER）。

3.实验答案

(1)程序如下：

declare

name varchar2(10):=’SMITH’;

begin

update emp set sal=sal+100 where ename=name;

commit;

end;

(2)程序如下:

declare

salary number(7,2);

begin

select nvl(comm,0) into salary from emp where ename=’MARTIN’;

if salary < 1500 then update emp set sal=1500 where ename=’MARTIN’;

end if;

commit;

e nd;

(3)程序如下:

WHILE循环实现

declare

cursor c1 is

select e1.ename, e2.ename from emp e1,emp e2 where e1.job='CLERK'

and e1.mgr=e2.empno and e1.hiredate < e2.hiredate;

empname emp.ename%TYPE;

mgrname emp.ename%TYPE;

begin

open c1;

fetch c1 into empname,mgrname;

while c1%FOUND loop

insert into name values(empname, mgrname);

fetch c1 into empname,mgrname;

end loop;

close c1;

end;

FOR循环实现:

declare

cursor c1 is

select e1.ename empname, e2.ename mgrname from emp e1,emp e2 where

e1.job='CLERK' and e1.mgr=e2.empno and e1.hiredate < e2.hiredate;

emp_rec c1%ROWTYPE;

begin

for emp_rec in c1 loop

insert into name values(emp_rec.empname, emp_rec.mgrname);

end loop;

end;

(4)程序如下:

declare

edate date;

sdate date;

begin

select min(hiredate) into edate from emp;

select min(hiredate) into sdate from emp where job='CLERK';

if sdate = edate then update emp set job='MANAGER' where hiredate=sdate and job=’CLERK’;

end if;

end;

实验二数据库对象练习

1.实验内容与目的

掌握创建索引、视图、触发器等数据库对象的办法；

了解相关的数据字典表：user_objects, user_source, user_constraints……

2.实验步骤

(1)根据EMP表建立一个雇员和经理对应表ETAB，所用的列名如下：

雇员姓名；

经理姓名；

部门编号；

部门名称；

(2)对上表的前两列建立素引，并将索引命名为EINDX。

(3)根据EMP表建立包含上表数据的视图EVIEW，要求没有重复记录;

(4)创建一个序列ESEQ。

(5)修改ETAB表，增加一列存放序列的取值，并从EMP和DEPT表中将相

应数据填充到ETAB中。

(6)给ETAB表新增加的列增加主键约束。

(7)在EMP表上编写一个触发器，审计任何对Sal和comm列的修改（将每次

修改此两列的数据库用户，修改的时间，修改的用户，改前和改后此两列

的值存入新建的一个审计表中），然后测试其工作是否正常。

3.实验答案

(1)create table etab(

empname varchar2(10),

mgrname varchar2(10),

deptno number(2),

deptname varchar2(14)

);

(2)create index eindx on etab(empname,mgrname);

(3)create view eview as select distinct e1.ename empname,e2.ename

mgrname,e1.deptno deptno,dname deptname from emp e1,emp e2,dept where e1.mgr=e2.empno and e1.deptno=dept.deptno;

(4)create sequence eseq increment by 1 start with 10000 maxvalue 19999;

(5)alter table etab add( no number(5));

insert into etab select e1.ename empname,e2.ename mgrname,e1.deptno deptno,dname deptname,eseq.nextval from emp e1,emp e2,dept where e1.mgr=e2.empno and e1.deptno=dept.deptno

(6)alter table etab add ( constraint c1 primary key(no));

(7)

create table audit_emp

(

database_user varchar2(30),

modify_date date,

modify_user number(4),

sal_before_update number(7,2),

sal_after_update number(7,2),

comm_before_update number(7,2),

comm_after_update number(7,2)

);

create or replace trigger audit_emp before update of sal, comm on emp

for each row

begin

insert into audit_emp

(database_user, modify_date, modify_user,

sal_before_update, sal_after_update,

comm_before_update,comm_after_update)

values(user, sysdate, :old.empno, :old.sal, :new.sal, :https://www.360docs.net/doc/f114953098.html,m, :https://www.360docs.net/doc/f114953098.html,m);

end;

实验三PL/SQL编程

1.实验内容与目的

通过PL/SQL程序完成一些复杂的任务，掌握过程和包的编写规范；

掌握用show error命令分析错误的方法。

2.实验步骤

分别编写带参数的存储过程和包，有两个参数，参数1为要加薪的部门号，参数2为加薪幅度，实现给职工加工资，部门号为0表示给所有部门的职工都加。

3.实验答案

过程实现如下：

create or replace procedure add_sal (dep in number, add_level in number)

as

begin

if (dep = 0) then

update emp set sal=sal*(1+add_level);

else

update emp set sal=sal*(1+add_level)

where deptno=dep;

end if;

commit;

end add_sal;

包实现如下：

create or replace package test_pack as

procedure add_sal(dep in number, add_level in number);

end test_pack;

create or replace package body test_pack as

procedure add_sal

(

dep in number,

add_level in number

)

as

begin

if (dep = 0) then

update emp set sal=sal*(1+add_level);

else

update emp set sal=sal*(1+add_level) where deptno=dep;

end if;

commit;

end add_sal;

end test_pack;