3493949142《数据库原理及应用》实验指导书

1、数据库原理及应用-oracle实验指导书信息科学与工程学院 2014年9月作者：何小卫目 录实验一oracle平台下基本sql语言的应用实验二oracle基本操作与用户权限基本管理实验三 数据字典视图实验四 pl-sql语言设计实验五 存储过程实验六 游标实验七 数据库触发器实验一：oracle平台下基本sql语言的应用实验目的在oracle下熟练运用sql语言来完成基本表的管理、索引的建立和删除、数据查询、数据更新、视图建立和删除等等操作。实验要求1、 件基本配置：intel pentiumiii以上级别的cpu，大于512mb的内存。2、 软件要求：window xp操作系统，oracle

2、 9i3、 实验学时：4学时。4、 实验报告。实验准备oracle 9i实验内容上机题1：利用create table 创建scott用户下的3个表，包括每个表的码和外码的定义。empc员工表字段内容如下:empno员工号（主码），ename员工姓名，job工作，mgr上级编号（外码），hiredate受雇日期，sal薪金，comm佣金，deptno部门编号（外码）；create table empc(empno number,ename varchar(10),job varchar(20),mgr number,hiredate date,sal number,comm number,de

3、ptno number,primary key(empno),foreign key(mgr) references empc(empno);alter table empc add constraint deptno foreign key(deptno) references deptc(deptno);deptc部门表字段内容如下：deptno 部门号（主码），dname部门名称，loc办公地方create table deptc(deptno number,ename varchar(10),loc varchar(10),primary key(deptno);bonusc奖金表字段

4、内容如下：ename员工姓名，job 工作名称，sal薪金，comm佣金create table bounsc(ename varchar(10),job varchar(10),sal number,comm number);上机题2：用alter table向表中bonusc增加comm的约束为0-3000元之间。alter table bounsc add constraint comm check(comm between 0 and 3000);上机题3：将一个员工记录插入到emp中（每个属性值自定）；insert into emp values(102,zhangsan,manag

5、er,102,to_date(2010-05-06,yyyy-mm-dd),null,null,10);上机题4：删除所有张三职工记录；delete from emp where ename=zhangsan;上机题5：创建如下的视图：创建“research”部门的职工情况，包含如下字段的视图view_research：包括empno、ename、sal、comm。create view research as select empno,ename,sal,comm from emp;上机题6：使用select语句创建查询：1、列出至少有一个员工的全部部门及人数。select count(em

6、pno),deptno from emp group by deptno having count(ename)1; 2、列出薪金比“smith”多的全部员工。select ename,sal from emp where sal(select sal from emp where ename=smith); 3、列出全部员工的姓名及其直接上级的姓名 select table1.ename,table2.ename from emp table1,emp table2 where table1.mgr=table2.empno;4、列出受雇日期早于其直接上级的全部员工。 select a.en

7、ame from emp a,emp b where a.mgr=b.empno and a.hiredate1500;8、列出在部分门“sales”（贩卖部）做事的员工的姓名，假定不知道贩卖部的部门编号。select ename from (select deptno from dept where dname=sales)a,emp where a.deptno=emp.deptno; 9、列出薪金高于公司平均薪金的全部员工。 select emp.* from emp where sal(select avg(sal)from emp);10、列出与“scott”从事相同工作的全部员工。

8、select emp.* from emp where job in(select job from emp where ename=scott); 11、列出薪金是部门30中员工的薪金的全部员工的姓名和薪金。select ename,sal from emp where sal in (select sal from emp where deptno=30); 12、列出薪金高于在部门30做事的全部员工的薪金的员工姓名和薪金。select ename,sal from emp where sal (select max(sal) from emp where deptno=30); 13、列

9、出在每个部门做事的员工数量 、均匀收入、平均做事限期。 select count(ename),avg(sal),floor(sysdate-hiredate/365) from emp group by(deptno);14、列出全部员工的姓名、部门名称和收入。select ename,dname,sal from emp,dept where emp.deptno=dept.deptno; 15、列出从事同一种工作但属于不同部门的员工。 select a.ename from emp a,emp b where a.job=b.job and a.deptnob.deptno;16、列出全

10、部部门的具体信息和部门人数。 select * from dept a left join(select deptno.count(*)from emp group by dept(no) b on a.deptno=b.deptno;17、列出种种工作的最低收入。 select job,min(sal) from emp group by job;18、列出各个部门的manager（司理）的最低薪金。 select min(sal) from emp where job=managergroup by deptno;19、列出全部员工的年收入 ,按年薪从低到高排序。 select ename

11、,sal*12 a from emp order by a asc;20、找出佣金高于薪金的60%的员工. select ename from emp where commsal*0.6;21、找出部门10中所有经理(manager),部门20中所有办事员(clerk),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. select * name emp where (deptno=10 and job=manager) or (deptno=20 and job=clerk) or (job not in (manager, clerk) and sal=2000)22

12、、找出不收取佣金或收取的佣金低于100的员工. select * from emp where comm0 thenv_comm:=emp_m+100; elsif emp_m is null thenv_comm:=0; elsev_comm:=200;end if;update emp set comm=v_comm where current of emp_comm;end loop;end;/实验六 存储过程实验目的在oracle下熟练掌握pl-sql语言中存储过程的定义和使用。实验要求1、基本配置：intel pentiumiii以上级别的cpu，大于512mb的内存。2、软件要求：

13、window xp操作系统，oracle 9i3、实验学时：4学时。4、实验报告。实验准备存储过程定义create or replace procedure procedure_name isbeginend上机题1.请编写一个过程，可以输入雇员编号，显示该雇员的姓名薪水。核心参考代码:open emp_cursor for select ename,sal from emp where deptno=v_empno;-循环取出loopfetch emp_cursor into v_ename,v_sal; -判断是否test_cursor 为空 exit when emp_cursor %n

14、otfound; dbms_output.put_line(名字：|v_ename| 工资：|v_sal);end loop;上机题2. 编写一个存储过程，完成以下功能: 根据输入的部门编号,返回该部门的所有职工的平均工资,并写出调用指令。上机题3. 编写一个存储过程，更改所有雇员的补助：如果雇员的补助大于0就在原来的基础上增加100；如果补助等于0就把补助设为200；如果是空值按照等于0处理。实验七 触发器的设计实验目的熟练掌握oracle触发器的设计。实验要求1、基本配置：intel pentiumiii以上级别的cpu，大于512mb的内存。2、软件要求：window xp操作系统，or

15、acle 9i3、实验学时：4学时。4、实验报告。实验准备1.dml除触发器的语法：createorreplacetriggertrigger_namebefore|afterinsert|delete|updateofcolumn,columnorinsert|delete|updateofcolumn,column.onschema.table_name|schema.view_nameforeachrowwhenconditionpl/sql_block|callprocedure_name;例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表

16、中去。createtableemp_hisasselect*fromempwhere1=2;createorreplacetriggertr_del_empbeforedelete-指定触发时机为删除操作前触发onscott.empforeachrow-说明创建的是行级触发器begin-将修改前数据插入到日志记录表del_emp,以供监督使用。insertintoemp_his(deptno,empno,ename,job,mgr,sal,comm,hiredate)values(:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.

17、sal,:m,:old.hiredate);end;例2：限制对dept表修改（包括insert,delete,update）的时间范围，即不允许在非工作时间修改dept表。createorreplacetriggertr_dept_timebeforeinsertordeleteorupdateondeptbeginif(to_char(sysdate,day)in(星期六,星期日)or(to_char(sysdate,hh24:mi)notbetween08:30and18:00)thenraise_application_error(-20001,不是上班时间，不能修改dept表);en

18、dif;end;2、oracle10g提供的系统事件触发器可以在ddl或数据库系统上被触发。ddl指的是数据定义语言，如create 、alter及drop 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：createorreplacetriggersachema.trigger_namebefore|afterddl_event_list|database_event_listondatabase|schema.schemawhenconditionpl/sql_block|callprocedure_name;下面给出系统触发器的种类和事件出现的时机（前或后）：事件允许的时机说明startupafter启动数据库实例之后触发shutdownbefore关闭数据库实例之前触发（非正常关闭不触发） servererrorafter数据库服务器发生错误之后触发logonafter成功登录连接到数据库后触发logoffbefore开始断开数据库连接之前触发