第12章 存储过程、异常处理和游标

第12章存储过程、异常处理和游标学习目标：了解存储过程的基本概念，掌握存储过程的创建、调用、查看、修改和删除的基本操作方法；理解异常处理的基本概念，熟悉自定义异常处理的基本操作方法；了解游标的基本概念。12.1存储过程12.1.1存储过程的概念存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在MySQL服务器的数据库中，通过指定存储过程名并给定参数来调用执行它。存储过程的操作主要包括创建存储过程、调用存储过程、查看存储过程，以及修改和删除存储过程。12.1存储过程12.1.2创建存储过程CREATEPROCEDUREproc_name([parameter1,parameter2,…])[characteristic…]routine_body;每个参数均由3部分组成，分别是输入/输出类型、参数名和参数类型，形式如下：[IN|OUT|INOUT]parameter_nametype12.1存储过程10.1.2执行存储过程用SQL语句执行存储过程的语法格式为。CALLsp_name([parameter[,…]])【例10-2】调用执行up_display_all_student过程。SQL语句如下。CALLup_display_all_student();12.1存储过程characteristic用于指定存储过程中使用SQL语句的特性参数，该参数的取值由一种或几种选项组合而成，语法格式：LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'12.1存储过程【例12-1】在student_db数据库中，创建存储过程pr_id_avg_score，输入学号，显示score表中该学号的平均成绩、参加考试的课程门数。CREATEPROCEDUREpr_id_avg_score(INst_idCHAR(10))READSSQLDATACOMMENT'显示学号、平均成绩和考试课程的门数'BEGINSELECTStudentID学号,ROUND(AVG(Score))平均分,COUNT(CourseID)考试课程门数

FROMscoreWHEREStudentID=st_id;END;12.1存储过程12.1存储过程12.1.3调用存储过程CALLproc_name([parameter1,parameter2,…]);【例12-2】调用存储过程pr_id_avg_score，查看其返回值。SET@id='2023510103';CALLpr_id_avg_score(@id);SELECTStudentID学号,ROUND(AVG(Score))平均分,COUNT(CourseID)考试课程门数

FROMscoreWHEREStudentID='2023510103';12.1存储过程12.1存储过程12.1.4创建和调用存储过程的实例【例12-3】创建存储过程pr_avg_score，输入学号，输出score表中该学号的平均成绩、参加考试的课程门数。1）SET@st_ID='2023510103';SET@avg_Score=0,@count_CourseID=0;SELECTROUND(AVG(Score)),COUNT(CourseID)INTO@avg_Score,@count_CourseIDFROMscoreWHEREStudentID=@st_ID;SELECT@st_ID学号,@avg_Score平均成绩,@count_CourseID考试课程的门数;

12.1存储过程2）CREATEPROCEDUREpr_avg_score(INst_IDCHAR(10),OUTavg_ScoreINT,OUTcount_CourseIDINT)NOTDETERMINISTICREADSSQLDATACOMMENT'显示学号、平均成绩和考试课程的门数'BEGINSELECTROUND(AVG(Score)),COUNT(CourseID)INTOavg_Score,count_CourseIDFROMscoreWHEREStudentID=st_ID;END;12.1存储过程3）SET@id='2023510103';SET@avg=0,@count=0;CALLpr_avg_score(@id,@avg,@count);SELECT@idAS学号,@avgAS平均成绩,@countAS考试课程的门数;12.1存储过程12.1.5查看存储过程1．查看存储过程的状态SHOWPROCEDURE|FUNCTIONSTATUS[LIKE'pattern'];【例12-4】查看pr开头的存储过程。SHOWPROCEDURESTATUSLIKE'pr%';12.1存储过程2．查看存储过程的定义SHOWCREATEPROCEDURE|FUNCTIONpf_name;【例12-5】查看存储过程pr_avg_score的定义。SHOWCREATEPROCEDUREpr_avg_score;12.1存储过程3．查看所有的存储过程SELECT*FROMinformation_schema.routines[WHEREroutine_name='pf_name'];【例12-6】查看存储过程pr_avg_score，以及全部存储过程和自定义函数。SELECT*FROMinformation_schema.routinesWHEREroutine_name='pr_avg_score';SELECT*FROMinformation_schema.routines;12.1存储过程12.1.6修改存储过程ALTERPROCEDUREproc_name[characteristic…]12.1存储过程【例12-7】修改存储过程pr_avg_score，将特性改为CONTAINSSQL，并指明权限调用者可以执行。SQL语句如下：ALTERPROCEDUREpr_avg_scoreCONTAINSSQLSQLSECURITYINVOKER;SELECTSPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPEFROMinformation_schema.routinesWHEREROUTINE_NAME='pr_avg_score'ANDROUTINE_TYPE='PROCEDURE';12.1存储过程12.1.7删除存储过程DROPPROCEDURE[IFEXISTS]proc_name;【例12-8】删除存储过程pr_avg_score。DROPPROCEDUREIFEXISTSpr_avg_score;12.1存储过程12.1.8使用NavicatforMySQL管理存储过程在NavicatforMySQL的“导航”窗格中，展开某个数据库中的“函数”，将显示创建的所有存储过程和自定义函数。12.1存储过程12.1.9存储过程的各种参数应用1．不带参数的存储过程（1）创建不带参数的存储过程CREATEPROCEDUREproc_name()[characteristic…]routine_body;（2）调用不带参数的存储过程CALLproc_name;12.1存储过程【例12-9】创建无参存储过程pr_course，显示course表中的课程名、学分和课时。CREATEPROCEDUREpr_course()READSSQLDATACOMMENT'显示course表中的课程名、学分和课时'BEGINSELECTCourseNameAS课程名,CreditAS学分,CourseHourAS课时

FROMcourse;END;执行存储过程pr_course，SQL语句如下：CALLpr_course;12.1存储过程2．带IN参数的存储过程（1）创建带IN参数的存储过程CREATEPROCEDUREproc_name(INparameter_name1type1[,INparameter_name2type2,…])[characteristic…]routine_body;（2）调用带IN参数的存储过程CALLproc_name(parameter1[,parameter2,…]);12.1存储过程【例12-10】创建带有输入参数的存储过程pr_course_name，给定课程名，显示该课程的学分和课时。CREATEPROCEDUREpr_course_name(INvCourseNameVARCHAR(20))READSSQLDATABEGINSELECT*FROMcourseWHERECourseName=vCourseName;WHERECourseName=vCourseNameFROMcourse;END;CALLpr_course_name('数据库原理');或SET@CourseName='数据库原理';CALLpr_course_name(@CourseName);12.1存储函数3．带OUT参数的存储过程（1）创建带OUT参数的存储过程CREATEPROCEDUREproc_name(INparam_name1type1[,…],OUTparam_name2type2[,…])[characteristic…]routine_body（2）调用带OUT参数的存储过程SET@variable_name=表达式;CALLproc_name(parameter1[,…],@variable_name[,…]);12.1存储函数【例12-11】创建带有输出参数的存储过程pr_count_score，给定学号，统计该学生的考试课程数、通过的课程数和不通过的课程数，并通过输出参数返回。DROPPROCEDUREIFEXISTSpr_count_score;CREATEPROCEDUREpr_count_score(INvStudentIDCHAR(10),OUTvCountINT,OUTvPassINT,OUTvFailINT)READSSQLDATABEGINSELECTCOUNT(CourseID)INTOvCountFROMscoreWHEREStudentID=vStudentID;SELECTCOUNT(CourseID)INTOvPassFROMscoreWHEREStudentID=vStudentIDANDScore>=60;SETvFail=vCount-vPass;END;12.1存储函数调用存储过程：SET@StudentID='2023510102';CALLpr_count_score(@StudentID,@count,@pass,@fail);显示3个用户对话局部变量的值，SQL语句如下：SELECT@countAS考试课程数,@passAS通过的课程数,@failAS不通过的课程数;12.1存储函数4．带INOUT参数的存储过程（1）创建带INOUT参数的存储过程CREATEPROCEDUREproc_name(INOUTparam_nametype[,…])[characteristic…]routine_body;（2）调用带INOUT参数的存储过程SET@variable_name=表达式;CALLproc_name(@variable_name[,…]);12.1存储函数【例12-12】创建带有INOUT参数的存储过程pr_negate，给定一个数值，返回相反的数值。INOUT参数为一个数值x，创建存储过程pr_negate的SQL语句如下：CREATEPROCEDUREpr_negate(INOUTxFLOAT)BEGINSETx=-x;END;SET@a=10;CALLpr_negate(@a);显示@a的值，SQL语句如下：SELECT@a;12.2异常处理12.2.1自定义异常名称DECLAREcondition_nameCONDITIONFORcondition_value;SQLSTATEsqlstate_value|mysql_error_code;例如，在错误代码“1062(23000)”中，sqlstate_value的值为字符串'23000'，mysql_error_code为1062。具体的对应方式可以查看MySQL的异常错误代码。12.2异常处理【例12-13】用名字定义“1062(23000)”这个错误，名字为error_insert。使用两种不同的方法定义。方法一：使用sqlstate_value，SQL语句如下：DECLAREerror_insertCONDITIONFORSQLSTATE'23000';方法二：使用mysql_error_code，SQL语句如下：DECLAREerror_insertCONDITIONFOR1062;12.2异常处理12.2.2自定义异常处理程序DECLAREhandler_typeHANDLERFORcondition_valuesp_statement;condition_name|mysql_error_code|SQLSTATEsqlstate_value|SQLWARNING|NOTFOUND|SQLEXCEPTION12.3使用游标处理结果集12.3.1游标的概念游标是一种能从多条记录的结果集中每次提取一条记录的机制。游标总是与一条SELECT语句相关联，并存放SELECT语句的运行结果。游标由结果集（可以是零条、一条或多条记录）和结果集中指向特定记录的游标指针组成。游标的功能就是可逐行访问由SELECT语句返回的结果集，然后用SQL语句逐行从游标中获取记录，并赋给变量。10.3过程体12.3.2定义游标DECLAREcursor_nameCURSORFORselect_statement;【例12-14】创建一个游标cur_student，从student表中查询出学号、姓名和班级号的记录。定义名为cur_student游标的SQL语句如下：DECLAREcur_studentCURSORFORSELECTStudentID,StudentName,ClassIDFROMstudent;其中，游标指向结果集对应的查询语句如下：SELECTStudentID,StudentName,ClassIDFROMstudent;10.3过程体12.3.3打开游标OPENcursor_name;例如，打开前面例题创建的cur_student游标，SQL语句如下：OPENcur_student;10.3过程体12.3.4使用游标FETCHcursor_nameINTOvar_name1[,var_name2,…];OPENcur_student; FETCHcur_studentINTO…; 10.3过程体DECLAREdoneBOOLEANDEFAULT0;--DECLAREdoneINTDEFAULTFalse;DECLAREcurCURSORFORSELECT…;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; --DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1; --DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=True;10.3过程体OPENcur;FETCHcurINTO…;WHILE(done!=1)DO #处理语句;FETCHcurINTO…;ENDWHILE;CLOSEcur; OPENcur;REPEATFETCHcurINTO…;IFdone!=1THEN #处理语句;ENDIF;UNTILdoneENDREPEAT