《MySQL数据库原理与应用案例教程》 课件 项目8 存储过程

《MySQL数据库项目化教程》项目8存储过程存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。游标就是按照指定要求把数据从数据库中提取出形成相应的数据集，然后逐条进行数据处理。本项目通过典型任务学习存储过程的创建、查看、调用、修改和删除操作，复杂的存储过程的编写，以及创建游标、使用游标循环遍历查询到的结果集。前言掌握如何创建无参数、有参数存储过程。掌握如何查看、调用、修改和删除存储过程。掌握复杂的存储过程的编写。掌握如何创建游标、使用游标循环遍历查询到的结果集。学习目标知识重点创建无参数、有参数存储过程。查看、调用、修改和删除存储过程。知识难点复杂的存储过程的编写。声明游标、打开游标、读取游标和关闭游标。知识重点与难点创建、调用和管理存储过程任务1任务2维护数据表学习任务外键约束任务3创建、调用和管理存储过程任务1任务描述任务目标任务分析任务实施123知识链接45

在实际工作过程中，数据库开发人员经常需要重复编写相同的SQL语句，可以通过创建存储过程，将这些相同的SQL语句封装成一个代码块，然后直接调用这些存储过程来执行已经定义好的SQL语句。从而减少数据库开发人员的工作量，提高工作效率。并且，存储过程是在MySQL服务器中存储和执行，可以减少客户端和服务器端的数据传输。根据需求，完成存储过程的创建、查看、调用、修改和删除操作。任务描述（1）会创建无参数存储过程。（2）会创建带in、out、inout参数的存储过程。（3）会查看、调用存储过程。（4）会修改、删除存储过程。（5）通过使用存储过程，培养学生在工作过程中应具备提高工作效率的意识。任务目标学习使用存储过程的场景，以及创建、查看、调用、修改和删除存储过程的语句，完成无参数存储过程、有参数存储过程的创建，以及查看、调用、修改和删除存储过程等各种操作。任务分析存储过程的优点创建存储过程delimiter语句存储过程任务实施查看存储过程调用存储过程修改存储过程删除存储过程步骤1创建无参数存储过程（1）创建一个存储过程，名称为pro_stu，功能为完成在tb_student数据表中查询计算机应用专业的学生的学号、姓名和专业。mysql>usechjgl_db;mysql>delimiter//mysql>createprocedurepro_stu()->begin->selectsnoas学号,snameas姓名,sspecialtyas专业fromtb_studentwheresspecialty='计算机应用';->end//mysql>delimiter;任务实施任务实施小贴士：在创建存储过程中，一般首先使用delimiter命令临时将语句结束标志修改为其他字符，比如，执行“delimiter//”这条命令后，sql语句的结束符就换成了双斜杠符号“//”了。并且在存储过程创建之后再将语句结束符修改为默认的“;”。（2）调用存储过程pro_stu。mysql>callpro_stu;任务实施步骤2创建带in参数的存储过程（1）创建一个存储过程，名称为pro_grade，输入参数为指定的分数，功能为完成在tb_grade数据表中查询综合成绩大于指定分数的学生的成绩单。mysql>delimiter//mysql>createprocedurepro_grade(inscorefloat)->begin->select*fromtb_gradewheretotalgrade>score;->end//mysql>delimiter;任务实施任务实施（2）调用存储过程pro_grade。mysql>callpro_grade(95);执行结果显示如图所示。任务实施步骤3创建带out参数的存储过程（1）创建一个存储过程，名称为pro_grade1，输入参数为指定的分数，功能为完成在tb_grade数据表中查询综合成绩低于指定分数的学生的人数，符合条件的学生人数通过存储过程的out参数返回。mysql>delimiter//mysql>createprocedurepro_grade1(inscorefloat,outtotalint)->begin->selectcount(*)intototalfromtb_gradewheretotalgrade<score;->end//mysql>delimiter;任务实施任务实施（2）调用存储过程pro_grade1。mysql>callpro_grade1(80,@total);mysql>select@total;任务实施步骤4创建带inout参数的存储过程（1）创建一个存储过程，名称为pro_stu1，输入参数为专业名称、需要增加的人数，并将在该专业的学生的人数加上需要增加的人数，返回增加后的人数。mysql>delimiter//mysql>createprocedurepro_stu1(inspecialtyvarchar(20),inouttotalint)->begin->selectcount(*)+totalintototalfromtb_studentwheresspecialty=specialty;->end//mysql>delimiter;任务实施任务实施（2）调用存储过程pro_stu1。mysql>set@total=10;mysql>callpro_stu1('计算机应用',@total);mysql>select@total;任务实施步骤5查看存储过程（1）查看存储过程pro_stu的状态。mysql>showprocedurestatuslike'pro_stu'\G;任务实施（2）查看存储过程pro_stu的定义。mysql>showcreateprocedurepro_stu\G;任务实施步骤6修改存储过程修改存储过程pro_stu的定义，将读写权限修改为MODIFIESSQLDATA。并查看修改后的结果。mysql>alterprocedurepro_stumodifiessqldata;mysql>showcreateprocedurepro_stu\G;任务实施任务实施步骤7删除存储过程删除存储过程pro_grade1。mysql>dropprocedureifexistspro_grade1;任务实施存储过程是一组为了完成特定功能的SQL语句集合。使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用存储过程即可自动完成。存储过程在数据库中创建并保存，一般由SQL语句和一些特殊的控制结构组成。适用于在不同的应用程序或平台上执行相同的特定功能时调用。知识链接——存储过程（1）一次创建，多次调用：存储过程可以重复使用，从而可以减少开发人员的工作量。（2）减少网络流量：存储过程存储在数据库服务器中，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。（3）很强的灵活性，存储过程可以使用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。知识链接——存储过程的优点CREATEPROCEDURE：创建存储过程关键字。sp_name：要创建的存储过程名称。[IN|OUT|INOUT]param_nametype：表示存储过程的参数。其中，IN表示输入参数；OUT表示输出参数；INOUT表示即可以是输入参数，也可以是输出参数；param_nam表示存储过程参数名称，type指定存储过程的参数的数据类型。存储过程也可以不加参数，但是存储过程名称后面的括号是不可以省略的。characteristic：指定存储过程的特性。有以下几种情况：（1）COMMENT'string'：表明存储过程的注释信息，注释信息要写到单引号里，只能写单行注释。知识链接——创建存储过程（2）LANGUAGESQL：声明存储过程中使用的语言，默认是SQL。（3）[NOT]DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。即每次执行存储过程时，相同的输入会得到相同的输出；NOTDETERMINISTIC表示结果是不确定的，即相同的输入可能输出不同。如果不指定任意一个值，默认为NOTDETERMINISTIC。（4）{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指明子程序使用SQL语句的限制。其中，CONTAINSSQL表明子程序包含SQL语句，但是不包含读写数据的语句；NOSQL表明子程序不包含SQL语句；READSSQLDATA：说明子程序包含读数据的语句；MODIFIESSQLDATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINSSQL。知识链接——创建存储过程（5）SQLSECURITY{DEFINER|INVOKER}：指明谁有权限来执行存储过程。其中，DEFINER表示只有存储过程的定义者才能够执行存储过程。INVOKER表示存储过程的调用者可以执行。routine_body：存储过程体，是SQL语句的内容，用“begin...end”来标注SQL语句的开始和结束。若存储过程体中只有一条SQL语句，则可以省略“begin...end”标志。知识链接——创建存储过程知识链接——delimiter语句MySQL中，服务器处理SQL语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条SQL语句，这些SQL语句如果仍以分号作为语句结束符，那么MySQL服务器在处理时会以遇到的第一条SQL语句结尾处的分号作为整个程序的结束标志，而不再去处理存储过程体中后面的SQL语句，这样显然不行。delimiter语句的作用就是将MySQL语句的结束标志修改为其他的符号。语法格式为：$$：用户定义的结束标志。语法分析：SHOWPROCEDURESTATUS：查看存储过程关键字。LIKE'pattern'：用来匹配存储过程名称。知识链接——查看存储过程CALL：调用存储过程关键字。sp_name：表示存储过程名称。parameter：表示存储过程的参数。知识链接——调用存储过程MySQL针对存储工程的修改功能只提供了修改存储过程特性，不提供修改存储过程体，如果需要修改存储过程，则需要先删除存储过程，再创建同名的存储过程。修改存储过程的基本语法结构为：知识链接——修改存储过程PROCEDURE：修改存储过程关键字。sp_name：存储过程名。characteristic：指定存储过程的特性。语法分析：DROPPROCEDURE：删除存储过程关键字。[IFEXISTS]：如果存储过程不存在，删除时可以防止发生错误。sp_name：表示要删除的存储过程名称。知识链接——删除存储过程存储过程的高级应用任务2任务描述任务目标任务分析任务实施123知识链接45

在实际工作过程中，使用存储过程的场合一般是比较复杂的业务逻辑，需要使用流程控制语句来控制程序的流程。MySQL中流程控制语句有：if语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句等。根据需求，完成复杂的存储过程的编写。任务描述（1）会定义变量。（2）会为变量赋值。（3）会使用条件语句：if语句、case语句。（4）会使用循环语句：loop语句、repeat语句和while语句等。（5）通过完成复杂的存储过程的编写，培养学生用系统思考的方法分析解决复杂问题的能力。任务目标学习MySQL变量的定义与赋值，掌握流程控制语句if语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句等的基本语法格式，完成复杂的存储过程的编写。任务分析为变量赋值任务实施定义变量循环语句条件语句步骤1存储过程的条件语句（1）创建存储过程pro_stuavg，查询某专业学生的综合成绩的平均分，如果平均分大于等于80分，输出“本次考试成绩优秀”，否则输出“本次考试成绩一般”。调用存储过程，输入参数“计算机应用”进行测试。mysql>usechjgl_db;mysql>delimiter//mysql>createprocedurepro_stuavg(inspecialtyvarchar(20))->begin->declaregavgfloat;->selectround(avg(g.totalgrade),2)into@gavgfromtb_studentsinnerjointb_gradegons.sno=g.snowheres.sspecialty=specialty;任务实施->If(@gavg>=80)then->select'本次考试成绩优秀'asgrade;->else->select'本次考试成绩一般'asgrade;->endif;->end//mysql>delimiter;mysql>callpro_stuavg('计算机应用');任务实施任务实施任务实施（2）创建存储过程pro_level，完成统计各专业学生的综合成绩平均成绩优秀（90分-100分）、良好（76分-89分）、及格（60分-75分）和不及格（0分-59分）的人数。调用存储过程，输入参数“良好”进行测试。mysql>delimiter//mysql>createprocedurepro_level(inlevelvarchar(20))->begin->caselevel->when'优秀'then->selects.sspecialtyas专业,count(*)as优秀人数fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)>=90;任务实施->when'良好'then->selects.sspecialtyas专业,count(*)as良好人数fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)<90andavg(g.totalgrade)>75;->when'及格'then->selects.sspecialtyas专业,count(*)as及格人数fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)<=75andavg(g.totalgrade)>60;->when'不及格'then->selects.sspecialtyas专业,count(*)as不及格人数fromtb_studentsinnerjointb_gradegons.sno=g.snogroupbys.sspecialtyhavingavg(g.totalgrade)<60;任务实施->endcase;->end//mysql>delimiter;mysql>callpro_level('良好');任务实施步骤2存储过程的循环语句（1）创建存储过程pro_while，使用while循环语句，计算n！，并调用存储过程进行验证。mysql>delimiter//mysql>createprocedurepro_while(innint,outresultint)->begin->setresult=1;->whilen>1do->setresult=n*result;->setn=n-1;->endwhile;任务实施->end//mysql>delimiter;mysql>callpro_while(5,@result);mysql>select'5!=',@result;任务实施任务实施（2）创建存储过程pro_repeat，使用repeat循环语句，计算n！，并调用存储过程进行验证。mysql>delimiter//mysql>createprocedurepro_repeat(innint,outresultint)->begin->setresult=1;->repeat->setresult=n*result;->setn=n-1;->untiln<=1->endrepeat;

任务实施->end//mysql>delimiter;mysql>callpro_repeat(6,@result);mysql>select'6!=',@result;任务实施（3）创建存储过程pro_loop，使用loop循环语句，计算n！，并调用存储过程进行验证。mysql>delimiter//mysql>createprocedurepro_loop(innint,outresultint)->begin->setresult=1;->label:loop->setresult=n*result;->setn=n-1;->ifn<=1then->leavelabel;任务实施->endif;->endlooplabel;->end//mysql>delimiter;mysql>callpro_loop(5,@result);mysql>select'5!=',@result;任务实施任务实施任务实施（4）创建存储过程pro_loop1，使用loop循环语句，输出n以内的偶数，并调用存储过程进行验证。mysql>delimiter//mysql>createprocedurepro_loop1(innint)->begin->declareiint;->declarestrvarchar(255);->seti=1;->setstr='';->label:loop任务实施->ifi>=nthen->leavelabel;->endif;->seti=i+1;->if(imod2)then->iteratelabel;->else->setstr=concat(str,i,',');->endif;->endloop;->selectstr;->end//mysql>delimiter;mysql>callpro_loop1(20);任务实施在创建存储过程中，有时会使用变量来保存数据处理过程中的值。MySQL中可以使用DECLARE关键字来定义变量，其基本语法格式如下：知识链接——定义变量DECLARE：定义变量关键字。var_name：变量名。type：变量的数据类型。[DEFAULTvalue]：定义变量的默认值。没有使用DEFAULT子句时，默认值为NULL。MySQL中可以使用SET关键字来为变量赋值，一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。其基本语法格式如下：知识链接——为变量赋值语法分析：SET：为变量赋值关键字。variable：变量名。expr：赋值表达式。（1）IF语句IF语句用来进行条件判断，根据是否满足条件（可包含多个条件），将执行不同的语句，是流程控制中最常用的条件判断语句。其基本语法格式如下：知识链接——条件语句search_condition：表示条件判断语句，如果返回值为TRUE，相应的statement_list中的语句被执行；如果返回值为FALSE，则ELSE关键字后面statement_list中的语句被执行。statement_list：SQL语句列表，可以包括一个或多个语句。（2）case语句CASE语句也是用来进行条件判断的，它提供了多个条件进行选择，可以实现比IF语句更复杂的条件判断。CASE语句有两种语法格式，第一种其基本语法格式如下：知识链接——条件语句case_value：表示条件判断的变量，决定了哪一个WHEN子句会被执行；when_value：表示变量的取值，如果某个when_value的值与case_value变量的值相同，则执行对应的THEN关键字后的statement_list中的语句；如果没有条件匹配，ELSE子句里的语句被执行。知识链接——条件语句statement_list：SQL语句列表，可以包括一个或多个语句。CASE语句都要使用ENDCASE结束。第二种其基本语法格式如下：search_condition：表示条件判断语句。statement_list：SQL语句列表，可以包括一个或多个语句。该语句中的WHEN语句将被逐个执行，直到某个search_condition表达式为真，则执行对应THEN关键字后面的statement_list中的语句。如果没有条件匹配，ELSE子句里的语句被执行。（1）while语句while语句是有条件控制的循环语句，当满足条件时，执行循环内的语句，否则退出循环。其基本语法格式如下：知识链接——循环语句search_condition：表示循环执行的条件，满足该条件时循环执行。statement_list：循环的SQL执行语句。WHILE循环需要使用ENDWHILE来结束。（2）repeat语句repeat语句是有条件控制的循环语句，每次语句执行完毕后，会对条件表达式进行判断，如果表达式返回值为TRUE，则循环结束，否则重复执行循环中的语句。其基本语法格式如下：知识链接——循环语句search_condition：表示循环执行的条件，满足该条件时循环执行。statement_list：循环的SQL执行语句。WHILE循环需要使用ENDWHILE来结束。（3）loop语句使某些特定的语句重复执行。LOOP语句只实现一个简单的循环，并不进行条件判断。LOOP语句本身没有停止循环的语句，必须使用LEAVE语句等才能停止循环，跳出循环过程。其基本语法格式如下：知识链接——循环语句begin_label和end_label：分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略。statement_list：表示需要循环执行的语句。（4）leave语句主要用于跳出循环控制。其基本语法格式如下：知识链接——循环语句label：表示循环的标志。（5）iterate语句iterate是“再次循环”的意思，用来跳出本次循环，直接进入下一次循环。其基本语法格式如下：小贴士：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。游标任务3任务描述任务目标任务分析任务实施123知识链接45

在实际工作过程中，经常会遇到这样一种情况，需要对存储过程或存储函数中查询的结果进行遍历操作，并对遍历到的每一条数据进行处理，这时候就会使用到游标。根据需求，创建游标，循环遍历查询到的结果集。任务描述（1）会声明游标、打开游标、读取游标和关闭游标。（2）会循环遍历查询到的结果集。（3）会声明处理程序。（4）通过使用游标，培养学生追求卓越的精神。任务目标学习游标的作用，以及声明游标、打开游标、读取游标和关闭游标的语句，了解如何声明处理程序，使用游标和循环语句完成循环遍历查询到的结果集。任务分析声明游标打开游标使用游标游标任务实施关闭游标声明处理程序步骤1创建一个简单的游标（1）在数据库chjgl_db中创建数据表tb_department（系部），数据表结构如表4-7。任务实施mysql>usechjgl_db;mysql>delimiter//mysql>createprocedurepro_cur1()->begin->declarerow_snovarchar(20);->declarerow_snamevarchar(20);->declarerow_specialtyvarchar(20);->declarecur_stucursorforselectsnoas学号,snameas姓名,sspecialtyas专业fromtb_studentwheresspecialty='计算机应用';

任务实施->opencur_stu;->fetchcur_stuintorow_sno,row_sname,row_specialty;->selectrow_sno,row_sname,row_specialty;->closecur_stu;->end//mysql>delimiter;mysql>callpro_cur1();任务实施步骤2循环遍历查询到的结果集创建一个存储过程，名称为pro_cur2，功能为完成在tb_student数据表中查询计算机应用专业的学生的学号、姓名和专业，并创建游标cur_stu，循环遍历查询到的结果集。（1）使用计数器来控制循环mysql>delimiter//mysql>createprocedurepro_cur2()->begin->declarenintdefault0;->declareiintdefault0;->declarerow_snovarchar(20);

任务实施->declarerow_snamevarchar(20);->declarerow_specialtyvarchar(20);->declarecur_stucursorforselectsnoas学号,snameas姓名,sspecialtyas专业fromtb_studentwheresspecialty='计算机应用';->selectcount(*)intonfromtb_studentwheresspecialty='计算机应用';->opencur_stu;->repeat->fetchcur_stuintorow_sno,row_sname,row_specialty;->selectrow_sno,row_sname,row_specialty;->seti=i+1;->untili>=nendrepeat;任务实施->closecur_stu;->end//mysql>delimiter;mysql>callpro_cur2();任务实施（2）使用越界标志来控制循环。mysql>delimiter//mysql>createprocedurepro_cur21()->begin->declarehaveintdefault1;->declarerow_snovarchar(20);->declarerow_snamevarchar(20);->declarerow_specialtyvarchar(20);->declarecur_stucursorforselectsnoas学号,snameas姓名,sspecialtyas专业fromtb_studentwheresspecialty='计算机应用';->declareexithandlerfornotfoundsethave=0;

任务实施->opencur_stu;->repeat->fetchcur_stuintorow_sno,row_sname,row_specialty;->selectro