第六章 存储过程与事务存储过程概述 创建和执行存储过程 管理存储过程 存储函数 事务

存储过程概述创建和执行存储过程管理存储过程存储函数事务第六章存储过程与事务学习目标掌握了解理解1掌握存储过程的创建、修改和执行、存储函数的创建和执行、事务处理的过程2理解存储过程的作用、事务的隔离级别3了解存储过程和存储函数、程序存储的类型MySQL程序设计基础6.1存储过程概述6.26.3创建和执行存储过程目录管理存储过程6.4存储函数6.56.6事务目录创建存储过程语法格式如下：CREATEPROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_body其中：

proc_parameter:[IN|OUT|INOUT]param_nametypecharacteristic:LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'routine_body:ValidSQLprocedurestatementorstatements6.3创建和执行存储过程6.3.1创建和执行不带参数的存储过程使用SQL语句在“学生选课”数据库中创建一个名为p_jiaoshi1的存储过程。该存储过程返回“教师”表中所有学历为“硕士研究生”的记录。对应的SQL语句如下：DELIMITER//CREATEPROCEDUREp_jiaoshi1()BEGINSELECT*FROMteacherWHEREtedu='硕士研究生';ENDEND//DELIMITER;实例6-1使用SQL语句执行例6-1中创建的存储过程。对应的SQL语句如下：执行结果如图6.1所示。执行结果如下所示：USE学生选课;CALLp_jiaoshi1();+------+--------+---------+------------+----------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------------+----------+|t002|李琦|男|硕士研究生|副教授||t003|王艳红|女|硕士研究生|讲师||t005|万丽|女|硕士研究生|助理讲师|+------+--------+---------+------------+----------+3rowsinset(0.00sec)实例6-21.创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数，在创建存储过程语句中定义输入参数，而在执行该存储过程中给出参数相应的值。6.3.2创建和执行带输入参数的存储过程（1）使用变量名传递参数值在执行存储过程的语句中，通过语句SET@parameter_name=value设定给参数的传递值，然后按位置传递参数。其语法格式如下：CALLprocedure_name([@parameter_name=value][,…n]);使用SQL语句在“学生选课”数据库中创建一个名为p_jiaoshi2的存储过程。该存储过程能根据用户给定的学历值查询返回“教师”表中对应的记录SET@输入学历='本科';CALLp_jiaoshi2(@输入学历);SET@输入学历='博士研究生';CALLp_jiaoshi2(@输入学历);实例6-4+------+--------+---------+------+------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------+------+|t001|吴亚飞|男|本科|讲师|+------+--------+---------+------+------+1rowinset(0.19sec)QueryOK,0rowsaffected(0.19sec)mysql>SET@输入学历='博士研究生';QueryOK,0rowsaffected(0.00sec)mysql>CALLp_jiaoshi2(@输入学历);+------+--------+---------+------------+------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------------+------+|t004|马志超|男|博士研究生|教授|+------+--------+---------+------------+------+1rowinset(0.00sec)运行结果：2.执行带输入参数的存储过程执行带输入参数的存储过程，有两种方法：一种是使用变量名传递参数值；另一种是按给定表达式值传递参数值。用参数名传递参数值的方法执行存储过程p_jiaoshi2，分别查询学历为“本科”和“博士研究生”的记录。

DELIMITER//SET@输入学历='本科';CALLp_jiaoshi2(@输入学历);SET@输入学历='博士研究生';CALLp_jiaoshi2(@输入学历);//DELIMITER;实例6-4（2）按给定表达式值传递参数在执行存储过程的语句中，当存储过程含有多个输入参数时，采用这种方式传递值，给定参数值的顺序必须与存储过程中定义的输入变量的顺序一致。其语法格式如下：CALLprocedure_name(value1,value2,…)用按给定表达式值传递参数的方式执行存储过程p_jiaoshi2，分别查找学历为“本科”和“博士研究生”的记录。对应的SQL语句如下：运行结果和例6.4运行结果相同。CALLp_jiaoshi2('本科');CALLp_jiaoshi2('博士研究生');实例6-5如果需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现。定义输出参数，需要在CREATEPROCEDURE语句中定义参数时在参数名后面指定OUT关键字。语法格式如下：OUTparameter_namedatatype[=default]6.3.3创建和执行带输出参数的存储过程创建存储过程p_jiaoshi3，要求能根据用户给定的学历值，统计出“教师”表的所有教师中，学历为该值的教师人数，并将结果以输出变量的形式返回给调用者。DELIMITER//CREATEPROCEDUREp_jiaoshi3(INtedu1varchar(10),OUTteachernumsmallint)BEGINSELECTCOUNT(*)INTOteachernumFROMteacherWHEREtedu=tedu1;END//DELIMITER;实例6-6执行存储过程p_jiaoshi3，统计教师表中学历为“硕士研究生”的教师人数。运行结果：CALLp_jiaoshi3('硕士研究生',@abc);SELECT@abc;mysql>SELECT@abc;+------+|@abc|+------+|3|+------+1rowinset(0.00sec)实例6-7