存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合
存储过程和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用in,out,inout类型,而函数的参数只有in类型的
创建一个存储过程
DELIMITER // (为了和sql语句中 ; 区别 所以用//作为结尾)
CREATE PROCEDURE PRO()
BEGIN
SELECT *FROM TB;
END //
DELIMITER ;
调用存储过程 :CALL PRO();
删除存储过程:drop procedure pro;
创建存储函数
DELIMITER //
CREATE FUNCTION QUERY(CLASS ID INT,STUDENT ID INT)
RETURNS INT
RETURN (SELECT GRADE FROM TB WHERE CID=CLASSID AND SID=STUDENTID);
//
DELIMITER ;
调用存储函数
SELECT QUERY(1,1);
删除存储函数:drop function query;
读者可能会发现存储过程的查询结果可能为多个值,而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用CALL而存储函数是SELECT。那么存储过程和函数具体的区别又是什么呢?
- 存储过程的功能更加复杂,而函数的功能针对性更强;
- 存储过程可以返回参数(通过OUT|INOUT),而函数只能返回单一值或者表对象;
- 存储过程作为一个独立的部分来执行,而函数可以作为查询语句的一部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字之后;
- 存储过程是通过关键字CALL来调用,作为一个独立的执行部分。而存储函数则可作为SELECT语句的一部分调用,嵌入到SQL语句中;
变量
变量可以在子程序中声明并使用,作用范围是在BEGIN...END程序中,如下将主要介绍如何定义变量和为变量赋值。
- 定义变量。语法格式为:DECLARE var_name[,var_name]...data_type[DEFAULT value]; var_name为局部变量名称,DEFAULT value给变量提供一个默认值。值除了可以被声明为一个常数之外,还可以被指定为一个表达式。如果缺少DEFAULT子句,初始值为NULL。
- 为变量赋值。MySQL中使用SET语句为变量赋值,语法格式为:SET var_name=expr[,var_name=expr]...
定义条件和处理
在MySQL中,特定异常需要特定处理。这些异常可以联系到错误,以及子程序中的一般流程控制。定义异常
是事先定义程序执行过程中遇到的问题,异常处理定义了在遇到问题时对应当采取的处理方式,并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。这样可以增强程序处理问题的能力,避免程序异常停止。
declare condition_name condition for condition_value;
其中,condition_name参数表示条件的名称;condition_value参数表示条件的类型;sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。
条件定义举例
下面定义”ERROR 1146 (42S02)”这个错误,名称为can_not_find。可以用两种不同的方法来定义,代码如下:
#方法一:使用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_name:|sqlwarning|not found|sqlexception| mysql_error_code
其中,handler_type参数指明错误的处理方式,该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理,继续向下执行;EXIT表示遇到错误后马上退出;UNDO表示遇到错误后撤回之前的操作,MySQL中暂时还不支持这种处理方式。
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值。
sp_statement表示一些存储过程或函数的执行语句。
定义条件处理程序举例
#方法一:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @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';
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02,执行CONTINUE操作,并且输出”CAN NOT FIND”信息。
第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146,执行CONTINUE操作,并且输出”CAN NOT FIND”信息。
第三种方法是先定义条件,然后再调用条件。这里先定义can_not_find条件,遇到1146错误就执行CONTINUE操作。
第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输出”ERROR”信息。
第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出”CAN NOT FIND”信息。
第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出”ERROR”信息。
光标的使用
流程控制