MySQL存储过程和函数

一、基本介绍

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，减少数据在数据库和应用服务之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于 函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，可能需要将函数改造成存储过程。

存储过程和函数允许包含DDL语句，也允许使用事务，还可以调用其他的存储过程和函数，但不允许执行 Load Data Infile 语句；

二、相关操作

创建存储过程或函数需要 CREATE ROUTINE 权限，修改或删除存储过程或函数需要 ALTER ROUTINE 权限，执行存储过程或函数需要 EXECUTE 权限。

创建、修改存储过程或函数

Create procedure sp_name（[proc_parameter[,…]）

[characteristic…] routine_body

Create function sp_name（[func_parameter[,…]）

Returns type

[characteristic…] routine_body

Return xxx

调用语法：call sp_name([parameter[,…])

参数说明：

pro_parameter

　　　　[ IN | OUT | INOUT ] param_name type

func_parameter

　　　　param_name type

存储过程和函数中不允许执行 LOAD DATA INFILE 语句。

Delimiter $$ 修改命令结束符

Characteristic特征值：

　　　　Language sql 说明下面body是使用sql编写，系统默认

　　　　Sql security{ definer | invoker } 可以指定子程序该用创建子程序者的许可来执行还是使用调用者的权限执行。默认是definer

　　　　Comment ‘string’ 存储过程或函数的注释信息

　　　　{ Contains sql | no sql | reads sql data | modifies sql data} 供子程序使用数据的内在信息，目前只提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况，默认是contains sql；

1. 　　Contains sql 表示子程序不包含读或写数据的语句。
2. 　　No sql 表示子程序不包含sql语句。
3. 　　Reads sql data 表示子程序包含读数据的语句，但不包含写数据的语句。
4. 　　Modifies sql data 表示子程序包含写数据的语句。

　　实例：

　　返回值用 @xxx

删除存储过程或函数：

　　一次只能删除一个存储过程或函数，需 ALTER ROUTINE 权限

　　Drop procedure name；

查看存储过程或者函数：

查看存储过程或函数的状态：

　　Show { procedure | function } status [like ‘pattern’];

查看存储过程或函数的定义：

　　Show create { procedure | function } name；

通过查看information_schema.Routines了解存储过程和函数的信息

　　Select * from Routines where routine_name = “name”;

三、变量的使用

变量不区分大小写

变量的定义

　　Declare 定义一个局部变量，作用域在 BEGIN … END 块中，可以用在嵌套的块中。必须写在复合语句的开头，并且在任何其他语句的前面。可一次声明多个相同类型的变量。如需要，可以使用default赋默认值。

　　Declare var_name[,…] type [default value];

变量的赋值

　　变量可以直接赋值，或者通过查询赋值。直接赋值使用set，可以赋常量或者赋表达式。

　　Set var_name = expr [,var_name = expr] …

　　Select col_name [,…] INTO var_name [,…] from xxx….; #查询结果必须只有一行

　　Set @a = xxx; 相当于全局变量

定义条件和处理：

处理过程中遇到问题时相应的处理步骤。

条件定义

　　Declare condition_name CONDITION FOR condition_value

条件处理（游标中有实例）

　　Declare handler_type HANDLER FOR condition_value [,…] sp_statement

　　说明：

　　Handler_type 目前支持 continue 和 exit ，continue继续执行下面的语句，exit表示终止。

　　Condition_value 值可以通过declare定义的 condition_name，可以是SQLSTATE 的值或者mysql-error-code的值或SQLWARING、NOT FOUND、SQLEXECEPTION，这3个值是3种定义好的错误类别。

SQLWARING 是对所有以01开头的SQLSTATE代码速记
NOT FOUND 是对所有以02开头的SQLSTATE 代码速记
SQLEXCEPTION 是对所有没有被SQLWARING 或 NOT FOUND 捕获的SQLSTATE 代码速记

四、游标的使用

对结果集进行循环的处理，包括光标的声明、open、fetch 和 close。

1条sql，对应N条结果集的资源，取出资源接口/句柄，就是游标，沿着游标，可以一次取出1行。好处是，每一行的处理权利在我们手中。

游标通俗来讲相当于你买东西别人一件件的给你，而不是一下子全给你。

执行没有数据错误：

修改存储过程：

BEGIN

DECLARE row_id int;
DECLARE row_dt varchar(50);
DECLARE row_catalog int;
DECLARE row_total int;
DECLARE i int default 1;
DECLARE getArticle CURSOR FOR select id,dt,catalog from tblarticle where catalog = 75;
select count(*) INTO row_total from tblarticle where catalog=75;

OPEN getArticle;

WHILE i<=row_total DO
    FETCH getArticle into row_id, row_dt, row_catalog;

    SELECT row_id, row_dt, row_catalog;
    set i = i+1;
END WHILE;
CLOSE getArticle;

END

BEGIN

DECLARE row_id int;
DECLARE row_dt varchar(50);
DECLARE row_catalog int;
DECLARE row_total int;
DECLARE i int DEFAULT 1;
DECLARE getArticle CURSOR FOR select id,dt,catalog from tblarticle where catalog = 75;
DECLARE EXIT HANDLER FOR NOT FOUND set i = 0;

OPEN getArticle;

REPEAT
    FETCH getArticle into row_id, row_dt, row_catalog;
    SELECT row_id, row_dt, row_catalog;
UNTIL i = 0 END REPEAT;

CLOSE getArticle;

END

Declare continue/exit handler for not found close 游标名;