create proc proc_checkTimer__dataUpdateTime(@startIndex INT, @endIndex INT)
as
DECLARE @tbm_id int --table_meta表主鍵
DECLARE @varId int --品種id
DECLARE @dbname varchar(300) --表名
DECLARE @dataUpdateTime datetime --物理表最近更新時間
DECLARE @Update_Str nvarchar(1000) --更新資料的sql
DECLARE @EditTime datetime --邏輯表對應最大修改時間
DECLARE @Select_Str nvarchar(1000) --查詢最大EditTime的sql
--定義游標 遍歷table_meta表
DECLARE dbname_Cursor CURSOR LOCAL FOR
--篩選出更新時間大于上次定時任務執行時間的邏輯表
select id,varId,dbName,dataUpdateTime from CFG_TABLE_META_NEW where (dataupdateTime is NULL OR DATEDIFF(SECOND, dataupdateTime, dealtime) > 0)
AND (id BETWEEN @startIndex and @endIndex)
ORDER BY id
open dbname_Cursor
fetch next from dbname_Cursor into @tbm_id,@varId,@dbname,@dataUpdateTime
while @@fetch_status = 0
begin try
--通過dbname 和 varId查詢物理表只定品種最大editTime 更新到cfg_table_meta_new表中
-- 獲取回傳值避免需執行更新陳述句時再次查詢，寫法1
--SET @Select_Str = 'SELECT @EditTime = max(editTime) from ' + @dbname + ' where varId = ' + convert(varchar(5),@varId)
--exec sp_executesql @Select_Str,N'@EditTime datetime out', @EditTime out -- 獲取回傳值避免需執行更新陳述句時再次查詢
--寫法2（注意表名只能字串連接不可作為入參）
SET @Select_Str = 'SELECT @EditTime = max(editTime) from ' + @dbname + ' where varId = convert(varchar(5),@varId)'
exec sp_executesql @Select_Str,N'@EditTime datetime out, @varId int', @EditTime = @EditTime out, @varId = @varId
IF(@EditTime IS NULL)
BEGIN
PRINT('if edittime is null:-------------------------')
set @Update_Str = 'update cfg_table_meta_new set dataupdateTime = getdate() where [id] = ' + convert(varchar(10),@tbm_id)
EXEC(@Update_Str) --慎重打開
--PRINT @Update_Str
END
ELSE IF(@dataUpdateTime is NULL)
BEGIN
--PRINT('else if dataupdatetime is null:---------------------')
set @Update_Str = 'update cfg_table_meta_new set dataupdateTime = ''' + CONVERT(VARCHAR(24),@EditTime,120) + ''' where [id] = ' + convert(varchar(10),@tbm_id)
EXEC(@Update_Str) --慎重打開
--PRINT(@Update_Str)
END
ELSE IF(DATEDIFF(ss, @dataUpdateTime, @EditTime)> 0)
BEGIN
PRINT('else if:---------------------')
set @Update_Str = 'update cfg_table_meta_new set dataupdateTime = ''' + CONVERT(VARCHAR(24),@EditTime,120) + ''' where [id] = ' + convert(varchar(10),@tbm_id)
EXEC(@Update_Str) --慎重打開
--PRINT(@Update_Str)
END
ELSE
BEGIN
PRINT ('else:' + convert(varchar(10),@tbm_id) + '----------')
END
fetch next from dbname_Cursor into @tbm_id,@varId,@dbname,@dataUpdateTime
end try
begin catch
PRINT('exception:' + @dbname + '---' + convert(varchar(5),@varId))
PRINT(error_message())
fetch next from dbname_Cursor into @tbm_id,@varId,@dbname,@dataUpdateTime
end catch
close dbname_Cursor
deallocate dbname_Cursor
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/118025.html
標籤：MySQL