sybase函数例子(1)
Sybase
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;
?