Mysql 存储过程 批量给表加字段 多表记录数统计 存储过程返回值

在查询出的结果中，统计表记录总和

CREATE DEFINER=`root`@`%` PROCEDURE `dev_stat`()
BEGIN
# 申明当前使用databaseTest库
DECLARE databaseTest VARCHAR(100);
# 累计值
DECLARE count INT DEFAULT 0;
# 退出标识
DECLARE lp_flag BOOLEAN DEFAULT TRUE;
# table_name
DECLARE tn VARCHAR(100);
# 存入表名列表
DECLARE table_names CURSOR FOR
select name from table_define where table_type_id in (3,4,5,6);
# 循环到最后，改变标识
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lp_flag = FALSE;
SELECT DATABASE() INTO databaseTest;
OPEN table_names;
# loop
loop_la:LOOP
# 循环内赋值
FETCH table_names INTO tn;
if lp_flag THEN
SET @stmt = CONCAT('SELECT count(1) into @current from ',tn,' where load_level = 0');
PREPARE stmt from @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# count +
SET count = count + @current;
ELSE
LEAVE loop_la;
END IF;
END LOOP loop_la;
SELECT count;
CLOSE table_names;
END

在查询出的结果中，给结果中的表新增字段，新增前判断字段是否存在。

CREATE DEFINER=`root`@`%` PROCEDURE `dev_table_add_column`()
BEGIN
# 申明当前使用库
DECLARE databaseTest VARCHAR(100);
# 退出标识
DECLARE lp_flag BOOLEAN DEFAULT TRUE;
# table_name
DECLARE tn VARCHAR(100);
# 存入表名列表
DECLARE table_names CURSOR FOR
select name from table_define where table_type_id in (3,4,5,6);
# 循环到最后，改变标识
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lp_flag = FALSE;
SELECT DATABASE() INTO databaseTest;
OPEN table_names;
# loop
loop_la:LOOP
# 循环内赋值
FETCH table_names INTO tn;
if lp_flag THEN
SET @stmt= CONCAT('SELECT count(*) into @count FROM information_schema.columns WHERE table_schema=''databaseTest'' AND table_name =''',tn,
''' AND column_name in (''a'',''b'',''c'',''d'') ');
PREPARE stmt from @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
if @count <> 4
THEN
SET @stmt2 = CONCAT('ALTER TABLE ',tn,
' ADD COLUMN `a` int(11) NULL COMMENT ''xxx'',
ADD COLUMN `b` int(11) NULL COMMENT ''xxx'',
ADD COLUMN `c` int(11) NULL COMMENT ''xxx'',
ADD COLUMN `d` int(11) NULL COMMENT ''xxx''
');
PREPARE stmt from @stmt2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END if;
ELSE
LEAVE loop_la;
END IF;
END LOOP loop_la;
CLOSE table_names;
END

最后

以上就是迅速小懒虫为你收集整理的Mysql 存储过程 批量给表加字段 多表记录数统计 存储过程返回值的全部内容，希望文章能够帮你解决Mysql 存储过程 批量给表加字段 多表记录数统计 存储过程返回值所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。