日日是Oracle APEX: 4月 2020

2020年4月24日金曜日

Oracle APEX 20.1のダウンロード版がリリースされました

Oracle APEX 20.1のダウンロード版が4月23日にリリースされました。ダウンロード版のリリースに関するオラクルの発表(英語)はこちらです。

Oracle APEX 20.1はapex.oracle.comにて、すでに利用可能になっており、それを元に20.1の新機能の紹介をこちらの記事に書いています。提供される機能は基本的に同じなのですが、１つだけ、ダウンロード版ならではの新機能があります。大変便利なその機能を以下に紹介します。

アプリケーションのバックアップの管理

アプリケーションのバックアップを管理する機能が追加されました。日次でアプリケーションの更新がチェックされ、最終のバックアップから更新があるアプリケーションはバックアップが取得されます。また、手動でバックアップを取得することも可能です。自動バックアップについては、Oracle APEXの管理サービスにログインして構成します。

管理サービスのインスタンスの管理から機能構成を開きます。アプリケーション開発のセクションに含まれるアプリケーション当たりのバックアップ数を設定します。設定可能な値は０から３０までで、０もしくは空欄(null)にすると自動バックアップは行われません。設定値を超える古いバックアップは、日次のメンテナンス処理によって削除されます。初期値として２５が設定されているため、デフォルトで自動バックアップの機能は有効になっています。

手動でのバックアップの取得および取得されたバックアップの操作は、それぞれのアプリケーションのユーティリティからバックアップの管理を呼び出すか、

アプリケーション・ビルダーのワークスペース・ユーティリティに含まれるバックアップの管理を呼び出します。

ワークスペース・ユーティリティのバックアップの管理を呼び出したときは、アプリケーション名の一覧が表示されます。ここでアプリケーション名をクリックすると、アプリケーションのユーティリティから呼び出されるバックアップの管理に遷移します。

アプリケーションのバッックアップの管理を開くと、取得済みのバックアップが一覧されます。

バックアップの作成をクリックすると、コメントの入力を求められます。バックアップをクリックするとバックアップが取得されます。

最終のバックアップから変更がない場合は、以下のように変更がないと通知され、バックアップは取得されません。

取得されたバックアップはサマリーの変更(変更のサマリーが正しい日本語でしょう)として、直前のバックアップからの更新内容のサマリーが表示されます。そして、それぞれのバックアップを対象として、リストア、ダウンロード、詳細の表示、ロックの管理、削除の操作を行うことができます。

リストアを実行すると以下の画面が開きます。アプリケーションのインポートと同様の選択肢が示され、既存のアプリケーションを上書きするか、新規のアプリケーションとしてリストアすることができます。

ダウンロードを実行すると、アプリケーションのエクスポートと同様にファイルがダウンロードされます。アプリケーションのエクスポートとは異なり、単一のSQLファイルがZIPで固められています。20.1のアプリケーション・エクスポートは、ZIPとしてエクスポートされるときはコンポーネント毎のSQLファイルに分割した上でZIPで固められます。単一のファイルとしてエクスポートされるときはSQLファイルの形式です。

詳細の表示を実行すると、直前のバックアップからの差分情報が表示されます。

ロックの管理にてバックアップをロックすることで、日次処理による削除対象から除くことができます。

削除の実行で、バックアップを手動で削除することができます。バックアップの削除の実施には注意が必要です。時間にそって、A,B,Cとバックアップを取得していて中間のバックアップBを削除すると、残りのバックアップはA,Cになります。バックアップCの差分情報はBとCの間にあった変更です。バックアップBが削除されても、差分情報はそのままBとCの差分を表示し、AとCの差分には置き換えられないようです。バックアップ自体は差分ではないので、バックアップBを削除してもバックアップCのリストアは問題なく実施可能です。つまりバックアップが削除されていると、サマリーの更新の情報はすべての差分を表していない場合があります。

アプリケーションごとのバックアップの画面、右下にリソース・セクションがあり、すべてのバックアップとすべてのバックアップの詳細へのリンクがあります。すべてのバックアップをクリックすると、ワークスペース・ユーティリティのバックアップの管理へ遷移します。すべてのバックアップの詳細をクリックすると、バックアップを選んで指定する詳細の表示の情報を、すべてのバックアップを対象として表示されます。

今まではアプリケーション・ギャラリに含まれているAPEXアプリケーション・アーカイブをバックアップの管理に使用することができました。このアプリケーションは20.1でも継続して使用することができます。とはいえ、標準機能の利用に移行することをお勧めします。

完

Emailキャンペーンを行うアプリケーションの作成(2) - APEXアプリケーションの作成

こちらの記事の続きになります。

OCI Email Deliveryを通してOracle APEXから電子メールを送信できるようになりました。これから、Salim Hlayelの記事にある電子メールによるキャンペーンを行うアプリケーションを作ります。

スキーマを定義する

Oracle APEXのアプリケーションを作成する前に、データベースに表を作成します。

SQLワークショプのユーティリティのクイックSQLを開きます。

クイックSQLの以下のモデルから、表を作成します。

#prefix: cust
#language: ja
customers /insert 10
  name 
  email
  gender vc6 /values 男性, 女性
  marital status /values 既婚, 死別, 独身
  country vc255 /values 米国, 日本, 英国

モデルを画面左側に書き込み、SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次クリックします。

SQLスクリプトを保存をクリックするとポップアップが開きます。

スクリプト名を入力してスクリプトの保存をクリックします。

スクリプトを保存したのち、レビューおよび実行をクリッックします。

レビューおよび実行をクリックすると、SQLワークショップのSQLスクリプトに含まれるスクリプト・エディタへ画面が遷移します。生成されたスクリプトを編集できます。

今回は編集は不要です。そのまま実行をクリックします。

実行内容の確認を求められます。即時実行をクリックします。

13行が成功していてエラーが無いことを確認します。

アプリケーションの作成をクリックします。

アプリケーションを作成する

SQLスクリプトの実行結果が表示されている画面の、右上にあるアプリケーションの作成をクリックします。

確認画面が表示されるので、再度、アプリケーションの作成をクリックします。

アプリケーション作成ウィザードが起動します。

作成するアプリケーションの名前は電子メールキャンペーンとしています。

今回はホームページを使用しないため、編集をクリックしてダイアログを開きページを削除します。残りは対話レポートのページだけなので、自動的にレポートのページがホームになります。その他はデフォルトから変更しません。

アプリケーションの作成をクリックします。

アプリケーションが作成されると、開発画面に遷移します。作成されたアプリケーションを実行します。

アプリケーションの実行をクリックします。

アプリケーションへのサインインを要求されます。

デフォルトでは、Oracle APEXのワークスペースに登録されているユーザーによって認証されます。開発画面へのログインに使用したユーザー名とパスワードを入力してください。

クイックSQLを使って表を作成した際に、顧客のサンプル・データとして10行投入しています。ですので10人分の顧客がリストされています。

この顧客の中から、独身男性を選択します。

Gender(性別)のヘッダーをクリックしフィルタ条件として男性を選びます。続けて、Marital Status(婚姻状況)のヘッダーをクリックし、独身を選びます。フィルタ条件は、アクション・メニューに含まれるフィルタからも設定できます。どのようなフィルタ条件を設定しても、これ以降の作業に影響はありませんので、好みのフィルタ条件を設定して構いません。

このように対話モード・レポートでフィルタ条件を指定してリストされた顧客に、電子メールを送付する機能を作成します。

電子メールのテンプレートを作成する

キャンペーンとして送信する電子メールのテンプレートを作成します。

テンプレートは置き換え可能な顧客名や商品名の記述を含みます。テンプレートに含まれる顧客名や商品名といった情報は、電子メールとして送信する際に実際のデータで置換します。

共有コンポーネントのユーザー・インターフェースに含まれる、電子メール・テンプレートを開きます。

定義済みの電子メール・テンプレートの一覧画面が開きます。

電子メール・テンプレートの作成をクリックします。

想定しているキャンペーンに即したテンプレートを登録します。

識別として、テンプレート名、静的識別子、電子メールの件名を設定します。テンプレート名、静的識別子は英数字限定です。

テンプレート名: New Year Promotion from Japan Branch
静的識別子: NEW_YEAR_PROMOTION_FROM_JAPAN_BRANCH
電子メールの件名: 新春プロモーション - 東京

画面では以下のようになります。

HTMLフォーマットでの電子メールのヘッダー、本文、フッターを設定します。

ヘッダー

<b style="font-size: 24px;">New Year Promotion!</b>

本文

<b>こんにちは #CUSTOMER#さん</b><br>
<br>
<b>ただいま私たちのオンラインショップからの購入に限り、最大<span style="color: red;">75%</span>の割引を実施しております:</b><br>
<br>
<table width="100%">  
  <tr>
    <th align="left">セール期間の開始</th>
    <td>#START_DATE#</td>
  </tr>
  <tr>
    <th align="left">セール期間の終了</th>
    <td>#END_DATE#</td>
  </tr>  
  <tr>
    <th align="left" valign="top">対象店舗</th>
    <td>#LOCATION#</td>
  </tr>  
  <tr>
    <th align="left" valign="top">ご案内</th>
    <td>#NOTES#</td>
  </tr>
    <tr>
    <th align="left" valign="top">対象製品</th>
    <td>#ITEMS!RAW#</td>
  </tr>
</table>
<br>
<b>数量に限りがこざいますので、ご購入はお早めに。</b><br>
<br>

フッター

<a href="#MY_APPLICATION_LINK#">This App created proudly using Oracle APEX</a>.

画面では以下のようになります。

置換文字列は#CUSTOMER#のように、前後に'#'を付加して表現します。

CUSTOMERとして与えた文字列は、HTMLとしてエスケープ(例えば>を>、"を"とする)した上で置き換えられます。これは、クロスサイトスクリプティングというセキュリティ上のリスクへの対応です。#ITEMS!RAW#として!RAWを指定すると、エスケープ処理は抑制されます。例えば置き換える文字列に<br>が含まれていると、デフォルトでは<br>と画面にそのまま表示されますが、!RAWが指定されるとHTMLタグ本来の意味である改行として扱われます。さらに、プレーン・テキスト・フォーマットの指定にある#ITEMS!STRIPHTML#のように!STRIPHTMLが指定されると、HTMLのタグ自体が置き換え対象から除外されます。<br>であれば、<br>が丸ごと除外されます。

電子メール・テンプレートの説明はマニュアルのこちら、置換文字列の説明はこちらにあります。

プレーン・テキスト・フォーマット

最近はあまりないと思いますが、HTMLの表示ができないクライアント向けにプレーン・テキスト・フォーマットでの本文を設定します。

Hello #CUSTOMER#,
This email is to remind you of an upcoming event you are associated with.
Sale Starts: #START_DATE#
Sale Ends:   #END_DATE#
Location:    #LOCATION#
Notes:       #NOTES#
Items:       #ITEMS!STRIPHTML#
View additional details at: #MY_APPLICATION_LINK#

画面では以下のようになります。

テンプレートの設定は以上です。

サンプルAPIの使用状況

最後にサンプルAPIの使用状況を確認しておきます。

このテンプレートの使用を前提とした、APEX_MAILパッケージのSENDプロシージャの記述方法が示されます。このテンプレートが、CUSTOMER, END_DATE, ITEMS, LOCATION, MY_APPLICATION_LINK, NOTES, START_DATEの置換文字列を含んでいることがわかります。

以上で、電子メール・テンプレートの作成をクリックし、テンプレートを作成します。

電子メール・テンプレートが作成されました。

電子メールを送信するフォームを作成する

対話レポートで選択済みの顧客を対象として、キャンペーンを案内する電子メールを一括で送信するプロシージャーを作成します。

以下のコードをSQLワークショップのSQLコマンドに貼り付け実行すると、対話レポートで選択済みの顧客に電子メールを送信するプロシージャーsend_chanpaign_emailが作成されます。電子メールの本文は、登録したテンプレートをそれぞれの顧客名と、キャンペーン開始日(p_start_date)、キャンペーン終了日(p_end_date)、対象店舗(p_location)、案内文(p_notes)、対象商品(p_items)で穴埋めします。

	create or replace procedure send_campaign_email(
	p_start_date in date
	,p_end_date in date
	,p_location in varchar2
	,p_notes in varchar2
	,p_items in varchar2
	)
	is
	l_context apex_exec.t_context;
	l_emailsidx pls_integer;
	l_namesids pls_integer;
	l_region_id number;
	begin
	-- 対話モード・レポートのリージョンIDを取得する。
	select region_id
	into l_region_id
	from apex_application_page_regions
	where application_id = v('APP_ID')
	and page_id = 1
	and static_id = 'CUSTOMERS';

	-- 指定した対話レポートに設定済みのフィルタ条件で検索し、結果セットを l_context として取得する。
	l_context := apex_region.open_query_context(
	p_page_id => 1
	,p_region_id => l_region_id
	);

	-- EMAILとNAMEの値を取り出すためのインデックスを取得する。
	l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
	l_namesids := apex_exec.get_column_position( l_context, 'NAME' );

	-- 結果セットより一行つづ取り出し、それぞれキャンペーンのメールを送信する。
	while apex_exec.next_row( l_context ) loop
	apex_mail.send(
	p_to => apex_exec.get_varchar2( l_context, l_emailsidx )
	,p_from => '承認済送信者の電子メール・アドレスに置き換える'
	,p_template_static_id => 'NEW_YEAR_PROMOTION_FROM_JAPAN_BRANCH'
	,p_placeholders => '{' \|\|
	' "CUSTOMER":' \|\| apex_json.stringify( apex_exec.get_varchar2( l_context, l_namesids )) \|\|
	' ,"START_DATE":' \|\| apex_json.stringify( p_start_date ) \|\|
	' ,"END_DATE":' \|\| apex_json.stringify( p_end_date ) \|\|
	' ,"LOCATION":' \|\| apex_json.stringify( p_location ) \|\|
	' ,"NOTES":' \|\| apex_json.stringify( p_notes ) \|\|
	' ,"ITEMS":' \|\| apex_json.stringify( p_items ) \|\|
	' ,"MY_APPLICATION_LINK":' \|\| apex_json.stringify( apex_mail.get_instance_url \|\| apex_page.get_url( 1 )) \|\|
	'}');
	end loop;

	apex_exec.close( l_context );
	apex_mail.push_queue; -- 即座にメールを送信する。テスト用途でありプロダクションでは推奨されない。
	exception
	when others then
	apex_exec.close( l_context );
	raise;
	end send_campaign_email;

view raw send_campain_email.sql hosted with ❤ by GitHub

フォームを作成する

作成したプロシージャを呼び出し、電子メールを送信するフォームを作成します。

ページの作成をクリックします。

ページの作成のダイアログでレガシー・ページを開き、ローカル・プロシージャのフォームを選択します。

ページ番号として３、ページ名として電子メールの送信、ページ・モードはモーダル・ダイアログを選択します。ページ・モードがモーダル・ダイアログなので、ナビゲーションのブレッドクラムの使用、ナビゲーションの使用ともにOFFになります。

次へ進みます。

作成するページに含まれるフォームを送信したときに呼び出されるプロシージャを指定します。

プロシージャの所有者には現在作業中のワークスペースに紐づいたスキーマがデフォルトで設定されます。変更の必要はありません。ストアド・プロシージャ名として、先ほど作成したプロシージャSEND_CAMPAIGN_EMAILを選びます。プロシージャを選択すると、その引数がフォームに含まれるプロシージャ引数の選択に一覧されます。

デフォルトでSEND_CAMPAIGN_EMAILプロシージャが持つすべての引数が含まれるので、それはそのままにします。ラベルと表示タイプをそれぞれ以下のように設定します。

P_START_DATE: キャンペーン開始日、日付ピッカー
P_END_DATE: キャンペーン終了日、日付ピッカー
P_LOCATION: 対象店舗、テキスト・フィールド
P_NOTES: 案内文、テキスト・フィールド
P_ITEMS: 対象商品、テキスト・フィールド

以上を設定したのち、作成をクリックします。

電子メールを送信するページが作成されます。

ページ・アイテムP3_ITEMSのタイプをHTMLの入力が可能なようにリッチ・テキスト・エディタに変更します。設定の書式はHTMLです。電子メール・テンプレートでは#ITEMS!RAW#としてHTMLをエスケープせずに埋め込む記述になっていますので、P3_ITEMS(ラベル名は対象商品)として記載したHTMLは、電子メールでもHTMLとしてレンダリングされて表示されます。

以前にページ・アテイムに入力された値が保存されないよう、すべてのページ・アイテムのセッション・ステートのストレージをリクエストごと(メモリーのみ)に変更します。

プロセス・ビューを開き、プロセスRun Stored Procedureを選択します。

識別のタイプをAPIの呼出しに変更し、設定のタイプとしてPL/SQL Procedure or Function、プロシージャまたはファンクションとしてSEND_CAMPAIGN_EMAILを選択します。パラメータはデフォルトで、適切なページ・アイテムが割り当たります。

以上で電子メールを送信するページは完成です。

対話モード・レポートから電子メールの送信ページを開く

電子メールの送信を行うページはモーダル・ダイアログとして作成されています。ですので、かならず基底となるページがあって、そのページからダイアログとしてページを開きます。

作成したページをダイアログとして開くボタンを、対話モード・レポートがあるページに追加します。

ページ１をページ・デザイナで開きます。そして、右ペインに表示されているレンダリング・ツリーのBreadcrumb Barに含まれる電子メールキャンペーンのリージョン上でコンテキスト・メニューを表示させ、ボタンの作成を実行します。

作成したボタンにたいして、識別のボタン名をSEND_MAIL、ラベルを電子メールの送信、レイアウトのボタン位置をNextとし、動作のアクションとしてこのアプリケーションのページにリダイレクト、ターゲットとして電子メールを送信するページを指定します。

ターゲットの設定は以下になります。

タイプはこのアプリケーションのページ、ページは３です。

対話モード・レポートの詳細の静的IDとして、CUSTOMERSを設定します。

以上でページの変更を保存し、実行します。

ボタン電子メールの送信をクリックすると、追加で作成したキャンペーンの電子メールを送信するダイアログが開きます。

これで電子メールによるキャンペーンを行うアプリケーションは完成です。

電子メールを送信する

独身男性を対象としたキャンペーンを想定して、電子メールを送信してみます。

対話モード・レポートでGenderを男性、Marital Statusを独身としてフィルタします。リストに現れるEmailはサンプル・データであるため、このままではメールの送信は確認できません。送信されたメールを受け取ることができる電子メール・アドレス（大抵はこの作業を行っている本人の電子メール・アドレスになるかと思います)を設定してください。サンプル・データの電子メール・アドレスは実際の電子メール送信には使わないでください。

電子メール・アドレスを修正した後、電子メールの送信ボタンをクリックし、ダイアログを開きます。

電子メールの穴埋め項目に与えるデータは任意です。思い思いのデータを入力して、右下の送信ボタンをクリックします。

以下のような電子メールの受信を確認できたら、動作確認も完了です。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/email-campaign.zip

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;