mysql课件第十四章存储过程和函数.ppt

MySql数据库,天津理工大学 华信软件学院 田春艳 2011年9月-2012年1月,存储过程和函数（1）,存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。,存储过程和函数（2）,将使用的一组SQL语句的组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。例如，银行经常需要计算用户的利息。不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个存储过程或者存储函数。只要调用这个存储过程或者存储函数，就可以将不同类别用户的利息计算出来。,内容,创建存储过程和函数定义变量的定义赋值定义条件和处理程序游标的使用流程控制的使用修改存储过程和函数删除存储过程和函数,创建存储过程,MySQL中，创建存储过程的基本形式如下：CREATE procedure sp_name（过程参数1，过程参数2）begin 过程体 end,例如,mysqlDELIMITER/mysqlCREATEPROCEDUREproc1(OUTsint)BEGINSELECTCOUNT(*)INTOsFROM student;ENDmysqlDELIMITER;,注意事项,DELIMITER/和 DELIMITER；MySQL默认以“;”为一个命令的结束参数CREATE PROCEDURE(IN|OUT|INOUT 参数名数据类形.)IN输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值OUT输出参数:该值可在存储过程内部被改变，并可返回INOUT输入输出参数:调用时指定，并且可被改变和返回过程体的开始与结束使用BEGIN与END进行标识。,创建存储函数,MySQL中，创建存储函数的基本形式如下：CREATE function sp_name(函数的参数）RETURNS type characteristic.routine_body,例如,Delimiter/Create function t_f(s int)returns char(20)bengin return(select sname from student where sno=s);end/delimiter;,存储过程和函数的区别,关键字 PROCEDURE、function参数上 存储过程可以的参数分为IN|OUT|INOUT 存储函数没有分类返回值上存储函数是有返回值的 而PROCEDURE是通过参数返回的,内容,创建存储过程和函数定义变量的定义赋值定义条件和处理程序游标的使用流程控制的使用修改存储过程和函数删除存储过程和函数,变量的使用,存储过程和函数中可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGINEND程序段中。本小节将讲解如何定义变量和为变量赋值。1定义变量2为变量赋值,定义变量,DECLARE var_name,.type DEFAULT value 如declare a,b,c int default 0其中，DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。,为变量赋值（1）,使用SET关键字来为变量赋值。基本语法如下：SET var_name=expr,var_name=expr.如：Set a=20,b=40,c=50一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。,为变量赋值（2）,使用SELECTINTO语句为变量赋值。其基本语法如下：SELECT col_name,INTO var_name,FROM table_name WEHRE condition 其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。SELECT d_id INTO my_sql FROM employee WEHRE id=2;,使用set定义变量,Set a=3；会话变量 对当前客户端有效Set global 变量名全局变量 对所有客户端生效,内容,创建存储过程和函数定义变量的定义赋值定义条件和处理程序游标的使用流程控制的使用修改存储过程和函数删除存储过程和函数,定义条件和处理程序,定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。1定义条件2定义处理程序,定义条件,MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：DECLARE condition_name CONDITION FOR condition_value condition_value:SQLSTATE VALUE sqlstate_value|mysql_error_code,/方法一：使用sqlstate_value DECLARE can_not_find CONDITION FOR SQLSTATE 42S02;/方法二：使用mysql_error_code DECLARE can_not_find CONDITION FOR 1146;,定义处理程序,DECLARE handler_type HANDLER for condition_value,.sp_statement handler_type:CONTINUE|EXIT|UNDO,condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。,/方法一：捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE 42S02SET info=CAN NOT FIND;/方法二：捕获mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET info=CAN NOT FIND;/方法三：先定义条件，然后调用 DECLARE can_not_find CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR can_not_find SET info=CAN NOT FIND;,/方法四：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET info=ERROR;/方法五：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET info=CAN NOT FIND;/方法六：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET info=ERROR;,内容,创建存储过程和函数定义变量的定义赋值定义条件和处理程序游标的使用流程控制的使用修改存储过程和函数删除存储过程和函数,游标的使用,查询语句可能查询出多条记录，在存储过程和存储函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。1声明游标2打开游标3使用游标4关闭游标,声明游标（1）,MySQL中使用DECLARE关键字来声明游标。其语法的基本形式如下：DECLARE cursor_name CURSOR FOR select_statement;,声明游标（2）,下面声明一个名为cur_employee的游标。代码如下：DECLARE cur_employee CURSOR FOR SELECT name,age FROM employee;上面的示例中，游标的名称为cur_employee；SELECT语句部分是从employee表中查询出name和age字段的值。,打开游标,MySQL中使用OPEN关键字来打开游标。其语法的基本形式如下：OPEN cursor_name;其中，cursor_name参数表示游标的名称。例如：下面打开一个名为cur_employee的游标，代码如下：OPEN cur_employee;,使用游标（1）,MySQL中使用FETCH关键字来使用游标。其语法的基本形式如下：FETCH cur_employee INTO var_name,var_name;其中，cursor_name参数表示游标的名称；var_name参数表示将游标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明游标之前就定义好。,使用游标（2）,下面使用一个名为cur_employee的游标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下：FETCH cur_employee INTO emp_name,emp_age;上面的示例中，将游标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。,关闭游标,MySQL中使用CLOSE关键字来关闭游标。其语法的基本形式如下：CLOSE cursor_name;其中，cursor_name参数表示游标的名称。下面关闭一个名为cur_employee的游标。代码如下：CLOSE cur_employee;,内容,创建存储过程和函数定义变量的定义赋值定义条件和处理程序游标的使用流程控制的使用修改存储过程和函数删除存储过程和函数,流程控制的使用（1）,存储过程和存储函数中可以使用流程控制来控制语句的执行。MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句、WHILE语句来进行流程控制。本小节将详细讲解这些流程控制语句。,流程控制的使用（2）,1IF语句2CASE语句3LOOP语句4LEAVE语句5ITERATE语句6REPEAT语句7WHILE语句,IF语句,其语法的基本形式如下：IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list.ELSE statement_list END IF,If语句示例,下面是一个IF语句的示例。代码如下：IF age=20,CASE 语句,CASE case_value WHEN when_value THEN statement_list WHEN when_value THEN statement_list.ELSE statement_list END CASE 其中，case_value参数表示条件判断的变量；when_value参数表示变量的取值；statement_list参数表示不同when_value值的执行语句。适用于离散型,CASE 语句,CASE语句还有另一种形式。CASE WHEN search_condition THEN statement_list WHEN search_condition THEN statement_list.ELSE statement_list END CASE 其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。适用于具体范围的情况,CASE 语句,CASE age WHEN 20 THEN SET count1=count1+1;ELSE SET count2=count2+1;END CASE;代码也可以是下面的形式：CASE WHEN age=20 THEN SET count1=count1+1;ELSE SET count2=count2+1;END CASE;,LOOP语句,LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。LOOP语句的语法的基本形式如下：begin_label:LOOP statement_list END LOOP end_label 其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。,LEAVE语句,LEAVE语句主要用于跳出循环控制。其语法形式如下：LEAVE label 其中，label参数表示循环的标志。,ITERATE语句,ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。ITERATE语句的基本语法形式如下：ITERATE label 其中，label参数表示循环的标志。,ITERATE语句的示例,下面是一个ITERATE语句的示例。代码如下：CREATE PROCEDURE doiterate(p1 INT)BEGIN label1:LOOP SET p1=p1+1;IF p1 10 THEN ITERATE label1;END IF;LEAVE label1;SET y=p1;END LOOP label1;SET x=p1;END,REPEAT语句,REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：begin_label:REPEAT statement_list UNTIL search_condition END REPEAT end_label 其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。,REPEAT,REPEAT SET count=count+1;UNTIL count=100 END REPEAT;,WHILE语句,WHILE语句也是有条件控制的循环语句。WHILE语句是当满足条件时，执行循环内的语句。WHILE语句的基本语法形式如下：begin_label:WHILE 表达式1 DO statement_list END WHILE end_label 其中，表达式1参数表示循环执行的条件，满足该条件时循环执行；statement_list参数表示循环的执行语句。,示例,WHILE count100 DO SET count=count+1;END WHILE;该示例循环执行count加1的操作，count值小于100时执行循环。如果count值等于100了，则跳出循环。WHILE循环需要使用END WHILE来结束。,内容,创建存储过程和函数定义变量的定义赋值定义条件和处理程序游标的使用流程控制的使用调用存储过程和函数修改和删除存储过程和函数,调用存储过程和函数,存储过程和存储函数都是存储在服务器端的SQL语句的集合。要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现。存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。,调用存储过程（2）,MySQL中使用CALL语句来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的语句。然后，将结果返回给输出值。CALL语句的基本语法形式如下：CALL sp_name(parameter,);,调用存储函数（2）,MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。,查看存储过程和函数,存储过程和函数创建以后，用户可以查看存储过程和函数的状态和定义。用户可以通过SHOW STATUS语句来查看存储过程和函数的状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息。,SHOW STATUS语句查看存储过程和函数的状态,MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法形式如下：SHOW PROCEDURE|FUNCTION STATUS LIKE pattern;其中，“PROCEDURE”参数表示查询存储过程；“FUNCTION”参数表示查询存储函数；“LIKE pattern”参数用来匹配存储过程或函数的名称。,SHOW CREATE语句查看存储过程和函数的定义,MySQL中可以通过SHOW CREATE语句查看存储过程和函数的状态。其基本语法形式如下：SHOW CREATE PROCEDURE|FUNCTION sp_name;其中，“PROCEDURE”参数表示查询存储过程；“FUNCTION”参数表示查询存储函数；“sp_name”参数表示存储过程或函数的名称。,从information_schema.Routines表中查看存储过程和函数的信息,存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：SELECT*FROM information_schema.Routines WHERE ROUTINE_NAME=sp_name;其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；“sp_name”参数表示存储过程或函数的名称。,修改存储过程和函数,修改存储过程和函数是指修改已经定义好的存储过程和函数。MySQL中通过ALTER PROCEDURE语句来修改存储过程。通过ALTER FUNCTION语句来修改存储函数。,MySQL中修改存储过程和函数的语句的语法形式如下：ALTER PROCEDURE|FUNCTION sp_name characteristic.characteristic:CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA|SQL SECURITY DEFINER|INVOKER|COMMENT string,删除存储过程和函数,删除存储过程和函数指删除数据库中已经存在的存储过程和函数。MySQL中使用DROP PROCEDURE语句来删除存储过程。通过DROP FUNCTION语句来删除存储函数。其基本形式如下：DROP PROCEDURE|FUNCTION sp_name;,总结,Q&A！,