こんにちは。AIです。（Artificial Intelligence ではありません）
最近はブルーベリーにハマっており、先週 2 パック買って消費してしまいました。。食べすぎといわれても致し方なし。。
ちなみにカナダのスーパーでは、たくさんの種類の生のベリーが並んでおり、手軽にみずみずしいベリーを楽しむことができます。
初夏にはブルーベリー狩りもできますので、カナダに訪れた際はぜひ体験してみてください。
さて、前回の 23ai 新機能の記事では JSONリレーショナル二面性ビューの概要や作成方法についてお伝えしましたが、今回は ORDS を使用したデータの取得や更新について検証していきます！
■事前準備
まずは、今回使用する JSONリレーショナル二面性ビューを作成します。
今回の元表は hr.employees 表となります。
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW employees_dv AS
2 SELECT JSON {'_id' IS e.employee_id,
3 'first_name' IS e.first_name,
4 'last_name' IS e.last_name,
5 'email' IS e.email,
6 'phone_number' IS e.phone_number
7 } FROM employees e WITH INSERT UPDATE DELETE;
View created.このビューに対して ORDS からアクセスできるよう、ORDS.ENABLE_OBJECT を実行します。
SQL> BEGIN
2 ORDS_METADATA.ORDS.ENABLE_OBJECT(
3 p_enabled => TRUE,
4 p_schema => 'HR',
5 p_object => 'EMPLOYEES_DV',
6 p_object_type => 'VIEW',
7 p_object_alias => 'employees_dv',
8 p_auto_rest_auth => FALSE);
9 COMMIT;
10 END;
11 /
PL/SQL procedure successfully completed.■ORDS を使用してデータを挿入
それでは早速、ORDS を使用してテーブルのデータを変更してみましょう。
※リクエストは curl コマンドを使用します。
データを変更する際の SQL コマンドと、HTTP リクエストメソッドはそれぞれ下記のように対応しています。
INSERT = POST
UPDATE = PUT
DELETE = DELETE
まずは POST をリクエストしてデータを挿入します。
なお今回の記事で指定する URL については以下のような構造となっています。
プロトコル : http
ホスト名 : localhost:8080
コンテキスト・ルート : /ords
リクエスト・パス : /スキーマ名/オブジェクト名 or /スキーマ名/オブジェクト名/_id
[oracle@localhost ~]$ curl --request POST \
> --url http://localhost:8080/ords/hr/employees_dv/ \
> --header 'Content-Type: application/json' \
> --data '{"first_name" : "Ryunosuke",
> "last_name" : "Akutagawa",
> "email" : "RAKUTAGAWA",
> "hire_date" : "2013-06-17T00:00:00",
> "job_id" : "ST_MAN"
> }'
{"_id":208★,"first_name":"Ryunosuke","last_name":"Akutagawa","email":"RAKUTAGAWA","hire_date":"2013-06-17T00:00:00","job_id":"ST_MAN","_metadata":{"etag":"2679E2E75FDE0E35B77BC65314DBC942","asof":"0000000000D0C930"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/employees_dv/208"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees_dv/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/employees_dv/"}]}今回は、_id を指定せずにデータを挿入したため自動的に採番されましたが、明示的に指定して挿入することも可能です。
それでは、早速 POST したデータを確認してみましょう！
ORDS から JSON リレーショナル二面性ビューの特定のデータを確認したい場合は _id の値を URL の末尾に入れます。
[oracle@localhost ~]$ curl --location http://localhost:8080/ords/hr/employees_dv/208
{"_id":208,"first_name":"Ryunosuke","last_name":"Akutagawa","email":"RAKUTAGAWA","hire_date":"2013-06-17T00:00:00","job_id":"ST_MAN","_metadata":{"etag":"2679E2E75FDE0E35B77BC65314DBC942","asof":"0000000000E02EA8"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/employees_dv/208"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees_dv/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/employees_dv/"}]}[oracle@localhost ~]$更に JSON リレーショナル二面性ビューと実際の表も確認してみます。
SQL> SELECT * FROM employees_dv WHERE DATA LIKE '%208%';
DATA
--------------------------------------------------------------------------------
:
{"_id":208,"_metadata":{"etag":"2679E2E75FDE0E35B77BC65314DBC942","asof":"000000
0000E032FA"},"first_name":"Ryunosuke","last_name":"Akutagawa","email":"RAKUTAGAW
A","hire_date":"2013-06-17T00:00:00","job_id":"ST_MAN"}SQL> SELECT employee_id,first_name,last_name FROM employees
2 WHERE employee_id=208;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
208 Ryunosuke Akutagawaリレーショナル二面性ビューの元表にもデータが追加されたのがわかりますね！
■ORDS を使用してデータを更新
では続いて、追加したデータの内容を更新してみましょう。
変更するのは、employee_id が 208 のデータの EMAIL 列とします。
現在は employees 表を確認すると、「RAKUTAGAWA」となっていますね。
SQL> SELECT employee_id,email FROM employees
2 WHERE employee_id=208;
EMPLOYEE_ID EMAIL
----------- -------------------------
208 RAKUTAGAWAこれを、PUT で「RAKUTAGAWA_02」に変更してみます。
PUT で既存のデータを更新する際は、前回の記事でもお伝えしている通り etag 情報を含めます。
[oracle@localhost ~]$ curl --request PUT \
> --url http://localhost:8080/ords/hr/employees_dv/208 \
> --header 'Content-Type: application/json' \
> --data '{"_id" : "208",
> "_metadata" : {"etag":"2679E2E75FDE0E35B77BC65314DBC942"},★
> "first_name" : "Ryunosuke",
> "last_name" : "Akutagawa",
> "email" : "RAKUTAGAWA_02",
> "hire_date" : "2013-06-17T00:00:00",
> "job_id" : "ST_MAN"
> }'
{"_id":208,"first_name":"Ryunosuke","last_name":"Akutagawa","email":"RAKUTAGAWA_02","hire_date":"2013-06-17T00:00:00","job_id":"ST_MAN","_metadata":{"etag":"F81E9D06B528EAB2684BFCF974A33BA2","asof":"0000000000E03655"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/employees_dv/208"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees_dv/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/employees_dv/"}]}[oracle@localhost ~]$employees 表を確認すると、EMAIL 列の値が変更されていることが確認できました！
SQL> SELECT employee_id,email FROM employees
2 WHERE employee_id=208;
EMPLOYEE_ID EMAIL
----------- -------------------------
208 RAKUTAGAWA_02■ORDS を使用してデータを削除
最後に DELETE をリクエストして、employee_id が 208 のデータを削除します。
[oracle@localhost ~]$ curl --request DELETE \
> --url http://localhost:8080/ords/hr/employees_dv/208
{"rowsDeleted":1}SQL> SELECT employee_id,email FROM employees
2 WHERE employee_id=208;
no rows selected指定したデータが削除されていることが確認できました。
（コミットとかないのでちょっとドキドキ）
■まとめ
今回の記事はいかがでしたでしょうか。
コーソルカナダでは Oracle Database に対して Zabbix の監視を導入する案件を受けたことがありますが、その際も SELECT したデータを JSON データとして扱う際に苦労しました…
JSONリレーショナル二面性ビューは、JSON データを更新や挿入にも使用できるようになったものなので、方向性としては逆になりますがこちらも助かる人が多くいるでしょう。
それでは次回の更新もお楽しみに！