《mysql存储过程和函数.ppt》由会员分享，可在线阅读，更多相关《mysql存储过程和函数.ppt（37页珍藏版）》请在三一办公上搜索。
1、第9讲 存储过程与存储函数,存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。,9.1 创建存储过程和函数,创建存储过程和函数是指将经常使用的一组SQL语句的组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。例如，银行经常需要计算用户的利息。不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个存储过程或者存储函数。只要调用这个存储过程或者存储函数，就
2、可以将不同类别用户的利息计算出来。,创建存储过程,MySQL中，创建存储过程的基本形式如下：CREATE PROCEDURE sp_name(proc_parameter,.)characteristic.routine_body,Sp_name:存储过程的名称，默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称Proc_parameter:存储过程的参数列表格式IN|OUT|INOUTparam_name typeParam_name为参数名，type为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入/输出参数，分别用in/out/inout标识。
3、参数的取名不要与数据表的列名相同。,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表示存储过程包含读或写数据
4、的语句（默认）No sql表示不包含sql语句Reads sql data表示存储过程只包含读数据的语句Modifies sql data 表示存储过程只包含写数据的语句5 sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer,Routine_body:存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以begin开始，以end结束。如果存储过程体中只有一条sql语句,可以省略begin-end标志。,例题在数据库example中创建一个存储过程，用于实现给定表
5、customers中一个id号即可修改表customers中该客户的性别为一个指定的性别。调用存储过程sp_update_sex，将id为2的客户性别修改为男性”M”Call sp_update_sex(2,M);,课堂练习在数据库example中创建一个存储过程，用于实现给定employee表中给定一个部门号d_id,即可统计出该部门的总人数。,9.1.2 存储过程体,存储过程体中可以使用各种sql语句和过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。下面主要介绍几个用于构造存储过程体的常用语法元素。,1 局部变量在存储过程体中可以声明局部变量，用来
6、存储存储过程体中临时结果。DECLARE var_name,type DEFAULT valueVar_name:指定局部变量的名称Type:用于声明局部变量的数据类型default子句:用于为局部变量指定一个默认值。若没有指定，默认为null.Declare cid int(10);,使用说明：局部变量只能在存储过程体的beginend语句块中声明。局部变量必须在存储过程体的开头处声明。局部变量的作用范围仅限于声明它的begin.end语句块，其他语句块中的语句不可以使用它。局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用符号，并且它只能在begin.end语句块中使用；而
7、用户变量在声明时，会在其名称前面使用符号，同时已声明的用户变量存在于整个会话之中。,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 定义处理程序是事先定义程序执行过程中可
8、能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。DECLARE handler_type HANDLER FOR condition_value,sp_statementhandler_type:CONTINUE|EXIT|UNDOCondition_value:Sqlwarning|not found|sqlexception,5 流程控制语句（1）条件判断语句If语句If search_condition then statement_listelseif search_condition then statement_list
9、else 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语句语
10、法格式：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是
11、否为真，倘若为真，则结束循环，若不为真，继续循环。Repeat先执行后判断，while先判断后执行。,Loop语句语法格式：begin_label:loopStatement_listEnd loopend_labelLoop语句允许重复执行某个特定语句或语句块，实现一个简单的循环构造，其中statement_list用于指定需要重复执行的语句。在循环体statement_list中语句会一直重复被执行，直至循环使用leave语句或者iterate退出。,Leave语句 主要用于跳出循环控制，语法结构Leave labeladd_num:loopset count=count+1;If cou
12、nt=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 光标（游标）查询语句可能查询出多条记录，在存储过程和函数中使用光标标来逐条读取查询结果集中的记录。光标的使用包括声
13、明光标、打开光标、使用光标和关闭光标。光标必须声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。,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关键字来使用光标
14、，语法形式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语句打开。,
15、课堂习题在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 修改存储
16、过程,Alter procedure proc_namecharacteristic只能修改存储过程的特征，如果要修改存储过程的内容，可以先删除该存储过程，然后再重新创建,9.4 删除存储过程,Drop procedure if exists sp_name;,存储过程与存储函数联系与区别,存储过程与存储函数一样，都是由sql语句和过程式语句所组成的代码片段，并且可以被应用程序和其他sql语句调用。区别：存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。可以直接对存储函数进行调用，而不需要使用call语句;而对存储过程的调用，需要使用call语句。存储函数中必
17、须包含一条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修
18、改存储函数alter 修改存储函数的某些相关特征。,习题：在food表上创建名为food_price_count的存储过程。存储过程有3个参数，输入参数为price_info1和price_info2，输出参数为count。存储过程的作用是查询food表中食品单价高于price_info1且低于price_info2的食品种数，然后由count参数来输出。并且计算满足条件的单价的总和。,小结,本章介绍了MySQL数据库的存储过程和存储函数。存储过程和存储函数都是用户自己定义的SQL语句的集合。它们都存储在服务器端，只要调用就可以在服务器端执行。本章重点讲解了创建存储过程和存储函数的方法。通过CREATE PROCEDURE语句来创建存储过程，通过CREATE FUNCTION语句来创建存储函数。这两个内容也是本章的难点，尤其是变量、条件、光标、流程控制的使用。,
