版本比较

旧版本 5

changes.mady.by.user zhongluqiang

保存于

比较

新版本 6

changes.mady.by.user zhongluqiang

保存于

标识

  • 该行被添加。
  • 该行被删除。
  • 格式已经改变。

指组合在一起的一堆SQL语句，用于简化数据处理流程。

可以将某个常用功能对应的一些语句封装成一个存储程序，之后只需要调用这个存储程序就可以完成这个常用功能，从而免去每次都要写好多语句的麻烦。

存储程序包含以下分类：

Jsmind
<?xml version="1.0" encoding="utf-8"?>

<map version="1.0.1"> 
  <node ID="aiRrV41m" TEXT="存储程序"> 
    <node ID="tzxlYvKW" TEXT="存储例程" POSITION="right"> 
      <node ID="rO72tK0L" TEXT="存储函数" POSITION="right"></node>  
      <node ID="K0e1zLOZ" TEXT="存储过程" POSITION="right"></node>  
    </node>  
    <node ID="7U20o0J9" TEXT="触发器" POSITION="right"></node>  
    <node ID="ETPVzxlF" TEXT="事件" POSITION="right"></node>  
  </node> 
</map>

用户自定义变量

代码块
SET @a = 1;
SELECT @a;
SET @a = 2;
SET @b = @a;
SET @a = (SELECT m1 FROM t1 LIMIT 1);
SELECT n1 FROM t1 LIMIT 1 INTO @b;
SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b;
提示

如果定义变量时没有加@符号，则MySQL会把这个变量当成系统变量来对待。

存储函数

创建存储函数

格式如下：

代码块
DELIMITER $  # 将MySQL客户端语句分隔符替换为$

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

DELIMITER ;  # 恢复MySQL客户端语句分隔符为;

比如：

代码块
DELIMITER $

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

DELIMITER ;

调用上面的存储函数的语句如下：

代码块
SELECT avg_score('MySQL是怎样运行的');
提示

如果创建存储函数的过程中报如下错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

则可以先执行下面的语句，然后再尝试：

SET global log_bin_trust_function_creators=TRUE;


查看和删除存储函数

查看已定义的存储函数：

代码块
SHOW FUNCTION STATUS [LIKE 需要匹配的函数名];

查看某个存储函数的具体定义：

代码块
SHOW CREATE FUNCTION 函数名\G

删除存储函数：

代码块
DROP FUNCTION 函数名

函数体的定义

定义局部变量

代码块
DECLARE 变量名1, 变量名2, ...数据类型 [DEFAULT 默认值];

示例：

代码块
DELIMITER $

CREATE FUNCTION var_default_demo()
RETURN INT
BEGIN
    DECLARE c INT DEFAULT 1;
    RETURN c;
END $

DELIMITER ;

使用自定义变量

代码块
DELIMITER $

CREATE FUNCTION user_defined_var_demo()
RETURN INT
BEGIN
    SET @abc = 10;
    RETURN @abc;
END $

DELIMITER ;
提示

注意自定义变量和局部变量的区别，局部变量只在当前作用域有效，而自定义变量可以在函数体外生效，相当于全局变量，如下：

代码块
mysql> DELIMITER $
mysql> CREATE FUNCTION user_defined_var_demo()
    -> RETURNS INT
    -> BEGIN
    -> SET @abc = 10;
    -> return @abc;
    -> END $
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql>
mysql> SELECT @abc;
+------------+
| @abc       |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT user_defined_var_demo();
+-------------------------+
| user_defined_var_demo() |
+-------------------------+
|                      10 |
+-------------------------+
1 row in set (0.02 sec)

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

存储函数的参数

定义存储函数时可以指定多个参数，每个参数都要指定对应的数据类型，像这样：

参数名 数据类型

示例：

代码块
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS INT
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

判断语句

代码块
IF 表达式 THEN
    语句列表
[ELSEIF 表达式 THEN 语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE 语句列表]
END IF;

示例：

代码块
DELIMITER $

CREATE FUNCTION condition_demo(i INT)
RETURNS VARCHAR(10)
BEGIN
	DECLARE result VARCHAR(10);
	IF i = 1 THEN
		SET result = '结果是1';
	ELSEIF i = 2 THEN
		SET result = '结果是2';
	ELSEIF i = 3 THEN
		SET result = '结果是3';
	ELSE
		SET result = '非法参数';
	END IF;
	RETURN result;
END $

DELIMITER ;

循环语句

WHILE循环：

代码块
WHILE 表达式 DO
	语句列表
END WHILE;

REPEAT循环：

代码块
REPEAT
	语句列表
UNTIL 表达式 END REPEAT;
提示

REPEAT循环是先执行语句列表，再判断表达式是否为真，如果为真则退出循环，否则继续执行语句。

REPEAT循环至少执行次，WHILE循环可能一次都不执行。

LOOP循环：

代码块
LOOP
	语句列表
END LOOP;
提示

LOOP循环没有终止条件，它的终止要靠RETURN语句或是LEAVE语句，如下：

代码块
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE i INT DEFAULT 1;
	flag:LOOP
		IF i > n THEN
			LEAVE flag;
		END IF;
		SET result = result + i;
		SET i = i + 1;
	END LOOP flag;
	RETURN result
END

其实也可以在BEGIN ... END、REPEAT和WHILE这些语句上打上标记，以便于跳转到指定的语句。










目录