MySql存储过程与自定义函数

迷南。 2022-05-25 10:14 190阅读 0赞

### 一、存储过程概述 ###

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

存储过程由以下几个优点

1.  通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中
2.  可用于提高代码的重用性，简化操作
3.  存储的程序是安全的

### 二、存储过程语法结构 ###

创建存储过程的语法结构

DROP PROCEDURE IF EXISTS [存储过程名];
    DELIMITER [结束标记]
    CREATE PROCEDURE [存储过程名]([参数1], [参数2] ...) BEGIN [存储过程体(一组合法的SQL语句)] END [结束标记] DELIMITER ;

当存储过程有且仅有一条 SQL 语句时，`BEGIN` 和 `END` 可以省略。 存储过程中的参数可分为 3 部分，分别是 `[参数模式] [参数名] [参数类型]` ，比如 `IN name VARCHAR[20]`。

参数模式也分为 3 种，如下

`IN`：输入参数  
`OUT`：可以作为返回值的参数  
`INOUT`：既可以作为输入参数，也可以作为返回值参数

`DELIMITER [存储过程名]` 本身与存储过程的语法无关，用于表示存储过程的结束。最后一个命令(`DELIMITER ;`)将分隔符更改回分号(`;`)。

调用存储过程使用`CALL [存储过程名]([参数1], [参数2] ...);`语句。

删除存储过程使用`DROP PROCEDURE IF EXISTS [存储过程名];`。

### 三、存储过程的简单使用 ###

创建测试表

DROP TABLE IF EXISTS `t_user`;
    CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#### 3.1创建无参的存储过程 ####

创建存储过程（向 `t_user` 数据表中插入两条数据）

DROP PROCEDURE IF EXISTS myp1;
    DELIMITER $
    CREATE PROCEDURE myp1() BEGIN INSERT INTO t_user VALUES(NULL, 'Jas'),(NULL, 'Joy');
    END $
    DELIMITER ;

调用存储过程并查看结果  
![这里写图片描述][70]

#### 3.2创建`IN`模式参数的存储过程 ####

创建存储过程（根据传入的 `id` 获取用户名）

DROP PROCEDURE IF EXISTS myp2;
    DELIMITER $
    CREATE PROCEDURE myp2(IN userId INT) BEGIN SELECT name FROM t_user WHERE id = userId;
    END $
    DELIMITER ;

调用存储过程并查看结果  
![这里写图片描述][70 1]

#### 3.3创建`OUT`模式参数的存储过程 ####

创建存储过程（根据传入的 `id` 获取用户名赋值给输出变量）

DROP PROCEDURE IF EXISTS myp3;
    DELIMITER $
    CREATE PROCEDURE myp3(IN userId INT, OUT username VARCHAR(20)) BEGIN SELECT name INTO username # 将查询到的用户名赋值给 username FROM t_user WHERE id = userId;
    END $
    DELIMITER ;

调用存储过程并查看结果  
![这里写图片描述][70 2]

#### 3.4创建`INOUT`模式参数的存储过程 ####

创建存储过程（传入一个整数参数，值扩大 2 倍后返回）

DROP PROCEDURE IF EXISTS myp4;
    DELIMITER $
    CREATE PROCEDURE myp4(INOUT a INT) BEGIN SET a = a * 2;
    END $
    DELIMITER ;

调用存储过程并查看结果  
![这里写图片描述][70 3]

### 四、自定义函数概述 ###

自定义函数的使用方法与存储过程类似，只不过自定义函数必须要有返回值，返回值有且只有一个。

自定义函数语法

DROP FUNCTION IF EXISTS [函数名];
    DELIMITER [结束标记]
    CREATE FUNCTION [函数名]([参数1], [参数2] ...) RETURNS [返回值类型] BEGIN [方法体] RETURN [返回值];
    END [结束标记] DELIMITER ;

自定义函数中的变量只有两部分，分别是`[变量名] [变量类型]`，比如`username VARCHAR[20]`。通过上面的语法对比，不知道大家能不能发现MySql 中的自定义函数与 Java 中的有返回值函数很像。

调用自定义函数的语法是`SELECT [函数名]([参数1], [参数2] ...);`。

删除自定义函数的语法是 `DROP FUNCTION IF EXISTS [函数名];`。

### 五、自定义函数的简单使用 ###

#### 5.1创建无参的自定义函数 ####

创建自定义函数（查询 `t_user` 中的所有记录数，并返回）

DROP FUNCTION IF EXISTS myf1;
    DELIMITER $
    CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE sum INT DEFAULT 0;    # 定义局部变量 sum，默认值为 0
            SELECT COUNT(*) INTO sum # 将查询的结果赋值给 sum FROM t_user;
            RETURN sum;
    END $ DELIMITER ;

调用自定义函数并查看结果  
![这里写图片描述][70 4]

#### 5.2创建有参的自定义函数 ####

创建自定义函数（根据用户 `id` 获取用户名，赋值后返回）

DROP FUNCTION IF EXISTS myf2;
    DELIMITER $
    CREATE FUNCTION myf2(userId INT) RETURNS VARCHAR(20) BEGIN SET @username='';    # 定义系统会话变量
            SELECT name INTO @username # 将用户名赋值给 username FROM t_user WHERE id = userId;
            RETURN @username;
    END $ DELIMITER ;

调用自定义函数并查看结果  
![这里写图片描述][70 5]

### 六、总结 ###

自定义函数与存储过程有很多相似的地方，下面是一些主要的区别对比

1.  一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强
2.  存储过程可以有返回值也可以没有返回值，而自定义函数必须要返回值，且返回值有且只有一个
3.  存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，因此它可以在查询语句中位于 `FROM` 关键字的后面。 SQL 语句中不可用存储过程，而可以使用函数

[70]: /images/20220525/83fd796b7a444772adc41642fe8dee00.png
[70 1]: /images/20220525/4830915102684e5cb1328972863fb37f.png
[70 2]: /images/20220525/273b06ac472443a9a75680ce5cb47660.png
[70 3]: /images/20220525/763ec13b5c38463d8f5f1fce94405f50.png
[70 4]: /images/20220525/218fc4b2c3154782a3b96d834498d334.png
[70 5]: /images/20220525/c3919e7b070b4ec19546a87758f2ec72.png