mysql存储过程和函数.ppt

第第9讲讲 存储过程与存储函数存储过程与存储函数存储过程和函数是在数据库中定义一些存储过程和函数是在数据库中定义一些SQL语句的集合，语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在语句。而且，存储过程和函数是在MySQL服务器中存服务器中存储和执行的，可以减少客户端和服务器端的数据传输。储和执行的，可以减少客户端和服务器端的数据传输。9.1 创建存储过程和函数创建存储过程和函数创建存储过程和函数是指将经常使用的一组创建存储过程和函数是指将经常使用的一组SQL语句语句的组合在一起，并将这些的组合在一起，并将这些SQL语句当作一个整体存储在语句当作一个整体存储在MySQL服务器中。例如，银行经常需要计算用户的利息。服务器中。例如，银行经常需要计算用户的利息。不同类别的用户的利率是不一样的。这就可以将计算利率不同类别的用户的利率是不一样的。这就可以将计算利率的的SQL代码写成一个存储过程或者存储函数。只要调用这代码写成一个存储过程或者存储函数。只要调用这个存储过程或者存储函数，就可以将不同类别用户的利息个存储过程或者存储函数，就可以将不同类别用户的利息计算出来。计算出来。创建存储过程创建存储过程MySQL中，创建存储过程的基本形式如下：中，创建存储过程的基本形式如下：CREATE PROCEDURE sp_name(proc_parameter,.)characteristic.routine_bodySp_name:存储过程的名称，默认在当前数据库中创存储过程的名称，默认在当前数据库中创建。这个名称应当尽量避免与建。这个名称应当尽量避免与MySQL的内置函数相同的名的内置函数相同的名称称Proc_parameter:存储过程的参数列表存储过程的参数列表格式格式IN|OUT|INOUTparam_name typeParam_name为参数名，为参数名，type为参数的数据类型。多为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入个参数彼此间用逗号分隔。输入参数、输出参数和输入/输输出参数，分别用出参数，分别用in/out/inout标识。参数的取名不要与数据标识。参数的取名不要与数据表的列名相同。表的列名相同。Characteristic:存储过程的某些特征设定，分别介绍存储过程的某些特征设定，分别介绍1 COMMENTstring:用于对存储过程的描述，其中用于对存储过程的描述，其中string为描述内容为描述内容,comment为关键字。为关键字。2 LANGUAGE SQL:指明编写这个存储过程的语言为指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。语言。这个选项可以不指定。3 DETERMINISTIC:表示存储过程对同样的输入参数表示存储过程对同样的输入参数产生相同的结果产生相同的结果;NOT DETERMINISTIC，则表示会产生不，则表示会产生不确定的结果（默认）。确定的结果（默认）。4 contains sql|no sql|reads sql data|modifies sql dataContains sql表示存储过程包含读或写数据的语句表示存储过程包含读或写数据的语句（默认）（默认）No sql表示不包含表示不包含sql语句语句Reads sql data表示存储过程只包含读数据的语句表示存储过程只包含读数据的语句Modifies sql data 表示存储过程只包含写数据的语句表示存储过程只包含写数据的语句5 sql security:这个特征用来指定存储过程使用创建这个特征用来指定存储过程使用创建该存储过程的用户该存储过程的用户(definer)的许可来执行，还是使用调用的许可来执行，还是使用调用者者(invoker)的许可来执行。默认是的许可来执行。默认是definerRoutine_body:存储过程的主体部分，包含了在过程存储过程的主体部分，包含了在过程调用的时候必须执行的调用的时候必须执行的sql语句。以语句。以begin开始，以开始，以end结结束。如果存储过程体中只有一条束。如果存储过程体中只有一条sql语句语句,可以省略可以省略begin-end标志。标志。例题例题在数据库在数据库example中创建一个存储过程，用于实现给中创建一个存储过程，用于实现给定表定表customers中一个中一个id号即可修改表号即可修改表customers中该客中该客户的性别为一个指定的性别。户的性别为一个指定的性别。调用存储过程调用存储过程sp_update_sex，将，将id为为2的客户性别的客户性别修改为男性修改为男性”M”Call sp_update_sex(2,M);课堂练习课堂练习在数据库在数据库example中创建一个存储过程，用于实现给中创建一个存储过程，用于实现给定定employee表中给定一个部门号表中给定一个部门号d_id,即可统计出该部门即可统计出该部门的总人数。的总人数。9.1.2 存储过程体存储过程体存储过程体中可以使用各种存储过程体中可以使用各种sql语句和过程式语句的组语句和过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。下面主要介绍几个用于构造实现数据库应用的灵活编程。下面主要介绍几个用于构造存储过程体的常用语法元素。存储过程体的常用语法元素。1 局部变量局部变量在存储过程体中可以声明局部变量，用来存储存储过在存储过程体中可以声明局部变量，用来存储存储过程体中临时结果。程体中临时结果。DECLARE var_name,type DEFAULT valueVar_name:指定局部变量的名称指定局部变量的名称Type:用于声明局部变量的数据类型用于声明局部变量的数据类型default子句子句:用于为局部变量指定一个默认值。若没用于为局部变量指定一个默认值。若没有指定，默认为有指定，默认为null.Declare cid int(10);使用说明：使用说明：局部变量只能在存储过程体的局部变量只能在存储过程体的beginend语句块中声语句块中声明。明。局部变量必须在存储过程体的开头处声明。局部变量必须在存储过程体的开头处声明。局部变量的作用范围仅限于声明它的局部变量的作用范围仅限于声明它的begin.end语句语句块，其他语句块中的语句不可以使用它。块，其他语句块中的语句不可以使用它。局部变量不同于用户变量，两者区别：局部变量声明局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用时，在其前面没有使用符号，并且它只能在符号，并且它只能在begin.end语语句块中使用；而用户变量在声明时，会在其名称前面使用句块中使用；而用户变量在声明时，会在其名称前面使用符号，同时已声明的用户变量存在于整个会话之中。符号，同时已声明的用户变量存在于整个会话之中。2 set语句语句使用使用set语句为局部变量赋值语句为局部变量赋值Set var_name=exprSet cid=910;3 select into 语句语句把选定列的值直接存储到局部变量中，语法格式把选定列的值直接存储到局部变量中，语法格式Select col_name,into var_name,table_exprCol_name:用于指定列名用于指定列名Var_name:用于指定要赋值的变量名用于指定要赋值的变量名Table_expr:表示表示select语句中的语句中的from字句及后面的字句及后面的语法部分语法部分说明说明:存储过程体中的存储过程体中的selectinto语句返回的结果集语句返回的结果集只能有一行数据。只能有一行数据。4 定义处理程序定义处理程序是事先定义程序执行过程中可能遇到的问题。并且可是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。提前预测可能出现的问题，并提出解决方法。DECLARE handler_type HANDLER FOR condition_value,sp_statementhandler_type:CONTINUE|EXIT|UNDOCondition_value:Sqlwarning|not found|sqlexception5 流程控制语句流程控制语句（1）条件判断语句）条件判断语句If语句语句If search_condition then statement_listelseif search_condition then statement_listelse statement_listEnd ifSearch_condition参数参数:条件判断语句条件判断语句Statement_list参数参数:不同条件的执行语句不同条件的执行语句Case 语句语句表达形式表达形式1Case case_valueWhen when_value then statement_listWhen when_value then statement_listelse statement_listEnd case表达形式表达形式2Case When search_condition then statement_listEnd case(2)循环语句循环语句While语句、语句、repeat语句和语句和loop语句。语句。While语句语法格式：语句语法格式：begin_label:while search_condition do Statement_listEnd whileend_label判断条件判断条件search_condition是否为真是否为真,若为真若为真,则执行则执行statement_list中的语句，然后再进行判断，如若仍然为中的语句，然后再进行判断，如若仍然为真则继续循环，直至条件判断不为真时循环结束。真则继续循环，直至条件判断不为真时循环结束。Repeat语句语法格式语句语法格式begin_label:repeatStatement_listUntil search_conditionEnd repeatend_labelRepeat语句首先执行语句首先执行statement_list中的语句，然后中的语句，然后判断条件判断条件search_condition是否为真，倘若为真，则结束是否为真，倘若为真，则结束循环，若不为真，继续循环。循环，若不为真，继续循环。Repeat先执行后判断，先执行后判断，while先判断后执行。先判断后执行。Loop语句语法格式：语句语法格式：begin_label:loopStatement_listEnd loopend_labelLoop语句允许重复执行某个特定语句或语句块，实现语句允许重复执行某个特定语句或语句块，实现一个简单的循环构造，其中一个简单的循环构造，其中statement_list用于指定需要用于指定需要重复执行的语句。重复执行的语句。在循环体在循环体statement_list中语句会一直重复被执行，中语句会一直重复被执行，直至循环使用直至循环使用leave语句或者语句或者iterate退出。退出。Leave语句语句 主要用于跳出循环控制，语法结构主要用于跳出循环控制，语法结构Leave labeladd_num:loopset count=count+1;If count=100 then leave add_num;End loop add_num;ITERATE语句语句跳出循环语句，跳出本次循环，然后直接进入下一个跳出循环语句，跳出本次循环，然后直接进入下一个循环。语法形式循环。语法形式Iterate labelAdd_num:loopSet count=count+1;If count=100 then leave add_num;Else if mod(count,3)=0 then iterate add_num;Select*from employee;End loop add_num;5 光标（游标）光标（游标）查询语句可能查询出多条记录，在存储过程和函数中查询语句可能查询出多条记录，在存储过程和函数中使用光标标来逐条读取查询结果集中的记录。光标的使用使用光标标来逐条读取查询结果集中的记录。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必包括声明光标、打开光标、使用光标和关闭光标。光标必须声明光标、打开光标、使用光标和关闭光标。光标必须须声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。声明在处理程序之前，并且声明在变量和条件之后。1 声明光标声明光标Declare cursor_name cursor for select_statement;Cursor_name:光标名称光标名称Select_statement:select语句的内容语句的内容Declare cur_employee cursor for select name,age from employee;2 打开光标打开光标Open cursor_nameOpen cur_employee;3 使用光标使用光标Mysql中使用中使用fetch关键字来使用光标，语法形式关键字来使用光标，语法形式Fetch cur_name into var_name,var_name;Cur_name表示光标的名称表示光标的名称Var_name表示将光标中的表示将光标中的select语句查询出来的信语句查询出来的信息存入该参数。息存入该参数。Var_name必须在声明光标前就定义好。必须在声明光标前就定义好。Fetch cur_employee into emp_name,emp_age;4 关闭光标关闭光标Close cursor_name;Close cur_employee;每个光标不再需要时都应该被关闭，使用每个光标不再需要时都应该被关闭，使用close语句语句将会释放光标所使用的全部资源。在一个光标被关闭后，将会释放光标所使用的全部资源。在一个光标被关闭后，如果没有重新被打开，则不能被使用。对于声明过的光标，如果没有重新被打开，则不能被使用。对于声明过的光标，则不需要再次声明，可直接使用则不需要再次声明，可直接使用open语句打开。语句打开。课堂习题课堂习题在在teacher表上创建名为表上创建名为teacher_info1的存储过程，的存储过程，要求：要求：teacher_info1有有3个参数。输入参数为个参数。输入参数为teacher_id和和type，输出参数为，输出参数为info。存储过程的作用是根据编号。存储过程的作用是根据编号teacher_id来查询来查询teacher表中的记录。如果表中的记录。如果type的值为的值为1时，将姓名时，将姓名name传给输出参数传给输出参数info；如果；如果type的值为的值为2时，时，将年龄传给输出参数将年龄传给输出参数info；如果；如果type的值为其他值，则返的值为其他值，则返回字符串回字符串”Error”。9.2 调用存储过程调用存储过程Call sp_name(parameter,);Sp_name被调用存储过程的名称被调用存储过程的名称Parameter:指定调用存储过程所要使用的参数。指定调用存储过程所要使用的参数。9.3 修改存储过程修改存储过程Alter procedure proc_namecharacteristic只能修改存储过程的特征，如果要修改存储过程的内只能修改存储过程的特征，如果要修改存储过程的内容，可以先删除该存储过程，然后再重新创建容，可以先删除该存储过程，然后再重新创建9.4 删除存储过程删除存储过程Drop procedure if exists sp_name;存储过程与存储函数联系与区别存储过程与存储函数联系与区别存储过程与存储函数一样，都是由存储过程与存储函数一样，都是由sql语句和过程式语语句和过程式语句所组成的代码片段，并且可以被应用程序和其他句所组成的代码片段，并且可以被应用程序和其他sql语句语句调用。调用。区别：区别：存储函数不能拥有输出参数，因为存储函数自身就是存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。输出参数；而存储过程可以拥有输出参数。可以直接对存储函数进行调用，而不需要使用可以直接对存储函数进行调用，而不需要使用call语语句句;而对存储过程的调用，需要使用而对存储过程的调用，需要使用call语句。语句。存储函数中必须包含一条存储函数中必须包含一条return语句，而这条特殊的语句，而这条特殊的sql语句不允许包含于存储过程中。语句不允许包含于存储过程中。创建存储函数创建存储函数MySQL中，创建存储函数的基本形式如下：中，创建存储函数的基本形式如下：CREATE FUNCTION sp_name(func_parameter,.)RETURNS typecharacteristic.routine_bodyReturn子句用于声明存储函数返回值的数据类型。子句用于声明存储函数返回值的数据类型。示例示例在数据库中创建一个存储函数，要求该函数能根据给在数据库中创建一个存储函数，要求该函数能根据给定的定的id返回客户的性别，如果数据库中没有给定的返回客户的性别，如果数据库中没有给定的id，则，则返回没有该客户。返回没有该客户。调用存储函数调用存储函数Select sp_name(func_parameter)Select fn_search(2);删除存储函数删除存储函数drop修改存储函数修改存储函数alter 修改存储函数的某些相关特征。修改存储函数的某些相关特征。习题：习题：在在food表上创建名为表上创建名为food_price_count的存储过程。的存储过程。存储过程有存储过程有3个参数，输入参数为个参数，输入参数为price_info1和和price_info2，输出参数为，输出参数为count。存储过程的作用是查询。存储过程的作用是查询food表中食品单价高于表中食品单价高于price_info1且低于且低于price_info2的的食品种数，然后由食品种数，然后由count参数来输出。并且计算满足条件参数来输出。并且计算满足条件的单价的总和。的单价的总和。小结小结本章介绍了本章介绍了MySQL数据库的存储过程和存储函数。存数据库的存储过程和存储函数。存储过程和存储函数都是用户自己定义的储过程和存储函数都是用户自己定义的SQL语句的集合。语句的集合。它们都存储在服务器端，只要调用就可以在服务器端执行。它们都存储在服务器端，只要调用就可以在服务器端执行。本章重点讲解了创建存储过程和存储函数的方法。通过本章重点讲解了创建存储过程和存储函数的方法。通过CREATE PROCEDURE语句来创建存储过程，通过语句来创建存储过程，通过CREATE FUNCTION语句来创建存储函数。这两个内容也语句来创建存储函数。这两个内容也是本章的难点，尤其是变量、条件、光标、流程控制的使是本章的难点，尤其是变量、条件、光标、流程控制的使用。用。