程序员人生 网站导航

一个sybase函数(1)

栏目:sybase时间:2013-12-16 08:42:48

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;

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐