MySQL数据库DDL操作之存储过程和函数

上篇文章介绍了MySQL数据库DDL操作中的触发器，本章将详细介绍MySQL数据库DDL操作中的存储过程和函数，存储过程和函数在某些复杂业务场景下还是有很大作用的。

1、定义和作用：

存储过程和函数是数据库中预先编译好的一个为了完成特定功能的SQL语句集。通过存储过程和函数，可以完成一些具有负责处理逻辑的数据库操作，同时可以减少应用程序和数据库服务器之间的数据传输，提升数据库的数据处理效率。

2、使用存储过程和函数的前提：

（1）创建存储过程和函数时，需要用户具备"CREATE ROUTINE"的权限；
（2）在删除和修改存储过程和函数时，需要用户具备"ALTER ROUTINE"的权限；
（3）在执行存储过程和函数时，需要用户具备"EXECUTE"的权限；

3、储存过程的创建和修改：

（1）存储过程创建的语法：

CREATE PROCEDURE p_name ([procedure_parameter[,...]])
BEGIN
    [characteristic ...]
    ...procedure_statement...  #存储过程的逻辑处理语句
END

参数解释：

procedure_name：存储过程的名称
procedure_parameter：存储过程的参数，可以包含多个参数，procedure_parameter中又包含了存储过程参数
类型、参数名称和参数的具体数据类型，它的定义格式为：[IN|OUT|INOUT] parameter_name field_type
characteristic:表示要修改存储过程的哪个部分，该参数的取值包括以下几种：
    a. CONTAINS SQL，表示子程序包含SQL语句，但是，不包含读或写数据的语句
    b. NO SQL，表示子程序中，不包含SQL语句
    c. READS SQL DATA，表示子程序中，包含读数据的语句
    d. MODIFIES DATA，表示子程序中，包含写数据的语句
    e. SQL SECURITY {DEFINER | INVOKER}，指明谁有权限来执行
        DEFINER，表示只有定义者，自己才能够执行
        INVOKER，表示调用者可以执行
    f. COMMENT "conte"，表示注释信息
    g. LANGUAGE SQL，表示存储过程使用SQL语句来实现[默认]，为后期引入其他语言实现做准备

存储过程参数类型[IN|OUT|INOUT]说明：

IN：表示该参数为输入参数，即：调用存储过程时所要接收的输入参数，为一个具体的值
OUT：表示该参数为输出参数，即：存储过程在被调用时，需要接收的一个外部变量，通常使用@加变量名定义，比如："@a"。在存储过程处理完结果之后，可以将结果放在该外部变量中，供外部查看；
INOUT：表示该参数即可作为输入参数，接收一个具体的值；同时也可以作为输出参数，通过传入外部变量，完成在存储过程外部查看变量的值；

（2）示例应用：

示例1：创建一个存储过程，查询表出指定表中的记录数，并可以在存储过程外部查看：

修改默认定界符为$，也可使用DELIMITER命令完成

mysql> \d $

创建存储过程：

mysql> CREATE PROCEDURE p_count(OUT param INT)
       BEGIN
            SELECT COUNT(*) FROM t_user INTO param FROM t_user;
       END $

将修改的定界符恢复至默认定界符

mysql> \d ;

使用CALL命令调用存储过程，传入@a变量，该变量在存储过程中被赋值：

mysql> CALL p_count(@a);

查看通过存储过程所查询到的表中记录数量：
注：查询自定义变量使用"@<变量名>"，如果查询系统中的变量，可以使用"@@<变量名>"，后期系统参数调优时介绍，此处可以先了解

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

示例2：创建一个存储过程，封装MySQL的LIMIT函数，实现分页：

创建存储过程：

mysql> \d $
mysql> CREATE PROCEDURE p_page(IN pageNo INT,IN pageSize INT)
       BEGIN
           SELECT * FROM t_user LIMIT pageNo,pageSize;
       END $
mysql> \d ;

调用存储过程，根据具体传入的值查询记录：

mysql> CALL p_page(1,10);

注意：
（1）如果使用的是普通用户登录MySQL来执行存储过程，则需要注意权限问题。如果用户对某个表没有查询权限，则该用户如果调用了某个包含对该表有查询操作的存储过程，会调用失败；
（2）在一个存储过程中可以调用其他的函数或者存储过程，如上示例2，可以调用系统函数LIMIT；
（3）在存储过程中可以完成事务的操作，比如："提交事务(COMMIT)和回滚事务(ROLLBACK)"，但是不能完成"LOAD DATA INFILE"操作；

（3）存储过程的修改：
目前，存储过程暂时不支持直接修改其逻辑语句，只支持修改一些存储过程的特征，也就是对characteristic进行修改。

语法：

ALTER PROCEDURE procedure_name [characteristic ...];

示例：对上述的p_page存储过程进行修改，指明调用者可以执行该存储过程：

mysql> ALTER PROCEDURE p_page SQL SECURITY INVOKER;

4、函数的创建和修改：

（1）函数创建的语法：

CREATE FUNCTION function_name([function_parameter[,...]])
RETURNS type
BEGIN
    [chracteristic ...]
    ...function statement... #函数中的实现逻辑
END

参数解释：

function_name：函数名称
function_parameter：函数的参数，它的定义格式为："param_name field_type"
[characteristic]：表示要修改的函数的哪个部分，包括的内容和存储过程相同
RETURNS type：表示返回值的声明

（2）示例应用：
示例1：创建一个函数，对于输入的内容，会在内容之前拼接"hello"字符串，在结束位置拼接"!"符号，实现如下：
创建函数：

mysql> CREATE FUNCTION function_hello(str CHAR(20))
        RETURNS CHAR(50) DETERMINISTIC
        RETURN CONCAT("Hello ",str,'!');

调用函数，传入字符串"Tomcat"，输出为："Hello ,Tomcat!"，如下：

mysql> SELECT function_hello("Tomcat");
+--------------------------+
| function_hello("Tomcat") |
+--------------------------+
| Hello Tomcat!            |
+--------------------------+
1 row in set (0.00 sec)

说明：
RETURNS CHAR(50)：表示返回值类型为CHAR(50)
RETURN CONCAT('Hello ',str,'!')：表示具体的返回值是使用CONCAT函数拼接得到的
DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。

示例2：实现一个加法功能的函数，输入两个INT类型的值，计算这两个数的和：
定义函数：

mysql> CREATE FUNCTION function_add1(num1 VARCHAR(20),num2 VARCHAR(20))
       RETURNS INT DETERMINISTIC
       RETURN (IF(num1 REGEXP "[0-9]{1,}",num1,0) + IF(num2 REGEXP "[0-9]{1,}",num2,0));
       

调用，传入10和20，计算出这两个数的和为30，如下：

mysql> SELECT function_add(10,20);
+---------------------+
| function_add(10,20) |
+---------------------+
|                  30 |
+---------------------+
1 row in set (0.00 sec)

注意：该函数中对入参做了简单判断，判断是否为数值，如果不为数值，默认当做0处理；

（3）函数的修改：
上面修改存储过程中介绍了使用ALTER完成的方法，此处直接通过修改系统中的mysql表中的数据来修改函数，对应的表为：mysql库中的proc

示例：将function_hello函数的定义者修改为"tomcat"@"localhost"。要修改的用户必须提前存在，修改方式如下：

选库：

mysql> USE mysql;

修改函数的定义者：

mysql> UPDATE proc SET definer = 'tomcat@localhost' WHERE name = 'function_hello';

刷新权限，如果不执行该操作，修改的结果不会生效：

mysql> FLUSH PRIVILEGES;

5、存储过程和函数的查看和删除：
（1）查看已经创建的存储过程或者函数：
语法：

SHOW <PROCEDURE|FUNCTION> STATUS LIKE "pattern";

参数解释：

pattern：表示存储过程或者函数名称的匹配串，支持模糊匹配

示例1：查看创建的p_page存储过程：

mysql> SHOW PROCEDURE STATUS LIKE "p_page" \G
*************************** 1. row ***************************
                  Db: test
                Name: p_page
                Type: PROCEDURE
             Definer: root@127.0.0.1
            Modified: 2018-05-16 11:56:56
             Created: 2018-05-16 11:21:45
       Security_type: INVOKER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

示例2：查看创建的函数"function_hello"：

mysql> SHOW FUNCTION STATUS LIKE '%hello' \G
*************************** 1. row ***************************
                  Db: test
                Name: function_hello
                Type: FUNCTION
             Definer: root@127.0.0.1
            Modified: 2018-05-16 12:09:17
             Created: 2018-05-16 12:09:17
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

（2）查看存储过程或者函数的定义：
语法：

SHOW CREATE <PROCEDURE|FUNCTION> <procedure_name|function_name>;

示例1：查看触发器p_page的创建过程：
选库：

mysql> USE test;

查看创建过程：

mysql> SHOW CREATE PROCEDURE p_page \G
*************************** 1. row ***************************
           Procedure: p_page
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `p_page`(IN pageNo INT,IN pageSize INT)
    SQL SECURITY INVOKER
begin
select * from t_user limit pageNo,pageSize ;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

示例2：查看函数function_hello的创建过程：
选库：

mysql> USE test;

查看创建过程：

mysql> SHOW CREATE FUNCTION function_hello \G
*************************** 1. row ***************************
            Function: function_hello
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `function_hello`(str CHAR(20)) RETURNS char(50) CHARSET utf8
    DETERMINISTIC
RETURN CONCAT("Hello ",str,'!')
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

注意：
查看存储过程和函数的定义还可以通过系统库information_schema中的routines表来查看，主要包括的字段有：ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_BODY,ROUTINE_COMMENT,DEFINER

mysql> SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_BODY,ROUTINE_COMMENT,DEFINER FROM information_schema.routines;

解释：

ROUTINE_SCHEMA:存储过程或者函数所在的库
ROUTINE_NAME:存储过程或者函数名称
ROUTINE_BODY:存储过程或者函数体
ROUTINE_COMMENT:注释信息
DEFINER:存储过程或者函数的定义者

6、删除存储过程和函数：
语法：

DROP <PROCEDURE|FUNCTION> <procedure_name|function_name>;

示例1：删除存储过程p_page：

mysql> USE test;
mysql> DROP PROCEDURE p_page;

示例2：删除函数function_hello：

mysql> USE test;
mysql> DROP FUNCTION function_hello;

7、变量的定义和赋值：
（1）变量的定义：
语法：

DECLARE var_name type <DEFAULT var_value>;

参数解释：

var_name:表示参数名称
type:表示参数类型
var_value:表示参数的默认初始值

示例1：定义一个INT类型的变量SUM，默认值为0：

DECLARE SUM INT DEFAULT 0;

示例2：定义一个VARCHAR(20)类型的变量STR，无初始值：

DECLARE STR VARCHAR(20);

（2）变量的赋值：
语法：
a.直接通过SET赋值：

SET var_name = var_value;

b.通过SELECT查询到结果之后再赋值：

SELECT <result> INTO var_name <FROM table_name>;

示例1：给VARCHAR(20)类型的变量STR赋一个初始值为""

mysql> SET STR = "";

示例2：查询表t_user中的记录数量，并将结果赋值给INT类型的STU_COUNT变量

mysql> SELECT COUNT(*) INTO STU_COUNT FROM t_user;

8、条件定义和处理：
语法：
a.条件的定义：

DECLARE condition_name CONDITION FOR condition_value;
condition_value:
    SQLSTATE sql_state_value

参数解释：

condition_name:表示条件的名称
condition_value:表示条件中所关注的执行结果，通常为SQL的执行状态

b.条件的处理：

DECLARE handler_name HANDLER FOR condition_value do_statement;

参数解释：

handler_name:表示处理器的名称，常用的有："CONTINUE","SQLWARNING","NOT FOUND","SQLEXCEPTION"
condition_value:表示执行的结果，当处理器匹配到该结果时，会执行后面的do_statement

示例：在一个存储过程中连续给t_user表中插入相同的数据，如果插入失败，则继续向下执行，而不退出

mysql> \d $
mysql> CREATE PROCEDURE p_insert()
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @state = 1;
            INSERT INTO t_user(id,name,age) VALUES(1,'bing',23);
            INSERT INTO t_user(id,name,age) VALUES(1,'bing',23);
            SET @val = 666;  
        END $
mysql> \d ;
mysql> SELECT @state,@val;
+--------+------+
| @state | @val |
+--------+------+
|      1 |  666 |
+--------+------+
1 row in set (0.00 sec)

结果分析：在上述的存储过程中，第二次插入t_user表中的记录由于主键冲突，故会插入失败。如果不定义条件处理的话。"SET @val=666"不会被执行，最后查出来的@val也不会为666，同样，@state也不会为1，现在查出来的结果分别为1和666，表示处理器起作用了。主键冲突的时候会提示"23000"状态码.

9、游标的使用：
（1）定义：
简单的理解，游标就是一个查询结果集的出口。在这个出口，可以完成对结果的筛选和其他操作。

（2）语法：
a.声明：

DECLARE cursor_name CURSOR FOR select_statement;

b.OPEN:打开游标

OPEN cursor_name;

c.FETCH：将游标的结果保存到某些中间变量中

FETCH cursor_name INTO var_name,...;

d.CLOSE：关闭游标

CLOSE cursor_name;

（3）示例：
使用游标统计出学生表t_student中男生和女生的总人数：
创建测试表结构：

CREATE TABLE t_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    gender VARCHAR(1) DEFAULT '0' COMMENT "0-男,1-女",
    name VARCHAR(50) DEFAULT ''
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

测试数据：

INSERT INTO t_student(gender,name) VALUES('1','name01');
INSERT INTO t_student(gender,name) VALUES('0','name03');
INSERT INTO t_student(gender,name) VALUES('0','name06');
INSERT INTO t_student(gender,name) VALUES('0','name08');

创建存储过程：

mysql> \d $
mysql> CREATE PROCEDURE student_count()
    BEGIN
        DECLARE str VARCHAR(1);
        DECLARE gender_str CURSOR FOR SELECT gender FROM t_student;
        DECLARE EXIT HANDLER FOR NOT FOUND CLOSE gender_str;
        SET @m_count = 0;
        SET @f_count = 0;
        
        OPEN gender_str;
        REPEAT
            FETCH gender_str INTO str;
            IF str = '1' THEN
                SET @m_count = @m_count + 1;
            ELSE
                SET @f_count = @f_count + 1;
            END IF;
        UNTIL 0 END REPEAT;
        CLOSE gender_str;
    END $

mysql> \d ;

调用存储过程：

mysql> CALL student_count();
Query OK, 0 rows affected (0.00 sec)

查看统计结果，M表示女生数量，F表示男生数量，可见，已经使用游标统计完毕：

mysql> SELECT @m_count AS 'M',@f_count AS "F" FROM DUAL;
+------+------+
| M    | F    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

10、存储过程和函数中的流程控制，主要介绍一下常用的IF，CASE，LOOP，REPEAT，WHILE流程控制：
（1）IF
语法：

IF search_conditoin THEN statement
    elseif search_condition THEN state
END IF

示例：已经在上述的游标中使用到了，可自行查看。

（2）CASE
语法：

CASE case_value
    WHEN when_value THEN statement
    WHEN when_value THEN statement
    ... #可有多个判断语句
    ELSE statement
END CASE

示例：将上述判断学生性别中使用的IF改为CASE如下：

CASE str
    WHEN '1' THEN
        SET @m_count = @m_count + 1;
    ELSE
        SET @f_count = @m_count + 1;
END CASE;

（3）LOOP
语法：

[loop_label:] LOOP
    statement
END LOOP [loop_label];

示例：
LOOP通常用在循环语句中，处于BEGIN...END之间，如果没有退出语句的话，会一直循环下去，造成死循环，可以和LEAVE一块使用，如下，求1+2+...+100的和：

创建存储过程：

mysql> \d $
mysql> CREATE PROCEDURE p_getsum()
BEGIN
    SET @sum = 0;
    SET @i = 0;
    label:LOOP
        SET @i = @i + 1;
        SET @sum = @sum + @i;
        IF @i = 100 THEN
            LEAVE label;
        END IF;
    END LOOP label;
END $
mysql> \d ;

调用存储过程并查看结果：

mysql> CALL p_getsum();
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

说明：
LEAVE：通常用在循环结构中，用来退出指定标记的循环，如上"LEAVE label"，表示跳出名称为label的循环，即：退出LOOP循环。
（4）REPEAT
语法：

[label:]REPEAT
    statement
UNTIL search_condition
END REPEAT [label]

示例：求1+2+...+100的和：
创建存储过程：

mysql> \d $
mysql> CREATE PROCEDURE p_getsum1()
BEGIN
    SET @i = 0;
    SET @SUM = 0;
    REPEAT
        SET @i = @i + 1;
        SET @sum = @sum + @i;
    UNTIL @i > 99
    END REPEAT;
    SELECT @sum FROM DUAL;
END $
mysql> \d ;

调用存储过程，显示1+2+...+100的值：

mysql> CALL p_getsum1();
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

（5）WHILE
语法：

[label:]WHILE search_condition DO
    statement
END WHILE [label];

示例：求1+2+...+100的和：
创建存储过程：

mysql> \d $
mysql> CREATE PROCEDURE p_getsum()
BEGIN
    SET @i = 0;
    SET @sum = 0;
    WHILE @i < 100 DO
        SET @i = @i + 1;
        SET @sum = @sum + @i;
    END WHILE;
    SELECT @sum FROM DUAL;
END $
\d ;

调用存储过程，查看结果：

CALL p_getsum();
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

此处举了一个简单的求和的例子，说明了一下存储过程中的流程控制，负责的流程控制也是由这些简单的组合而成的。简单的掌握了，就可以在这个基础上写出更复杂的存储过程。

11、存储过程的优缺点：
优点：
（1）可以完成实时的复杂报表数据处理及统计；
（2）可以很好的解决某些业务系统和数据库之间的耦合度，比如政府、银行或者金融系统中，一旦存储过程调试完毕，会稳定运行，减少很大一部分不必要的系统间交互；
缺点：
（1）互联网行业大并发场景，存储过程会由于访问量大，而且同时操作多张表，有可能会造成死锁，不好排查，导致数据库出现瓶颈。所以应该慎用，最好别用；
（2）迁移会比较麻烦，如果存储过程中用到了多张表，必须先保证表结构迁移正确，否则存储过程迁移时会出现错误；
（3）如果数据库中的某些表结构变化了，可能需要重新删除并创建存储过程，可扩展性较差；
（4）存储过程多数情况下都是由DBA编写，普通开发人员不容易掌握；

至此，存储过程和函数相关的内容介绍完毕，文中举的示例都是特别简单的，能够说明问题，有错误请大家指出。欢迎评论，转发，一块学习~

后续更多文章将更新在个人小站上，欢迎查看。

另外提供一些优秀的IT视频资料，可免费下载！如需要请查看https://www.592xuexi.com