一個sybase函數(shù)（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，如果分表，，將起始結(jié)束時間范圍內(nèi)的表后綴返回
    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));
           
            --循環(huán)將按天分表的表后綴拼接成字符串，以","分隔。
            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
            --不在取值范圍內(nèi)默認為不分表
            set v_sTblSuff = 0;
        end case;
    end if;
   
    --返回結(jié)果
    return v_sTblSuff;
end;

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學習有所幫助,可以手機掃描二維碼進行捐贈