Oracle APEX勉強会 - 色々なデータ操作方法を知ろう！

Oracle APEX勉強会色々なデータ操作方法を知ろう！中越祐治 Oracle Groundbreaker Advocate Oracle APEXユーザーグループ 2021年2月24日

1. Oracle APEXの移り変わり 2. Oracle APEXでのデータ操作 • 標準コンポーネント • フォーム
• 対話グリッド 3. 色々と実装してみましょう • マスター・ディテール • 積み上げ • 左右 • ドリルダウン • ジョインした表の操作 4. リモート・アクセスの構成 5. リモート・アクセスを使う本日のアジェンダ Copyright © 2021, Oracle and/or its affiliates 4

HTMLDB以前(〜2004) • HTMLの生成を、全てコードで行う。 • PL/SQL Gateway、 mod_plsqlと呼んでいたもの。 •
OWA_UTILなどの OWA_xxxxパッケージによるHTTPプロトコルの処理 • HTP, HTFパッケージによるHTML出力 • マニュアル「PL/SQL パッケージおよびタイプ・リファレンス」に記載。 • 現在も使えます。 HTMLDB(2004〜) • HTMLの生成は、テンプレートを元に行う。 • 一貫したUIを提供するテンプレートの集まりがテーマ。 • コードは不要になった。 • 表現の違いで大量のテーマとテンプレートを作成。 • ユニバーサル・テーマ以外は製品に含まれませんが、異なるテーマは現在も使えます。ユニバーサル・テーマ (2015〜) • 表現の違いはCSSで吸収し、テンプレートは１つにする。 • HTML5 + CSS3 • Bootstrapに似たグリッド • レスポンシブ・デザイン • 現在提供されている唯一のテーマです。対話グリッド(2016〜) • リージョンをコンポーネント化する。 • 対話グリッド • カード • Oracle JETチャート • カレンダ (FullCalender – Third Party) • テキスト・エディタ (CKEditor – Thrid Party) • コンポーネントの利用は、現在のトレンドです。 6 Copyright © 2021, Oracle and/or its affiliates Oracle APEXの移り変わり

見た目の変更でも、コードの変更 create or replace procedure prcemp as begin owa_util.mime_header('text/html',true,'utf-8'); htp.prn('<html><body><table>');
htp.prn('<tr><th>従業員番号</th><th>従業員名</th></tr>'); for c in (select empno, ename from emp) loop htp.prn('<tr><td>'); htp.prn(c.empno); htp.prn('</td><td>'); htp.prn(c.ename); htp.prn('</td></tr>'); end loop; htp.prn('</table></body></html>'); end prcemp; PL/SQL Gatewayを使ってみる Copyright © 2021, Oracle and/or its affiliates 7

当時のコードの活用 PL/SQL動的コンテンツ Copyright © 2021, Oracle and/or its affiliates 8
<html><body></body></html>の出力は除く

テンプレートとSQLを開発 HTMLDB – テーマ(テンプレートの集合)の導入 (1) Copyright © 2021, Oracle and/or
its affiliates 9 create or replace procedure prcemp2 as l_html_template constant varchar2(4000) := '<html>#HTML#</html>'; l_body_template constant varchar2(4000) := '<body>#BODY#</body>'; l_table_template constant varchar2(4000) := '<table>#TABLE#</table>'; l_row_template constant varchar2(4000) := '<tr>#ROW#</tr>'; l_header_template constant varchar2(4000) := '<th>従業員番号</th><th>従業員名</th>'; l_data_template constant varchar2(4000) := '<td>#EMPNO#</td><td>#ENAME#</th>'; l_buffer varchar2(32000); l_content varchar2(32000); c sys_refcursor; r emp%rowtype; l_sql varchar2(80) := 'select * from emp'; begin HTML(テンプレート)やSQLを定数にしていますが、これらはデータベースの表に保存されています。

置き換えるコードは共用 HTMLDB – テーマ(テンプレートの集合)の導入 (2) Copyright © 2021, Oracle and/or
its affiliates 10 begin owa_util.mime_header('text/html',true,'utf-8'); -- TABLEを作る -- ヘッダーの挿入 l_buffer := replace(l_row_template, '#ROW#', l_header_template); -- 行テンプレートをヘッダー・テンプレートで置き換える l_content := l_content || l_buffer; -- 出力バッファに追加する open c for l_sql; -- SQLを実行する loop -- データ行を追加する。 fetch c into r; exit when c%notfound; l_buffer := l_data_template; -- データ行のテンプレートを選択する l_buffer := replace(l_buffer, '#EMPNO#', r.empno); -- 従業員番号を置き換える l_buffer := replace(l_buffer, '#ENAME#', r.ename); -- 従業員名を置き換える l_buffer := replace(l_row_template, '#ROW#', l_buffer); -- 行テンプレートをデータ行で置き換える l_content := l_content || l_buffer; -- 出力バッファに追加する end loop; close c; l_buffer := l_content; l_content := replace(l_table_template, '#TABLE#', l_buffer); -- 表テンプレートをヘッダーとデータで置き換える l_buffer := l_content; l_content := replace(l_body_template, '#BODY#', l_buffer); -- 本文テンプレートを表で置き換える l_buffer := l_content; l_content := replace(l_html_template, '#HTML#', l_buffer); -- HTMLテンプレートを本文で置き換える htp.prn(l_content); end prcemp2;

多数のテーマから１つのテーマへユニバーサル・テーマの導入 Copyright © 2021, Oracle and/or its affiliates 11
ユニバーサル・テーマ = HTML5 + CSS3 + グリッド・システム(Bootstrap様) レスポンシブ・デザイン見た目を調整するために、テーマ(テンプレートのセット)が多数

テンプレートの編集によらない見かけの変更 CSSによるカスタマイズ Copyright © 2021, Oracle and/or its affiliates 12
テンプレート・オプション事前定義されたCSSクラスカスタム定義列CSSクラス CSSクラス

過去の互換性を保つ今でもユニバーサル・テーマ以外の利用は可能 Copyright © 2021, Oracle and/or its affiliates 13
例えば Material APEX – Google Material Design (https://material.io/) を元にテーマを作成 Vincent Morneauさん(Insum)作新規の作成はお勧めしません。すべてのテンプレートを、いちからすべて作るのは現実的ではありません。

テンプレートによる見かけの変更クラシック・レポートのテンプレート Copyright © 2021, Oracle and/or its affiliates 14
テンプレートの切り替えによって見かけを変更する機構は、現在も利用されています。

よりモダンな形式へ対話グリッドの導入 – リージョンのコンポーネント化 Copyright © 2021, Oracle and/or its
affiliates 15 • リージョン内はテンプレートを使わずブラウザ側でレンダリング • ページ全体の送信をせずに更新カード・リージョン、チャート(Oracle JET)、カレンダ(FullCalendar)など。今後もコンポーネントは追加される予定です。

対話グリッド(Oracle APEX 5.1〜) フォーム(Oracle APEX 19〜) データ操作に使用するコンポーネント Copyright © 2021,
Oracle and/or its affiliates 17 • それまでの対話モード・レポートはデータの更新ができない。 • ページの全体の送受信を行わず、データのCRUD操作を行う。 • データ更新用の専用リージョンは無かった (プロセスがあるだけ)。 • 送信されたページを処理する。 • 設定は対話グリッドを踏襲している。

データの読み出し元 – 対話グリッド、フォーム共通ローカル・データベース • 表/ビュー • SQL問合せ • SQL問合せを返すファンク
ション本体 REST対応SQL • リモートでのSQL実行 • キャッシュ対応 RESTデータ・ソース • SQLの実行はなし • 実際のソースとはJSONによるデータのやり取りのケースが多いリージョン・ソースの設定 Copyright © 2021, Oracle and/or its affiliates 18 色々な方法で更新対象となるデータを読み出すことができます。

Attributesはほとんど同じ対話グリッドフォームリージョンのAttributes Copyright © 2021, Oracle and/or its
affiliates 19

• 編集 • 実行可能な操作 • 無条件で許可/不許可を決める。 • 許可された行操作列 • 行ごとにフラグ(U/D)を立てる。
• フラグとなる列を指定する。 • 失われた更新タイプ • 行の値(チェックサム)か行バージョン列(数値) のどちらかを選ぶ。 • デフォルトは行の値。 • 認可の編集 • 認可スキームを条件として、追加、更新、削除の許可/不許可を決める。リージョンの属性(Attributes)について Copyright © 2021, Oracle and/or its affiliates 20

対話グリッド、フォームで同じ対話グリッド/フォームターゲット・タイプターゲット・タイプ • Region Source • リージョンに設定されたソースを対象に更新す
る。 • Table / View • リージョンのソースに関わらず、ここで指定した表/ビューを更新する。 • PL/SQL Code • PL/SQLのコードによってデータ操作を行う。データの読み出しとデータの操作は独立しています。更新プロセス Copyright © 2021, Oracle and/or its affiliates 21

フォームにのみ存在リージョンに紐づくページ・アイテムに、Region Sourceから値を設定します。フォームにのみ存在フォームの初期化プロセス Copyright © 2021, Oracle and/or
its affiliates 22

Copyright © 2021, Oracle and/or its affiliates 23 色々と実装してみましょう Subhead
goes here

24 Copyright © 2021, Oracle and/or its affiliates ページ作成ウィザードによって作成できるページ •
対話モード・レポートとフォームの組み合わせ • 対話グリッド • マスター・ディテールの積上げ • マスター・ディテールの左右 • マスター・ディテールのドリルダウン

ジョインした表を同時に更新する Copyright © 2021, Oracle and/or its affiliates 25 select
e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, d.dname, d.loc from emp e join dept d on e.deptno = d.deptno 所属部門も同時に編集します。表DEPTに存在しない場合は追加します。

表示はするけど更新しようとすると… Copyright © 2021, Oracle and/or its affiliates 26 ORA-01776:
結合ビューを介して複数の実表を変更できません。

リージョン・ソースを対象とした更新 update ( select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal,
e.comm, d.dname, d.loc from emp e join dept d on e.deptno = d.deptno ) j set j.sal = 4000 where j.empno = 7698; update ( select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, d.dname, d.loc from emp e join dept d on e.deptno = d.deptno ) j set j.sal = 4000, j.dname = 'セールス' where j.empno = 7698; 実際の処理 Copyright © 2021, Oracle and/or its affiliates 27 更新対象が表EMPのみであれば更新できる。実際は全列が更新対象になるのでエラーとなる。

PL/SQL Code Copyright © 2021, Oracle and/or its affiliates 28
declare l_deptno dept.deptno%type; begin -- 部門の番号を取得する。未登録であれば表DEPTに登録する。 begin select deptno into l_deptno from dept where dname = :DNAME and loc = :LOC; exception when no_data_found then insert into dept(dname, loc) values(:DNAME, :LOC) returning deptno into l_deptno; end; -- 表EMPを操作します。 case :APEX$ROW_STATUS when 'C' then -- 主キーの値を対話グリッドに返すことは必須です。 insert into emp(ename, job, mgr, hiredate, sal, comm, deptno) values(:ENAME, :JOB, :MGR, :HIREDATE, :SAL, :COMM, l_deptno) returning empno into :EMPNO; when 'U' then -- 更新するのは従業員の情報のみです。 update emp set ename = :ENAME, job = :JOB, mgr = :MGR, hiredate = :HIREDATE, sal = :SAL, comm = :COMM, deptno = l_deptno where empno = :EMPNO; when 'D' then -- 削除するのも従業員の情報のみです。 delete from emp where empno = :EMPNO; end case; end;

失われた更新タイプ - 行の値 Copyright © 2021, Oracle and/or its affiliates
30 select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, d.dname, d.loc, apex_item.md5_checksum(e.empno,e.ename,e.job, e.mgr,e.hiredate,e.sal,e.comm,d.dname,d.loc) cs from emp e join dept d on e.deptno = d.deptno 更新可能な列の値をまとめて、チェックサムを生成する。

行のロック Copyright © 2021, Oracle and/or its affiliates 31 select
apex_item.md5_checksum(e.empno,e.ename,e.job, e.mgr,e.hiredate,e.sal,e.comm,d.dname,d.loc) cs from emp e join dept d on e.deptno = d.deptno where empno = :EMPNO for update 行を読み出したときのチェックサムと比較し、一致したときのみUPDATE処理を行う。チェックサムの再読み出しと実際のUPDATE処理の間で行が更新されないよう、for updateをつける。 for updateをつけることで行のロックができない場合は、 Noにするか、PL/SQL Codeでロックする。

行バージョン列 Copyright © 2021, Oracle and/or its affiliates 32 --
create tables create table emp ( row_version integer not null, empno number generated by default on null as identity constraint emp_empno_pk primary key, ename varchar2(255 char) ) ; -- triggers create or replace trigger emp_biu before insert or update on emp for each row begin if inserting then :new.row_version := 1; elsif updating then :new.row_version := nvl(:old.row_version,0) + 1; end if; end emp_biu; / 更新の度に数値をインクリメントする。チェックサムの計算ができない場合に使用する。 RESTサービス動的アクションによる行の更新には、失われた更新の防止の機能はない。自力で機能を記述する必要があります。

動的アクションでの上書き Copyright © 2021, Oracle and/or its affiliates 34 declare
l_deptno dept.deptno%type; begin -- 部門の番号を取得する。未登録であれば表DEPTに登録する。 begin select deptno into l_deptno from dept where dname = :P19_DNAME and loc = :P19_LOC; exception when no_data_found then insert into dept(dname, loc) values(:P19_DNAME, :P19_LOC) returning deptno into l_deptno; end; -- 更新するのは従業員の情報のみです。 update emp set ename = :P19_ENAME, job = :P19_JOB, mgr = :P19_MGR, hiredate = :P19_HIREDATE, sal = :P19_SAL, comm = :P19_COMM, deptno = l_deptno where empno = :P19_EMPNO; end; 動的アクションでも表の更新はできますが、ロストアップデートは保護されません。

許可された行操作列 Copyright © 2021, Oracle and/or its affiliates 36 select
e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, d.dname, d.loc, case d.dname when 'セールス' then 'UD’ when '研究開発' then 'U’ when '会計' then 'D’ else null end op from emp e join dept d on e.deptno = d.deptno UD – 更新と削除 U – 更新 D – 削除

ローカル表と同じようにみえるけど Copyright © 2021, Oracle and/or its affiliates 50 declare
type t is table of varchar2(32767)index by binary_integer; ld number;ld1 number;lop varchar2(10);lc pls_integer:=1;sk boolean:=false;rc pls_integer:=1;le boolean:=false; li t:=:APX$C;lo t; begin begin begin execute immediate q'#alter session set NLS_TERRITORY='JAPAN' NLS_LANGUAGE='JAPANESE' NLS_DATE_FORMAT='DS' NLS_TIMESTAMP_FORMAT='DS' NLS_TIMESTAMP_TZ_FORMAT='DS' NLS_NUMERIC_CHARACTERS='.,'#'; end; begin execute immediate 'begin sys.dbms_application_info.set_client_info(''APEX using REST Enabled SQL'');sys.dbms_application_info.set_module(''APEX:APP 100:3'',''PAGE 3'');end;';exception when others then null;end; end; [中略] when others then lo(rc*1+lc):=r||'#'||sqlcode||'#'||sqlerrm; lc:=lc+1;le:=true; end; end if; end loop; end; if le then rollback; end if; :APX$R:=lo; end データの操作では、リモート・サーバーで長いプロシージャを実行します。キャッシュの機能が追加されています。データ操作はリモートにのみ実施されます。

ローカル表と同じようにみえるけど Copyright © 2021, Oracle and/or its affiliates 55 making
PUT request to https://bp9ncf74sqibu4p- apexdev.adb.us-ashburn- 1.oraclecloudapps.com/ords/apexdev/emp /7698, using request body: { "empno": 7698, "ename": "伊藤明子", "job": "マネージャー", "mgr": 7839, "hiredate": "1981-05- 01T00:00:00.000000000Z", "sal": 3000, "comm": null, "deptno": 30 } JSONを送信しています。リモート側での検索条件とソート条件の指定ローカル側での検索条件とソート条件の指定

Oracle APEX勉強会 - 色々なデータ操作方法を知ろう！

Oracle APEX勉強会 - 色々なデータ操作方法を知ろう！

More Decks by oracle4engineer

Other Decks in Programming

Featured

Transcript