MySQL数据库应用教程（下篇共上下2篇）472

第8章编程基础和自定义函数目录第8章编程基础和自定义函数8.1编程基础8.2运算符和表达式8.3系统函数8.4自定义函数8.5控制流程语句8.1.1SQL语言概述1）数据定义语言（DataDefinitionlanguage，DDL）2）数据操纵语言（DataManipulationLanguage，DML）3）数据控制语言（DataControlLanguage，DCL）8.1编程基础8.1.2标识符标识符用来命名一些对象，如数据库、表、列、变量等，以便在程序中的其他地方引用。8.1编程基础8.1.3常量字符串常量【例8-1】SELECT语句输出3个字符串。SELECT'MySQL','I\'mastudent.'ASStudent,"I'mastudent."st;8.1编程基础2.数值常量（1）整型常量（2）实数常量【例8-2】SELECT输出数据常量。SELECT20,+123,-21368,1.356,-0.3,+12.356,0.12E-3,+326E+5,-358E-21;8.1编程基础3.日期时间常量'2022-09-23'、'2022/09/23'、'23.09.2022'、'09/23/2022''September23,2022'、'Sept23,2022'。'20220923'、"20220923"。'18:46:08'、'03:56PM'。'2022-09-2318:46:08'、'2022-09-2308:01:03.420'。'2022-09-32'是错误的日期常量。8.1编程基础4.布尔值布尔值只包含两个值：True和False，在代码中，也可以用0表示False，非0表示True。【例8-3】SELECT语句输出True和False。SELECTTrue,False,10>5,10<5,TrueORFalse,10>5ANDFalse,0AND13;

8.1编程基础5.NULL值NULL值可适用于各种列的类型，它通常表示“未知”、“值不确定”、“没有值”等含义。8.1编程基础8.1.4变量1.系统变量（1）查看系统变量语法格式为：SHOW[GLOBAL|SESSION]VARIABLES[LIKE'匹配模式'|WHERE表达式];8.1编程基础【例8-4】查看MySQL中所有的全局系统变量。SHOWGLOBALVARIABLES;8.1编程基础【例8-5】查看当前会话相关的所有会话变量以及全局变量。SHOWSESSIONVARIABLES;【例8-6】查看指定的变量character_set_client，该变量既是全局变量，又是会话变量。SHOWSESSIONVARIABLESLIKE'character_set_client';SHOWGLOBALVARIABLESLIKE'character_set_client';8.1编程基础【例8-7】查看变量名以character开头的会话变量，即MySQL采用的字符集。SHOWSESSIONVARIABLESLIKE'character%';8.1编程基础（2）查询系统变量SELECT系统变量名1[,系统变量名2,…];【例8-8】显示系统变量的值。SELECT@@VERSION,@@HOSTNAME,CURRENT_USER,@@BASEDIR,@@DATADIR;8.1编程基础【例8-9】显示系统变量当前日期、时间、时间戳。SELECTCURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP;8.1编程基础（3）设置系统变量SETGLOBAL系统变量名=值;SETSESSION会话变量名=值;SET@@会话变量名=值;【例8-10】显示和修改会话变量sort_buffer_size的值。SELECT@@sort_buffer_size;SET@@sort_buffer_size=50000;SELECT@@sort_buffer_size;8.1编程基础2.用户会话变量（1）定义用户会话变量1）方法一：SET@user_variable1[:]=expression1[,@user_variable2[:]=expression2,…];输出用户对话变量：SELECT@user_variable1[,@user_variable2,...];8.1编程基础【例8-11】在studentinfo数据库中，创建用户会话变量@user_name和@age，并为其赋值，然后使用SELECT语句输出变量的值。SET@user_name='Jack',@age=18;SELECT@user_name,@age;

SET@age=@age+1;SELECT@user_name,@age;

8.1编程基础2）方法二：SELECT@user_variable1:=expression1[,@user_variable2:=expression2,…];8.1编程基础【例8-12】创建用户会话变量@aa和@bb，分别用“:=”与“=”运算符，解释其区别。SELECT@aa:=100;

SELECT@aa;

SELECT@bb;

SELECT@bb=100,@aa=100; 8.1编程基础3）方法三：SELECTexpression1[,expression2,…]INTO@user_variable1[,@user_variable2,…];8.1编程基础【例8-13】创建用户会话变量@xx、@name并赋值，然后使用SELECT语句输出该变量的值。SELECT100,'Jack'INTO@xx,@name;SELECT@xx,@name;

8.1编程基础（2）用户会话变量在SQL语句中的使用【例8-14】在studentinfo数据库中，使用聚合函数计算学生人数，并赋值给@StCount变量。1）方法一：USEstudentinfo;SET@StCount=(SELECTCOUNT(*)FROMstudent);SELECT@StCount;

8.1编程基础2）方法二：SELECT@StCount:=(SELECTCOUNT(*)FROMstudent);

更改显示的列名。SELECT@StCount:=(SELECTCOUNT(*)FROMstudent)AS人数;8.1编程基础3）方法三：SELECT@StCount:=COUNT(*)FROMstudent;

4）方法四：SELECTCOUNT(*)INTO@StCountFROMstudent;SELECT@StCountAS人数;

8.1编程基础5）方法五：SELECTCOUNT(*)FROMstudentINTO@StCount;SELECT@StCount;8.1编程基础【例8-15】在studentinfo数据库中，先把要查找的学号赋值给用户会话变量，然后用该用户会话变量与列作比较。

USEstudentinfo;SET@StudentID='202210010123';SELECT*FROMstudentWHEREStudentID=@StudentID;

8.1编程基础【例8-16】把学号为“202210010123”的学生的所在班级保存到一个用户会话变量中，然后查询这个班级的所有学生名单。SET@StudentID='202210010123';SET@ClassID=(SELECTClassIDFROMstudentWHEREStudentID=@StudentID);SELECT*FROMstudentWHEREClassID=@ClassID;8.1编程基础【例8-17】利用SELECT语句把表中的一个数据赋值给用户会话变量。SELECT@StName:=StudentNameFROMstudentLIMIT2,3;

SELECT@StName;8.1编程基础（3）用户会话变量的特点3.局部变量8.1编程基础8.1.5注释1.单行注释#注释内容--注释内容2.多行注释/*注释内容注释内容*/8.1编程基础8.1.6BEGIN…END语句块[label]BEGIN[statement1;][statement2;][…]END[label];8.1编程基础8.1.7更改语句结束符DELIMITER语句DELIMITER[结束符][;]【例8-18】在NavicatforMySQL命令列界面或MySQL8.0CommandLineClient，将MySQL语句的结束符修改为“$$”，最后再恢复为默认的“;”。将MySQL语句的结束符修改为“$$”的SQL语句如下：USEstudentinfo;DELIMITER$$SELECT*FROMclassWHEREClassNum>20$$恢复为默认“;”的SQL语句如下：DELIMITER;SELECT*FROMclassWHEREClassNum>30;8.1编程基础8.2.1表达式的概念1.按照表达式值的类型分类2.按照值的形式分类（1）标量表达式（2）行表达式（3）表表达式8.2运算符和表达式3.按照表达式的形式分类4.按照运算符的类型分类8.2运算符和表达式8.2.2算术运算符和算术表达式8.2运算符和表达式8.2.3比较运算符和比较表达式8.2运算符和表达式1.等于运算（=）【例8-19】使用相等（=）判断。SELECT2=2,'2'=2,3.0=3,'Ab'='aB',(2+3)=(2*3),NULL=null,0=NULL;8.2运算符和表达式2.安全等于运算符（<=>）【例8-20】使用<=>进行相等的判断。SELECT2<=>2,'2'<=>2,3.0<=>3,'Ab'<=>'aB',(2+3)<=>(2*3),NULL<=>null,0<=>NULL;8.2运算符和表达式3.BETWEEN…AND…【例8-21】使用BETWEEN…AND…查询班级人数在20到30之间的班级。SELECT*FROMclassWHEREClassNumBETWEEN20AND30;8.2运算符和表达式8.2.4逻辑运算符和逻辑表达式8.2运算符和表达式1.NOT或者！【例8-22】使用NOT和!进行逻辑判断。SELECTNOT1+2,!1+2,NOT-2+1,!-2+1,NOTNULL,!NULL,NOT(1+2),!(1+2);8.2运算符和表达式2.AND或者&&【例8-23】使用与运算符AND和&&进行逻辑判断。SELECT1AND-1,1&&-1,1AND0,1&&0,0ANDNULL,1ANDNULL;8.2运算符和表达式3.OR或者||【例8-24】使用或运算符OR和||进行逻辑判断。SELECT1OR-1OR0,1OR2,1ORNULL,0ORNULL,NULLORNULL;

SELECT1||-1||0,1||2,1||NULL,0||NULL,NULL||NULL;8.2运算符和表达式4.ХOR【例8-25】使用异或运算符XOR进行逻辑判断。SELECT1XOR1,0XOR0,1XOR0,1XORNULL,1XOR1XOR1;8.2运算符和表达式8.3.1数学函数8.3系统函数【例8-26】数学函数的使用示例。SELECTPI(),TRUNCATE(3.28,1),ROUND(32.127,2),RAND(),SQRT(16),FLOOR(-5.97);

8.3系统函数【例8-27】使用随机函数RAND()对查询结果集随机排序。SELECT*FROMschool.studentORDERBYRAND();8.3系统函数【例8-28】二进制、十六进制函数的调用。SELECTBIN(128),OCT(128),HEX(128);8.3系统函数8.3.2字符串函数8.3系统函数1.获取字符串的长度和字节数【例8-29】字符串函数使用示例。SELECTCHAR_LENGTH('MySQL数据库系统'),LENGTH('MySQL数据库系统');8.3系统函数2.比较两个字符串的大小【例8-30】比较两个字符串。SELECTSTRCMP('ABC','ABB'),STRCMP('ABC','abc'),STRCMP('ABC','ABD');8.3系统函数3.连接字符串【例8-31】把3个字符串连接成为一个字符串。SELECTCONCAT('ABC','abc123','2022-05-01');8.3系统函数8.3.3日期和时间函数8.3系统函数1.时间转字符串【例8-32】时间转字符串示例。SELECTDATE_FORMAT(NOW(),'%Y-%m-%d%H:%i:%s');8.3系统函数2.字符串转日期时间【例8-33】字符串转日期时间示例。SELECTSTR_TO_DATE('2022-09-2918:57:32','%Y-%m-%d%H:%i:%s');8.3系统函数3.字符串转时间戳【例8-34】把当前日期时间转时间戳示例，把字符串转时间戳示例。SELECTUNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2022-09-29');8.3系统函数4.时间戳转字符串【例8-35】把时间戳转字符串示例。SELECTFROM_UNIXTIME(1664380800,'%Y-%m-%d');8.3系统函数5.获取当前服务器的时间【例8-36】获取服务器的当前日期、时间。SELECTNOW(),LOCALTIME(),CURRENT_TIMESTAMP(),CURDATE(),CURTIME(),YEAR(NOW());8.3系统函数6.SLEEP()延时函数例如，执行SELECT时间为3秒：SELECTSLEEP(3);【例8-37】SLEEP()函数能够放在SELECT子句中，让每行记录都休眠指定的时间。SELECTSLEEP(2),StudentID,StudentNameFROMstudent;假设表中有8行记录，整个语句执行的时间为2*8=16秒。如果过程中不允许有输出，可以采用如下SQL语句：DECLAREdelayINTDEFAULT0;SELECTSLEEP(3)INTOdelay;8.3系统函数8.3.4数据类型转换函数1.CAST()函数CAST(valueASdatatype)【例8-38】把数值120、23.58转换为字符串，'25'、'12.893'转换为数值，把'2022-09-13'转换为日期。SELECTCAST(120ASCHAR),CAST(23.58ASCHAR(10)),CAST('25'ASFLOAT),CAST('12.893'ASDECIMAL(10,4)),CAST('2022-09-13'ASDATE);8.3系统函数2.CONVERT()函数CONVERT(value,type)【例8-39】把数值120、23.58转换为字符串，'25'、'12.893'转换为数值，把'2022-09-13'转换为日期。SELECTCONVERT(21,CHAR),CONVERT(23.58,CHAR(10)),CONVERT('12.83',DECIMAL(10,3)),CONVERT('2022-09-13',DATE),CONVERT("13:32:56",TIME);8.3系统函数8.3.5系统信息函数8.3系统函数【例8-40】系统信息函数应用示例。SELECTVERSION(),@@VERSION,CURRENT_USER(),USER(),SYSTEM_USER(),DATABASE();8.3系统函数8.3.6加密函数8.3系统函数【例8-41】加密解密函数应用示例。SELECTAES_ENCRYPT('123456','OK12'),AES_DECRYPT(AES_ENCRYPT('123456','OK12'),'OK12');8.3系统函数8.3.7条件判断函数8.3系统函数1.IF()函数【例8-42】IF()函数示例。SET@score1=90;SET@score2=55;SELECTIF(@score1>=60,'及格','不及格'),IF(@score2>=60,'及格','不及格');8.3系统函数【例8-43】从selectcourse表中查询CourseID前3位是630的课程成绩，当Score>=60时显示Pass，否则显示Notpassed。SELECTStudentID,CourseID,Score,IF(Score>=60,'Pass','Notpassed')FROMselectcourseWHERESUBSTR(CourseID,1,3)='630';8.3系统函数2.IFNULL()函数【例8-44】IFNULL()函数示例。SET@score3=60;SELECTIFNULL(@score3,'没有成绩'),IFNULL(@score4,'没有成绩');8.3系统函数3.CASE函数CASEWHENsearch_condition1THENstatement_list1;[WHENsearch_condition2THENstatement_list2;]…[ELSEstatement_list3;]END[CASE];8.3系统函数【例8-45】查询成绩表selectcourse，输出学号、课程编号、成绩和成绩等级。SELECTStudentIDAS学号,CourseIDAS课程号,CASEWHENScore>=90THEN'优秀'WHENScore>=80THEN'良好'WHENScore>=70THEN'中等'WHENScore>=60THEN'及格'ELSE'不及格'ENDAS成绩等级,ScoreAS成绩

FROMselectcourse;8.3系统函数8.4.1自定义函数的概念8.4.2创建自定义函数自定义函数必须创建到某个数据库中，所以在先打开相应的数据库。创建自定义函数的基本语法格式为：CREATEFUNCTIONfunc_name([parameter1type1[,parameter2type2,…]])RETURNStype[characteristic…]BEGINfunction_body_statements;RETURNvalues;END;8.4自定义函数characteristic是指定函数的特性参数：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'8.4自定义函数【例8-46】在studentinfo数据库中，创建计算长方形面积的函数，给定长、宽，返回面积。CREATEFUNCTIONfu_rectangle_area(lengthFLOAT,widthFLOAT)RETURNSFLOATDETERMINISTICBEGINRETURNlength*width;END;8.4自定义函数【例8-47】在studentinfo数据库中，创建函数fu_getStudentName()，给定学号，返回该学生的姓名。CREATEFUNCTIONfu_getStudentName(stuIDCHAR(12))RETURNSVARCHAR(20)DETERMINISTICBEGINRETURN(SELECTStudentNameFROMstudentWHEREStudentID=stuID);END;8.4自定义函数8.4.3调用自定义函数func_name([parameter1[,parameter2,…]])【例8-48】在studentinfo数据库中，分别调用fu_rectangle_area()函数和fu_getStudentName()函数。1）计算长和宽分别为20和30的矩形面积，SQL语句和运行结果如下：SELECTfu_rectangle_area(20,30);8.4自定义函数2）调用fu_getStudentName()函数，查询学号为202260010306的学生姓名。SELECTfu_getStudentName('202260010306');SET@ID='202260010306';SELECTfu_getStudentName(@ID);8.4自定义函数8.4.4管理自定义函数1.查看函数1）查看所有的函数：SHOWFUNCTIONSTATUS;SHOWFUNCTIONSTATUSLIKE'模式';例如，在studentinfo数据库中，显示函数名开头是fu的函数名，SQL语句如下：SHOWFUNCTIONSTATUSLIKE'fu%';8.4自定义函数2）查看指定的函数名：SHOWCREATEFUNCTIONfunc_name;例如，在studentinfo数据库中，查看fu_rectangle_area()函数的详细信息：SHOWCREATEFUNCTIONfu_rectangle_area;8.4自定义函数3）使用information_schema：SELECT*FROMinformation_schema.ROUTINESWHEREroutine_name='函数名';例如，查看fu_rectangle_area()函数的信息，SQL语句如下：SELECT*FROMinformation_schema.ROUTINESWHEREroutine_name='fu_rectangle_area';8.4自定义函数2.修改自定义函数ALTERFUNCTIONfunc_name[characteristic…]【例8-49】修改存储函数fu_getStudentName()的定义，将读/写权限改为MODIFIESSQLDATA，并指明调用者可以执行。ALTERFUNCTIONfu_getStudentNameMODIFIESSQLDATASQLSECURITYINVOKER;8.4自定义函数3.删除自定义函数DROPFUNCTION[IFEXISTS]func_name;【例8-50】查看studentinfo数据库中的函数，查看fu_getStudentName()函数的具体信息，然后删除该函数)。SHOWFUNCTIONSTATUS;SHOWCREATEFUNCTIONfu_getStudentName;DROPFUNCTIONIFEXISTSfu_getStudentName;8.4自定义函数4.使用NavicatforMySQL管理自定义函数8.4自定义函数8.5.1局部变量1.局部变量的定义DECLAREvar_name1[,var_name2,…]type[DEFAULTvalue];8.5控制流程语句【例8-51】在studentinfo数据库中，在函数中定义局部变量。1）定义局部变量的语句要写在过程的BEGIN...END块内：DROPFUNCTIONIFEXISTSfu_temp1;CREATEFUNCTIONfu_temp1() RETURNSCHAR(12)DETERMINISTIC BEGINDECLAREaFLOAT;DECLAREi,jINTDEFAULT0;DECLAREidCHAR(12); RETURNid; END;8.5控制流程语句2）执行调用函数，SQL语句如下：SELECTfu_temp1();8.5控制流程语句2.局部变量的赋值（1）使用SET语句SETvar_name1=expr1[,var_name2=expr2,…]【例8-52】为上例定义的局部变量赋值。在存储过程中使用如下SQL语句：SETz=(x+y)*2,vStuNo='2022110131';8.5控制流程语句（2）使用SELECT…INTO语句SELECTcol_name1[,col_name2,...]INTOvar_name1[,var_name2,...][FROMtb_name[WHEREcondition]];8.5控制流程语句【例8-53】在成绩表selectcourse中，查询指定学号和课程号的成绩。1）创建自定义函数的SQL语句如下：DROPFUNCTIONIFEXISTSfu_temp2;CREATEFUNCTIONfu_temp2(vStuIDCHAR(12),vCouIDCHAR(6))RETURNSINTDETERMINISTICBEGINDECLAREvStudentIDCHAR(12);DECLAREvCourseIDCHAR(6);DECLAREvScoreFLOAT;SELECTStudentID,CourseID,ScoreINTOvStudentID,vCourseID,vScoreFROMselectcourseWHEREStudentID=vStuIDANDCourseID=vCouID;

RETURN(SELECTvScore);END;8.5控制流程语句2）调用函数SET@vStuID='202260010306',@vCouID='600131';SELECTfu_temp2(@vStuID,@vCouID);

8.5控制流程语句3.局部变量的应用场合1）场合一。2）场合二。3）场合三。4.局部变量与用户会话变量的区别8.5控制流程语句8.5.2条件控制语句1.IF语句IFsearch_condition1THENstatement_list1;[ELSEIFsearch_condition2THENstatement_list2;]…[ELSEstatement_list3;]ENDIF;8.5控制流程语句【例8-54】在studentinfo数据库中，创建函数fu_getmin，给定两个整数，输出较小的数。1）创建fu_getmin()函数的SQL语句如下：DROPFUNCTIONIFEXISTSfu_getmin;CREATEFUNCTIONfu_getmin(aINT,bINT)RETURNSINTDETERMINISTICBEGINDECLAREminINT;IFa<bTHENSETmin=a;ELSESETmin=b;ENDIF;RETURNmin;END;8.5控制流程语句2）执行调用函数fu_getmin()，SQL语句和运行结果如下。SELECTfu_getmin(3,8),fu_getmin(30,10),fu_getmin(7,7);8.5控制流程语句【例8-55】在studentinfo数据库中，创建函数fu_search()，要求该函数能根据给定的学号返回学生的性别，如果没有找到给定学号的学生，则返回“没有该学生”。1）创建自定义函数的SQL语句如下：DROPFUNCTIONIFEXISTSfu_search;CREATEFUNCTIONfu_search(sidCHAR(12))RETURNSCHAR(5) DETERMINISTICBEGINDECLAREvSexCHAR(1);SELECTSexINTOvSexFROMstudentWHEREStudentID=sid;IFvSexISNULLTHEN RETURN(SELECT'没有该学生');ELSEIFvSex='女'THENRETURN(SELECT'女');ELSERETURN(SELECT'男');ENDIF;ENDIF;END;8.5控制流程语句2）执行调用函数，SQL语句和运行结果如下。SET@id1='202210010123',@id2='202260010309',@id3='1234567890';SELECTfu_search(@id1),fu_search(@id2),fu_search(@id3);8.5控制流程语句【例8-56】创建函数fu_getscore，给定学号和课程号，查询该学生指定课程号的成绩，如果成绩合格，则返回1；否则返回0。1）创建自定义函数的SQL语句如下：DROPFUNCTIONIFEXISTSfu_getscore;CREATEFUNCTIONfu_getscore(vStIDCHAR(12),vCoIDCHAR(6))RETURNSINTDETERMINISTICBEGINDECLAREvScore,flagINT;SELECTScoreINTOvScoreFROMselectcourseWHEREStudentID=vStIDANDCourseID=vCoID;IFvScore>=60THENSETflag=1;ELSESETflag=0;ENDIF;RETURNflag;END;8.5控制流程语句2）执行调用函数，SQL语句和运行结果如下。SET@StudentID='202240010215',@CourseID='400121',@Score=Null;SELECTfu_getscore(@StudentID,@CourseID)INTO@state;SELECT@StudentIDAS学号,@CourseIDAS课程号,@state;8.5控制流程语句2.CASE语句CASE语句的语法格式为：CASEcase_valueWHENwhen_value1THENstatement_list1;[WHENwhen_value2THENstatement_list2;]…[ELSEstatement_list3;]END[CASE];8.5控制流程语句【例8-57】创建函数fu_getgrade()，输入分数成绩，返回成绩的等级。1）创建自定义函数的SQL语句如下：CREATEFUNCTIONfu_getgrade(scoreINT)RETURNSCHAR(3)NOSQLBEGINDECLAREscoreGradeCHAR(3);SETscore=FLOOR(score/10); CASEscoreWHEN10THENSETscoreGrade='优秀';WHEN9THENSETscoreGrade='优秀';WHEN8THENSETscoreGrade='良好';WHEN7THENSETscoreGrade='中等';WHEN6THENSETscoreGrade='及格';ELSESETscoreGrade='不及格';ENDCASE;RETURNscoreGrade;END;8.5控制流程语句2）执行调用函数，SQL语句和运行结果如下。SELECTfu_getgrade(50),fu_getgrade(60),fu_getgrade(70),fu_getgrade(80),fu_getgrade(90),fu_getgrade(100);8.5控制流程语句【例8-58】创建依据给定日期，获得中文星期的函数。1）创建自定义函数的SQL语句如下：CREATEFUNCTIONfu_getweek(weeknoINT)RETURNSCHAR(20)NOSQLBEGINDECLAREweekCHAR(4); CASEweeknoWHEN2THENSETweek='星期一';WHEN3THENSETweek='星期二';WHEN4THENSETweek='星期三';WHEN5THENSETweek='星期四';WHEN6THENSETweek='星期五';ELSESETweek='今天休息'; ENDCASE;RETURNweek;END;8.5控制流程语句2）SELECTfu_getweek(DAYOFWEEK(NOW())),fu_getweek(DAYOFWEEK(STR_TO_DATE('2022-09-23','%Y-%m-%d')));8.5控制流程语句8.5.3循环控制语句1.WHILE语句[label:]WHILEconditionDOstatement_list;ENDWHILE[label];8.5控制流程语句【例8-59】创建函数fu_sum()，使用while语句求1+2+3+...+n的和。1）创建自定义函数的SQL语句如下：CREATEFUNCTIONfu_sum(nINT)RETURNSINTNOSQLBEGINDECLAREi,sumINTDEFAULT0;SETi=1;WHILEi<=nDOSETsum=sum+i;SETi=i+1;ENDWHILE;RETURNsum;END;8.5控制流程语句2）执行调用函数，计算前100项的和，SQL语句和运行结果如下。SELECTfu_sum(100);8.5控制流程语句【例8-60】创建fu_sum2()函数，使该函数实现从1~n的偶数累加，其中add_label为循环标签。1）CREATEFUNCTIONfu_sum2(nINT)RETURNSINTNOSQLBEGINDECLAREsumINTDEFAULT0;DECLAREiINTDEFAULT0;label:WHILETRUEDOSETi=i+1;IF(i%2=0)THENSETsum=sum+i;ELSEITERATElabel; #向前返回到循环开始

ENDIF;IF(i=n)THENLEAVElabel; #向后退出循环

ENDIF;ENDWHILElabel;RETURNsum;END;8.5控制流程语句2）SELECTfn_sum2(100);8.5控制流程语句2.REPEAT语句[label:]REPEATstatement_list;UNTILconditionENDREPEAT[label];8.5控制流程语句【例8-61】使用REPEAT语句创建计算1+2+…+n的函数fu_sum3()。1）CREATEFUNCTIONfu_sum3(nINT)RETURNSINTNOSQLBEGINDECLAREsumINTDEFAULT0;DECLAREiINTDEFAULT1;REPEATSETsum=sum+i;SETi=i+1;UNTILi>nENDREPEAT;RETURNsum;END;8.5控制流程语句2）SELECTfu_sum3(100);8.5控制流程语句3.LOOP语句[label:]LOOPstatement_list;[LEAVElabel;]ENDLOOP[label];4.LEAVE语句LEAVElabel;8.5控制流程语句【例8-62】使用LOOP和LEAVE语句创建计算1+2+…+n的函数fu_sum4()。1）CREATEFUNCTIONfu_sum4(nINT)RETURNSINTNOSQLBEGINDECLAREi,sumINT;SETi=1,sum=0;loop_label:LOOPSETsum=sum+i;SETi=i+1;IFi>nTHENLEAVEloop_label;

ENDIF;ENDLOOPloop_label;RETURNsum;END;8.5控制流程语句2）SELECTfu_sum4(100);8.5控制流程语句5.ITERATE语句ITERATElabel;8.5控制流程语句【例8-63】使用WHILE和ITERATE语句创建函数fn_sum5()，计算l+2+3+...+n的和，但不包括同时能被3和7整除的数。1）创建自定义函数的SQL语句如下：CREATEFUNCTIONfu_sum5(nINT)RETURNSINTNOSQLBEGINDECLAREi,sumINT;SETi=1,sum=0;label:WHILEi<=nDOIFi%3=0&&i%7=0THENSETi=i+1;ITERATElabel; ENDIF;SETsum=sum+i;SETi=i+1;ENDWHILElabel;RETURNsum;END;8.5控制流程语句2）SELECTfu_sum5(100);8.5控制流程语句第9章存储过程、异常处理和游标目录第9章存储过程、异常处理和游标9.1存储过程9.2异常处理9.3使用游标处理结果集9.1.1存储过程的概念9.1.2创建存储过程CREATEPROCEDUREsp_name([proc_parameter1,proc_parameter2,…])[characteristic…]routine_body;9.1存储过程【例9-1】在studentinfo数据库中，创建一个显示student表中所有记录的存储过程。CREATEPROCEDUREproc_display_all_student()READSSQLDATABEGINSELECT*FROMstudent;END;9.1存储过程9.1存储过程9.1.3执行存储过程CALL[db_name.]sp_name([parameter1,parameter2,…]);【例9-2】执行proc_display_all_student过程。执行存储过程的SQL语句如下：CALLproc_display_all_student();9.1存储过程9.1.4创建存储过程的步骤1.实现存储过程的功能【例9-3】在studentinfo数据库中，创建不带参数的存储过程proc_selectcourse_avg，显示selectcourse表中的学号和每位学生的平均成绩。编写下面语句，SQL语句如下：SELECTstudentID,avg(Score)平均分

FROMselectcourseGROUPBYStudentID;9.1存储过程2.创建存储过程CREATEPROCEDUREproc_selectcourse_avg()READSSQLDATACOMMENT'显示学号和每位学生的平均成绩'BEGINSELECTstudentID,avg(Score)平均分FROMselectcourseGROUPBYStudentID;END;9.1存储过程3.执行存储过程CALLproc_selectcourse_avg();9.1存储过程9.1.5存储过程的管理1.查看存储过程的状态和定义（1）查看存储过程的状态SHOWPROCEDURESTATUS[LIKE'pattern'];例如，SQL语句如下：SHOWPROCEDURESTATUSLIKE'pro%';9.1存储过程（2）查看存储过程的定义SHOWCREATEPROCEDUREsp_name;例如，SQL语句如下：SHOWCREATEPROCEDUREproc_display_all_student;9.1存储过程（3）查看所有的存储过程SELECT*FROMinformation_schema.routines[WHEREroutine_name='名称'];例如SELECT*FROMinformation_schema.routines;SELECT*FROMinformation_schema.routinesWHEREroutine_name='proc_display_all_student';9.1存储过程2.修改存储过程ALTERPROCEDUREsp_name[characteristic…]【例9-4】修改存储过程up_display_all_student的定义，将特性改为MODIFIESSQLDATA，并指明权限调用者可以执行。ALTERPROCEDUREproc_display_all_studentMODIFIESSQLDATASQLSECURITYINVOKER;9.1存储过程3.删除存储过程DROPPROCEDURE[IFEXISTS]sp_name;【例9-5】删除存储过程proc_display_all_student。SQL语句如下：DROPPROCEDUREIFEXISTSproc_display_all_student;9.1存储过程3.使用Navicat管理存储过程9.1存储过程9.1.6存储过程的各种参数应用1.不带参数的存储过程（1）创建不带参数的存储过程CREATEPROCEDUREsp_name()[characteristic…]routine_body;9.1存储过程（2）执行不带参数的存储过程执行不带参数的存储过程的语法格式为：CALLsp_name();9.1存储过程【例9-6】在studentinfo数据库中，创建不带参数的存储过程proc_student_age，查询学生表student中的全体学生，显示姓名、性别和年龄。CREATEPROCEDUREproc_student_age()READSSQLDATACOMMENT'查询学生表student中的全体学生，显示姓名、性别和年龄'BEGINSELECTStudentNameAS姓名,SexAS性别,YEAR(NOW())-YEAR(Birthday)AS年龄

FROMstudent;END;CALLproc_student_age();9.1存储过程2.带IN参数的存储过程CREATEPROCEDUREsp_name(INparam_name1type1[,INparam_name2type2,…])[characteristic…]routine_body;在执行调用存储过程时，实参要给出具体的值。执行带IN参数的存储过程的语法格式为：CALLsp_name(parameter1[,parameter2,…]);9.1存储过程【例9-7】创建带有输入参数的存储过程proc_student_class，给定班级编号，查询出该班级的所有学生记录。1）CREATEPROCEDUREproc_student_class(INvClassIDCHAR(10))READSSQLDATABEGINSELECT*FROMstudentWHEREClassID=vClassID;END;2）CALLproc_student_class('2022600103');或SET@ClassID='2022600103';CALLproc_student_class(@ClassID);9.1存储过程3.带OUT参数的存储过程CREATEPROCEDUREsp_name(INparam_name1type1[,…],OUTparam_name2type2[,…])[characteristic…]routine_body执行带OUT参数的存储过程的语法格式为：SET@variable_name=表达式;CALLsp_name(parameter1[,…],@variable_name[,…]);9.1存储过程【例9-8】创建带有输入参数和输出参数的存储过程proc_selectcourse，给定学号，查询出该学生选修课程的数量和平均分，并通过输出参数返回。1）CREATEPROCEDUREproc_selectcourse(INvStudentIDCHAR(12),OUTvCountCourseINT,OUTvAvgScoreFLOAT)READSSQLDATABEGINSELECTCOUNT(CourseID)INTOvCountCourseFROMselectcourseWHEREStudentID=vStudentID;SELECTAVG(Score)INTOvAvgScoreFROMselectcourseWHEREStudentID=vStudentID;END;9.1存储过程2）SQL语句如下：CALLproc_selectcourse('202263050132',@CountCourse,@AvgScore);SQL语句如下：SET@StudentID='202263050132',@CountCourse=NULL,@AvgScore=NULL;CALLproc_selectcourse(@StudentID,@CountCourse,@AvgScore);SQL语句如下：SELECT@CountCourse,@AvgScore;9.1存储过程9.1存储过程【例9-9】创建带有输入参数和输出参数的存储过程proc_getscores，给定学号，统计该学生的考试课程数和合格的课程数，并通过输出参数返回。1）CREATEPROCEDUREproc_getscores(INvStudentIDCHAR(12),OUTvCountCourseINT,OUTvCountCoursesPassINT)READSSQLDATABEGINSELECTCOUNT(CourseID)INTOvCountCourseFROMselectcourseWHEREStudentID=vStudentID;SELECTCOUNT(CourseID)INTOvCountCoursesPassFROMselectcourseWHEREStudentID=vStudentIDANDScore>=60;END;9.1存储过程2）SET@StudentID='202263050132',@CountCourse=NULL,@CountCoursePass=NULL;CALLproc_getscores(@StudentID,@CountCourse,@CountCoursePass);SELECT@CountCourseAS考试课程数,@CountCoursePassAS合格的课程数;9.1存储过程【例9-10】创建存储过程proc_query_score，传入学号，显示该学号学生的成绩，如果全部成绩>=60，则返回“Allpasses”；否则返回通过的课程门数和不通过的课程门数。CREATEPROCEDUREproc_query_score(INst_idCHAR(12),OUTstrCHAR(30))BEGINDECLAREpass,notpassTINYINTDEFAULT0;SELECTCOUNT(*)INTOpassFROMselectcourseWHEREStudentID=st_idANDScore>=60;SELECTCOUNT(*)INTOnotpassFROMselectcourseWHEREStudentID=st_idANDScore<60;IFnotpass=0THENBEGINSETstr='Allpasses';END;ELSEBEGINSETstr=CONCAT('Pass:',CONVERT(pass,CHAR(2)),'Notpass:',CONVERT(notpass,CHAR(2)));END;ENDIF;SELECT*FROMselectcourseWHEREStudentID=st_id;END;9.1存储过程CALLproc_query_score('202263050132',@str);

SELECT@str;

CALLproc_query_score('202263050133',@str);

SELECT@str;9.1存储过程4.带INOUT参数的存储过程CREATEPROCEDUREsp_name(INOUTparam_nametype[,…])[characteristic…]routine_body;SET@variable_name=表达式;CALLsp_name(@variable_name[,…]);9.1存储过程【例9-11】创建带有INOUT参数的存储过程proc_ispass，给定学号、课程号，查询得到对应的成绩如果大于或等于60，则为1，否则为0，通过INOUT参数返回该值。CREATEPROCEDUREproc_ispass(INvStudentIDCHAR(12),INvCourseIDCHAR(10),INOUTpassINT)READSSQLDATABEGINDECLAREvScoreFLOAT;SELECTScoreINTOvScoreFROMselectcourseWHEREStudentID=vStudentIDANDCourseID=vCourseID;IFvScore>=60THENSETpass=1;ELSESETpass=0;ENDIF;END;9.1存储过程2）调用存储过程proc_ispass，INOUT参数保存在@pass中。SQL语句如下：SET@pass=0;CALLproc_ispass('202263050132','630575',@pass);SELECT@pass;9.1存储过程9.2.1自定义异常名称语句DECLAREcondition_nameCONDITIONFORcondition_value;SQLSTATEsqlstate_value|mysql_error_code;【例9-12】用名字定义“1062(23000)”这个错误，名称为error_insert。可以用两种不同的方法定义。方法一：使用sqlstate_value，SQL语句如下：DECLAREerror_insertCONDITIONFORSQLSTATE'23000';方法二：使用mysql_error_code，SQL语句如下：DECLAREerror_insertCONDITIONFOR1062;9.2异常处理9.2.2自定义异常处理程序DECLAREhandler_typeHANDLERFORcondition_valuesp_statement;condition_name|mysql_error_code|SQLSTATEsqlstate_value|SQLWARNING|NOTFOUND|SQLEXCEPTION9.2异常处理9.2.3异常处理实例【例9-13】在studentinfo数据库中创建一个表users，该表的u_id列为主键，当插入相同的主键值时触发异常。1）USEstudentinfo;DROPTABLEIFEXISTSusers;CREATETABLEusers(u_idINTPRIMARYKEY,u_nameCHAR(10));2）DROPPROCEDUREIFEXISTSproc_insert_userCREATEPROCEDUREproc_insert_user(INuidINT,INunameCHAR(10),OUTinfoCHAR(20))MODIFIESSQLDATABEGININSERTINTOusers(u_id,u_name)VALUES(uid,uname);SETinfo='Insertcomplete'; END;9.2异常处理3）调用存储过程。①CALLproc_insert_user(123,'Jack',@info);

SELECT*FROMusers;9.2异常处理②CALLproc_insert_user(123,'Lily',@info);

SELECT@info;9.2异常处理4）DROPPROCEDUREIFEXISTSproc_insert_user1;CREATEPROCEDUREproc_insert_user1(INuidINT,INunameCHAR(10),OUTinfoCHAR(20))MODIFIESSQLDATABEGIN#方式1DECLAREerror1CONDITIONFOR1062;

DECLAREEXITHANDLERFORerror1SETinfo='Cannotinsert';

#方式2#DECLAREEXITHANDLERFOR1062SETinfo='Cannotinsert';INSERTINTOusers(u_id,u_name)VALUES(uid,uname);SETinfo='Insertcomplete';END;9.2异常处理3）调用存储过程。①CALLproc_insert_user1(301,'Lily',@info);

SELECT*FROMusers;9.2异常处理②CALLproc_insert_user1(301,'Alex',@info);

SELECT@info;

SELECT*FROMusers;9.2异常处理9.3.1游标的概念9.3.2定义游标DECLAREcursor_nameCURSORFORselect_statement;9.3使用游标处理结果集【例9-14】在studentinfo数据库中，创建一个游标，从student表中查询出学号、姓名和班级号列的记录。DECLAREcur_studentCURSORFORSELECTStudentID,StudentName,BirthdayFROMstudent;SELECTStudentID,StudentName,BirthdayFROMstudent;9.3使用游标处理结果集9.3.3打开游标OPENcursor_name;例如，打开前面例题创建的cur_student游标，SQL语句如下：OPENcu