-- DROP PROCEDURE update_name;
CREATE PROCEDURE update_name () // 创建一个存储过程
BEGIN
DECLARE
tableName VARCHAR (30); 定义一个变量
DECLARE
sqls VARCHAR (200); 定义一个sqls
DECLARE
done INT DEFAULT FALSE; 定义一个游标的结束标志
DECLARE
createName VARCHAR (200); 定义一个字段的名称
DECLARE
cursor_name CURSOR FOR SELECT 创建一个游标
TABLE_NAME
FROM
information_schema. COLUMNS T
WHERE
table_schema = 'ktc'
AND table_name = 't_base_commodity'
AND T.COLUMN_NAME = 'create_name';
-- 指定游标循环结束时的返回值
DECLARE
CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
SET createName = '''捡东西''';
OPEN cursor_name;
my_loop :
LOOP
FETCH cursor_name INTO tableName;
SET sqls = CONCAT(
' update ',
tableName,
' set create_name=',
createName
);
INSERT com_log (str1)
VALUES
(sqls);
set @a=sqls;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1 ;
IF done THEN
LEAVE my_loop;
END
IF;
END
LOOP
;
-- CLOSE CURSOR ;
END;