版本比较

旧版本 2

changes.mady.by.user zhongluqiang

保存于

比较

新版本 当前版本

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这些语句上打上标记，以便于跳转到指定的语句。

存储过程

存储过程与存储函数形式一致，只不过没有返回值，仅表示一个处理过程。

创建存储过程

代码块
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	语句列表
END

示例：

代码块
mysql> DELIMITER $
mysql> CREATE PROCEDURE t1_operation(
    -> m1_value INT,
    -> n1_value CHAR(1)
    -> )
    -> BEGIN
    ->     SELECT * FROM t1;
    ->     INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
    ->     SELECT * FROM t1;
    -> END $
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

调用存储过程

代码块
CALL 存储过程([参数列表]);

查看和删除存储过程

查看：

代码块
SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]

查看定义：

代码块
SHOW CREATE PROCEDURE 存储过程名称

删除：

代码块
DROP PROCEDURE 存储过程名称

存储过程中的语句

前面存储函数中的语句都可以使用。

存储过程的参数前缀

存储过程在定义参数时可以加上前缀，如下：

代码块
[IN | OUT | INOUT] 参数名 数据类型

这三种前缀的作用如下：

前缀实际参数是否必须是变量描述
IN否用于调用者向存储过程传递数据，如果IN参数在存储过程中修改，则调用者不可见（形参）
OUT是用于把存储过程运行中产生的数据赋值给OUT参数，存储过程执行结束后，调用都可以访问OUT参数
INOUT是综合IN和OUT的特点，即可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据，以供调用者使用

存储过程和存储函数的不同点

  • 存储函数需要显示使用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN来指定返回值，存储过程则不需要。
  • 存储函数不支持IN、OUT、INOUT参数前缀。
  • 存储函数只能返回一个值，存储过程可以通过OUT参数来返回多个结果。
  • 存储函数在执行过程中产生的结果集不会被显示到客户端；存储过程在执行过程中产生的结果集会被显示到客户端。
  • 存储函数以函数的形式调用；存储过程只能通过CALL语句来显示调用。

游标

用于分步骤将结果集中的记录逐条赋值给某些变量。

初始时，游标指向结果集中的第一条记录，可以根据游标取出它对应的信息，随后再移动游标，让它指向下一条记录。

游标既可以用于存储函数，也可以用于存储过程。

创建游标

代码块
DECLARE 游标名称 CURSOR FOR 查询语句;

示例：

代码块
CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
END
提示

如果存储过程中有声明局部变量，则创建游标的语句一定要放在局部变量声明语句的后面。

打开和关闭游标

代码块
OPEN 游标名称;
CLOSE 游标名称;

打开游标意味着执行查询语句，获取结果集合，并使游标指向第一条结果。关闭游标意味着释放与该游标相关的资源，游标使用结束之后要关闭掉。存储过程的END语句执行之后也会自动关闭游标。

示例：

代码块
CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	OPEN t1_record_cursor;
	CLOSE t1_record_cursor;
END

通过游标获取记录

代码块
FETCH 游标名 INTO 变量1, 变量2, ... 变量n

示例：

代码块
CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE m_value INT;
	DECLARE n_value CHAR(1);

	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	OPEN t1_record_cursor;
	FETCH t1_record_cursor INTO m_value, n_value;
	SELECT m_value, n_value;
	CLOSE t1_record_cursor;
END

以上存储过程在CALL调用后会显示出m_value和n_value的结果。当然这里只显示了第一条记录的结果，如果要让游标遍历完全部结果，则需要使用循环语句，如下：

代码块
CREATE PROCEDURE cursor_demo()
BEGIN
 	DECLARE m_value INT;
	DECLARE n_value CHAR(1);
	DECLARE record_count INT;
	DECLARE i INT DEFAULT 0;

	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	SELECT COUNT(*) FROM t1 INTO record_count;

	OPEN t1_record_cursor;
	
	WHILE i < record_count DO
		FETCH t1_record_cursor INTO m_value, n_value;
		SELECT m_value, n_value;
		SET i = i + 1;
	END WHILE;

	CLOSE t1_record_cursor;
END

遍历结束时的执行策略

前面的示例通过预先获得结果集的记录数来结束游标循环，其实FETCH自带结束标志，当FETCH语句获取不到记录时默认支停止存储函数或存储过程的执行，并且向客户端返回一个如下的错误：

代码块
ERROR 1329 (02000): No data - zero rows fetched, selected or processed

于是我们可以在存储函数或存储过程中事先声明针对这种错误的处理方式，以执行相关的语句，如下：

代码块
DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句;

下面改写一下上面的查询语句：

代码块
CREATE PROCEDURE cursor_demo()
BEGIN
 	DECLARE m_value INT;
	DECLARE n_value CHAR(1);
	DECLARE done INT DEFAULT 0;

	DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN t1_record_cursor;
	
	flag:LOOP
		FETCH t1_record_cursor INTO m_value, n_value;
		IF done = 1 THEN
			LEAVE flag;
		END IF;
		SELECT m_value, n_value;
	END LOOP flag;

	CLOSE t1_record_cursor;
END






目录