欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

sybase函数事例(1)

更新时间: 2014-01-05 02:21:19 责任编辑: Author_N1

 

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;

 

上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:21:19
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/8808/
本文WWW.DOC100.NET DOC100.NET版权所有。