《MySQL数据库应用与管理（第3版）》 课件 8.2 存储过程

存储过程和存储函数

存储过程

本节内容1.局部变量2.使用CREATEPROCEDURE语句创建存储过程3.创建带有输入、输出参数的存储过程本节内容4.调用执行存储过程5.使用ALTERPROCEDURE语句修改存储过程6.使用DROPPROCEDURE语句删除存储过程存储过程在存储过程或存储函数中，可以定义和使用变量。定义变量使用DECLARE语句，定义在存储过程或存储函数中的变量称为局部变量，其作用范围是本存储过程或存储函数，定义后就可以给变量进行赋值。1.局部变量存储过程（1）定义变量使用DECLARE语句可以定义变量。其语法格式如下：

DECLARE<变量名>[,…]<数据类型>[DEFAULT默认值]说明：可以同时定义多个变量，变量名之间用逗号隔开；也可以设置变量的默认值，如果没有设置，则默认值为NULL。1.局部变量例如：定义一个名为myVar的变量，INT类型，默认值为10。

DECLAREmyVarINTDEFAULT10;存储过程（2）给变量赋值使用SET语句可以给变量赋值。其语法格式如下：

SET<变量名1>=<赋值表达式1>[,<变量名2>=<赋值表达式2>,…]说明：可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。1.局部变量例如：为变量myVar赋值为30。

SETmyVar=30;存储过程（2）给变量赋值使用SELECT…INTO语句可以给变量赋值。其语法格式如下：

SELECT<字段名1>[,字段名2…]INTO<变量名1>[,变量名2…] FROM<表名>WHERE<查询条件>1.局部变量例如：在学生表（student）中查询出学号为“1308013101”的学生的班级，并把它赋给变量myVar。

DECLAREmyVarVARCHAR(30); SELECTdeptNameINTOmyVarFROMstudentWHEREsNo='1308013101';存储过程创建存储过程使用CREATEPROCEDURE语句，创建不带有参数的存储过程的语法格式如下：

CREATEPROCEDURE<存储过程名>() [characteristic…] <存储过程体>2.使用CREATEPROCEDURE语句创建存储过程存储过程说明：characteristic参数指定存储过程的特性，其主要取值及说明如下：[NOT]DETERMINISTIC：指定DETERMINISTIC的优化器是否开启，默认选项为NOTDETERMINISTIC。CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA：指定子程序使用SQL语句的限制，默认指定为CONTAINSSQL。SQLSECURITY{DEFINER|INVOKER}：指定谁有权限来执行，默认指定为DEFINER。COMMENT'string'：注释信息。<存储过程体>是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。2.使用CREATEPROCEDURE语句创建存储过程操作案例知识点概述示例8-1运行结果如下：创建一个不带有参数的存储过程up_softwareStudent，查询出“软件131”班级中的所有学生记录。存储过程创建带有输入、输出参数的存储过程的语法格式如下：

CREATEPROCEDURE<存储过程名>([参数1[,参数2[,…[,参数n]]]]) [characteristic…] <存储过程体>3.创建带有输入、输出参数的存储过程存储过程说明：<参数>的格式为：[IN|OUT|INOUT]<参数名><类型>。IN表示输入参数；OUT表示输出参数；INOUT表示既可以是输入、也可以是输出参数。<参数名>表示存储过程的参数名称。<类型>表示存储过程的参数类型，可以是MySQL数据库的任意数据类型。3.创建带有输入、输出参数的存储过程操作案例知识点概述示例8-2运行结果如下：创建一个带有输入参数的存储过程up_deptStudentInfo，通过一个给定的班级，查询出该班级中的所有学生记录。操作案例知识点概述示例8-3运行结果如下：创建一个带有输入和输出参数的存储过程up_scoreGradeInfo，通过一个给定的学号，查询出该学生选修课程的数量及平均分，并通过输出参数返回。存储过程存储过程是存储在服务器端的SQL语句的集合，要使用这些已经定义好的存储过程，就必须通过调用的方式来实现。存储过程被调用后，数据库系统将执行存储过程中的语句。执行存储过程需要拥有EXECUTE权限。调用存储过程使用CALL语句，其语法格式如下：

CALL<存储过程名>([<实际参数值1>[,实际参数值2….]])4.调用执行存储过程操作案例知识点概述示例8-4运行结果如下：调用示例8-2中的存储过程up_deptStudentInfo。操作案例知识点概述示例8-5运行结果如下：调用示例8-3中的存储过程up_scoreGradeInfo。存储过程修改存储过程使用ALTERPROCEDURE语句，其语法格式如下：

ALTERPROCEDURE<存储过程名>[characteristic…]

characteristic: {CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}| SQLSECURITY{DEFINER|INVOKER}| COMMENT'string'说明：characteristic参数的取值及说明与创建存储过程中的一样。5.使用ALTERPROCEDURE语句修改存储过程操作案例知识点概述示例8-6运行结果如下：修改示例8-2中存储过程up_deptStudentInfo的定义，将访问数据权限更改为READSSQLDATA。存储过程删除存储过程使用DROPPR