接触追跡用表関数

contact_tracing_table_func.sql

drop type t_cf_tab;
drop type t_cf_row;

-- 表関数contact_tracingの行となるタイプ
create type t_cf_row as object (
    contact_id    number
    , in_user_id  number
    , out_user_id number
    , duration_in_minutes number
    , start_time date
    , end_time   date
    , num_contact_times number
    , x number
    , y number
);
/

-- 表関数contact_tracingの表となるタイプ
create type t_cf_tab is table of t_cf_row;
/

-- 接触追跡の結果を返す表関数
create or replace function contact_tracing(
    p_user_id in number
    , p_start_time in date
    , p_end_time in date 
    , p_distance in number
    , p_time_tolerance_in_sec in number
    , p_chaining_tolerance_in_sec in number
) return t_cf_tab
as
    l_tab t_cf_tab := t_cf_tab();
begin
    for c in
    (
        SELECT
            ROWNUM AS contact_id,
            t.in_user_id,
            t.out_user_id,
            ROUND(t.duration / 60, 2) AS duration_in_minutes,
            t.start_time,
            t.end_time,
            t.num_contact_times,
            t.geom
        FROM
            TABLE(
                sdo_obj_tracing.get_all_durations(
                user_id                      => p_user_id
                , start_time                 => p_start_time
                , end_time                   => p_end_time
                , distance                   => p_distance
                , time_tolerance_in_sec      => p_time_tolerance_in_sec
                , chaining_tolerance_in_sec  => p_chaining_tolerance_in_sec
                -- 異なるユーザーから呼び出す場合は、スキーマ名による修飾が必要。
                , track_table_name => sys_context('userenv','current_schema') || '.track_geom'
                -- , track_table_name           => 'track_geom'
                , geom_column_name           => 'geom'
                , user_id_column_name        => 'user_id'
                , time_column_name           => 'capture_time'
                , date_as_number_column_name => 'capture_time_as_number'
                )
            ) t
        WHERE
            t.segment_or_all = 'ALL'
    )
    loop
        for p in (
            select p.x, p.y from table(sdo_util.getvertices(c.geom)) p
        )
        loop
            l_tab.extend;
            l_tab(l_tab.last) := t_cf_row(
                c.contact_id
                , c.in_user_id
                , c.out_user_id
                , c.duration_in_minutes
                , c.start_time
                , c.end_time
                , c.num_contact_times
                , p.x
                , p.y
            );
        end loop;
    end loop;
    return l_tab;
end;
/