15.5 ADD_FILTERプロシージャ

このプロシージャは、フィルタ・コレクションにフィルタを追加します。

構文

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            varchar2,
    p_null_result       in            boolean  default false,
    p_is_case_sensitive in            boolean  default true );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            varchar2,
    p_to_value          in            varchar2,
    p_null_result       in            boolean default false,
    p_is_case_sensitive in            boolean default true );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_values            in            wwv_flow_t_varchar2,
    p_null_result       in            boolean default false,
    p_is_case_sensitive in            boolean default true );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            number,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            number,
    p_to_value          in            number,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_values            in            wwv_flow_t_number,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            date,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            date,
    p_to_value          in            date,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            timestamp,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            timestamp,
    p_to_value          in            timestamp,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            timestamp with time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            timestamp with time zone,
    p_to_value          in            timestamp with time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            timestamp with local time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            timestamp with local time zone,
    p_to_value          in            timestamp with local time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_interval          in            pls_integer,
    p_interval_type     in            t_filter_interval_type,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_search_columns    in            t_columns,
    p_is_case_sensitive in            boolean default false,
    p_value             in            varchar2 );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_sql_expression    in            varchar2 );

パラメータ

表15-3 ADD_FILTERプロシージャのパラメータ

パラメータ	説明
p_filters	フィルタ配列。
p_filter_type	フィルタのタイプ - t_filter_type定数のいずれかを使用します。
p_column_name	このフィルタを適用する列。
p_value	1つの値を必要とするフィルタ(たとえば、等しいや次より大きい)の値。
p_values	INフィルタまたはNOT INフィルタの値の配列。
p_from_value	範囲を必要とするフィルタ(たとえば、次の範囲内)の下限値。
p_to_value	範囲を必要とするフィルタ(たとえば、次の範囲内)の上限値。
p_interval	日付フィルタの間隔(たとえば、過去X月)。
p_interval_type	日付フィルタの間隔タイプ(月や日)。
p_sql_expression	フィルタとして使用する汎用SQL式。
p_null_result	実際の列値がNULLのときに返される結果。
p_is_case_sensitive	このフィルタが大/小文字を区別するかどうか。
p_search_columns	行検索フィルタを適用する列のリスト。

例

declare
    l_filters     apex_exec.t_filters;
    l_context     apex_exec.t_context;
begin
    apex_exec.add_filter(
        p_filters     => l_filters,
        p_filter_type => apex_exec.c_filter_eq,
        p_column_name => 'ENAME',
        p_value       => 'KING' );

   apex_exec.add_filter(
       p_filters     => l_filters,
       p_filter_type => apex_exec.c_filter_gt,
       p_column_name => 'SAL',
       p_value       => 2000 );

   l_context := apex_exec.open_web_source_query(
       p_module_static_id => '{web source module static ID}', 
       p_filters          => l_filters
       p_max_rows         => 1000 );

       while apex_exec.next_row( l_context ) loop
           -- process rows here ...
       end loop;

  apex_exec.close( l_context );
exception
     when others then
        apex_exec.close( l_context );
        raise;    
end;