Oracle课程实验指导书.doc

一、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) to_date(1981-6-30, yyyy-mm-dd);(9) select deptno,count(*),avg(sal) from emp group by deptno having avg(sal) 1500;(10) select deptno,count(*),avg(sal) from emp group by deptno order by 3;注意：缺省为升序排列，3表示第三个变量，group by中不能用；(11) select 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) select * from emp where deptno=20 and sal (select avg(sal) from emp where deptno =20);(13) select ename,hiredate,sal from emp where job=SALESMAN and ename in (select ename from bonus); (14) select 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) select 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 运行，输入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) revoke select on test_system from SCOTT; rollback; 现象： 不报错，但回滚没有起作用，revoke操作自动提交；(11) drop 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) 程序如下：declarename varchar2(10):=SMITH;beginupdate emp set sal=sal+100 where ename=name;commit;end;(2) 程序如下:declaresalary number(7,2);beginselect 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;end;(3) 程序如下:WHILE循环实现declarecursor c1 isselect 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;beginopen c1; fetch c1 into empname,mgrname;while c1%FOUND loopinsert into name values(empname, mgrname);fetch c1 into empname,mgrname;end loop;close c1;end;FOR循环实现:declarecursor c1 isselect 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;beginfor emp_rec in c1 loopinsert into name values(emp_rec.empname, emp_rec.mgrname);end loop;end;(4) 程序如下:declareedate 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_constraints2. 实验步骤(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_uservarchar2(30),modify_datedate,modify_usernumber(4),sal_before_updatenumber(7,2),sal_after_updatenumber(7,2),comm_before_updatenumber(7,2),comm_after_updatenumber(7,2);create or replace trigger audit_emp before update of sal, comm on empfor each rowbegin 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.e