mysql--存储过程和函数

一、什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

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

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

1、创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])    --存储过程的参数列表
    [characteristic ...]    -- 存储过程的特征
    routine_body    -- 存储过程体

2、创建函数

CREATE FUNCTION sp_name ([func_parameter[,...]])    -- 函数的参数列表
    RETURNS type    -- 函数的返回值类型
    [characteristics ...]    -- 函数的特征
    routine_body    -- 函数体

格式：

proc_parameter:
    [IN | OUT | INOUT] param_name type -- 存储过程参数的格式
eg: IN id INT -- 参数为IN类型，参数名为id，参数的数据类型为INT

func_parameter:
    param_name type -- 函数参数格式

characteristic：
        LANGUAGE SQL    -- 说明下面过程的BODY是用SQL语言编写，这条是系统默认的，为今后MYSQL会支持的除SQL外的其他语言支持的存储过程而准备
    |   [NOT] DETERMINISTIC    -- DETERMINISTIC确定的，即当过程输入相同时，输出也相同，默认为NOT DETERMINISTIC
    |   { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }    -- CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READ SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给出，默认使用的值是CONTAINS SQL.
    |   SQL SECURITY { DEFINER | INVOKER }    -- 可以用来指定子程序用该子程序创建者的许可来执行，还是用调用者的许可来执行。默认值是DEFINER
    |   COMMENT 'string'    -- 存储过程或者函数的注释信息
    -- 特征格式

3、调用函数或存储过程

CALL sp_name ([parameter[,...]])

MYSQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交（COMMIT）或者回滚（ROLLBACK），但是存储过程和函数中不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的存储过程或函数。

4、删除存储过程或函数

DROP { PROCEDURE | FUNCTION } [IF EXISTS] sp_name
-- 一次只能删除一个存储过程或函数，删除存储过程或者函数需要有该过程或函数的ALTER ROUTINE权限

三、变量的使用

存储过程中可以使用变量，而且在MYSQL5.1版本中，变量是不区分大小写的。

1、变量的定义：通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN...END块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个变量。如果需要，可以使用DEFAULT赋默认值。

语法：

DECLARE var_name[,...] type [DEFAULT value]

eg:DECLARE last_month_start DATE    -- 定义一个DATE类型的变量，名称是last_month_start

2、变量的赋值

变量可以直接赋值，或者通过查询赋值。

直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [, var_name = expr] ...

也可以通过查询将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下：

SELECT col_name [,...] INTO var_name [,...] table_expr