实验二PLSQL编程实验报告.doc

课程名称：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 dept.deptno%type)iscursor c1 is select * from emp where emp.deptno=v_deptno;record1 emp%rowtype;deptno_not_found exception;beginopen c1;fetch c1 into record1;if(not c1%found) then raise deptno_not_found;end if;while c1%found loopif nvl(record1.sal,0)1000 then dbms_output.put_line(record1.ename|工资低);elsif nvl(record1.sal,0)3000 then dbms_output.put_line(record1.ename|工资中等);elsedbms_output.put_line(record1.ename|工资高);end if;fetch c1 into record1;end loop;close c1;exception when deptno_not_found thendbms_output.put_line(deptno not found);end;2.有这么一张表temp1，他只有一个 number(8)的字段no，由于在创建表时忘记设置主键约束，导致表中有很多重复的记录。请你编写一个存储过程，将表中重复的记录保留一个，删除其余的。create or replace procedure mypro1 iscursor v_cursor is select distinct * from temp1;f_no temp1.no%type;begin open v_cursor; fetch v_cursor into f_no; delete from temp1 ; while v_cursor%found loop insert into temp1 values(f_no); fetch v_cursor into f_no; end loop; close v_cursor;end;3.编写一个存储函数，用于判断DEPT 表中某一编号的部门是否存在，若存在此部门编号，则返回 TRUE，否则返回 FALSE。Create or replace function judge_dept(v_deptno dept.deptno%type) return boolean isv_deptno2 dept.deptno%type:=-1;beginselect deptno into v_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 dept.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 loopdbms_output.put_line(record2.ename| |record2.job);fetch c1 into record2;end loop;close c1;else dbms_output.put_line(deptno not found);end if;end;5、编写一个触发器，在 DEPT 表执行 INSERT 语句后被激发，此触发器将新部门的编号(deptno)、名称(dname)及执行此操作的用户（USER）、当时的日期（SYSDATE）插入 N_DEPT 表（注：此表已建好，表结构为 N_DEPT（DEPTNO NUMBER(4),DNAME VARCHAR2(10), UNAME VARCHAR2(20),INDATE DATE）。create table n_dept(deptno number(4),dname varchar2(10),uname varchar2(20),indate date);create or replace trigger del_emp after insert on dept for each row begin insert into n_dept values(:new.deptno,:new.dname,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(:new.sal not between 1000 and 2000 and :old.job=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 emp.empno%type)return number;procedure counts;end emp_man;create or replace package body emp_man isfunction pers(v_no emp.empno%type)return number isv_sal number:=0;begin select sal*0.1 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;begin open cursor2;fetch cursor2 into record3;while cursor2%found loopv_sal2:=pers(record3.empno);dbms_output.put_line(record3.ename| |v_sal2);fetch cursor2 into record3;end loop;close cursor2;end;end emp_man;