SqlServer存储过程

1、sqlserver使用存储过程利用游标进行循环处理统计信息

DECLARE DataBaseNames CURSOR FOR (select dataname from #min_Tmp)

2、存储过程保存在[master]下
3、通过 SysObjects 获取指定数据库(master)下的指定表名(Test_01)

SELECT Name FROM   master..SysObjects Where XType='U' and name like 'Test%';

4、通过exec(sqlStr)执行动态拼接的sql语句

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Statistics_min] 

AS
BEGIN
    DECLARE @NowDate nvarchar(100);

    DECLARE @DataBaseName nvarchar(100);
    DECLARE @DataTableName nvarchar(100);

	DECLARE @DBNameTemp nvarchar(100);
	DECLARE @DTNameMaxTemp nvarchar(100);
	DECLARE @DTNameMinTemp nvarchar(100);
	DECLARE @DTNameAvgTemp nvarchar(100);
	DECLARE @DTNameVarTemp nvarchar(100);

	DECLARE @dataTime nvarchar(100);
	DECLARE @max_col1 nvarchar(100);
	DECLARE @min_col1 nvarchar(100);
	DECLARE @avg_col1 nvarchar(100);
	DECLARE @var_col1 nvarchar(100);
	DECLARE @max_col2 nvarchar(100);
	DECLARE @min_col2 nvarchar(100);
	DECLARE @avg_col2 nvarchar(100);
	DECLARE @var_col2 nvarchar(100);

	declare @sql nvarchar(2000);

    --start 查询所有数据库名
	--创建临时表
	create table #min_Tmp(dataname varchar(100));
	insert into #min_Tmp([dataname])values('数据库_1');
	insert into #min_Tmp([dataname])values('数据库_2');
	insert into #min_Tmp([dataname])values('数据库_3');

	--获取当前时间并将 2021-06-30 14:53:52 转换为 2021-06-30-14-53格式
    set @NowDate= (select CONCAT(year(dateadd(MINUTE,-2,getdate())),'-',right('00'+convert(nvarchar(50),month(dateadd(MINUTE,-2,getdate()))),2),'-',right('00'+convert(nvarchar(50),day(dateadd(MINUTE,-2,getdate()))),2),'-',right('00'+convert(nvarchar(50),datename(hh,dateadd(MINUTE,-2,getdate()))),2),'-',right('00'+convert(nvarchar(50),datename(mi,dateadd(MINUTE,-2,getdate()))),2)));
	
	--设置DataBaseNames游标
	DECLARE DataBaseNames CURSOR FOR 
	(select dataname from #min_Tmp)
	OPEN DataBaseNames  --打开DataBaseNames游标
	FETCH NEXT FROM DataBaseNames INTO @DataBaseName  --获取第一个值并赋值给@DataBaseName

	WHILE @@fetch_status = 0 
	BEGIN
	    --start 查询包含Test的所有表名并设置游标
		exec('DECLARE DataTableNames CURSOR FOR( SELECT Name FROM  ['+@DataBaseName+']..SysObjects Where XType=''U'' and name like ''Test%'')')
	    OPEN DataTableNames  --打开DataTableNames游标
		FETCH NEXT FROM	DataTableNames INTO @DataTableName   --获取第一个值并赋值给@DataTableName
		WHILE @@fetch_status = 0 
		BEGIN
		--start 统计指定数据库下的指定表名的数据信息 设置DataNames游标
		exec('DECLARE DataNames CURSOR FOR(select (left(col0,16) +''-00.000'') as ''dataTime'',
			max(col1) as ''max_col1'',min(col1) as ''min_col1'',avg(col1) as ''avg_col1'',var(col1) as ''var_col1'',
			max(col2) as ''max_col2'',min(col2) as ''min_col2'',avg(col2) as ''avg_col2'',var(col2) as ''var_col2'' 
			from [' +@DataBaseName +'].[dbo].['+@DataTableName+']
			where  left(col0,16)='''+ @NowDate+ '''
			group by left(col0,16)
			)'
		)
		OPEN DataNames --打开DataNames游标
		FETCH NEXT FROM	DataNames INTO @dataTime,@max_col1,@min_col1,@avg_col1,@var_col1,@max_col2,@min_col2,@avg_col2,@var_col2
		WHILE @@fetch_status = 0 
		BEGIN
		--start 处理  要插入数据的表
		set @DBNameTemp=replace(@DataBaseName,'_c','_anays')
		set @DTNameMaxTemp=concat(@DataTableName,'_min','_max')
		set @DTNameMinTemp=concat(@DataTableName,'_min','_min')
		set @DTNameAvgTemp=concat(@DataTableName,'_min','_avg')
		set @DTNameVarTemp=concat(@DataTableName,'_min','_var')

			----最大值
			set @sql='delete from ['+@DBNameTemp+'].[dbo].['+@DTNameMaxTemp+'] where col0='''+@dataTime+''';';
			set @sql +='insert into ['+@DBNameTemp+'].[dbo].['+@DTNameMaxTemp+']([col0],[col1],[col2])'
			set @sql +='values('''+@dataTime+''','+@max_col1+','+@max_col2+');'
			----最小值
			set @sql +='delete from ['+@DBNameTemp+'].[dbo].['+@DTNameMinTemp+'] where col0='''+@dataTime+''';';
			set @sql +='insert into ['+@DBNameTemp+'].[dbo].['+@DTNameMinTemp+']([col0],[col1],[col2])'
			set @sql +='values('''+@dataTime+''','+@min_col1+','+@min_col2+');'
			----平均值
			set @sql +='delete from ['+@DBNameTemp+'].[dbo].['+@DTNameAvgTemp+'] where col0='''+@dataTime+''';';
			set @sql +='insert into ['+@DBNameTemp+'].[dbo].['+@DTNameAvgTemp+']([col0],[col1],[col2])'
			set @sql +='values('''+@dataTime+''','+@avg_col1+','+@avg_col2+');'
			----方差
			set @sql +='delete from ['+@DBNameTemp+'].[dbo].['+@DTNameVarTemp+'] where col0='''+@dataTime+''';';
			set @sql +='insert into ['+@DBNameTemp+'].[dbo].['+@DTNameVarTemp+']([col0],[col1],[col2])'
			set @sql +='values('''+@dataTime+''','+@var_col1+','+@var_col2+');'

			exec(@sql);
		--end 处理
				FETCH NEXT FROM	DataNames INTO @dataTime,@max_col1,@min_col1,@avg_col1,@var_col1,@max_col2,@min_col2,@avg_col2,@var_col2 
				END CLOSE DataNames  --结束  关闭DataNames游标
				DEALLOCATE DataNames  --释放DataNames游标
		--end 统计
				FETCH NEXT FROM	DataTableNames INTO @DataTableName  --将下一个值赋值给@DataTableName
				END CLOSE DataTableNames   --结束  关闭DataTableNames游标
				DEALLOCATE DataTableNames  ---释放DataTableNames游标
		    --end 查询所有表名
			FETCH NEXT FROM	DataBaseNames INTO @DataBaseName   --将下一个值赋值给@DataBaseName
			END CLOSE DataBaseNames   --结束  关闭DataBaseNames游标
			DEALLOCATE DataBaseNames  --释放DataBaseNames游标
			--end 查询所有数据库名	
		END 

		drop table #min_Tmp   --删除临时表#Tmp