日日是Oracle APEX: APEXコレクションの値を表関数で使うためにCROSS APPLYを使用する

表関数を実装するとAPI呼び出しの結果をSELECT文で扱うことができます。SQLのCROSS APPLYを使うと、APEXコレクションなどに保存した値を表関数に与えることができます。

少し前に、Oracle Spatialの接触追跡を使うAPEXアプリケーションを作成しました（こちらの記事）。この記事では（問題があったため）接触追跡はRESTサービスとして実装しています。その後、表関数でも接触追跡（SDO_OBJ_TRACING.GET_ALL_DURATIONSの呼び出し）が出来たので、その実装方法を紹介しています。（こちらの記事）。

この接触追跡の表関数を使って、CROSS APPLYの使い方を紹介します。

接触追跡を行う対象をAPEXコレクションCONTACT_USERSに保存します。APEXコレクションへの値の挿入、変更、削除には、対話グリッドを利用します。

作成したアプリケーションは以下のように動作します。

元記事で作成しているREST APIを使ったAPEXアプリケーションを改変します。表関数contact_tracingが作成済みであることを前提とします。

APEXコレクションの初期化

接触追跡の条件を保存するAPEXコレクションを初期化します。使用するAPEXコレクションの名前はCONTACT_USERSとします。これから対話グリッドのソースやマップのレイヤーのソース、その他のコードにもAPEXコレクション名を指定する箇所があるので、直書きを避けるためにアプリケーション定義の置換として、置換文字列を設定します。

アプリケーション定義の置換のセクションにて、置換文字列としてG_COLLECTION_NAME、置換値にCONTACT_USERSを設定します。

セッションの開始時に、このAPEXコレクションを初期化します。

共有コンポーネントのアプリケーション・プロセスを開きます。

アプリケーション・プロセスの一覧が表示されます。作成を実行します。

名前をコレクションの初期化、ポイントとして新規インスタンス(新規セッション開始時)を選択します。

次へ進みます。

APEXコレクションの作成または再初期化を行うAPI、APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTIONを呼び出すコードを記述します。

apex_collection.create_or_truncate_collection(

p_collection_name => :G_COLLECTION_NAME
);

次へ進みます。

新規にセッションが作成される際に必ず実行するため、条件タイプに何も指定しません。

プロセスの作成を実行します。

APEXコレクションを初期化するアプリケーション・プロセスが作成されました。

すでに接触追跡のアプリケーションを実行していると、セッションは開始済みになります。APEXコレクションCONTACT_USERSをアプリケーションで使用するには、一旦、実行中のアプリケーションからサインアウトし、サインインし直す必要があります。

アプリケーションの日時書式の設定

アプリケーション定義のグローバリゼーションのアプリケーションの日付書式をYYYY-MM-DD HH24:MI:SSに設定します。

これから記述するSELECT文などから、ファンクションto_dateやto_charの呼び出しを減らします。

ページの編集

これから接触追跡の結果を表示するページを編集します。

新たに対話グリッドのリージョンを作成します。

識別の名前はパラメータ、タイプとして対話グリッドを選択します。ソースのタイプにSQL問合せを選択し、SQL問合せとして以下を記述します。

	select
	seq_id id
	, n001 user_id
	, d001 start_time
	, d002 end_time
	, n002 distance
	, n003 time_tolerance_in_sec
	, n004 chaining_tolerance_in_sec
	from
	apex_collections
	where
	collection_name = :G_COLLECTION_NAME

view raw contact_tracing_ig_source.sql hosted with ❤ by GitHub

作成した対話グリッドのリージョンは、Bodyの直下に配置します。すでに作成されているリージョンパラメータは、後に削除します。

APEXコレクションのSEQ_IDは、列IDとして認識されます。これを主キーとして扱います。レンダリング・ツリーより列IDを選択し、識別のタイプを非表示、ソースの主キーをONに変更します。

対話グリッドの編集を有効にします。

リージョンパラメータを選択し、Attributesを開いて編集の有効をONにします。

APEXコレクションへの行の挿入、更新、削除を行うプロセスを変更します。

プロセス - 対話グリッド・データの保存を選択し、設定のターゲット・タイプをPL/SQL Codeに変更します。

挿入/更新/削除するPL/SQLコードに、以下のコードを記述します。

	begin
	case :APEX$ROW_STATUS
	when 'C' then
	:ID := apex_collection.add_member(
	p_collection_name => :G_COLLECTION_NAME
	, p_d001 => :START_TIME
	, p_d002 => :END_TIME
	, p_n001 => :USER_ID
	, p_n002 => :DISTANCE
	, p_n003 => :TIME_TOLERANCE_IN_SEC
	, p_n004 => :CHAINING_TOLERANCE_IN_SEC
	);
	when 'U' then
	apex_collection.update_member(
	p_collection_name => :G_COLLECTION_NAME
	, p_seq => :ID
	, p_d001 => :START_TIME
	, p_d002 => :END_TIME
	, p_n001 => :USER_ID
	, p_n002 => :DISTANCE
	, p_n003 => :TIME_TOLERANCE_IN_SEC
	, p_n004 => :CHAINING_TOLERANCE_IN_SEC
	);
	when 'D' then
	apex_collection.delete_member(
	p_collection_name => :G_COLLECTION_NAME
	, p_seq => :ID
	);
	end case;
	end;

view raw contact_tracing_ig_process.pls hosted with ❤ by GitHub

APEXコレクションはAPEXセッション毎に異なるため、更新の競合を考慮する必要はありません。そのため、失われた更新の防止はOFF、行のロックはNoにします。

次にマップ・リージョンを更新します。

マップ・リージョンContact Tracingのレイヤー接触を選択します。

ソースの位置をローカル・データベース、タイプをSQL問合せに変更し、SQL問合せとして以下を記述します。

	select
	r.x
	, r.y
	, r.end_time
	, r.contact_id
	, r.in_user_id
	,　　r.start_time
	, r.out_user_id
	, r.num_contact_times
	, r.duration_in_minutes
	, case
	when contact_id = 1 then
	'red'
	when contact_id = 2 then
	'green'
	when contact_id = 3 then
	'yellow'
	when contact_id = 4 then
	'blue'
	else
	'black'
	end col
	from
	(
	select
	n001 user_id
	, d001 start_time
	, d002 end_time
	, n002 distance
	, n003 time_tolerance_in_sec
	, n004 chaining_tolerance_in_sec
	from apex_collections
	where collection_name = :G_COLLECTION_NAME
	) p
	-- APEXコレクションの値を表関数に渡す。
	cross apply
	(
	table(
	contact_tracing(
	p.user_id
	, p.start_time
	, p.end_time
	, p.distance
	, p.time_tolerance_in_sec
	, p.chaining_tolerance_in_sec
	)
	)
	) r