一个sybase函数(1)
create or replace function fn_hw_PreStatGetTableIdx
(
in p_sTblName varchar(60),
in p_BeginTime varchar(20),
in p_EndTime varchar(20)
)
returns varchar(4000)
begin
declare v_iFlag int;
declare v_iType int;
declare v_icount int;
declare v_iStarttime int;
declare v_iEndTime int;
declare v_iCurrent int;
declare v_iTmpTime int;
declare v_sTblSuff varchar(4000);
set v_sTblSuff = 0;
--判断传入的表名称是否在配置表中配置
select count(*) into v_icount from cfg_sub_table_para where sTableName = p_sTblName;
--从配置表获取分表方式
if ( v_icount <> 0 ) then
select iFlag,iType into v_iFlag,v_iType from cfg_sub_table_para
where sTableName = p_sTblName;
else
--如果没有配置默认为不分表
set v_sTblSuff = 0;
return v_sTblSuff;
end if;
--当不分表的时候,直接返回0,如果分表,将起始结束时间范围内的表后缀返回
if ( v_iFlag = 0 ) then
set v_sTblSuff = 0;
else
case v_iType
when 0 then --按天分表
if(p_BeginTime = ) then
set v_iCurrent = datediff(day,1970-01-01 00:00:00,getdate() + 1);
set v_sTblSuff = cast(v_iCurrent as varchar(128));
return v_sTblSuff;
end if;
set v_iStarttime = datediff(day,1970-01-01 00:00:00,convert(datetime,p_BeginTime,20));
set v_iEndtime = datediff(day,1970-01-01 00:00:00,convert(datetime,p_EndTime,20));
--循环将按天分表的表后缀拼接成字符串,以","分隔。
if ( v_iStarttime = v_iEndtime ) then
set v_sTblSuff = cast(v_iStarttime as varchar(128));
else
set v_iTmpTime = v_iStarttime;
--拼接返回值
while ( v_iTmpTime <> (v_iEndtime + 1) ) loop
if ( v_sTblSuff = 0 ) then
set v_sTblSuff = cast(v_iTmpTime as varchar(128));
else
set v_sTblSuff = v_sTblSuff||,||cast(v_iTmpTime as varchar(128));
end if;
set v_iTmpTime = v_iTmpTime + 1;
end loop;
end if;
else
--不在取值范围内默认为不分表
set v_sTblSuff = 0;
end case;
end if;
--返回结果
return v_sTblSuff;
end;