MySQL基础笔记（六）存储过程与函数

MySQL基础笔记（六）存储过程与函数
写在开头：本文所有的示例都是基于workers表，表中保存了某公司的员工姓名、性别、工资、年龄和居住城市，如下：
```
+----+-----------+--------+--------+------+----------------+
| id | name      | sex    | salary | age  | city           |
+----+-----------+--------+--------+------+----------------+
|  2 | Paul      | male   |   5170 |   23 | Boston         |
|  4 | Robin     | male   |   8350 |   40 | Beijing        |
|  5 | Nina      | female |   6700 |   27 | Chicago        |
|  6 | Jacky     | male   |   9000 |   35 | Beijing        |
|  7 | Tim       | male   |   5600 |   29 | Chicago        |
|  8 | Katherine | female |   7000 |   32 | Washington D.C |
+----+-----------+--------+--------+------+----------------+
```
一、定义

存储程序可以分为存储过程和函数。

1.1 存储过程的定义

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。存储过程在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

1.2 函数的定义

存储函数（简称函数）在本质上与存储过程没有区别。

只是函数有如：只能返回一个变量的限制，而存储过程可以返回多个。函数是可以嵌入在SQL中使用，可以在select中调用，而存储过程不行。

二、创建存储过程和函数

存储过程和函数的创建过程很相似。

2.1 创建存储过程

创建存储过程使用CREATE PROCEDURE语句：
```
CREATE PROCEDURE p_name(参数列表)
    [characteristics...]
    BEGIN
        routine_body;
    END;
```
begin和end语句用来限定存储过程体。参数列表可以为空，若不为空，则每个参数的形式如下：
```
[IN|OUT|INOUT] 参数名 参数类型
```
其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出。

characteristics 是可选的参数，用于指定存储过程的特性：
- language sql：说明存储过程由SQL语句组成，目前SQL是language特性的唯一值。
- [not]deterministic：表示结果是确定的，相同的输入会得到相同的输出；加not表示结果不确定，相同的输入可能得到不同的输出。默认为 not deterministic。
- contains sql|no sql|reads sql data|modifies sql data:
  
  contains sql：存储过程包含SQL语句，但不包含读写数据的语句。
  
  no sql：存储过程不包含SQL语句。
  
  reads sql data：存储过程包含读数据的语句。
  
  modifies sql data：存储过程包含读写数据的语句。
- sql security {definer|invoker}：指明谁有权限执行存储过程。definer表示只有定义者才能执行，invoker表示拥有权限的调用者可以执行。默认为definer。
- comment'string'：注释信息，可以用来描述存储过程或函数。
2.2 创建函数

创建函数使用CREATE FUNCTION语句：
```
CREATE FUNCTION f_name(参数列表)
    RETURNS type
    [characteristics...]
    routine_body
```
参数列表可以为空，若不为空，声明形式与存储过程的声明形式一样。characteristics用于指定函数的特性，取值同上，这里不再赘述。

RETURNS type表示函数的返回类型；routine_body是函数体，函数体中必须包含一个 RETURN value 语句。

三、调用存储过程和函数

下面通过两个实例来说明存储过程、函数的创建和调用过程。

3.1 存储过程示例

创建一个存储过程，用来计算workers表中每个员工的平均工资：
```
CREATE PROCEDURE getAverageSalary()
    BEGIN
        SELECT AVG(salary) FROM workers;
    END;
```
实际的执行过程如下：
```
mysql> DELIMITER //
mysql> CREATE PROCEDURE getAverageSalary()
    -> BEGIN
    -> SELECT AVG(salary) FROM workers;
    -> END//
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
```
DELIMITER //语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符为;，为了避免与存储过程中SQL语句结束符相冲突，需要使用 delimiter 改变存储过程的结束符，并以END//结束存储过程。存储过程定义完毕之后再用DELIMITER ;恢复默认结束符。当然也可以指定其他符号做为结束符。

下面我们调用一下 getAverageSalary() 存储过程，使用CALL命令：
```
mysql> CALL getAverageSalary();
+-------------+
| AVG(salary) |
+-------------+
|   6970.0000 |
+-------------+
1 row in set (0.20 sec)
```
3.2 函数示例

同样的，我们创建一个函数来计算平均工资：
```
CREATE FUNCTION getAverageSalary()
    RETURNS DECIMAL(8,4)
    RETURN(SELECT AVG(salary) FROM workers);
```
实际的执行过程如下：
```
mysql> DELIMITER //
mysql> CREATE FUNCTION getAverageSalary()
    -> RETURNS DECIMAL(8,4)
    -> RETURN(
    -> SELECT AVG(salary) FROM workers
    -> )//
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
```
在MySQL中，自定义的函数与MySQL内部函数的使用方法是一样的，可以嵌入SQL中。我们用SELECT调用：
```
mysql> SELECT getAverageSalary();
+--------------------+
| getAverageSalary() |
+--------------------+
|          6970.0000 |
+--------------------+
```
四、复杂的存储过程和函数

4.1 变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN...END中。

4.1.1 定义变量

在存储过程中使用declare语句定义变量：
```
DECLARE 变量名 变量类型 [DEFAULT val];
```
如果没有 DEFAULT 子句，初始值为NULL 。

4.1.2 为变量赋值

MySQL中使用SET语句为变量赋值：
```
SET 变量名=表达式；
```
例如：
```
DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;
```
另外还有一种给变量赋值的方法：
```
SELECT 字段1,字段2... INTO 变量1,变量2... FROM table_name WHERE condition
```
这个SELECT语法把选定的列直接存储到对应位置的变量中。

4.1.3 用户级变量

前面用DECLARE定义的变量是局部变量，只能在BEGIN...END之间起作用。而用户变量对于该连接的用户来说是全局的。

用户变量通常用@var_name表示。用户变量与连接有关，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

创建用户变量不需要事先声明，直接SET赋值即可：
```
SET @var_name = expr [, @var_name = expr] ...
```
- 在SET语句中，可以使用=或:=进行赋值。
- 在非SET语句中，只能使用:=进行赋值，因为=被视为一个比较操作符。
4.2 流程控制的使用

MySQL中的流程控制语句有：IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句和REPEAT语句。

4.2.1 IF语句

语法格式如下：
```
IF expr_condition THEN statement_list
    [ELSEIF expr_condition THEN statement_list]...
    [ELSE statement_list]
END IF;
```
4.2.2 CASE语句

CASE语句有两种语法格式，第一种如下：
```
CASE expr
    WHEN value1 THEN statement_list
    WHEN value2 THEN statement_list
    ...
    [ELSE statement_list]
END CASE;
```
第二种如下：
```
CASE
    WHEN expr_condition1 THEN statement_list
    WHEN expr_condition2 THEN statement_list
    ...
    [ELSE statement_list]
END CASE;
```
注意：在存储程序里的 CASE 语句与直接在SELECT查询里使用的 CASE 函数有略微的不同。在存储程序里的 CASE 语句不能有ELSE NULL子句，并且用END CASE而不是END来终止。

4.2.3 LOOP语句

LOOP语句的语法如下：
```
[loop_label:]LOOP
    statement_list
END LOOP [loop_label]
```
loop_label是LOOP语句的标签，该参数可以省略。 LOOP 内的语句一直重复执行直到退出循环，退出循环使用LEAVE语句。

4.2.4 LEAVE语句

LEAVE语句用来退出任何被标注的流程控制语句，语法如下：
```
LEAVE label;
```
4.2.5 ITERATE语句

ITERATE语句将执行顺序转到语句段开头处，ITERATE只可以出现在 LOOP、REPEAT和WHILE语句内。语法如下：
```
ITERATE label;
```
通俗点讲，就是相当于C++里的continue。

4.2.6 REPEAT语句

REPEAT语句创建一个带条件判断的循环过程：
```
[repeat_label:]REPEAT
    statement_list
UNTIL expr_condition
END REPEAT [repeat_label]
```
4.2.7 WHILE语句

WHILE语句也是创建一个带条件判断的循环过程，不同的是在每次执行循环体时先判断：
```
[while_label:]WHILE expr_condition DO
    statement_list
END WHILE [while_label]
```
4.3 定义条件和处理程序

特定条件需要特定处理，定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，这样可以保证存储过程或函数在遇到警告或错误时能继续执行。

4.3.1 定义条件

定义条件也是使用DECLARE语句：
```
DECLARE condition_name CONDITION FOR SQLSTATE 'sqlstate_value' | mysql_error_code;
```
sqlstate_value 和 mysql_error_code 都可以表示MySQL的错误，例如：ERROR 1064(42000)中，sqlstate_value的值是42000，mysql_error_code的值是1064。

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来，这个名字可以用在后面的处理程序中。例如：定义'ERROR 1064(42000)'错误名称为syntax_error。
```
DECLARE syntax_error CONDITION FOR SQLSTATE '42000';  /*方法一*/
DECLARE syntax_error CONDITION FOR 1064;              /*方法二*/
```
4.3.2 定义处理程序

定义处理程序语法如下：
```
DECLARE handler_type HANDLER FOR condition_value sp_statement;
```
- handler_type：表示错误处理方式，只能取以下3个值。
  
  CONTINUE：遇到错误不处理，继续执行；
  
  EXIT：遇到错误马上退出；
  
  UNDO：遇到错误后撤回之前的操作，MySQL暂不支持。
- condition_value：表示错误类型，可以有以下值：
  
  SQLSTATE 'sqlstate_value'
  
  mysql_error_code
  
  condition_name：自定义的条件名称
  
  SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
  
  NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
  
  SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
- sp_statement：程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。
4.4 光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要使用光标（cursor）来逐条读取查询结果集中的记录。

4.4.1 声明光标

光标必须在打开之前被声明，并且其中用到的变量或条件必须在声明光标之前被声明。MySQL中使用DECLARE关键字来声明光标：
```
DECLARE cursor_name CURSOR FOR select_statement;
```
其中的 SELECT 语句返回一个用于创建光标的结果集。

4.4.2 打开光标

打开光标的语法如下：
```
OPEN cursor_name;
```
4.4.3 使用光标

通过FETCH关键字从光标中逐条读取到变量中：
```
FETCH cursor_name INTO var_name[,...];
```
变量var_name必须在声明光标之前就定义好。

4.4.4 关闭光标

关闭光标的语法如下：
```
CLOSE cursor_name;
```
**注意：**MySQL中光标只能在存储过程和函数中使用。

4.4.5 示例

在workers表的基础上，创建一个存储过程，根据输入的城市名，输出该城市所有员工的名字。
```
mysql> DELIMITER //
mysql> CREATE PROCEDURE useCursorDemo(IN city_name VARCHAR(15))
    -> BEGIN
    -> DECLARE m_name VARCHAR(10);
    -> DECLARE m_city VARCHAR(15);
    -> DECLARE m_stop INT DEFAULT 0;
    -> DECLARE mycursor CURSOR FOR SELECT name,city FROM workers WHERE city=city_name;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET m_stop=1;
    -> OPEN mycursor;
    -> FETCH mycursor INTO m_name,m_city;
    -> WHILE m_stop!=1 DO
    -> SELECT m_name,m_city;
    -> FETCH mycursor INTO m_name,m_city;
    -> END WHILE;
    -> CLOSE mycursor;
    -> END//
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
```
调用useCursorDemo存储过程：
```
mysql> CALL useCursorDemo('Chicago');
+--------+---------+
| m_name | m_city  |
+--------+---------+
| Nina   | Chicago |
+--------+---------+
1 row in set (0.05 sec)

+--------+---------+
| m_name | m_city  |
+--------+---------+
| Tim    | Chicago |
+--------+---------+
1 row in set (0.05 sec)
```
通过这个例子，我们可以了解到如何在存储过程或函数中使用变量、光标和流程控制。

五、修改、删除存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性，语法如下：
```
ALTER {PROCEDURE|FUNCTION} sp_name [characteristic]
```
sp_name是存储过程或函数的名称，characteristic指定存储过程或函数的特性，与创建过程的参数取值是一样的。

使用DROP语句删除存储过程或函数，语法如下：
```
DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;
```
附：MySQL存储过程和函数有什么区别？

在本质上它们都是存储程序。
1. 函数只能通过 return 语句返回单个值或表对象；而存储过程不允许执行 return，但可以通过 OUT 参数返回多个值。
2. 函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。
3. 函数可以嵌入在SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。
个人站点：http://songlee24.github.com
相关阅读:
JQuery 绑定单击事件到某个函数的的方法
 一刻校园足球管理平台亮相安徽打造智慧足球解决方案
 jQuery 获取不到 kindeditor 内容的解决方法
 【视频】 ASP.NET MVC5&微信公众平台整合开发实战
 Set authorization for a whole area
生成Area URL链接
 在ASP.NET MVC5应用程序中快速接入QQ和新浪微博OAuth
IOS 监听通讯录是否改变
 ios 读取通讯录数据
 iOS中assign、copy 、retain等关键字的含义
原文地址：https://www.cnblogs.com/songlee/p/5738030.html

MySQL基础笔记（六） 存储过程与函数

一、定义

1.1 存储过程的定义

1.2 函数的定义

二、创建存储过程和函数

2.1 创建存储过程

2.2 创建函数

三、调用存储过程和函数

3.1 存储过程示例

3.2 函数示例

四、复杂的存储过程和函数

4.1 变量的使用

4.1.1 定义变量

4.1.2 为变量赋值

4.1.3 用户级变量

4.2 流程控制的使用

4.2.1 IF语句

4.2.2 CASE语句

4.2.3 LOOP语句

4.2.4 LEAVE语句

4.2.5 ITERATE语句

4.2.6 REPEAT语句

4.2.7 WHILE语句

4.3 定义条件和处理程序

4.3.1 定义条件

4.3.2 定义处理程序

4.4 光标的使用

4.4.1 声明光标

4.4.2 打开光标

4.4.3 使用光标

4.4.4 关闭光标

4.4.5 示例

五、修改、删除存储过程和函数

附：MySQL存储过程和函数有什么区别？

MySQL基础笔记（六）存储过程与函数