日日是Oracle APEX: 表をピボットさせて対話グリッドで操作する

2020年4月22日水曜日

表をピボットさせて対話グリッドで操作する

表題の相談を受けました。まだ十分ではないですが、何かの役に立つかもしれませんので、記事にしておきます。

画面に２つの対話グリッドがありますが、両方とも同じ表DEMO_PRODUCT_PRICESを参照しています。上の対話グリッドは、日付のデータが列になるように、対話グリッドに与えるSQLでピボット操作を行っています。下のグリッドは表DEMO_PRODUCT_PRICESをそのままソースとしています。最初に製品名OraPodsの日付ごとの時価を新規入力しています。その後、OraPodsのエントリを更新し、最後にOraPodsの行を削除しています。下にある対話グリッドを見ると、複数の行を対象にしたデータ操作が行われていることがわかります。

サンプルに使用している表定義

今回のサンプルを作成するにあたって、以下の表を作りました。使用している表はひとつだけです。

	create table demo_product_prices (
	id number generated by default on null as identity
	constraint demo_product_price_id_pk primary key,
	product_name varchar2(200),
	sampling_date varchar2(6),
	market_price number,
	constraint demo_product_prices_c1 check (regexp_like(sampling_date,'^(19\|20)[0-9][0-9](0[1-9]\|1[12])$')),
	constraint demo_product_prices_c2 unique (product_name, sampling_date)
	);

view raw create_demo_product_prices.sql hosted with ❤ by GitHub

表DEMO_PRODUCT_PRICESは、ある特定の製品PRODUCT_NAMEがSAMPLING_DATEである月に市場価格がいくらであったかを保持しています。

ピボット後の列の定義

列SAMPLING_DATEのデータをピボット処理に使用します。ピボットして列とするデータをDEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNSとして定義しています。

	create or replace package "DEMO_PRODUCT_PRICES_PKG" as
	type pivot_columns_t is table of varchar2(6);
	G_PIVOT_COLUMNS pivot_columns_t := pivot_columns_t();

	end "DEMO_PRODUCT_PRICES_PKG";
	/

	create or replace package body "DEMO_PRODUCT_PRICES_PKG" as
	begin
	for col in (
	/* dateで指定している月から */
	select to_char(
	date'2023-01-01' + numtoyminterval((level-1),'month'),
	'YYYYMM') as label
	from dual
	/* levelで指定している月数の行を生成する */
	connect by level <= 12
	)
	loop
	G_PIVOT_COLUMNS.EXTEND;
	G_PIVOT_COLUMNS(G_PIVOT_COLUMNS.LAST) := col.label;
	end loop;
	end "DEMO_PRODUCT_PRICES_PKG";
	/

view raw demo_product_prices_pkg.sql hosted with ❤ by GitHub

対話グリッドのソースSQL

製品名(PRODUCT_NAME)を縦軸、年月(SAMPLING_DATE)を横軸として、市場価格(MARKET_PRICE)を返すSQLは、おおよそ以下のようになります。

select * from 
  (
    select product_name as pid, product_name as product_name, sampling_date, market_price from demo_product_prices) 
    pivot (sum(market_price) for sampling_date in (
      '202301' as "202301",'202302' as "202302",'202303' as "202303",'202304' as "202304",'202305' as "202305",'202306' as "202306",'202307' as "202307",'202308' as "202308",'202309' as "202309",'202310' as "202310",'202311' as "202311",'202312' as "202312"
    )
  )

このピボット処理を行なうSQLを生成するPL/SQLコードを、対話グリッドのソースとして与えます。

ソースのタイプとしてSQL問合せを戻すPL/SQLファンクション本体を指定します。

	declare
	l_date varchar2(6);
	l_pivots varchar2(32767) := '';
	l_sql varchar2(32767) := '';
	begin
	for i in DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS.FIRST..DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS.LAST
	loop
	l_date := DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS(i);
	if length(l_pivots) > 0 then
	l_pivots := l_pivots \|\| ',';
	end if;
	l_pivots := l_pivots \|\| '''' \|\| l_date \|\| ''' as "' \|\| l_date \|\| '"';
	end loop;
	l_sql := 'select * from (select product_name as pid, product_name as product_name, sampling_date, market_price from demo_product_prices) pivot (sum(market_price) for sampling_date in ('
	\|\| l_pivots \|\| '))';
	return l_sql;
	end;

view raw ig-pivot-source.sql hosted with ❤ by GitHub

画面では、以下の設定になります。

対話グリッドの設定

select product_nam as pid, product_name as product_nameとして、同じPRODUCT_NAMEを２列にしているのは、PIDの方を対話グリッドの主キーとして扱うためです。PRODUCT_NAMEの方は通常のデータ入力に使用します。PIVOT句の利用では必ず集計関数を使わないといけないので、sum(market_price)として時価の合計にしています。PRODUCT_NAMEとSAMPLING_DATEの組み合わせで一意となる前提ですので、合計をとっても（値が１つしかないので)値は変わりません。PIVOT処理の列となる年月のデータはSAMPLING_DATEに含まれる値で変わります。

ソースとなるPL/SQLコードを定義して保存すると、対話グリッドに列が認識されます。列の中にPIDが含まれるので、タイプを非表示、問合せのみをON、主キーをONにします。

この他にはPRODUCT_NAMEは必須の値にしておくべきでしょう。

対話グリッドのプロセス定義

作成された対話グリッドでの操作を、実際の表DEMO_PRODUCT_PRICESへ反映させる必要があります。以下のPL/SQLコードを対話グリッドの変更処理を行うプロセスに設定します。

	declare
	l_date varchar2(6);
	l_price number;
	l_exist number;
	e_product_exist exception;
	begin
	case :APEX$ROW_STATUS
	when 'C' then
	/*
	* 新規行の挿入なので、すでに製品が登録されていないか確認する。登録されていれば例外をあげる。
	*/
	select count(*) into l_exist from demo_product_prices where product_name = :PRODUCT_NAME;
	if l_exist > 0 then
	raise e_product_exist;
	end if;
	/*
	* 未登録の製品なので、すべての列を行として挿入する。
	* 主キーとして製品名を返す。
	*/
	for i in DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS.FIRST..DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS.LAST
	loop
	l_date := DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS(i);
	l_price := nv(l_date);
	/* 時価のデータがあるときのみデータを書き込む。 */
	if l_price is not null then
	insert into demo_product_prices(product_name, sampling_date, market_price)
	values(:PRODUCT_NAME, l_date, l_price);
	end if;
	end loop;
	:PID := :PRODUCT_NAME;
	when 'U' then
	for i in DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS.FIRST..DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS.LAST
	loop
	l_date := DEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNS(i);
	l_price := nv(l_date);
	if l_price is null then
	/* 時価のデータがなければ、すでにあるデータを削除する。 */
	delete from demo_product_prices
	where product_name = :PRODUCT_NAME and sampling_date = l_date;
	else
	/* 既存レコードがあれば時価を更新、無ければ挿入する。 */
	merge into demo_product_prices p
	using (
	select
	:PRODUCT_NAME as product_name,
	l_date as sampling_date,
	l_price as market_price
	from dual
	) c
	on
	(
	p.product_name = c.product_name and p.sampling_date = c.sampling_date
	)
	when matched then
	update set p.market_price = c.market_price
	when not matched then
	insert (product_name, sampling_date, market_price)
	values (c.product_name, c.sampling_date, c.market_price);
	end if;
	end loop;
	when 'D' then
	/* ピボットされた行の削除は、対象製品の行の全削除を意味する。 */
	delete from demo_product_prices where product_name = :PRODUCT_NAME;
	end case;
	end;