《数据库应用与管理》 课件 项目6 存储过程与触发器

存储过程与触发器2023年2月存储过程与存储函数目录Content1建立和使用触发器2项目导言零存储过程由一组预先编辑好的SQL语句组成，而触发器是一种特殊类型的存储过程。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，这样可以提高代码的执行效率。让我们一起跟随本项目，一起来了解一下吧。学习目标零知识目标了解存储过程的概念；熟悉创建基本的存储过程的方法；了解存储过程的增删改查操作；掌握触发器的创建方法；熟悉触发器的增删改查操作方法技能目标具备独立设定存储过程配置的能力；具备独立创建和修改触发器的能力。素养目标具备精益求精、坚持不懈的精神；具备团队协作能力；具备灵活的思维和处理分析问题的能力；具备责任心。任务6-1：存储过程与存储函数壹认识存储过程任务技能存储过程创建基本的存储过程DELIMITER命令、创建存储过程、调用执行存储过程查看存储过程通过show语句查看存储过程、通过showcreateprocedure语句查看存储过程删除存储过程删除存储过程建立和使用存储函数创建存储函数、执行存储函数、查看存储函数、删除存储函数任务6-1：存储过程与存储函数壹1认识存储过程存储过程在服务器端运行，可以减少客户端和服务器端的数据传输，执行速度快，提高了系统性能使用存储过程提高了程序设计的灵活性。一旦被创建，存储过程将被作为一个整体，可以被其他程序多次反复调用确保数据库使用安全。使用存储过程可以完成数据库的所有操作，数据库管理员可以充分控制数据的访问权限，从而避免非授权用户的非法访问存储过程在被创建后，可以在程序中多次被调用而不必重新编写，避免开发人员重复地编写相同的SQL语句。而且，开发人员可以随时对存储过程进行修改，对应用程序源代码无影响任务6-1：存储过程与存储函数壹2创建基本的存储过程（1）DELIMITER命令DELIMITER命令式MySQL中的分隔符，在MySQL命令行的客户端中，服务器处理语句默认是以分号（;）为结束标志的，如果有一行命令以分号（;）结束，那么按Enter键后，MySQL将会执行该命令。但是在存储过程中，可能要输入较多的语句，且语句中包含有分号。如果还以分号作为结束标志，那么执行完第一个带有分号的语句后，就会认为程序结束，不能再往下执行其他语句，必须将MySQL语句的结束标志修改为其他符号。这时，可以使用DELIMITER命令来改变默认结束标志。语法格式：DELIMITER$$任务6-1：存储过程与存储函数壹使用DELIMITER定义分号命令，并查看students表中的信息。示例：delimiter//select*fromstudent//任务6-1：存储过程与存储函数壹（2）创建存储过程在MySQL中，创建存储过程可以使用createprocedure语句。语法格式：createprocedure存储过程名(参数[,…])存储过程体任务6-1：存储过程与存储函数壹（3）调用执行存储过程MySQL中执行存储过程的语句是“CALL”。CALL语句可以调用指定存储过程，调用存储过程后，数据库系统将执行存储过程中的SQL语句，然后将结果返回给输出值。语法格式：CALL存储过程的名称([参数[...]]);任务6-1：存储过程与存储函数壹①创建一个名为p_name的存储过程。该存储过程用于输出students表中所有性别为“男”的学生记录。示例：delimiter//createprocedurep_name()beginselect*fromstudentswheresex='男';end;//delimiter;任务6-1：存储过程与存储函数壹②在学生成绩管理数据库myStudent中，执行不带参数的存储过程p_name。任务6-1：存储过程与存储函数壹③在学生成绩管理数据库myStudent中，创建一个名为p_name1的存储过程。要让用户能够按任意给定的性别进行查询。delimiter//createprocedurep_name1(innvarchar(20))beginselect*fromstudentswheresex=n; end;//delimiter;任务6-1：存储过程与存储函数壹④在学生成绩管理数据库myStudent中，执行带参数的存储过程p_name1，查询性别为“女”的学生记录。set@sex='女';callp_name1(@sex);任务6-1：存储过程与存储函数壹⑤在学生成绩管理数据库myStudent中，创建一个名为p_sex的存储过程。要让用户能够按给定的性别进行查询，如果用户输入的性别不为男或女，则提示输入错误。delimiter//createprocedurep_sex(innvarchar(20),outmessagevarchar(10))beginifn='男'||n='女'thenselect*fromstudentswheresex=n;elsesetmessage='性别输入错误';endif;end;//delimiter;任务6-1：存储过程与存储函数壹⑥在学生成绩管理数据库myStudent中，执行带输入输出参数的存储过程p_sex，按给定的性别进行查询，如果用户输入的性别不为男或女，则提示输入错误。任务6-1：存储过程与存储函数壹3查看存储过程（1）通过show语句查看存储过程语法格式：showprocedurestatuswheredb='数据库名'\G任务6-1：存储过程与存储函数壹查看数据库中定义的存储过程。示例：showprocedurestatuswheredb='数据库名'\G任务6-1：存储过程与存储函数壹（2）通过showcreateprocedure语句查看存储过程语法格式：showcreateprocedure存储过程名\G任务6-1：存储过程与存储函数壹在学生成绩管理数据库myStudent中，查看存储过程p_name的定义信息示例：任务6-1：存储过程与存储函数壹4删除存储过程语法格式：dropprocedure存储过程名;任务6-1：存储过程与存储函数壹在学生成绩管理数据库myStudent中，删除存储过程p_name。之后使用show语句查看服务器上的存储过程。示例：dropprocedurep_name;showprocedurestatuswheredb='mystudent'\G任务6-1：存储过程与存储函数壹5建立和使用存储函数（1）创建存储函数语法格式：createfunction存储函数名(参数[,…])returnstype函数体任务6-1：存储过程与存储函数壹在学生成绩管理数据库myStudent中，创建一个存储函数，它返回course表中已开设的专业基础课门数。示例：DELIMITER$$CREATEFUNCTIONNUM_OF_COURSE()RETURNSINTEGERBEGINRETURN(SELECTCOUNT(*)FROMcourseWHEREtype='专业基础课');END$$DELIMITER;任务6-1：存储过程与存储函数壹（2）执行存储函数语法格式：select存储函数名([参数]);任务6-1：存储过程与存储函数壹在学生成绩管理数据库myStudent中，调用存储函数NUM_OF_COURSE。示例：任务6-1：存储过程与存储函数壹（3）查看存储函数语法格式：showfunctionstatuswheredb='数据库名';任务6-1：存储过程与存储函数壹在MySQL中，查看当前服务器上的存储函数。示例：任务6-1：存储过程与存储函数壹（4）删除存储函数语法格式：dropfunction存储过程名;任务6-1：存储过程与存储函数壹在MySQL中，在服务器上删除存储函数NUM_OF_COURSE。示例：6-1：任务实施壹创建存储过程打开myStudent数据库，创建存储过程，用指定的学号作为参数删除某一学生的记录创建存储过程，用指定的课程号作为参数统计该课程的平均成绩创建带多个输入参数的存储过程，用指定的学号和课程号作为参数查询学生成绩创建一个存储过程，根据指定的参数（学号）查看某位学生的不及格科目数，如果不及格科目数超过2门（含2门），则输出“启动成绩预警”并输出该生的成绩单，否则输出“成绩在可控范围”调用存储过程DO_QUERY并进行查询任务6-2：建立和使用触发器贰创建触发器任务技能createtrigger创建触发器查看触发器SHOWTRIGGERS查看触发器删除触发器DROP删除触发器任务6-2：建立和使用触发器贰1创建触发器语法格式：createtrigger触发器名触发时间触发事件on表名foreachrowbegin

触发程序end任务6-2：建立和使用触发器贰①在学生成绩管理数据库myStudent中，创建一个触发器，当向score表中插入数据时，如果成绩大于或等于60分，则利用触发器将credit表中该学生的总学分加上该门课程的学分；否则总学分不变。示例：DELIMITER$$CREATETRIGGERCREDIT_ADDAFTERINSERTONscoreFOREACHROWBEGINDECLAREXFINT(1);SELECTcreditINTOXFFROMcourseWHEREc_no=NEW.c_no;IFNEW.REPORT>=60THENUPDATEcreditSETCREDIT=CREDIT+XFWHEREs_no=NEW.s_no;ENDIF;END$$DELIMITER;任务6-2：建立和使用触发器贰②触发器创建成功后，使用insert语句插入一条带有性别错误的记录进行测试，代码如下所示，使用SELECT语句查看credit表中的情况，运行结果如图6-20所示。可以看到，已将A002课程的学分累加给了123004学生。INSERT

INTO

SCOREVALUES

('123004','A002',60);select*fromcredit;任务6-2：建立和使用触发器贰2查看触发器语法格式：SHOWTRIGGERS;任务6-2：建立和使用触发器贰在学生成绩管理数据库myStudent中，查看创建的触发器信息。示例：任务6-2：建立和使用触发器贰3删