参数说明

参数 说明
RETURNS 指定函数可以返回的数据类型
RETURN RETURN 语法后的实际变量或值，将返回到函数被调用的地方

第 30.1 节：带 IN、OUT、INOUT 参数的存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_nested_loop$$

CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT)
BEGIN
    DECLARE a INTEGER DEFAULT 0;
    DECLARE b INTEGER DEFAULT 0;
    DECLARE c INTEGER DEFAULT 0;

    WHILE a < i DO
        WHILE b < j DO
            SET c = c + 1;
            SET b = b + 1;
        END WHILE;
        SET a = a + 1;
        SET b = 0;
    END WHILE;

    SET x = a, y = c;
    SET z = x + y + z;
END$$

DELIMITER ;

调用存储过程：

SET @z = 30;
CALL sp_nested_loop(10, 20, @x, @y, @z);
SELECT @x, @y, @z;

结果：

[图片占位符]

  • IN 参数将一个值传递给存储过程。该过程可能会修改该值，但修改在过程返回时对调用者不可见。
  • OUT 参数将值从过程传回调用者。在过程中，其初始值为 NULL，当过程返回时，其值对调用者可见。
  • INOUT 参数由调用者初始化，可以被过程修改，并且过程所做的任何更改在过程返回时对调用者可见。

参考：http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

第 30.2 节：创建函数

以下简单的函数示例只返回常量整数值 12。

DELIMITER ||
CREATE FUNCTION functionname() RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;

第一行定义了分隔符要改为 ||，在创建函数之前需要设置它，否则如果保留默认的 ;，函数体中遇到的第一个 ; 将被视为 CREATE 语句的结束，这通常不是期望的结果。

在 CREATE FUNCTION 运行后，应该将分隔符设置回默认的 ;，如上面函数代码后所示（DELIMITER ;）。

执行此函数：

SELECT functionname();

结果：

+----------------+
| functionname() |
+----------------+
|             12 |
+----------------+

一个稍微复杂（但仍然简单）的例子接受一个参数并向其添加一个常量：

DELIMITER $$
CREATE FUNCTION add_2 (my_arg INT ) RETURNS INT
BEGIN
    RETURN (my_arg + 2);
END;
$$
DELIMITER ;

SELECT add_2(12);

结果：

+-----------+
| add_2(12) |
+-----------+
|        14 |
+-----------+

注意使用了不同的 DELIMITER 参数。实际上，你可以使用任何在 CREATE 语句体中没有出现的字符序列，但通常的做法是使用双倍的非字母数字字符，如 \\、|| 或 $$。

在创建或更新函数、存储过程或触发器之前和之后更改分隔符是一种好习惯，因为某些 GUI 不需要更改分隔符，而通过命令行运行查询总是需要设置分隔符。

第 30.3 节：游标

游标使你能够逐行遍历查询结果。DECLARE 命令用于初始化游标并将其与特定的 SQL 查询关联：

DECLARE student CURSOR FOR SELECT name FROM student;

假设我们销售若干类型的产品。我们想要计算每种类型的产品有多少个。

我们的数据：

CREATE TABLE product (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE product_type (
    name VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE product_type_count (
    type VARCHAR(50) NOT NULL PRIMARY KEY,
    count INT(10) UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO product_type (name) VALUES ('dress'), ('food');

INSERT INTO product (type, name) VALUES
('dress', 'T-shirt'),
('dress', 'Trousers'),
('food', 'Apple'),
('food', 'Tomatoes'),
('food', 'Meat');

我们可以通过使用游标的存储过程来实现目标：

DELIMITER //
DROP PROCEDURE IF EXISTS product_count;
CREATE PROCEDURE product_count()
BEGIN
    DECLARE p_type VARCHAR(255);
    DECLARE p_count INT(10) UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE product CURSOR FOR SELECT type, COUNT(*) FROM product GROUP BY type;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    TRUNCATE product_type_count;

    OPEN product;

    REPEAT
        FETCH product INTO p_type, p_count;
        IF NOT done THEN
            INSERT INTO product_type_count SET type = p_type, count = p_count;
        END IF;
    UNTIL done END REPEAT;

    CLOSE product;
END //
DELIMITER ;

然后你可以调用该过程：

CALL product_count();

结果将显示在 product_type_count 表中：

type count
dress 2
food 3

虽然这是一个很好的游标示例，但请注意，整个过程体可以简单地替换为：

INSERT INTO product_type_count (type, count)
SELECT type, COUNT(*) FROM product GROUP BY TYPE;

这将运行得快得多。

第 30.4 节：多个结果集

与 SELECT 语句不同，存储过程返回多个结果集。这需要在 Perl、PHP 等语言中使用不同的代码来收集 CALL 的结果。

（此处需要具体代码示例，可参考其他文档。）

第 30.5 节：创建函数（再述）

DELIMITER $$
CREATE DEFINER = 'db_username'@'hostname_or_IP'
FUNCTION `function_name` (optional_param data_type(length_if_applicable))
RETURNS data_type
BEGIN
    /* SQL 语句写在这里 */
END$$

RETURNS data_type 可以是任何 MySQL 数据类型。