Sybase IQ 自定义表备份

-- 初始化配置表
if object_id('t_sz_backup_tables') is not null
drop table t_sz_backup_tables
go
create table t_sz_backup_tables(
  tableName  varchar(60),
  datetype   int,
  begintime  datetime null,
  endtime    datetime null,
  status     int null,
  redes      varchar(200) null,
  indestatus int null,
  order_no   int null,
  isused     int default 1)
  
go
create unique index inx_uni on t_sz_backup_tables(tableName)
go

insert into t_sz_backup_tables(tableName,datetype,order_no) values('t_zbk_o2didf_ck',2,1)

 

-- 程序

if object_id('fn_backup_tables') is not null
drop procedure fn_backup_tables
go
create procedure fn_backup_tables(
@datadate char(8),
@datetype integer,
@reccount integer output,
@usedtime integer output,
@retcode integer output,
@retdesc char(200) output)
/*--******************************************************************************************************************/ 
 --  功能说明：
 --        读取配置表获取需要备份的表名，备份数据到 表名+yyyymm[dd] 表中
 --  参数说明：
 --       1、输入 
 --             @datadate 会计日期             
 --*******************************************************************************************************/
as
declare @tableName    varchar(60)    -- 源表表名
declare @cursorcount  int            -- 循环总数
declare @i_count      int            -- 循环变量
declare @c_backupName varchar(70)    -- 备份表名
declare @c_exesql     varchar(1000)  -- 动态语句
declare @i_zq         int            -- 执行周期 : 1 每天 2每月 3每季 4每年
declare @i_datetype   int            -- 游标变量
declare @i_errorNum   int            -- 错误数量统计
declare @dt_begintime datetime         -- 处理开始时间
declare @dt_endtime   datetime         -- 处理结束时间
begin
	--**************变量定义及初始化*************************************************************************************
  -- 定义局部变量
  select @dt_begintime = getdate()
	-- 如果年最后一天 @i_zq = 4
	if year(dateadd(day,1,@datadate))=year(@datadate)+1
	select @i_zq = 4
	-- 如果季度最后一天 @i_zq = 3
	else if Quarter(dateadd(day,1,@datadate))<>Quarter(@datadate)
	select @i_zq = 3
	-- 如果月最后一天 @i_zq = 2
	else if month(dateadd(day,1,@datadate))<>month(@datadate)
	select @i_zq = 2
	-- 否则@i_zq = 1 即每天执行
	else
	select @i_zq = 1
	select @retcode = 0
	select @i_errorNum = 0
	-- 根据执行日期是否为年末、季末、月末确定需备份的表
  select tableName,datetype ,rank()over(order by order_no) as rankno 
    into #temp 
    from t_sz_backup_tables 
   where isused = 1
     and (status <> 0 or status is null)
     and datetype <= @i_zq
  -- 初始化循环变量
  select @i_count = 1
  select @cursorcount = count(1) from #temp 
  while (@i_count<=@cursorcount)
    begin
    	-- 获取表名
    	select @tableName = tableName,@i_datetype = datetype from #temp where rankno = @i_count
    	select @reccount = 0
      update t_sz_backup_tables set begintime = getdate(),status=null,endtime=null,redes=null where tableName = @tableName
    	if object_id(@tableName) is not null
    	  begin
		    	-- 生成备份表名
		    	if @i_datetype = 1 
		    	select @c_backupName = @tableName+'_'+@datadate
		    	else 
		    	select @c_backupName = @tableName+'_'+substring(@datadate,1,6)
		    	-- 备份表存在则删除——获取最新表结构
		    	if object_id(@c_backupName) is not null
		    	  begin
		    	  	select @c_exesql = 'drop table '+@c_backupName
		    	  	execute (@c_exesql)
		    	  	-- 异常检测
		    	  	select @retcode = @@error,@reccount = @reccount+@@rowcount
		    	  	if @retcode<>0
		    	  	  begin
		    	  	  	update t_sz_backup_tables set status=1,endtime=getdate(),redes='drop table '+@c_backupName+' failed!'
		    	  	  	 where tableName = @tableName
		    	  	    select @i_errorNum = @i_errorNum +1
		    	  	    select @i_count = @i_count + 1
		    	  	    continue
		    	  	  end
		    	  end
		    	-- 创建备份表
		    	select @c_exesql = 'select * into '+@c_backupName +' from '+@tableName +' where 1=2'
		    	execute (@c_exesql)
		    	-- 异常检测
		    	select @retcode = @@error,@reccount = @reccount+@@rowcount
		    	if @retcode <>0
		    	  begin
		    	  	update t_sz_backup_tables set status=1,endtime=getdate(),redes='create table '+@c_backupName+' failed!'
		    	  	 where tableName = @tableName
		    	  	select @i_errorNum = @i_errorNum +1
		    	  	select @i_count = @i_count + 1
		    	  	continue
		    	  end
		    	-- 备份数据到历史表
		    	select @c_exesql = 'insert into '+@c_backupName+' select * from '+@tableName
		    	execute (@c_exesql)
		    	-- 异常检测
		    	select @retcode = @@error,@reccount = @reccount+@@rowcount
		    	if @retcode<>0 
		    	  begin
		    	  	update t_sz_backup_tables set status=1,endtime=getdate(),redes='insert into '+@c_backupName+' failed!'
		    	  	 where tableName = @tableName
		    	  	select @i_errorNum = @i_errorNum +1
		    	  	select @i_count = @i_count + 1
		    	  	continue
		    	  end
		    	-- 执行成功记录日志
		    	update t_sz_backup_tables set status=0,endtime=getdate(),redes='backup into '+@c_backupName+' success!' 
		    	  	 where tableName = @tableName
		    end
		  else
		    begin
		    	update t_sz_backup_tables set status = 1,endtime=getdate(),redes = convert(char(20),getdate(),120)+':backup failed!source table '+@tableName+' not found!' 
		    	 where tableName = @tableName
		    	select @i_errorNum = @i_errorNum + 1
		    	select @i_count = @i_count + 1
		    	continue
		    end
    	select @i_count=@i_count+1
    end
  --*******3.<操作结束,退出>*******************************************************************************
  select @dt_endtime = getdate()
  select @usedtime = datediff(ss,@dt_begintime,@dt_endtime)
  if @i_errorNum <>0
  begin
  	select @retcode = 1
  	select @retdesc = convert(char,@i_errorNum)+' tables backup failed,'+convert(char,@cursorcount-@i_errorNum)+' backup success,please check up the table t_sz_backup_tables!'
  	return @retcode 
  end
  else 
    begin
    	select @retcode = 0
    	select @retdesc = convert(varchar,@dt_endtime,120)+'：procedure fn_backup_tables '+convert(char,@cursorcount)+' tables was backuped success！'
    	return @retcode
    end
end
go