实验二-PL-SQL编程实验报告

湖南第一师范学院信息科学与工程系实验报告课程名称： ORACLE数据库系统及应用实验项目名称：实验二： PL/SQL编程学生姓名：学号：实验项目类型：设计实验地点：成绩评定：指导教师：专业班级：实验时间：年月日一、实验目的与要求：1、掌握PL/SQL 程序设计的基本知识；2、掌握PL/SQL 中 SELECT语句和DML 语句的正确使用方法；3、掌握存储过程、函数、游标、触发器与包的创建与使用。二、实验环境：（硬件环境、软件环境）1.硬件环境：奔PC。2.软件环境： Windows2000操作系统， Oracle 9i。三、实验内容：（原理、操作步骤、程序代码等）任务：1、 编写存储过程， 根据用户输入的部门编号实现在PL/SQL 中逐行显示emp表中该部门员工的工资级别。工资级别是：当工资为空时，为空，工资在1000元以下的为低 ，在1000和3000 之间的为中 ，高于3000元的为高 。要有异常处理（该部门编号不存在）。create or replace procedure review_ep(v_deptno in %type )iscursor c1 is select * from empwhere =v_deptno ;record1 emp%rowtype ;deptno_not_foundexception ;beginopen c1;fetch c1 into record1 ;if(not c1%found ) then raise deptno_not_found;end if;while c1%found loopif nvl,0)1000 then| 工资低 );elsif nvl,0)3000 then| 工资中等 ); else| 工资高 ); end if;fetch c1 into record1 ; end loop ;close c1;exception when deptno_not_found then (deptno not found );end;2.有这么一张表temp1 ，他只有一个number(8) 的字段no，由于在创建表时忘记设置主键约束，导致表中有很多重复的记录。请你编写一个存储过程，将表中重复的记录保留一个，删除其余的。create or replace procedure mypro1 iscursor v_cursor is select distinct *from temp1;f_no %type;beginopen v_cursor;fetchv_cursor into f_no;delete from temp1 ;while v_cursor%found loopinsert into temp1 values(f_no);fetchv_cursor into f_no;end loop;close v_cursor;end;3.编写一个存储函数，用于判断DEPT 表中某一编号的部门是否存在，若存在此部门编号，则返回 TRUE，否则返回 FALSE。Createor replace function judge_dept (v_deptno %type ) return boolean isv_deptno2 %type:=-1 ;beginselect deptno intov_deptno2 from dept where deptno =v_deptno ;if(v_deptno2-1 ) thenreturn true;else return false;end if;end;4、编写一过程， 调用第 3 题的函数判断某一编号的部门是否存在，存在则输出该部门员工的姓名、工作，否则提示不存在此部门或此部门无员工。create or replace procedure dno (v_deptno %type )iscursor c1 is select * from emp where deptno =v_deptno ;record2 emp%rowtype ;v_judge boolean :=false;beginv_judge :=judge_dept (v_deptno );if(v_judge ) thenopen c1;fetch c1 into record2 ;while c1%found loop| |;fetch c1 into record2 ;end loop ;close c1;else (deptno not found);end if;end;5、编写一个触发器，在 DEPT 表执行INSERT 语句后被激发， 此触发器将新部门的编号(deptno) 、名称 (dname) 及执行此操作的用户（USER）、当时的日期（SYSDATE）插入N_DEPT 表（注：此表已 建 好 ， 表 结 构 为N_DEPT （ DEPTNONUMBER(4),DNAMEVARCHAR2(10),UNAMEVARCHAR2(20),INDATE DATE）。create table n_dept(deptno number(4),dname varchar2(10),uname varchar2(20),indate date);create or replace trigger del_empafter insert on deptfor each rowbegininsert into n_dept values(:,:,user,sysdate);end;insert into dept values(60,MMMM,hunan);6、创建触发器CHECK_SAL，禁示对职务为CLERK 的雇员的工资修改值超出1000至2000的范围，即CLERK 职务员工的修改后工资值只能在10002000之间。要求测试该触发器。create or replace trigger update_empafter update on empfor each rowbeginif(: not between 1000 and 2000 and :=CLERK)thenrollback ;end if;end;7、编写一个管理雇员信息的包emp_manapack 。包中有过程或函数如下：1）通过员工编号计算出员工应交个人所得税款2）通过员工编号插入员工3）通过员工编号删除员工4）按工资升序输出所有雇员的应交所得税清单create or replace package emp_man isrecord2 emp%rowtype ;function pers(v_no %type )return number ;procedure counts ;end emp_man ;create or replace package body emp_man isfunction pers(v_no %type )return number isv_sal number :=0;beginselect sal* into v_sal from emp where empno = v_no;return v_sal;end;procedure counts iscursor cursor2 is select * from emp ;record3 emp%rowtype ;v_sal2 number :=0;beginopen cursor2 ;fetch cursor2 into record3 ;while cursor2 %found loopv_sal2:=pers;| | v_sal2);fetch cursor2 into record3 ;end loop ;close cursor2 ;end;end emp_man ;