mysql - 存储过程和函数

本文的内容，一般来说，整个职业生涯都用不上，不推荐花太多时间学习。

存储过程有用，是 oracle 的存储过程有用，确实有很多用 oracl 数据库的大厂。

mysql 一般纯粹用于存储数据，即便是做数据抽取，也不会使用存储过程，会使用专门的平台工具，例如：kettle。

在后期维护中，存储过程属于极难维护的级别：

• 代码格式没有统一规范，格式化 SQL 语句（美化 SQL），有时候会产生严重的错位；
• 存储过程不适合用于计算，有时候，四五百行的 SQL，其实是单条命令，第一批开发人员离职之后，其他人根本不敢动；
• 很多情况下，维护人员能做的，就是在程序最后，追加补充新脚本，迭代多次之后，形成庞大的屎山代码。

函数

普通的函数：

DROP FUNCTION IF EXISTS TEST;

CREATE FUNCTION TEST() RETURNS VARCHAR(20)
BEGIN
    RETURN 'test';
END;
    
SELECT TEST();

带参数的函数：

DROP FUNCTION IF EXISTS TEST;

CREATE FUNCTION TEST(a DOUBLE(10, 5), b DOUBLE(10, 5)) RETURNS DOUBLE(11, 5)
BEGIN
    RETURN a + b;
END;
    
SELECT TEST(1.11,2.22);

带局部变量的函数：

DROP FUNCTION IF EXISTS TEST;

CREATE FUNCTION TEST(a DOUBLE(10, 5), b DOUBLE(10, 5)) RETURNS DOUBLE(11, 5)
BEGIN
    DECLARE ret DOUBLE(11, 5);
    SET ret = a + b;
    return ret;
END;
    
SELECT TEST(1.11,2.22);

存储过程

function 只有一个返回值，这在数据处理过程中，是无法满足需求的。
举个例子：查询一次就可以拿到 name 和 age，使用 function 没办法同时返回这两个值。

这时候，就要使用存储过程了，存储过程不能 return，但是有 out 参数，
返回值可以通过 out 进行回传，一个存储过程，可以有很多个 out 参数。

当然，上面只是说明了其中一项区别，总得来说，存储过程用于处理数据（业务逻辑），而函数用于封装一些计算（纯粹的算法）。

IN 输入参数
OUT 输出参数
INOUT 既能输入，又能输出的参数

-- 基本结构
CREATE PROCEDURE empty()
BEGIN
	-- do sth.
END;

-- 创建存储过程
CREATE PROCEDURE GetCustomer(IN customer_id INT, OUT uname varchar(32))
BEGIN
	-- 查询客户信息
	SELECT user_name INTO uname FROM person WHERE id = customer_id;
END;

流程控制语句

-- 清除原先的函数
DROP FUNCTION IF EXISTS `fun_test`;

-- 创建函数
CREATE FUNCTION `fun_test`
    -- 设置函数参数
    (a DOUBLE, b DOUBLE)
    -- 设置返回值类型
    RETURNS VARCHAR(500)
BEGIN
    -- 设置成员变量
    DECLARE res VARCHAR(500);
    SET res = '';

    -- If...else...语句 
    IF a > 0 THEN
        SET res = CONCAT(res, 'a > 0; ');
    ELSEIF a < 0 THEN
        SET res = CONCAT(res, 'a < 0; ');
    ELSE
        SET res = CONCAT(res, 'a = 0; ');
    END IF;

    -- case...when...语句 类似于switch 
    CASE b
        WHEN 0 THEN SET res = CONCAT(res, 'b == 0; ');
        ELSE SET res = CONCAT(res, 'b != 0; ');
        END CASE;

    -- loop语句 类似于for循环，使用LEAVE跳出循环 
    SET @count = 0;
    loop_eg:
    LOOP
        SET @count = @count + 1;
        IF @count < 2 THEN
            SET res = CONCAT(res, 'LOOP循环测试:');
        ELSE
            LEAVE loop_eg;
        END IF;
    END LOOP loop_eg;

    -- loop语句 类似于for循环，使用ITERATE跳出本次循环 
    SET @count = 0;
    loop_eg:
    LOOP
        SET @count = @count + 1;
        IF @count = 1 THEN
            SET res = CONCAT(res, '1');
        ELSEIF @count = 2 THEN
            ITERATE loop_eg; SET res = CONCAT(res, '2');
        ELSEIF @count = 3 THEN
            SET res = CONCAT(res, '3;');
        ELSE
            LEAVE loop_eg;
        END IF;
    END LOOP loop_eg;

    -- repeat...util...语句 类似于do...WHILE...语句 
    SET @count = 0;
    REPEAT
        SET @count = @count + 1;
        SET res = CONCAT(res, @count);
    UNTIL @count = 5
        END REPEAT;

    -- while...语句 
    WHILE @count < 10
        DO
            SET @count = @count + 1;
            SET res = CONCAT(res, @count);
        END WHILE;

    RETURN res;
END;

-- 测试函数
SELECT fun_test(1, 2)