实验报告四-MYSQL存储过程与触发器

第2页共6页计算机科学系实验报告课程名称：MYSQL数据库技术实验指导教师：钟志宏姓名吴位吕学号2010081245班级B10计信实验名称MYSQL存储过程与触发器实验日期2012/10实验地点3506实验成绩实验目的１．理解存储过程在数据库中的作用，能够针对MYSQL编写所需的存储过程。２．理解触发器在数据库中的作用，能够针对MYSQL编写所需的触发器。实验内容：１．掌握MYSQL中存储过程编写的方法并完成指导书上要求的相关实验。２．掌握MYSQL中触发器编写的方法并完成级联更新、删除、及CHECK功能。\实验环境安装MYSQL数据库的PC实验要求：（在导入的教学管理STM数据库中完成）：基本储存过程的创建①创建一存储过程get_student_num，利用输出参数形式获取学生人数信息。并利用CALL调用该存储过程查看结果。DELIMITER$$CREATEPROCEDURE`stm`.`get_student_num`(OUTcount_numCHAR(10))BEGINSELECTCOUNT(sno)INTOcount_numFROMstudent;END$$DELIMITER;CALLget_student_num(@count_num)SELECT@count_num②创建一存储过程get_student_by_sno，通过输入学生编号作为参数，获得该学生的记录信息。并利用CALL调用该存储过程查看结果。DELIMITER$$CREATEPROCEDURE`stm`.`get_student_by_sno`(INsno_inCHAR(10))BEGINSELECT*FROMstudentWHEREsno=sno_in;END$$DELIMITER;CALLget_student_by_sno('900262')③创建一存储过程update_sage_by_sno，通过输入学生编号、年龄作为参数，将指定学生的年龄更改为指定的年龄。并利用CALL调用该存储过程查看结果。DELIMITER$$CREATEPROCEDURE`stm`.`update_sage_by_sno`(INsno_inCHAR(13),sage_inINT)BEGINUPDATEstudentSETsage=sage_inWHEREsno=sno_in;END$$DELIMITER;CALLupdate_sage_by_sno('900125',20)④创建一存储过程delete_student_by_sno，通过输入学生编号作为参数，删除该学生记录。并利用CALL调用该存储过程查看结果。DELIMITER$$CREATEPROCEDURE`stm`.`delete_student_by_sno`(INsno_inCHAR(10))BEGINDELETEFROMstudentWHEREsno=sno_in;END$$DELIMITER;CALLdelete_student_by_sno('900106')⑤创建一存储过程insert_student，通过输入相关信息作为参数，向学生表中添加一学生记录。并利用CALL调用该存储过程查看结果。DELIMITER$$CREATEPROCEDURE`stm`.`insert_student`(INsnoxCHAR(10),snamexVARCHAR(10),ssexxVARCHAR(1),sagexSMALLINT(5),enterdatexDATETIME)BEGININSERTINTOstudent(sno,sname,ssex,sage,enterdate)VALUES(snox,snamex,ssexx,sagex,enterdatex);END$$DELIMITER;CALLinsert_student('900104','里斯','男',21,'2010-09-12')2、基本函数过程的创建①创建一存储函数get_student_num，利用输出参数形式获取学生人数信息。并利用select调用该存储函数查看结果。DELIMITER$$CREATEFUNCTION`stm`.`get_student_num`()RETURNSINTBEGINDECLAREnumINT;SELECTCOUNT(*)INTOnumFROMstudent;RETURNnum;END$$DELIMITER;SELECTget_student_num()②创建一存储函数get_student_by_sno，通过输入学生编号作为参数，获得该学生的记录信息。并利用select调用该存储函数查看结果。DELIMITER$$CREATEFUNCTION`stm`.`get_student_by_sno`(snoxCHAR(10))RETURNSCHAR(100)BEGINDECLAREsnamexVARCHAR(10);DECLAREssexxVARCHAR(1);DECLAREsagexSMALLINT;DECLAREenterdatexDATETIME;DECLARErecordVARCHAR(100);SELECTsname,ssex,sage,enterdateINTOsnamex,ssexx,sagex,enterdatexFROMstudentWHEREsno=snox;SETrecord=CONCAT_WS(',',snox,snamex,ssexx,sagex,enterdatex); RETURNrecord;END$$DELIMITER;SELECTget_student_by_sno('900106')3、利用SHOWCREATE{PROCEDURE|FUNCTION}sp_name查看存储过程或函数的定义。SHOWCREATEPROCEDUREdelete_student_by_sno\GSHOWCREATEFUNCTIONget_student_by_sno\G4、定义条件和处理①创建一存储过程insert_student_condition，利用条件定义，当主键重复时结束存储过程，并提示“学生主键重复”。DELIMITER$$CREATEPROCEDUREinsert_student_condition(snoxCHAR(10),snamexVARCHAR(20),ssexxVARCHAR(2),sagexSMALLINT,enterdatexDATETIME)BEGINDECLAREEXITHANDLERFORSQLSTATE'23000'LECT'学生主键重复';INSERTINTOstudent(sno,sname,ssex,sage,enterdate)VALUES(snox,snamex,ssexx,sagex,enterdatex);END$$DELIMITER;CALLinsert_student_condition('900106','毕琴','女',20,'2010-09-15');②创建一存储过程insert_student_condition_user，利用条件定义，当插入学生的性别不为“男”或“女”时结束存储过程，并提示“学生性别不正确”。DELIMITER$$CREATEPROCEDUREinsert_student_condition_user(INsnoxCHAR(10),snamexVARCHAR(20),ssexxCHAR(2),sagexSMALLINT,enterDatexDATETIME)BEGINDECLARECONTINUEHANDLERFORSQLSTATE'42S02'SELECT'学生性别不正确';IFssexx='男'ORssexx='女'THENINSERTINTOstudent(sno,sname,ssex,sage,enterDate)VALUES(snox,snamex,ssexx,sagex,enterDatex);ELSEINSERTINTOxxVALUES('xxxxx');ENDIF;END$$DELIMITER;CALLinsert_student_condition_user('900134','向阳','中',20,'2010-09-12');CALLinsert_student_condition_user('900134','向阳','女',20,'2010-09-12');5、光标的使用①创建一存储过程update_student_borthyear，在学生表中添加字段”borthyear”（出生年份），在存储过错中利用光标，通过学生年龄计算出出生年份并修改表中对应字段。DELIMITER$$CREATEPROCEDUREupdate_student_borthyear(INsnoxCHAR(10))BEGINDECLARESagexVARCHAR(10);DECLAREborthyearxINT;DECLAREcur1CURSORFORSELECTsage,borthyearFROMstudent;DECLAREEXITHANDLERFORNOTFOUNDCLOSEcur1;OPENcur1;REPEATFETCHcur1INTOSagex,borthyearx;UPDATEstudentSETborthyear=2012-SageWHEREsno=snox;UNTIL0ENDREPEAT;CLOSEcur1;END$$DELIMITER;CALLupdate_student_borthyear('900104');6、触发器①创建一更新前触发器trigger_student_sc_update，实现在更新学生表的sno时，级联更新对于sc中的相关记录的sno值。DELIMITER$$CREATETRIGGER`stm`.`trigger_student_sc_update`AFTERUPDATEONstudentFOREACHROWBEGINDECLAREnewsnoCHAR(10);DECLAREoldsnoCHAR(10);SETnewsno=new.sno;SEToldsno=old.sno;IFnewsno<>oldsnoTHENUPDATEscSETsno=newsnoWHEREsno=oldsno;ENDIF;END$$DELIMITER;UPDATEstudentSETsno='900176'WHEREsname='楼飞';②创建一删除前触发器trigger_student_sc_delete，实现在删除一名学生时，级联删除sc中的相关记录。DELIMITER$$CREATETRIGGER`stm`.`trigger_student_sc_delete`BEFOREDELETEONstudentFOREACHROWBEGINDECLAREoldsnoCHAR(10);SEToldsno=old.sno;DELETEFROMscWHEREsno=oldsno;END$$DELIMITER;DELETEFROMstudentWHEREsno='900176';③创建一插入前触发器trigger_student_insert，实现检查学生的性别、年龄是满足要求才插入该学生信息。DELIMITER$$CREATETRIGGER`stm`.`trigger_student_insert`BEFOREINSERTONstudentFOREACHROWBEGINDECLAREssexCHAR(5);DECLAREsageSMALLINT;SETsage=new.sage;S