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

数据库原理及应用-ORACLE实验指导书信息科学与工程学院 2014年9月作者：何小卫目 录实验一ORACLE平台下基本SQL语言的应用实验二ORACLE基本操作与用户权限基本管理实验三 数据字典视图实验四 PL-SQL语言设计实验五 存储过程实验六 游标实验七 数据库触发器实验一：ORACLE平台下基本SQL语言的应用实验目的在oracle下熟练运用SQL语言来完成基本表的管理、索引的建立和删除、数据查询、数据更新、视图建立和删除等等操作。实验要求1、 件基本配置：Intel PentiumIII以上级别的CPU，大于512MB的内存。2、 软件要求：Window XP操作系统，ORACLE 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,deptno 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奖金表字段内容如下：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,manager,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(empno),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.ename 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”从事相同工作的全部员工。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、列出在每个部门做事的员工数量 、均匀收入、平均做事限期。 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、列出全部部门的具体信息和部门人数。 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,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、找出不收取佣金或收取的佣金低于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、软件要求：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 %notfound; 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操作系统，ORACLE 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 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。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.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表);ENDIF;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开始断开数据库连接之前触