MySQL数据库 课件 4.3 存储过程和函数的开发

主讲教师：姜云桥任务4.3存储过程和函数的开发子项目4数据库高级应用开发任务背景银行经常需要计算用户的利息，但不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个程序存放起来，用指定的用户类别作参数。这样的程序叫作存储过程或者存储函数。任务目标1.能创建存储过程2.能执行存储过程3.能修改和删除存储过程知识目标1.了解DElimitER命令2.理解存储过程3.掌握创建存储过程的方法4.掌握修改和删除存储过程的方法能力目标任务要求通过本次任务，使学生能够创建、执行、修改和删除存储过程；掌握创建基本的存储过程的方法，掌握创建带有变量的存储过程的方法，理解创建带有流程控制语句的存储过程的方法。任务必备知识想一想试解释存储过程？银行经常需要计算用户的利息，但不同类别的用户的利率是不一样的。这就可以将计算利率的sql代码写成一个程序存放起来，用指定的用户类别作参数。这样的程序叫作存储过程或者存储函数。使用时只要调用这个存储过程或者存储函数，根据指定的用户类别，就可以将不同类别用户的利息计算出来。任务必备知识任务必备知识4.3.1存储过程和函数的基本操作一、创建存储过程创建存储过程可以使用createprocedure语句。createprocedure[ifnotexists]sp_name([in|out|inout]param_nametype,...)routine_body任务必备知识【任务4.3.1】创建存储过程sp_delete_student，用指定的学号作为参数删除某一学生的记录4.3.1存储过程和函数的基本操作mysql>delimiter$$mysql>createproceduresp_delete_student(innochar(8))->begin->deletefromstudentwheres_no=no;->end$$mysql>delimiter;任务必备知识【任务4.3.2】创建带输出参数的存储过程sp_count_student，求学生人数4.3.1存储过程和函数的基本操作mysql>delimiter$$mysql>createproceduresp_count_student(outstu_cntintunsigned)->begin->selectcount(*)intostu_cntfromstudent;->end$$mysql>delimiter;任务必备知识【任务4.3.3】创建存储过程sp_search_teacher，以指定的系别号为参数，查找某学院的老师姓名、所在院系名称4.3.1存储过程和函数的基本操作mysql>delimiter$$mysql>createproceduresp_search_teacher(innochar(8))->begin->selectt.t_name,d.d_name->fromteachertjoindepartmentdont.d_no=d.d_no->wheret.d_no=no;->end$$mysql>delimiter;任务必备知识4.3.1存储过程和函数的基本操作二、调用存储过程创建完存储过程之后，调用存储过程需要使用call语句。callsp_name(parameter,...)【任务4.3.4】调用存储过程sp_delete_student,删除学号为122001的学生的信息mysql>callsp_delete_student('122001');任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.5】调用存储过程sp_count_student,统计学生人数mysql>callsp_count_student(@num);mysql>select@num;【任务4.3.6】调用存储过程sp_search_teacher,查询院系编号D001的教师姓名和院系名称mysql>callsp_search_teacher('D001');任务必备知识4.3.1存储过程和函数的基本操作三、查看存储过程showprocedurestatus[like'pattern'|whereexpr]说明：（1）like匹配存储过程的名称。（2）where可以指定更多的过滤条件。1.查看存储过程的状态信息任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.7】查看存储过程sp_search_teacher的状态信息mysql>showprocedurestatuslike'sp_search_teacher'\G***************************1.row***************************Db:jxglName:sp_search_teacherType:PROCEDUREDefiner:root@localhostModified:2023-01-2523:07:38Created:2023-01-2523:07:38Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8mb4_0900_ai_ci任务必备知识4.3.1存储过程和函数的基本操作类似与查看数据库和数据表的定义，存储过程的定义可通过SHOWCREATEPROCEDURE命令查看。2.查看存储过程的定义【任务4.3.8】查看存储过程sp_search_teacher的定义mysql>showcreateproceduresp_search_teacher\G任务必备知识4.3.1存储过程和函数的基本操作四、删除存储过程可以使用dropprocedure删除已经存在的存储过程。dropprocedure[ifexists]sp_name;【任务4.3.9】删除存储过程sp_search_teachermysql>dropproceduresp_search_teacher;任务必备知识4.3.1存储过程和函数的基本操作五、存储函数MySQL存储函数和存储过程类似，也是存储在数据库中的程序，其查看、删除过程和存储过程一样，只不过将procedure换成function即可，主要在于创建和调用过程稍有不同。创建过程：createfunction[ifnotexists]sp_name(param_nametype,...)returnstype[deterministic|nosql|readssqldata]routine_body任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.10】定义存储函数func_count_score,获取score表中分数超过指定成绩的记录数mysql>delimiter$$mysql>createfunctionfunc_count_score(sdecimal)returnsint->deterministic->begin->declares_countint;->selectcount(*)intos_countfromscorewheremark>=s;->returns_count;->end$$mysql>delimiter;任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.11】使用自定义的存储函数func_count_score，获取超过90分的成绩记录数mysql>selectfunc_count_score(90)as‘优秀记录数';任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.12】查看存储函数func_count_score状态信息mysql>showfunctionstatuslike'func_count_score'\G***************************1.row***************************Db:jxglName:func_count_scoreType:FUNCTIONDefiner:root@localhostModified:2023-01-2617:12:49Created:2023-01-2617:12:49Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8mb4_0900_ai_ci1rowinset(0.01sec)或者执行下列语句mysql>showfunctionstatuswherename='func_count_score'\G任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.13】删除存储函数func_count_scoremysql>dropfunctionfunc_count_score;任务必备知识4.3.1存储过程和函数的基本操作存储过程和存储函数除了参数和返回值方式不同之外，最重要的区别在于存储过程可以修改数据库对象的状态，而存储函数却不能。这也意味这存储函数中主要以查询为主，可以放在查询语句中使用，存储过程却不行。任务必备知识一、变量1.变量声明存储过程和函数可以定义和使用变量,它们可以用来存储临时结果。用户可以使用declare关键字来定义变量，这些变量的作用范围只适用于begin…end程序段中，所以是局部变量。过程和函数使用的局部变量必须在开头就声明。在声明局部变量的同时也可以为其赋一个初始值：declarevar_nametype[defaultvalue];4.3.2存储过程和函数的编程功能任务必备知识2.变量赋值给变量赋值有两种方式，一种通过SET语句：setvar1=expr1,var2=expr2,...;另外一种方式通过selectINTO语句完成赋值：selectexpr1,expr2,...intovar1,var2,...from...;4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.14】创建一个存储函数，根据课程号查询不及格学生的数量mysql>delimiter$$mysql>createfunctionfunc_fail_count(nochar(4))returnsint->readssqldata->begin->declares_countintdefault0;->selectcount(*)intos_countfromscorewherec_no=noandmark<60;->returns_count;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.14】创建一个存储函数，根据课程号查询不及格学生的数量调用函数，查询课程号A002不及格人数mysql>selectfunc_fail_count('A002')as'A002不及格人数';+---------------------+4.3.2存储过程和函数的编程功能任务必备知识二、条件控制语句1.If语句MySQL提供了两种条件控制语句：if语句和case语句IF语句可根据不同的条件执行不同的操作。ifsearch_conditionthenstatement_listelseifsearch_conditionthenstatement_list...elsestatement_listendif4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.15】创建一个存储过程，根据指定的参数（学号）查看某位学生的不及格科目数，如果不及格科目数超过2门（含2门），则输出“启动成绩预警！”并输出该生的成绩单，否则输出“成绩在可控范围”4.3.2存储过程和函数的编程功能任务必备知识首先，创建存储过程：mysql>delimiter$$mysql>createprocedureproc_query(innochar(8),outstrvarchar(20))->begin->declarefail_counttinyintunsigneddefault0;->selectcount(*)intofail_countfromscorewheres_no=noandmark<60;->iffail_count>=2then->setstr='启动成绩预警';->else->setstr='成绩在可控范围';->endif;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能任务必备知识调用存储过程，查询学号122001的成绩状况：mysql>callproc_query('122001',@msg);mysql>select@msg;4.3.2存储过程和函数的编程功能任务必备知识1.case语句case语句可以用于构造复杂的条件判断，类似于case表达式，case语句也存在两种形式：简单case语句和搜索case语句4.3.2存储过程和函数的编程功能任务必备知识casecase_valuewhenwhen_valuethenstatement_listwhenwhen_valuethenstatement_list...elsestatement_listendcase;4.3.2存储过程和函数的编程功能（1）简单case语句任务必备知识【任务4.3.16】创建一个存储过程，根据专业类型（专业课4分、专业基础课3分、必须课2分、选修课1分）更新学分，参数为专业类型首先，创建存储过程（不能创建函数，函数中不能更新表格数据）4.3.2存储过程和函数的编程功能任务必备知识mysql>delimiter$$mysql>createprocedureproc_update_credit(intypevarchar(10))->begin->casetype->when'专业课'then->updatecoursesetc_credit=4wherec_type=type;->when'专业基础课'then->updatecoursesetc_credit=3wherec_type=type;->when'必修课'then->updatecoursesetc_credit=2wherec_type=type;->else->updatecoursesetc_credit=1;->endcase;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能任务必备知识casewhensearch_conditionthenstatement_listwhensearch_conditionthenstatement_list...elsestatement_listendcase;4.3.2存储过程和函数的编程功能（2）搜索case语句任务必备知识【任务4.3.17】创建一个存储函数，根据学号输出学生的成绩等级，平均分>=90优秀，>=80良好，>=60及格，否则不及格首先，创建存储函数:4.3.2存储过程和函数的编程功能任务必备知识mysql>delimiter$$mysql>createfunctionfunc_score_level(nochar(8))returnsvarchar(20)->readssqldata->begin->declaremsgvarchar(20);->declareavg_markdecimal(4,1)default0;->->selectavg(mark)intoavg_markfromscorewheres_no=no;->case->whenavg_mark>=90thensetmsg='优秀';->whenavg_mark>=80thensetmsg='良好';->whenavg_mark>=60thensetmsg='及格';->elsesetmsg='不及格';->endcase;->returnmsg;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能调用存储函数，根据传入的成绩输出等级：mysql>selectfunc_score_level('122003');任务必备知识三、循环控制语句1.loop语句[label:]loopstatement_listendloop[label]4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.18】创建存储过程，使用loop语句求10以内奇数之和创建存储过程:mysql>delimiter$$mysql>createproceduresp_loop_sum()->begin->declareiintdefault0;->declarei_sumintdefault0;->label:loop->seti=i+1;->ifi>10thenleavelabel;4.3.2存储过程和函数的编程功能->endif;->ifi%2=0theniteratelabel;->elseseti_sum=i_sum+i;->endif;->endlooplabel;->selecti_sum;->end$$mysql>delimiter;任务必备知识【任务4.3.18】创建存储过程，使用loop语句求10以内奇数之和调用存储过程：mysql>callsp_loop_sum();4.3.2存储过程和函数的编程功能任务必备知识三、循环控制语句2.while语句[label:]whilesearch_conditiondostatement_listendwhile[label]4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.19】创建存储过程，使用while语句求10以内奇数之和4.3.2存储过程和函数的编程功能创建存储过程：mysql>delimiter$$mysql>createproceduresp_while_sum()->begin->declareiintdefault0;->declarei_sumintdefault0;->->label:whilei<10do->seti=i+1;->ifi%2=1then->seti_sum=i_sum+i;->endif;->endwhilelabel;->->selecti_sum;->end$$mysql>delimiter;任务必备知识调用存储过程：mysql>callsp_while_sum();4.3.2存储过程和函数的编程功能【任务4.3.19】创建存储过程，使用while语句求10以内奇数之和任务必备知识四、repeat语句[label:]repeatstatement_listuntilsearch_conditionendrepeat[label]4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.20】创建存储过程，使用repeat语句