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

1课程名称：ORACLE数据库系统及应用成绩评定：实验项目名称：实验二：PL/SQL编程指导教师：学生姓名：学号：专业班级：实验项目类型：设计实验地点：实验时间：年月日一、实验目的与要求：1、掌握PL/SQL程序设计的基本知识；2、掌握PL/SQL中SELECT语句和DML语句的正确使用方法；3、掌握存储过程、函数、游标、触发器与包的创建与使用。二、实验环境：（硬件环境、软件环境）1.硬件环境：奔ⅣPC。2.软件环境：Windows2000操作系统，Oracle9i。三、实验内容：（原理、操作步骤、程序代码等）任务：1、编写存储过程，根据用户输入的部门编号实现在PL/SQL中逐行显示emp表中该部门员工的工资级别。工资级别是：当工资为空时，为空，工资在1000元以下的为‘低’，在1000和3000之间的为‘中’，高于3000元的为‘高’。要有异常处理（该部门编号不存在）。createorreplaceprocedurereview_ep(v_deptnoindept.deptno%type)iscursorc1isselect*fromempwhereemp.deptno=v_deptno;record1emp%rowtype;deptno_not_foundexception;beginopenc1;fetchc1intorecord1;if(notc1%found)thenraisedeptno_not_found;endif;whilec1%foundloopifnvl(record1.sal,0)1000thendbms_output.put_line(record1.ename||'工资低');elsifnvl(record1.sal,0)3000thendbms_output.put_line(record1.ename||'工资中等');elsedbms_output.put_line(record1.ename||'工资高');endif;fetchc1intorecord1;湖南第一师范学院信息科学与工程系实验报告2endloop;closec1;exceptionwhendeptno_not_foundthendbms_output.put_line('deptnonotfound');end;2.有这么一张表temp1，他只有一个number(8)的字段no，由于在创建表时忘记设置主键约束，导致表中有很多重复的记录。请你编写一个存储过程，将表中重复的记录保留一个，删除其余的。createorreplaceproceduremypro1iscursorv_cursorisselectdistinct*fromtemp1;f_notemp1.no%type;beginopenv_cursor;fetchv_cursorintof_no;deletefromtemp1;whilev_cursor%foundloopinsertintotemp1values(f_no);fetchv_cursorintof_no;endloop;closev_cursor;end;3.编写一个存储函数，用于判断DEPT表中某一编号的部门是否存在，若存在此部门编号，则返回TRUE，否则返回FALSE。Createorreplacefunctionjudge_dept(v_deptnodept.deptno%type)returnbooleanisv_deptno2dept.deptno%type:=-1;beginselectdeptnointov_deptno2fromdeptwheredeptno=v_deptno;if(v_deptno2-1)thenreturntrue;elsereturnfalse;endif;end;4、编写一过程，调用第3题的函数判断某一编号的部门是否存在，存在则输出该部门员工的姓名、工作，否则提示不存在此部门或此部门无员工。createorreplaceproceduredno(v_deptnodept.deptno%type)iscursorc1isselect*fromempwheredeptno=v_deptno;record2emp%rowtype;v_judgeboolean:=false;beginv_judge:=judge_dept(v_deptno);if(v_judge)then3openc1;fetchc1intorecord2;whilec1%foundloopdbms_output.put_line(record2.ename||''||record2.job);fetchc1intorecord2;endloop;closec1;elsedbms_output.put_line('deptnonotfound');endif;end;5、编写一个触发器，在DEPT表执行INSERT语句后被激发，此触发器将新部门的编号(deptno)、名称(dname)及执行此操作的用户（USER）、当时的日期（SYSDATE）插入N_DEPT表（注：此表已建好，表结构为N_DEPT（DEPTNONUMBER(4),DNAMEVARCHAR2(10),UNAMEVARCHAR2(20),INDATEDATE））。createtablen_dept(deptnonumber(4),dnamevarchar2(10),unamevarchar2(20),indatedate);createorreplacetriggerdel_empafterinsertondeptforeachrowbegininsertinton_deptvalues(:new.deptno,:new.dname,user,sysdate);end;insertintodeptvalues(60,'MMMM','hunan');6、创建触发器CHECK_SAL，禁示对职务为CLERK的雇员的工资修改值超出1000至2000的范围，即CLERK职务员工的修改后工资值只能在1000~2000之间。要求测试该触发器。createorreplacetriggerupdate_empafterupdateonempforeachrowbeginif(:new.salnotbetween1000and2000and:old.job='CLERK')thenrollback;endif;end;7、编写一个管理雇员信息的包emp_manapack。包中有过程或函数如下：1）通过员工编号计算出员工应交个人所得税款2）通过员工编号插入员工3）通过员工编号删除员工4）按工资升序输出所有雇员的应交所得税清单createorreplacepackageemp_manisrecord2emp%rowtype;functionpers(v_noemp.empno%type)returnnumber;4procedurecounts;endemp_man;createorreplacepackagebodyemp_manisfunctionpers(v_noemp.empno%type)returnnumberisv_salnumber:=0;beginselectsal*0.1intov_salfromempwhereempno=v_no;returnv_sal;end;procedurecountsiscursorcursor2isselect*fromemp;record3emp%rowtype;v_sal2number:=0;beginopencursor2;fetchcursor2intorecord3;whilecursor2%foundloopv_sal2:=pers(record3.empno);dbms_output.put_line(record3.ename||''||v_sal2);fetchcursor2intorecord3;endloop;closecursor2;end;endemp_man;