ADD_FILTERプロシージャ

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            apex_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_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            apex_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            apex_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;

親トピック: APEX_EXEC