一、mysql 存储过程

概念：存储过程是一组为了完成特定功能的 SQL 语句集合，存储在数据库中，经过第一次编译后再次调用不需要再次编译，类似 JavaScript 中的函数
特点：提高代码复用性，减少编译次数和数据库连接次数

-- 创建测试表和插入测试数据

-- 用户表
CREATE TABLE IF NOT EXISTS `user`(
    id int PRIMARY key auto_increment,
    username VARCHAR(22) NOT NULL,
    `password` VARCHAR(22) NOT NULL
);

-- 用户详细信息表
CREATE TABLE IF NOT EXISTS user_info(
    id INT PRIMARY key auto_increment,
    uid INT NOT NULL,
    realname VARCHAR(22) NOT NULL,
    gender enum('男','女') NOT NULL,
    height SMALLINT NOT NULL,
    age SMALLINT NOT NULL
);

-- 插入测试数据
INSERT INTO `user`(username,`password`) VALUES ('zhangsan','123456'),('lisi','666666');
INSERT INTO user_info(uid,realname,gender,height,age) VALUES (1,'张三','男',175,22),(2,'李四','男',190,30);

1. 创建并调用存储过程

创建存储过程：CREATE PROCEDURE 存储过程名(形参列表) BEGIN 执行体 END;
调用存储过程：CALL 存储过程名(实参列表);
形参列表格式：(参数模式 参数名 参数数据类型  ...)
三种参数模式：分别是 IN（默认）、OUT、INOUT 模式
其中 IN 是输入模式（不能作为返回值），OUT 是输出模式（作为返回值），INOUT 是即输入又输出模式

1. 无参存储过程

-- 创建存储过程
CREATE PROCEDURE find_aaa()
BEGIN
DECLARE aaa INT DEFAULT 100;
SET aaa := 999;
SELECT aaa;
END;

CALL find_aaa(); -- 调用存储过程，结果：999

2. IN 模式参数的存储过程
   IN模式参数作为输入参数传入时必须有具体值，在存储过程中不可给IN参数赋值（只能使用传入的原始值），不可作为返回参数

--  通过用户 id，查询用户信息信息

-- 创建存储过程
CREATE PROCEDURE find_user(IN id_ INT)
BEGIN
SELECT * FROM `user` WHERE id = id_;
END;

CALL find_user(1); -- 调用存储过程，结果：1   zhangsan    123456

3. OUT 模式参数的存储过程
   OUT模式参数作为输出参数传入时没有具体值，在存储过程中必须给OUT参数赋值（因为没有原始值），最后将OUT参数传递给回调程序暴露出来

-- 通过用户 id，查询用户名和密码

-- 创建存储过程
CREATE PROCEDURE find_uname_pwd(IN id_ INT,OUT uname VARCHAR(22),OUT pwd VARCHAR(22))
BEGIN
SELECT username,`password` INTO uname,pwd FROM `user` WHERE id = id_;
END;

CALL find_uname_pwd(1,@un,@pw); -- 调用存储过程，将返回值的结果赋值给用户变量 @un @pw
SELECT @un,@pw; -- 查看用户变量，结果：zhangsan   123456

4. INOUT 模式参数的存储过程
   IN和OUT模式结合，INOUT模式参数作为输入输出参数传入时有具体值（必须是变量，因为需要通过回调程序接受返回值），在存储过程中可以给也可以不给INOUT参数赋值，最后将OUT参数传递给回调程序暴露出来

-- 将id为1的用户的账号密码加上 v_ 前缀

-- 创建存储过程
CREATE PROCEDURE prefix_uname_pwd(IN prefix VARCHAR(22), INOUT uname VARCHAR(22),INOUT pwd VARCHAR(22))
BEGIN
SET uname = CONCAT(prefix,uname);
SET pwd = CONCAT(prefix,pwd);
END;

-- 创建@un,@pw 变量，将id为1的用户信息赋值给 @un,@pw 变量
SELECT username,`password` INTO @un,@pw  FROM `user` WHERE id = 1; 

-- 调用存储过程，将返回值的结果赋值给用户变量 @un @pw
CALL prefix_uname_pwd('v_',@un,@pw); 

SELECT @un,@pw; -- 查看用户变量，结果：v_zhangsan v_123456

2. 删除存储过程

DROP PROCEDURE 存储过程名; -- 直接删除
DROP PROCEDURE IF EXISTS 存储过程名; -- 兼容写法，如果存在就删除

3. 查看存储过程

SHOW CREATE PROCEDURE 存储过程名; -- 可查看创建存储过程的sql语句（Create Procedure 字段）  

二、mysql 自定义函数

和存储过程类似，但在执行体中必须有一个 return 子句用来返回结果，并且返回结果只能是一个值

1. 创建并使用函数

创建函数：CREATE FUNCTION 函数名(形参列表) RETURNS 返回值的数据类型 BEGIN 执行体 END;
调用函数：SELECT 函数名(实参列表);
形参列表：(参数名 参数类型 ...)

1. 无参函数

-- 获取用户个数

-- 创建函数
CREATE FUNCTION get_users_num() RETURNS INT
BEGIN
DECLARE num int DEFAULT 0;
SELECT COUNT(*) INTO num FROM user;
RETURN num;
END;

SELECT get_users_num() users_num; -- 调用函数，结果：2

2. 有参函数

-- 获取id为1的用户的用户名

-- 创建函数
CREATE FUNCTION get_username(id_ INT) RETURNS VARCHAR(22)
BEGIN
DECLARE uname VARCHAR(22) DEFAULT '';
SELECT username INTO uname FROM `user` WHERE id = id_;
RETURN uname;
END;

SELECT get_username(1) username; -- 调用函数，结果：zhangsan

2. 删除函数

DROP FUNCTION 函数名; -- 直接删除
DROP FUNCTION IF EXISTS 函数名; -- 兼容写法，如果存在就删除

3. 查看函数

SHOW CREATE FUNCTION 函数名; -- 可查看创建函数的sql语句（Create FUNCTION 字段）  

三、mysql 存储过程和函数区别

                参数格式不同             返回值不同         调用方式不同               
存储过程    参数模式 参数名 数据类型       个数不限          CALL 存储过程名()                 
函数        参数名 数据类型              有且仅有1个        SELECT 函数名()