,Click to edit Master title style,Click to edit Master text styles,第9讲 存储过程与存储函数,存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。,9.1 创建存储过程和函数,创建存储过程和函数是指将经常使用的一组SQL语句的组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。例如，银行经常需要计算用户的利息。不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个存储过程或者存储函数。只要调用这个存储过程或者存储函数，就可以将不同类别用户的利息计算出来。,创建存储过程,MySQL中，创建存储过程的基本形式如下：,CREATE PROCEDURE sp_name(proc_parameter,.),characteristic.routine_body,Sp_name:存储过程的名称，默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称,Proc_parameter:存储过程的参数列表,格式IN|OUT|INOUTparam_name type,Param_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 data,Contains sql表示存储过程包含读或写数据的语句（默认）,No sql表示不包含sql语句,Reads sql data表示存储过程只包含读数据的语句,Modifies sql data 表示存储过程只包含写数据的语句,5 sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer,Routine_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 value,Var_name:指定局部变量的名称,Type:用于声明局部变量的数据类型,default子句:用于为局部变量指定一个默认值。若没有指定，默认为null.,Declare cid int(10);,使用说明：,局部变量只能在存储过程体的beginend语句块中声明。,局部变量必须在存储过程体的开头处声明。,局部变量的作用范围仅限于声明它的begin.end语句块，其他语句块中的语句不可以使用它。,局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用符号，并且它只能在begin.end语句块中使用；而用户变量在声明时，会在其名称前面使用符号，同时已声明的用户变量存在于整个会话之中。,2 set语句,使用set语句为局部变量赋值,Set var_name=expr,Set cid=910;,3 select into 语句,把选定列的值直接存储到局部变量中，语法格式,Select col_name,into var_name,table_expr,Col_name:用于指定列名,Var_name:用于指定要赋值的变量名,Table_expr:表示select语句中的from字句及后面的语法部分,说明:存储过程体中的selectinto语句返回的结果集只能有一行数据。,4 定义处理程序,是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。,DECLARE handler_type HANDLER FOR condition_value,sp_statement,handler_type:CONTINUE|EXIT|UNDO,Condition_value:Sqlwarning|not found|sqlexception,5 流程控制语句,（1）条件判断语句,If语句,If search_condition then statement_list,elseif search_condition then statement_list,else statement_list,End if,Search_condition参数:条件判断语句,Statement_list参数:不同条件的执行语句,Case 语句,表达形式1,Case case_value,When when_value then statement_list,When when_value then statement_list,else statement_list,End case,表达形式2,Case,When search_condition then statement_list,End case,(2)循环语句,While语句、repeat语句和loop语句。,While语句语法格式：,begin_label:while search_condition do,Statement_list,End whileend_label,判断条件search_condition是否为真,若为真,则执行statement_list中的语句，然后再进行判断，如若仍然为真则继续循环，直至条件判断不为真时循环结束。,Repeat语句语法格式,begin_label:repeat,Statement_list,Until search_condition,End repeatend_label,Repeat语句首先执行statement_list中的语句，然后判断条件search_condition是否为真，倘若为真，则结束循环，若不为真，继续循环。,Repeat先执行后判断，while先判断后执行。,Loop语句语法格式：,begin_label:loop,Statement_list,End loopend_label,Loop语句允许重复执行某个特定语句或语句块，实现一个简单的循环构造，其中statement_list用于指定需要重复执行的语句。,在循环体statement_list中语句会一直重复被执行，直至循环使用leave语句或者iterate退出。,Leave语句,主要用于跳出循环控制，语法结构,Leave label,add_num:loop,set count=count+1;,If count=100 then,leave add_num;,End loop add_num;,ITERATE语句,跳出循环语句，跳出本次循环，然后直接进入下一个循环。语法形式,Iterate label,Add_num:loop,Set 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_name,Open 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语