【Oracle26ai新機能】JSON Duality Viewの使い方｜配列から外した子の行は表から削除される【実行ログ付き】

SQL> CREATE TABLE V26_APP.dept (deptno NUMBER PRIMARY KEY, dname VARCHAR2(20));

Table created.

SQL> CREATE TABLE V26_APP.emp (empno NUMBER PRIMARY KEY, ename VARCHAR2(20), sal NUMBER, deptno NUMBER REFERENCES V26_APP.dept);

Table created.

SQL> INSERT INTO V26_APP.dept VALUES (10,'SALES'), (20,'DEV');

2 rows created.

SQL> INSERT INTO V26_APP.emp VALUES (1,'A',100,10), (2,'B',200,10), (3,'C',300,20);

3 rows created.

SQL> CREATE JSON RELATIONAL DUALITY VIEW V26_APP.dept_dv AS
    SELECT JSON {'_id'   : d.deptno,
                 'dname' : d.dname,
                 'emps'  : [ SELECT JSON {'empno' : e.empno, 'ename' : e.ename, 'sal' : e.sal}
                             FROM V26_APP.emp e WITH INSERT UPDATE DELETE
                             WHERE e.deptno = d.deptno ]}
    FROM V26_APP.dept d WITH INSERT UPDATE DELETE;

View created.

SQL> SELECT JSON_SERIALIZE(data PRETTY) AS doc FROM V26_APP.dept_dv d WHERE d.data."_id" = 10;

DOC
----------------------------------------
{
  "_id" : 10,
  "_metadata" :
  {
    "etag" : "A55E3080E3707ABEBCDFDABF3E4D5A8F",
    "asof" : "000000000308933B"
  },
  "dname" : "SALES",
  "emps" :
  [
    {
      "empno" : 1,
      "ename" : "A",
      "sal" : 100
    },
    {
      "empno" : 2,
      "ename" : "B",
      "sal" : 200
    }
  ]
}


1 row selected.

SQL> INSERT INTO V26_APP.dept_dv VALUES ('{"_id":30,"dname":"OPS","emps":[{"empno":4,"ename":"D","sal":400},{"empno":5,"ename":"E","sal":500}]}');

1 row created.

SQL> SELECT * FROM V26_APP.dept ORDER BY deptno;

    DEPTNO DNAME
---------- --------------------
        10 SALES
        20 DEV
        30 OPS

3 rows selected.

SQL> SELECT * FROM V26_APP.emp ORDER BY empno;

     EMPNO ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
         1 A                           100         10
         2 B                           200         10
         3 C                           300         20
         4 D                           400         30
         5 E                           500         30

5 rows selected.

SQL> UPDATE V26_APP.dept_dv d SET data = JSON_TRANSFORM(data, SET '$.dname' = 'OPS2') WHERE d.data."_id" = 30;

1 row updated.

SQL> SELECT deptno, dname FROM V26_APP.dept WHERE deptno = 30;

    DEPTNO DNAME
---------- --------------------
        30 OPS2

1 row selected.

SQL> SELECT JSON_SERIALIZE(d.data) AS doc FROM V26_DV.dept_dv d WHERE d.data."_id" = 20;

DOC
----------------------------------------
{"_id":20,"_metadata":{"etag":"A487F1FBC6B0AE562ACE0E274AB57502","asof":"00000000036BDBB5"
},"dname":"DEV","emps":[{"empno":3,"ename":"C","sal":300}]}


1 row selected.

SQL> UPDATE V26_DV.emp SET sal = 350 WHERE empno = 3;

1 row updated.

SQL> SELECT JSON_SERIALIZE(d.data) AS doc FROM V26_DV.dept_dv d WHERE d.data."_id" = 20;

DOC
----------------------------------------
{"_id":20,"_metadata":{"etag":"554DC6A115DE94C74E81CDC885E121AE","asof":"00000000036BDBBA"
},"dname":"DEV","emps":[{"empno":3,"ename":"C","sal":350}]}


1 row selected.

SQL> UPDATE V26_APP.dept_dv d SET data = ('{"_id":30,"dname":"OPS2","emps":[{"empno":4,"ename":"D","sal":400}]}') WHERE d.data."_id" = 30;

1 row updated.

SQL> SELECT empno, deptno FROM V26_APP.emp ORDER BY empno;

     EMPNO     DEPTNO
---------- ----------
         1         10
         2         10
         3         20
         4         30

4 rows selected.

SQL> SELECT JSON_VALUE(d.data, '$._metadata.etag') AS saved_etag FROM V26_DV.dept_dv d WHERE d.data."_id" = 20;

SAVED_ETAG
----------------------------------
A487F1FBC6B0AE562ACE0E274AB57502

1 row selected.

SQL> UPDATE V26_DV.dept SET dname = 'DEV2' WHERE deptno = 20;

1 row updated.

SQL> SELECT JSON_VALUE(d.data, '$._metadata.etag') AS current_etag FROM V26_DV.dept_dv d WHERE d.data."_id" = 20;

CURRENT_ETAG
----------------------------------
2F9A71BFD3FA21634D98A8072C95B67C

1 row selected.

SQL> PROMPT OLD_ETAG = A487F1FBC6B0AE562ACE0E274AB57502

OLD_ETAG = A487F1FBC6B0AE562ACE0E274AB57502

SQL> UPDATE V26_DV.dept_dv d SET data = '{"_id":20,"_metadata":{"etag":"A487F1FBC6B0AE562ACE0E274AB57502"},"dname":"DEV3","emps":[{"empno":3,"ename":"C","sal":300}]}' WHERE d.data."_id" = 20;

UPDATE V26_DV.dept_dv d SET data = '{"_id":20,"_metadata":{"etag":"A487F1FBC6B0AE562ACE0E274AB57502"},"dname":"DEV3","emps":[{"empno":3,"ename":"C","sal":300}]}' WHERE d.data."_id" = 20
*
ERROR at line 1:
ORA-42699: Cannot update JSON Relational Duality View 'DEPT_DV': The ETAG of document with ID 'FB03C11500' in the database did not match the ETAG passed in.

SQL> SELECT JSON_VALUE(d.data, '$._metadata.etag') AS current_etag FROM V26_DV.dept_dv d WHERE d.data."_id" = 20;

CURRENT_ETAG
----------------------------------
2F9A71BFD3FA21634D98A8072C95B67C

1 row selected.

SQL> UPDATE V26_DV.dept_dv d SET data = '{"_id":20,"_metadata":{"etag":"2F9A71BFD3FA21634D98A8072C95B67C"},"dname":"DEV3","emps":[{"empno":3,"ename":"C","sal":300}]}' WHERE d.data."_id" = 20;

1 row updated.

SQL> SELECT deptno, dname FROM V26_DV.dept WHERE deptno = 20;

    DEPTNO DNAME
---------- --------------------
        20 DEV3

1 row selected.

SQL> UPDATE V26_DV.dept_dv d SET data = '{"_id":20,"dname":"DEV4","emps":[{"empno":3,"ename":"C","sal":350}]}' WHERE d.data."_id" = 20;

1 row updated.

SQL> SELECT deptno, dname FROM V26_DV.dept WHERE deptno = 20;

    DEPTNO DNAME
---------- --------------------
        20 DEV4

1 row selected.

SQL> UPDATE V26_APP.dept_dv d SET data = JSON_TRANSFORM(data, SET '$._id' = 99) WHERE d.data."_id" = 20;

UPDATE V26_APP.dept_dv d SET data = JSON_TRANSFORM(data, SET '$._id' = 99) WHERE d.data."_id" = 20
*
ERROR at line 1:
ORA-42603: Cannot update JSON Relational Duality View 'DEPT_DV': The Primary Key column(s) of the root table 'DEPT' cannot be updated, omitted, or set to NULL.

SQL> INSERT INTO V26_APP.dept_dv VALUES ('{"_id":40,"dname":"X","unknown":1,"emps":[]}');

INSERT INTO V26_APP.dept_dv VALUES ('{"_id":40,"dname":"X","unknown":1,"emps":[]}')
*
ERROR at line 1:
ORA-40944: Cannot insert into JSON Relational Duality View 'DEPT_DV': The input JSON document is invalid.
JZN-00651: field 'unknown' is unknown or undefined

SQL> DELETE FROM V26_APP.dept_dv d WHERE d.data."_id" = 30;

1 row deleted.

SQL> SELECT COUNT(*) AS dept30 FROM V26_APP.dept WHERE deptno = 30;

    DEPT30
----------
         0

1 row selected.

SQL> SELECT COUNT(*) AS emp_of_30 FROM V26_APP.emp WHERE empno IN (4,5);

 EMP_OF_30
----------
         0

1 row selected.

SQL> CREATE JSON RELATIONAL DUALITY VIEW V26_APP.dept_gql AS
    V26_APP.dept @insert @update @delete {
      _id : deptno,
      name : dname,
      staff : V26_APP.emp @insert @update @delete { no : empno, name : ename }
    };

View created.

SQL> SELECT JSON_SERIALIZE(data PRETTY) AS doc FROM V26_APP.dept_gql;

DOC
----------------------------------------
{
  "_id" : 10,
  "_metadata" :
  {
    "etag" : "EBE1761F4289D258F77FBA8C1E963C85",
    "asof" : "0000000003093984"
  },
  "name" : "SALES",
  "staff" :
  [
    {
      "no" : 1,
      "name" : "A"
    },
    {
      "no" : 2,
      "name" : "B"
    }
  ]
}


1 row selected.

SQL> INSERT INTO V26_APP.dept_gql VALUES ('{"_id":20,"name":"DEV","staff":[{"no":3,"name":"C"}]}');

1 row created.

SQL> SELECT e.empno, e.deptno FROM V26_APP.emp e ORDER BY 1;

     EMPNO     DEPTNO
---------- ----------
         1         10
         2         10
         3         20

3 rows selected.

SQL> CREATE JSON RELATIONAL DUALITY VIEW V26_APP.dept_ro AS V26_APP.dept @update { _id : deptno, name : dname @noupdate };

View created.

SQL> UPDATE V26_APP.dept_ro d SET data = JSON_TRANSFORM(data, SET '$.name' = 'X') WHERE d.data."_id" = 10;

UPDATE V26_APP.dept_ro d SET data = JSON_TRANSFORM(data, SET '$.name' = 'X') WHERE d.data."_id" = 10
*
ERROR at line 1:
ORA-40940: Cannot update field 'name' corresponding to column 'DNAME' of table 'DEPT' in JSON Relational Duality View 'DEPT_RO': Missing UPDATE annotation or NOUPDATE annotation specified.

SQL> INSERT INTO V26_APP.dept_ro VALUES ('{"_id":30,"name":"OPS"}');

INSERT INTO V26_APP.dept_ro VALUES ('{"_id":30,"name":"OPS"}')
*
ERROR at line 1:
ORA-40937: Cannot insert into table 'DEPT' in JSON Relational Duality View 'DEPT_RO': Missing INSERT annotation or NOINSERT annotation specified.

SQL> SELECT view_name, root_table_name, allow_insert, allow_update, allow_delete, read_only, status FROM dba_json_duality_views WHERE view_owner = 'V26_DV';

VIEW_NAME ROOT_TABLE_NAME ALLOW_INSER ALLOW_UPDAT ALLOW_DELET READ_ONLY STATUS
--------- --------------- ----------- ----------- ----------- --------- ------
DEPT_DV   DEPT            TRUE        TRUE        TRUE        FALSE     VALID

1 row selected.

SQL> SELECT table_name, root_table, allow_insert, allow_update, allow_delete FROM dba_json_duality_view_tabs WHERE view_owner = 'V26_DV' ORDER BY table_number;

TABLE_NAME ROOT_TABLE  ALLOW_INSER ALLOW_UPDAT ALLOW_DELET
---------- ----------- ----------- ----------- -----------
DEPT       TRUE        TRUE        TRUE        TRUE
EMP        FALSE       TRUE        TRUE        TRUE

2 rows selected.

SQL> SELECT table_name, column_name, json_key_name, primary_key_pos, etag_pos FROM dba_json_duality_view_tab_cols WHERE view_owner = 'V26_DV' ORDER BY table_number, column_name;

TABLE_NAME COLUMN_N JSON_KEY_NAME PRIMARY_KEY_POS   ETAG_POS
---------- -------- ------------- --------------- ----------
DEPT       DEPTNO   _id                         1          1
DEPT       DNAME    dname                                  2
EMP        DEPTNO
EMP        EMPNO    empno                       1          1
EMP        ENAME    ename                                  2
EMP        SAL      sal                                    3

6 rows selected.