navicat 创建函数，传参

1、函数->新建函数

 2、填写名称、下一步

 3、配置输入、输出参数

IN        aa        varchar

 4、创建完如下图（含where条件 拼接）

 5、直接保存会报错

6、增加输入字段长度

7、查询结果赋值到变量中

set @tablenames = (
SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.tables WHERE table_schema = (SELECT DATABASE ()) and  TABLE_NAME LIKE '%tb_receive_record_details_st%'
);

SELECT @tablenames;

8、按照表名称前缀查询库中所有表名称

SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.tables WHERE table_schema = (SELECT DATABASE ()) and  TABLE_NAME LIKE '%tb_receive_record_details_st%'

9、变量作为表名称

set @tablename1 = 'xx';
set @num_count = 0;	
set @STMT := CONCAT('select count(*) INTO @num_count from ',@tablename1,';');
	
PREPARE STMT FROM @STMT;
EXECUTE STMT;

10、模糊查询所有表，统计总数后返回示例

CREATE DEFINER=`root`@`%` PROCEDURE `deviceDataTotal`(OUT `data_count` bigint)
BEGIN
	# 数据资源中心-设备总量
-- 	先获取前缀为   tb_receive_record_details_st 的所有表名称，然后统计各表数据总量

set @tablenames = (
SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.tables WHERE table_schema = (SELECT DATABASE ()) and  TABLE_NAME LIKE '%tb_receive_record_details_st%'
);

-- SELECT @tablenames;
-- 获得以“,”号分割的字符串的个数
set @splitCount = (
1+(length(@tablenames) - length(replace(@tablenames,',','')))
);

-- select @splitCount;

-- 按分割取出字符串
set @i = 0;
set @data_count = 0;

-- 创建临时表
DROP TABLE IF EXISTS temp_table;
-- 设备类型 与 总数量 存储与临时表
CREATE TEMPORARY TABLE temp_table(
	select 0 num
);

WHILE @i < @splitCount DO
	set @i = @i + 1;
	set @tablename1 = reverse(substring_index(reverse(substring_index(@tablenames,',',@i)),',',1));
	
	set @num_count = 0;
	set @STMT := CONCAT('select count(*) INTO @num_count from ',@tablename1,';');
	
	PREPARE STMT FROM @STMT;
  EXECUTE STMT;

	insert into temp_table (num) values (@num_count);
	
END WHILE;

select sum(num) into data_count from temp_table;

END

11、mybatis调用存储过程获取返回值

    <select id="deviceDataTotal" statementType="CALLABLE" parameterType="java.util.Map" >
        CALL deviceDataTotal(
        #{data_count,mode=OUT,jdbcType=BIGINT}
        )
    </select>

返回值会封装在 请求参数的map中