注册    登录
关注
Py学习  »  DATABASE

MySQL入门系列：存储程序（二）之存储函数简介

小孩子4919 • 5 年前 • 315 次点击  
阅读 61

MySQL入门系列：存储程序（二）之存储函数简介

存储例程

存储例程是存储程序的一种类型，本质上也是封装了一些可执行的语句，只不过它的调用方式是：需要手动去调用！存储例程又可以分为存储函数和存储过程，下边我们详细唠叨这两个家伙。

存储函数

创建存储函数

存储函数其实就是一种函数，只不过在这个函数里可以执行命令语句而已。函数的概念大家都应该不陌生，它可以把处理某个问题的过程封装起来，之后我们直接调用函数就可以去解决同样的问题了，简单方便又环保。MySQL中定义存储函数的语句如下：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END
复制代码

从这里我们可以看出，定义一个存储函数需要指定函数名称、参数列表、返回值类型以及函数体内容，如果该函数不需要参数，那参数列表可以被省略，函数体内容可以包括一条或多条语句，每条语句都要以分号;结尾。里边的制表符和换行仅仅是为了好看，如果你觉得烦，完全可以用空格代替！ 光看定义理解的不深刻，我们先写一个存储函数开开眼：

mysql> delimiter $
mysql> CREATE FUNCTION avg_score(s VARCHAR(100))
    -> RETURNS DOUBLE
    -> BEGIN
    ->     RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
复制代码

我们定义了一个名叫avg_score的函数，它接收一个VARCHAR(100)类型的参数，声明的返回值类型是DOUBLE，需要注意的是，我们在RETURN语句后边写了一个SELECT语句，表明这个函数的最后返回结果就是根据这个查询语句产生的，也就是返回了指定科目的平均成绩。

存储函数的调用

我们自定义的函数和系统内置函数的使用方式是一样的，都是在函数名后加小括号()表示函数调用，有参数的函数调用可以把参数写到小括号里边。函数调用可以作为查询对象或者搜索条件，或者和别的操作数一起组成更复杂的表达式，我们现在来调用一下刚刚写好的这个函数吧：

mysql> SELECT avg_score('母猪的产后护理');
+------------------------------------+
| avg_score('母猪的产后护理')        |
+------------------------------------+
|                                 73 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT avg_score('论萨达姆的战争准备');
+------------------------------------------+
| avg_score('论萨达姆的战争准备')          |
+------------------------------------------+
|                                    73.25 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>
复制代码

这样调用函数就比我们直接写两个又臭又长的查询语句简单多了。

查看和删除存储函数

如果我们想查看我们已经定义了多少个存储函数，可以使用下边这个语句：

SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]
复制代码

由于这个命令得到的结果太多，我们就不演示了哈，自己试试。

如果我们想查看某个函数的具体定义，可以使用这个语句：

SHOW CREATE FUNCTION 函数名
复制代码

比如这样：

mysql> SHOW CREATE FUNCTION avg_score\G
*************************** 1. row ***************************
            Function: avg_score
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `avg_score`(s VARCHAR(100)) RETURNS double
BEGIN
        RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

mysql>
复制代码

忽略结果中我们看不懂的那些东东，后边都会详细唠叨的，现在主要聚焦在这个函数的定义处。

如果想删除某个存储函数，使用这个语句：

DROP FUNCTION 函数名
复制代码

比如我们来删掉avg_score这个函数：

mysql> DROP FUNCTION avg_score;
Query OK, 0 rows affected (0.00 sec)

mysql>
复制代码

什么？你以为到这里存储函数就唠叨完了么？写完是不可能的，这辈子都不可能写完的！到现在为止我们只是勾勒出一个存储函数的大致轮廓，下边我们来详细说一下MySQL定义函数体时支持的一些语句。

在函数体中定义变量

我们在前边说过在命令行（黑框框）中自定义变量的方式，它可以不用声明就为变量赋值（也就是调用SET语句）。而在函数体中使用变量前必须先声明这个变量，声明方式如下：

DECLARE 变量名 数据类型 [DEFAULT 默认值];   
复制代码

需要特别留心的是，函数体中的变量名不允许加@前缀，这一点和黑框框中定义变量的方式是截然不同的，特别注意一下。在声明了这个变量之后，才可以使用它：

mysql> delimiter $;
mysql> CREATE FUNCTION var_demo()
-> RETURNS INT
-> BEGIN
->     DECLARE c INT;
->     SET c = 5;
->     RETURN c;
-> END $
Query OK, 0 rows affected (0.00 sec)
    
mysql> delimiter ;
复制代码

我们定义了一个名叫var_demo而且不需要参数的函数，我们在函数体中声明了一个名称为c的INT类型变量，之后我们调用SET语句为这个变量赋值了整数5，并且把变量c当作函数结果返回，我们调用一下这个函数：

mysql> select var_demo();
+------------+
| var_demo() |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql>
复制代码

如果我们不对声明的变量赋值的话，它的默认值就是NULL，当然我们也可以通过DEFAULT子句来显式的指定变量的默认值，比如这样：

mysql> delimiter $
mysql> CREATE FUNCTION var_default_demo()
-> RETURNS INT
-> BEGIN
->     DECLARE c INT DEFAULT 1;
->     RETURN c;
-> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
复制代码

在新创建的这个var_default_demo函数中，我们声明了一个变量c，并且指定了它的默认值为1，然后看一下函数的调用结果：

mysql> SELECT var_default_demo();
+--------------------+
| var_default_demo() |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql>
复制代码

得到的结果是1，说明了我们指定的变量默认值生效了！另外，特别需要注意一下我们可以将某个查询语句的结果赋值给变量的情况，比如我们改写一下前边的avg_score函数：

CREATE FUNCTION avg_score(s VARCHAR(100)) 
RETURNS DOUBLE
BEGIN
    DECLARE a DOUBLE;
    SET a = (SELECT AVG(score) FROM student_score WHERE subject = s);
    return a;
END
复制代码

我们先把一个查询语句的结果赋值给了变量a，然后再返回了这个变量。

参数的编写

在定义函数的时候，可以指定多个参数，每个参数都要指定对应的数据类型，就像这样：

参数名 数据类型
复制代码

比如我们上边编写的这个avg_score函数：

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END 
复制代码

这个函数只需要一个类型为VARCHAR(100)参数，我们这里给这个参数起的名称是s，需要注意的是，参数名不要和函数体语句中其他的变量名、命令语句的标识符冲突，比如如果把这个变量名命名为subject，它就与下边用到WHERE子句中的列名冲突了，导致列名失效。

另外，函数参数不可以指定默认值，我们在调用函数的时候，必须显式的指定所有的参数，并且参数类型也一定要匹配，比方说我们在调用函数avg_score时，必须指定我们要查询的课程名，不然会报错的：




    
mysql> select avg_score();
ERROR 1318 (42000): Incorrect number of arguments for FUNCTION xiaohaizi.avg_score; expected 1, got 0
mysql>
复制代码
判断语句的编写

像其他的编程语言一样，在MySQL的函数体里也可以使用判断的语句，语法格式如下：

IF 布尔表达式 THEN 
    处理语句
[ELSEIF 布尔表达式 THEN
    处理语句]
[ELSE 
    处理语句]    
END IF;
复制代码

需要注意的是，这里的处理语句可以是由多条语句构成的复合语句。我们举个例子：

mysql> delimiter $
mysql> CREATE FUNCTION condition_demo(i INT)
-> RETURNS VARCHAR(10)
-> BEGIN
->     DECLARE result VARCHAR(10);
->     IF i = 1 THEN
->         SET result = '结果是1';
->     ELSEIF i = 2 THEN
->         SET result = '结果是2';
->     ELSEIF i = 3 THEN
->         SET result = '结果是3';
->     ELSE
->         SET result = '非法参数';
->     END IF;
->     RETURN result;
-> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
复制代码

在我们定义的函数condition_demo中，它接收一个INT类型的参数，这个函数的处理逻辑如下：

  1. 如果这个参数的值是1，就把result变量的值设置为'结果是1'。
  2. 否则如果这个这个参数的值是2，就把result变量的值设置为'结果是2'。
  3. 否则如果这个这个参数的值是3，就把result变量的值设置为'结果是3'。
  4. 否则就把result变量的值设置为'非法参数'。

当然了，我们举的这个例子还是比较白痴的啦，当然了，我们只是为了说明语法怎么用，等于到更复杂一点的业务逻辑再往复杂了说哈。我们现在调用一下这个函数：

mysql> SELECT condition_demo(2);
+-------------------+
| condition_demo(2) |
+-------------------+
| 结果是2           |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT condition_demo(5);
+-------------------+
| condition_demo(5) |
+-------------------+
| 非法参数          |
+-------------------+
1 row in set (0.00 sec)

mysql>
复制代码
循环语句的编写

除了判断语句，MySQL还支持循环语句的编写，不过有3种形式的循环语句，我们一一道来：

  • WHILE循环语句：

    WHILE 布尔表达式 DO
    循环语句
END WHILE;
复制代码

    这个语句的意思是：如果满足给定的表达式，则执行循环语句，否则退出循环。比如我们想定义一个从1到n这n个数的和（假设n大于0），可以这么写：

    mysql> delimiter $
mysql> CREATE FUNCTION sum_all(n INT UNSIGNED)
-> RETURNS INT
-> BEGIN
->     DECLARE result INT DEFAULT 0;
->     DECLARE i INT DEFAULT 1;
->     WHILE i <= n DO
->         SET result = result + i;
->         SET i = i + 1;
->     END WHILE;
->     RETURN result;
-> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
复制代码

    在函数sum_all中，我们接收一个INT UNSIGNED类型的参数，声明了两个INT类型的变量i和result。我们先测试一下这个函数：

    mysql> SELECT sum_all(3);
+------------+
| sum_all(3) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql>
复制代码

    分析一下这个结果是怎么产生的，初始的情况下result的值是0，i的值是1，给定的参数n的值是3。这个过程就是：

    1. 先判断i <= n是否成立，显然成立，进入循环体，将result的值设置为1（result + i），i的值为2（i + 1）。
    2. 再判断i <= n是否成立，显然成立，进入循环体，将result的值设置为3（result + i），i的值为3（i + 1）。
    3. 再判断i <= n是否成立，显然成立，进入循环体，将result的值设置为6（result + i），i的值为4（i + 1）。
    4. 再判断i <= n是否成立，显然不成立，退出循环。

    所以最后返回的result的值就是6，也就是1、2、3这三个数的和。

  • REPEAT循环语句

    REPEAT循环和WHILE循环差不多，只是形式上变了一下：

    REPEAT
    循环语句
UNTIL 布尔表达式 END REPEAT;
复制代码

    先执行循环语句，再判断布尔表达式是否成立，如果成立继续执行循环语句，否则退出循环。与WHILE循环不同的一点是：WHILE循环先判断布尔表达式的值，再执行循环语句，REPEAT循环先执行循环语句，再判断布尔表达式的值，所以至少执行一次循环语句，所以如果sum_all函数用REPEAT循环改写，可以写成这样：

    CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    REPEAT 
        SET result = result + i;
        SET i = i + 1;
    UNTIL i <= n END REPEAT;
    RETURN result;
END
复制代码

  • LOOP循环语句

    这只是另一种形式的循环语句：

    循环标记:LOOP
    循环语句
    LEAVE 循环标记;
END LOOP 循环标记;
复制代码

    在LOOP循环语句中，比较特别的是需要我们设置循环标记来标识一个循环，在循环体内依靠 LEAVE 循环标记的形式来中断某个循环，比方说我们可以把sum_all函数改写成这样：

    CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP  
        IF i > n THEN
            LEAVE flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END
复制代码

    其中的flag就是一个循环标记，在循环体内判断i > n成立的时候就调用LEAVE flag来跳出这个循环。

注释的使用

不论什么时候，对语句添加注释都是一件好事儿！注释不仅仅是帮助别人理解我们写的语句是什么意思，对于我们自己来说，可能隔了几天之后再看自己写的语句就不知道是什么意思了。在函数体内以--开头的语句都算作注释语句，MySQL服务器在执行语句的时候会忽略掉这些注释语句。

-- 函数名：sum_all
-- 参数：n = 从1累加到的数字

CREATE FUNCTION sum_all(n INT UNSIGNED) COMMENT '求1到n这n个数的和'
RETURNS INT
BEGIN
    -- 当前累加的和
    DECLARE result INT DEFAULT 0;
    
    -- 当前累加的数字
    DECLARE i INT DEFAULT 1;
    
    -- 若当前累加的数字不大于指定数字，则继续执行循环
    WHILE i <= n DO
        SET result = result + i;
        SET i = i + 1;
    END WHILE;
    
    -- 返回累加的和
    RETURN result;
END
复制代码

除了--开头的语句表示注释，我们还可以在函数参数后写COMMENT注释语句说明这个函数的作用。

小册

本系列专栏都是MySQL入门知识，想看进阶知识可以到小册中查看：MySQL是怎样运行的链接 。小册的内容主要是从小白的角度出发，用比较通俗的语言讲解关于MySQL进阶的一些核心概念，比如记录、索引、页面、表空间、查询优化、事务和锁等，总共的字数大约是三四十万字，配有上百幅原创插图。主要是想降低普通程序员学习MySQL进阶的难度，让学习曲线更平滑一点～

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/30168
Reply
 
315 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号