"Очистка базы логов"

Название топика взято из формулировки задачи=)

В один прекрасный момент свалилось мне на почту такое письмо
"Посмотри, пожалуйста, описание задачки по автоматизации очистки базы логов.

Сервер – БД — Для очистки регламентным заданием таблиц логов в , необходима SQL процедура.
На вход процедура принимает параметры:
ПолеПериод
ИмяТаблицы
ДатаУдаления
ДатаУдаленияПо(Необязательный, если задан то данные удаляются в промежутке между ДатаУдаления и ДатаУдаленияПо, если не задан то удаляются данные меньше ДатаУдаления)
Порция(по умолнчанию можно 10000 записей)

Возвращать результат, числом (какие еще варианты?):
-1 (Нет данных для удаления)
КоличествоУдаленныхДанных"



В есть какое-то количество таблиц, которые содержат записи логов. Всё усложняется тем, что в таблицах неодинаковое количество столбцов и параметр «ПолеПериод» на самом деле является именем столбца с датами добавления лога, который в разных таблицах называется по-разному.
Итого — требуется хранимая процедура, с параметрами «имя таблицы», «имя столбца», «начальная дата», «конечная дата», «количество удаляемых записей», при этом 2 параметра должны быть опциональными — «начальная дата», «количество удаляемых записей». Процедура должна возвращать для дальнейшей обработки как минимум 1 значение — количество удаляемых записей.

Собственно, сам текст получившейся процедуры:

create procedure usp_clearlog
	--параметр имени таблицы
	@tablename nvarchar(100),
	--параметр имени столбца даты
	@fieldname nvarchar(100),
	--параметр конечной даты
	@hidate nvarchar(100),
	--параметр начальной даты
	@lowdate nvarchar(100) = '01.01.1900',
	--количество удаляемых записей
	@items int = '10000'
	
as
begin
	begin try
	--проверка корректности ввода дат
		if (convert(date,@hidate, 103) >= convert(date,@lowdate, 103))
		begin
			set nocount on
			--переменная общего количества записей, удовлетворяющих условиям дат
			declare @count table (test int)
			--переменная главного запроса на удаление записей
			declare @sql nvarchar(max)
			--переменная sql запроса для вычисления переменной @count
			declare @sql1 nvarchar(max)
		
			set @sql1 = 'select count(' + @fieldname + ') from dbo.' + @tablename + ' where convert(date, ' + @fieldname + ') between convert(date, ' + '''' + @lowdate + '''' + ', 103) and convert(date,' + '''' + @hidate + '''' + ', 103)'
			insert into @count exec sp_executesql @sql1
			--select @sql1
			--проверка наличия записей за диапазон дат
			if ((select test from @count) > 0)
			begin
				--проверка размера порции удаления
				if ((select test from @count) > @items)
				begin
					set @sql = 'delete from dbo.' + @tablename + ' where ' + @fieldname + ' in (select top(' + convert(nvarchar,@items) + ') ' + @fieldname + ' from dbo.' + @tablename+ ' where CONVERT(date, ' + @fieldname + ', 103) between CONVERT(date, ' + '''' +  @lowdate + '''' + ', 103) and CONVERT(date, ' + '''' + @hidate + '''' + ', 103) order by ' + @fieldname + ')'
					select @items
					exec sp_executesql @sql
				end
				else
				begin
					set @items = (select test from @count)
					set @sql = 'delete from dbo.' + @tablename + ' where ' + @fieldname + ' in (select top(' + convert(nvarchar,@items) + ') ' + @fieldname + ' from dbo.' + @tablename+ ' where CONVERT(date, ' + @fieldname + ', 103) between CONVERT(date, ' + '''' +  @lowdate + '''' + ', 103) and CONVERT(date, ' + '''' + @hidate + '''' + ', 103) order by ' + @fieldname + ')'
					select @items
					exec sp_executesql @sql
				end
				
			end
			else
			begin
				select '-1' --данных не найдено
			end
		end
		else
		begin
			select '-2' --начальная дата больше конечной
		end
	end try
	begin catch
		select '-3' --проблемы в хранимой процедуре
	end catch
end
go


Ну и описание:

Процедура принимает 5 параметров, 2 из которых необязательны.
Обязательные:
@tablename – имя таблицы
@fieldname – имя столбца
@hidate – дата (по заданию – ДатаУдаления)
Необязательные:
@lowdate – ранняя дата удаления(по заданию – ДатаУдаленияПо), по умолчанию имеет значение '01.01.1900'.
@items – количество удаляемых записей (по заданию – Порция), по умолчанию имеет значение 10000

По завершению процедура вернёт или количество удалённых записей, которое в случае если записей больше «порции» будет равно как раз этой самой «порции» удаляемых за 1 проход данных или один из «флагов», описанных ниже.

Пример:
Удаление одной записи за период с 01,05,2008 по 26,05,2008 включительно. (Удаляется самая поздняя запись)
exec usp_datetest @tablename = 'test_info'
, @fieldname = '_fld130'
, @hidate = '26.05.2008'
, @items = '1'
, @lowdate = '01.05.2008'

Удаление всех или 10000 записей за определённое число
exec usp_datetest @tablename = 'test_info'
, @fieldname = '_fld130'
, @hidate = '26.05.2008'
, @lowdate = '26.05.2008'

Удаление всех записей, начиная с определённого числа
exec usp_datetest @tablename = 'test_info'
, @fieldname = '_fld130'
, @hidate = '26.05.2008'

Возвращаемые значения
Положительное число – количество удалённых записей
‘-1’ – данных для удаления не найдено
‘-2’ – ошибка при вводе дат. Ранняя дата больше поздней.
‘-3’ – глобальная ошибка процедуры.

0 комментариев

Только зарегистрированные и авторизованные пользователи могут оставлять комментарии.