Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

2021年2月24日に実施したセッションの資料です。
1. Oracle APEXの移り変わり
2. Oracle APEXでのデータ操作
- 標準コンポーネント
- フォーム
- 対話グリッド
3. 色々と実装してみましょう
- マスター・ディテール
- 積み上げ
- 左右
- ドリルダウン
4. ジョインした表の操作
5. リモート・アクセスの構成
6. リモート・アクセスを使う

3115a782126be714b5f94d24073c957d?s=128

oracle4engineer

February 25, 2021
Tweet

Transcript

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

  2. 1. Oracle APEXの移り変わり 2. Oracle APEXでのデータ操作 • 標準コンポーネント • フォーム

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

  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で 吸収し、テンプレー トは1つにする。 • HTML5 + CSS3 • Bootstrapに似たグリ ッド • レスポンシブ・デザ イン • 現在提供されている 唯一のテーマです。 対話グリッド(2016〜) • リージョンをコンポ ーネント化する。 • 対話グリッド • カード • Oracle JETチャート • カレンダ (FullCalender – Third Party) • テキスト・エディタ (CKEditor – Thrid Party) • コンポーネントの利 用は、現在のトレン ドです。 6 Copyright © 2021, Oracle and/or its affiliates Oracle APEXの移り変わり
  5. 見た目の変更でも、コードの変更 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
  6. 当時のコードの活用 PL/SQL動的コンテンツ Copyright © 2021, Oracle and/or its affiliates 8

    <html><body></body></html>の出力は除く
  7. テンプレートと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を定数にしていますが、これらはデータベースの表に保存されています。
  8. 置き換えるコードは共用 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;
  9. 多数のテーマから1つのテーマへ ユニバーサル・テーマの導入 Copyright © 2021, Oracle and/or its affiliates 11

    ユニバーサル・テーマ = HTML5 + CSS3 + グリッド・システム(Bootstrap様) レスポンシブ・デザイン 見た目を調整するために、テーマ(テンプレートの セット)が多数
  10. テンプレートの編集によらない見かけの変更 CSSによるカスタマイズ Copyright © 2021, Oracle and/or its affiliates 12

    テンプレート・オプション 事前定義されたCSSクラス カスタム定義 列CSSクラス CSSクラス
  11. 過去の互換性を保つ 今でもユニバーサル・テーマ以外の利用は可能 Copyright © 2021, Oracle and/or its affiliates 13

    例えば Material APEX – Google Material Design (https://material.io/) を元にテーマを作成 Vincent Morneauさん(Insum)作 新規の作成はお勧めしません。 すべてのテンプレートを、いちから すべて作るのは現実的ではありませ ん。
  12. テンプレートによる見かけの変更 クラシック・レポートのテンプレート Copyright © 2021, Oracle and/or its affiliates 14

    テンプレートの切り替えによって見かけを変更 する機構は、現在も利用されています。
  13. よりモダンな形式へ 対話グリッドの導入 – リージョンのコンポーネント化 Copyright © 2021, Oracle and/or its

    affiliates 15 • リージョン内はテンプレートを使わず ブラウザ側でレンダリング • ページ全体の送信をせずに更新 カード・リージョン、チャート(Oracle JET)、カレンダ(FullCalendar)など。 今後もコンポーネントは追加される予定です。
  14. Copyright © 2021, Oracle and/or its affiliates 16 Oracle APEXでのデータ操作

  15. 対話グリッド(Oracle APEX 5.1〜) フォーム(Oracle APEX 19〜) データ操作に使用するコンポーネント Copyright © 2021,

    Oracle and/or its affiliates 17 • それまでの対話モード・レポートは データの更新ができない。 • ページの全体の送受信を行わず、デー タのCRUD操作を行う。 • データ更新用の専用リージョンは無かった (プロセスがあるだけ)。 • 送信されたページを処理する。 • 設定は対話グリッドを踏襲している。
  16. データの読み出し元 – 対話グリッド、フォーム共通 ローカル・データベース • 表/ビュー • SQL問合せ • SQL問合せを返すファンク

    ション本体 REST対応SQL • リモートでのSQL実行 • キャッシュ対応 RESTデータ・ソース • SQLの実行はなし • 実際のソースとはJSONによ るデータのやり取りのケース が多い リージョン・ソースの設定 Copyright © 2021, Oracle and/or its affiliates 18 色々な方法で更新対象となるデータを読み出すことができます。
  17. Attributesはほとんど同じ 対話グリッド フォーム リージョンのAttributes Copyright © 2021, Oracle and/or its

    affiliates 19
  18. • 編集 • 実行可能な操作 • 無条件で許可/不許可を決める。 • 許可された行操作列 • 行ごとにフラグ(U/D)を立てる。

    • フラグとなる列を指定する。 • 失われた更新タイプ • 行の値(チェックサム)か行バージョン列(数値) のどちらかを選ぶ。 • デフォルトは行の値。 • 認可の編集 • 認可スキームを条件として、追加、更新、 削除の許可/不許可を決める。 リージョンの属性(Attributes)について Copyright © 2021, Oracle and/or its affiliates 20
  19. 対話グリッド、フォームで同じ 対話グリッド/フォーム ターゲット・タイプ ターゲット・タイプ • Region Source • リージョンに設定された ソースを対象に更新す

    る。 • Table / View • リージョンのソースに関 わらず、ここで指定した 表/ビューを更新する。 • PL/SQL Code • PL/SQLのコードによって データ操作を行う。 データの読み出しとデータの操 作は独立しています。 更新プロセス Copyright © 2021, Oracle and/or its affiliates 21
  20. フォームにのみ存在 リージョンに紐づくページ・アイテムに、Region Sourceから値を設定します。 フォームにのみ存在 フォームの初期化プロセス Copyright © 2021, Oracle and/or

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

    goes here
  22. 24 Copyright © 2021, Oracle and/or its affiliates ページ作成ウィザードによって作成できるページ •

    対話モード・レポートとフォームの組み合わせ • 対話グリッド • マスター・ディテールの積上げ • マスター・ディテールの左右 • マスター・ディテールのドリルダウン
  23. ジョインした表を同時に更新する 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に存在しない場合は追加します。
  24. 表示はするけど更新しようとすると… Copyright © 2021, Oracle and/or its affiliates 26 ORA-01776:

    結合ビューを介して複数の実表を変更できません。
  25. リージョン・ソースを対象とした更新 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のみであれば更新できる。 実際は全列が更新対象になるのでエラーとなる。
  26. 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;
  27. 失われた更新の防止 Copyright © 2021, Oracle and/or its affiliates 29 ユーザーが更新処理を開始してから、データベース内の現行バージョンのデータが変更されています。

  28. 失われた更新タイプ - 行の値 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 更新可能な列の値をまとめて、チェックサムを生成する。
  29. 行のロック 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でロックする。
  30. 行バージョン列 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サービス 動的アクション による行の更新には、失われた更新の防止の 機能はない。自力で機能を記述する必要があ ります。
  31. ページの送信を動的アクションで置き換えてみる Copyright © 2021, Oracle and/or its affiliates 33

  32. 動的アクションでの上書き 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; 動的アクションでも表の更新はできますが、ロストアップデートは保護されません。
  33. 試してみます Copyright © 2021, Oracle and/or its affiliates 35 動的アクションで実装したページではエラーは

    発生しません。
  34. 許可された行操作列 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 – 削除
  35. 制限された行の操作 Copyright © 2021, Oracle and/or its affiliates 37 通常は非表示にする

    UD – 更新と削除が、できる U – 更新だけ、できる D – 削除だけ、できる
  36. Copyright © 2021, Oracle and/or its affiliates 38 リモート・アクセスの構成

  37. SQL Developer Web – ADMINによる作業 データベース・ユーザーのRESTの有効化 Copyright © 2021, Oracle

    and/or its affiliates 39 APEXのワークスペース・スキーマのRESTの有効化を行う
  38. SQL Developer Web – APEXワークスペース・スキーマでの作業 OAuth2クライアントの作成 Copyright © 2021, Oracle

    and/or its affiliates 40 GUIからOAuth2クライアントを登 録するには、何でもよいのでロー ルが必要。 ロールを何か、事前に登録しておく。
  39. API呼び出しでは必須ではないパラメータがGUIでは必須 OAuth2クライアントの属性指定 Copyright © 2021, Oracle and/or its affiliates 41

    ここで設定する値は、どれも 認証には使われません。 OAuth2クライアントを作成すると、クライアントIDとクライアント・シークレットが生成されます。
  40. GUIからできません SQL Developerロールの割り当て Copyright © 2021, Oracle and/or its affiliates

    42 begin oauth.grant_client_role( p_client_name => 'apexdev', p_role_name => 'SQL Developer' ); end; SQLワークシートにて実行
  41. SQLワークシートから実行 オブジェクトのREST有効化 Copyright © 2021, Oracle and/or its affiliates 43

  42. ロールを割り当てます OAuth2クライアントの編集 Copyright © 2021, Oracle and/or its affiliates 44

  43. Copyright © 2021, Oracle and/or its affiliates 45 リモート・アクセスを使う

  44. Web資格証明の登録 Copyright © 2021, Oracle and/or its affiliates 46 クライアントIDとクライアント・シークレットを設定する

  45. REST対応SQLの登録 Copyright © 2021, Oracle and/or its affiliates 47 エンドポイントURLは、スキーマ別名までを

    指定します。 作成済みのWeb資格証明を指定します。
  46. REST対応SQLをソースとしたページ作成 Copyright © 2021, Oracle and/or its affiliates 48 データ・ソースにREST対応SQLを選びます。

  47. 対話モード・レポートとフォーム Copyright © 2021, Oracle and/or its affiliates 49

  48. ローカル表と同じようにみえるけど 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 データの操作では、リモート・サーバーで長い プロシージャを実行します。 キャッシュの機能が追加されています。 データ操作はリモートにのみ実施されます。
  49. RESTデータ・ソースの作成 (1) Copyright © 2021, Oracle and/or its affiliates 51

  50. RESTデータ・ソースの作成 (2) Copyright © 2021, Oracle and/or its affiliates 52

  51. RESTデータ・ソースを使用するレポートとフォームの作成 Copyright © 2021, Oracle and/or its affiliates 53 データ・ソースにRESTデータ・ソースを選びます。

  52. 対話モード・レポートとフォーム Copyright © 2021, Oracle and/or its affiliates 54

  53. ローカル表と同じようにみえるけど 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を送信しています。 リモート側での検索条 件とソート条件の指定 ローカル側での検索条 件とソート条件の指定
  54. 定期的にRESTデータ・ソースとローカル表を同期します RESTデータ・ソースの同期化 Copyright © 2021, Oracle and/or its affiliates 56

    同期化表の使用 リモート処理の指 定がなくなります。
  55. ありがとうございました。 Text Text 57 Copyright © 2021, Oracle and/or its

    affiliates
  56. None