sybase函数例子(1)

最新推荐文章于 2019-04-07 14:11:10 发布

原创最新推荐文章于 2019-04-07 14:11:10 发布 · 262 阅读

0 ·

CC 4.0 BY-SA版权

数据库_Sybase 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了一个在Sybase数据库中实现的分表函数fn_hw_PreStatGetTableIdx。该函数通过输入表名、开始时间和结束时间来确定需要查询的具体分表。支持按天分表，并返回指定时间范围内所有相关子表的后缀。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;