MySQL存储过程使用随手记0

实现功能

批量更新指定数据库中所有表的字符编码。

语句

BEGIN

DECLARE dynamicSql varchar(200);
DECLARE stop int default  0;
DECLARE mycursor CURSOR FOR
SELECT
	CONCAT('alter table ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as new_sql
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = 'rbc_store';/*rbc_store为数据库名*/
 /*这把 游标 异常后 捕捉
         *        并设置 循环使用 变量 stop 为 null 跳出循环。
*/
DECLARE CONTINUE HANDLER for SQLSTATE '02000' SET STOP = null;
OPEN mycursor;
FETCH mycursor into dynamicSql;
WHILE (stop is not null) DO
SET @v_sql = dynamicSql;
PREPARE stmt from @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH mycursor into dynamicSql;
END WHILE;
CLOSE mycursor;
END

用到的关键语法

1.声明游标

DECLARE mycursor CURSOR FOR
SELECT
	CONCAT('alter table ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as new_sql
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = 'rbc_store';

2.循环语句

WHILE (条件) DO
END WHILE;

3.游标使用

OPEN mycursor;
FETCH mycursor into 变量;
CLOSE mycursor;

4.Declare ...Handler语法

DECLARE CONTINUE HANDLER for SQLSTATE '02000' SET STOP = null;

该语句指的是，定义一个处理器handler,该处理器针对存储过程出现状态为SQLSTATE '02000'的异常，做相应的处理即为stop变量赋值为null。
其中 CONTINUE是处理器类型，SQLSTATE '02000' 为异常描述，指游标操作中找不到数据，“set stop=null”为异常处理语句，也可以是以begin..end 结尾的语句块。

5.动态sql语句的执行

SET @v_sql = dynamicSql;
PREPARE stmt from @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

要动态执行sql语句，必须采用预编译的语法。即使用prepare关键字和execute关键字。