|
到此筛选列和需要匹配的数值，就可以得到灵活控制了。还需要对存储在表CONDITION_OPERATOR中的数值，也就是记录筛选列和需要匹配的数值之间的关系的数值进行处理，以便能动态处理。下面只以需要匹配的数值的数据类型为日期类型的情况为例。 paper51.com 当需要匹配的数值的数据类型为日期类型时，如下： 内容来自论文无忧网 www.paper51.com function Comparison_Date ( paper51.com
p_row V_Simple_Condition%rowtype, paper51.com
p_field_ref varchar2, 内容来自论文无忧网 www.paper51.com p_comparison_field_ref varchar2 ) return varchar2is http://www.paper51.com l_comparison varchar2( 4000 ); 内容来自www.paper51.com l_operator varchar2( 2 ); 内容来自www.paper51.com
l_value_this_day date; copyright paper51.com
l_value_this_day_string varchar2( 1000 ); paper51.com l_value_next_day date; 内容来自论文无忧网 www.paper51.com l_value_next_day_string varchar2( 1000 ); 内容来自www.paper51.com begin http://www.paper51.com --省略部分代码 copyright paper51.com
if( p_comparison_field_ref is not null ) then paper51.com --省略部分代码 内容来自论文无忧网 www.paper51.com l_value_this_day_string := 'trunc( ' ||p_comparison_field_ref 内容来自www.paper51.com || ', ''dd'' )'; http://www.paper51.com l_value_next_day_string := 'trunc( ' ||p_comparison_field_ref http://www.paper51.com
|| ' + 1, ''dd'' )'; 内容来自论文无忧网 www.paper51.com else 内容来自www.paper51.com if ( p_row.Value is not null ) then copyright paper51.com
l_value_this_day := to_date( p_row.Value,g_date_format ); copyright paper51.com
l_value_this_day_string := 'to_date( ' http://www.paper51.com
|| '''' || p_row.Value || ''', ' paper51.com || '''' || g_date_format || ''' )' ; 内容来自www.paper51.com l_value_next_day := l_value_this_day + 1; http://www.paper51.com l_value_next_day_string := 'to_date( ' 内容来自www.paper51.com || '''' || to_char( l_value_next_day, g_date_format) || ''', ' paper51.com
|| '''' || g_date_format || ''' )' ; http://www.paper51.com end if; 内容来自论文无忧网 www.paper51.com end if; paper51.com case p_row.Operator_Code paper51.com when 'Equals' then copyright paper51.com
if( p_comparison_field_ref is not null ) then http://www.paper51.com if( p_row.Negated = 0 ) then paper51.com l_comparison := paper51.com '( ( ' || p_field_ref || ' is null ) and ' copyright paper51.com
|| '( ' || p_comparison_field_ref || ' is null ) )or ' paper51.com || '( ( ' || p_field_ref || ' >= ' ||l_value_this_day_string || ' )' 内容来自论文无忧网 www.paper51.com || ' and ' 内容来自论文无忧网 www.paper51.com || '( ' || p_field_ref || ' < ' ||l_value_next_day_string || ' ) )'; copyright paper51.com else paper51.com l_comparison := copyright paper51.com '( ( ' || p_field_ref || ' is null ) and ' http://www.paper51.com || '( ' || p_comparison_field_ref || ' is not null) ) or ' 内容来自www.paper51.com || '( ( ' || p_field_ref || ' is not null ) and ' http://www.paper51.com || '( ' || p_comparison_field_ref || ' is null ) )or ' paper51.com
|| '( ' || p_field_ref || ' < ' ||l_value_this_day_string || ' ) or ' 内容来自www.paper51.com || '( ' || p_field_ref || ' >= ' ||l_value_next_day_string || ' )'; paper51.com end if; paper51.com else 内容来自论文无忧网 www.paper51.com if( p_row.Value is null ) then 内容来自www.paper51.com if( p_row.Negated = 0 ) then 内容来自www.paper51.com l_comparison := p_field_ref || ' is null'; 内容来自www.paper51.com else 内容来自论文无忧网 www.paper51.com l_comparison := p_field_ref || ' is not null'; http://www.paper51.com
end if; http://www.paper51.com else copyright paper51.com if( p_row.Negated = 0 ) then http://www.paper51.com l_comparison := copyright paper51.com '( ' || p_field_ref || ' >= ' ||l_value_this_day_string || ' )' paper51.com
|| ' and ' paper51.com
|| '( ' || p_field_ref || ' < ' ||l_value_next_day_string || ' )'; copyright paper51.com else http://www.paper51.com l_comparison := 内容来自www.paper51.com '( ' || p_field_ref || ' is null )' copyright paper51.com || ' or ' 内容来自论文无忧网 www.paper51.com || '( ' || p_field_ref || ' < ' ||l_value_this_day_string || ' )' 内容来自www.paper51.com || ' or ' copyright paper51.com
|| '( ' || p_field_ref || ' >= ' ||l_value_next_day_string || ' )'; 内容来自论文无忧网 www.paper51.com end if; copyright paper51.com end if; http://www.paper51.com end if; 内容来自论文无忧网 www.paper51.com when 'Greater' then 内容来自论文无忧网 www.paper51.com
null; 内容来自论文无忧网 www.paper51.com if( ( p_comparison_field_ref is not null ) or (p_row.Value is not null ) ) then copyright paper51.com
if( p_row.Negated = 0 ) then 内容来自论文无忧网 www.paper51.com l_operator := '>='; copyright paper51.com else 内容来自www.paper51.com l_operator := '<'; copyright paper51.com end if; 内容来自论文无忧网 www.paper51.com l_comparison := p_field_ref || ' ' || l_operator ||' ' || l_value_next_day_string; http://www.paper51.com else 内容来自论文无忧网 www.paper51.com -- The value we'rechecking against is null, so we should have no matches. 内容来自论文无忧网 www.paper51.com l_comparison := g_false_condition; http://www.paper51.com end if; 内容来自论文无忧网 www.paper51.com when 'Less' then 内容来自www.paper51.com if( ( p_comparison_field_ref is not null ) or (p_row.Value is not null ) ) then 内容来自论文无忧网 www.paper51.com if( p_row.Negated = 0 ) then 内容来自论文无忧网 www.paper51.com l_operator := '<'; 内容来自www.paper51.com
else 内容来自论文无忧网 www.paper51.com l_operator := '>='; 内容来自www.paper51.com end if; 内容来自论文无忧网 www.paper51.com
l_comparison := p_field_ref || ' ' || l_operator ||' ' || l_value_this_day_string; http://www.paper51.com else copyright paper51.com -- The value we'rechecking against is null, so we should have no matches. http://www.paper51.com
l_comparison := g_false_condition; copyright paper51.com end if; http://www.paper51.com
else 内容来自论文无忧网 www.paper51.com raise_application_error ( g_error_number, copyright paper51.com
'Unexpected operator: ' || p_row.Operator_Code ); 内容来自论文无忧网 www.paper51.com end case; 内容来自www.paper51.com --省略部分代码 paper51.com return l_comparison; 内容来自www.paper51.com end; 内容来自www.paper51.com 总结一下，本接口设计的PL/SQL程序设计部分是通过如下过程实现了灵活控制筛选条件，并完成了数据筛选接口的基本部分的。 paper51.com
paper51.com |