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