《数据库技术及应用》实验报告5

《数据库技术及应用》实验报告1实验报告课程名称数据库技术及应用实验项目名称实验5存储过程、函数、触发器操作实验时间（日期及节次）周一10-12节专业软件工程学生所在学院软件学院年级学号姓名指导教师实验室名称4号楼138实验成绩预习情况操作技术实验报告附加：综合创新能力实验综合成绩教师签字黑龙江大学教务处《数据库技术及应用》实验报告2黑龙江大学计算机科学与技术专业、软件工程专业《数据库技术及应用》课程实验报告学号姓名班级实验时间周一10-12节实验名称实验5存储过程、函数、触发器操作一、实验目的存储过程、函数、触发器操作二、实验环境WindowsXP，Oracle数据库三、主要内容已知一个已经建好的数据库factory,现在该数据库上存在三个表1.职工表worker,其结构为(职工号numnber,姓名char(8),性别char(2),出生日期date,党员否char,参加工作时间date,部门号number),其中职工号为主键2.部门表department结构为(部门号number,部门名称varchar(20)),其中部门号为主键.通常的部门信息有人事部,市场部,财务处等等3.职工工资表salary其结构为(职工号number,姓名char(10),日期date,工资number(10,2)).其中职工号和日期为关键字在以上的数据库上完成如下操作：1.创建一个为worker表添加职工记录的存储过程addworker2.创建一个为给定职工号,查询职工信息及部门名称的存储过程query_worker3.创建一个为给定职工号,删除worker表中记录的存储过程delete_worker4.显示存储过程5.创建一个函数，完成给定职工号返回职工所在部门的名称的函数6.创建一个函数，完成给定部门号返回该部门的最大年龄的函数7.在表department上创建一个触发器deaprt_update，当更改部门号时同步更改worker表中对应的部门号《数据库技术及应用》实验报告38.在表worker上创建一个触发器worker_delete，当删除职工记录同步删除对应职工的工资记录9.删除两个刚刚建立触发器四、实验总结CREATEORREPLACEPROCEDUREreturn_deptinfo(p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTemp.sal%TYPE)ASBEGINSELECTavg(sal),count(*)INTOp_avgsal,p_countFROMempWHEREdeptno=p_deptno;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdon’’texists!');ENDreturn_deptinfo;CREATEORREPLACEFUNCTIONreturn_maxsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_maxsalemp.sal%TYPE;BEGINSELECTmax(sal)INTOv_maxsalFROMempWHEREdeptno=p_deptno;RETURNv_maxsal;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedeptnoisinvalid!');《数据库技术及应用》实验报告4ENDreturn_maxsal;CREATEORREPLACEFUNCTIONreturn_sumsal(p_deptnoinemp.deptno%TYPE，p_deptnameoutdept.dname%type)RETURNemp.sal%TYPEASv_sumsalemp.sal%TYPE;BEGINSELECTsum(sal)INTOv_sumsalFROMempWHEREdeptno=p_deptno;selectdnameintop_deptnamefromdeptwheredeptno=p_deptno;RETURNv_sumsal;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedeptnoisinvalid!');ENDreturn_sumsal;DECLAREv_deptnoemp.deptno%TYPE;v_avgsalemp.sal%TYPE;FUNCTIONreturn_avgsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;RETURNv_sal;ENDreturn_avgsal;PROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASCURSORc_empISSELECT*FROMempWHEREsalreturn_avgsal(p_deptno);BEGIN《数据库技术及应用》实验报告5FORv_empINc_empLOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;ENDshow_emp;BEGINv_deptno:=&x;v_avgsal:=return_avgsal(v_deptno);show_emp(v_deptno);END;CREATEORREPLACETRIGGERtrg_emp_dmlAFTERINSERTORUPDATEORDELETEONempDECLAREv_countNUMBER;v_salNUMBER(6,2);BEGINIFINSERTINGTHENSELECTcount(*)INTOv_countFROMemp;DBMS_OUTPUT.PUT_LINE(v_count);ELSIFUPDATINGTHENSELECTavg(sal)INTOv_salFROMemp;DBMS_OUTPUT.PUT_LINE(v_sal);ELSEFORv_deptIN(SELECTdeptno,count(*)numFROMempGROUPBYdeptno)LOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.num);ENDLOOP;ENDIF;ENDtrg_emp_dml;CREATEORREPLACETRIGGERtrg_emp_dml_rowBEFOREINSERTORUPDATEORDELETEONemp《数据库技术及应用》实验报告6FOREACHROWBEGINIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(:new.empno||''||:new.ename);ELSIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(:old.sal||''||:new.sal);ELSEDBMS_OUTPUT.PUT_LINE(:old.empno||''||:old.ename);ENDIF;ENDtrg_emp_dml_row;