{"id":2162,"date":"2025-09-21T22:30:53","date_gmt":"2025-09-21T14:30:53","guid":{"rendered":"http:\/\/www.821121.com\/?p=2162"},"modified":"2026-04-23T23:39:00","modified_gmt":"2026-04-23T15:39:00","slug":"23ai-%e6%96%b0%e7%89%b9%e6%80%a7%e4%b9%8bjson-%e5%85%b3%e7%b3%bb%e5%8f%8c%e6%a0%96","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=2162","title":{"rendered":"23ai \u65b0\u7279\u6027\u4e4bJSON-\u5173\u7cfb\u53cc\u6816"},"content":{"rendered":"<p>Oracle 23ai\u7684JSON-\u5173\u7cfb\u53cc\u6816\uff0c\u6838\u5fc3\u662f\u901a\u8fc7\u201cJSON-\u5173\u7cfb\u53cc\u6816\u89c6\u56fe\u201d\uff08Duality View\uff09\u5b9e\u73b0\u5b58\u50a8\u683c\u5f0f\u4e0e\u8bbf\u95ee\u683c\u5f0f\u7684\u89e3\u8026\u2014\u2014\u6570\u636e\u5e95\u5c42\u4ecd\u4ee5\u89c4\u8303\u5316\u7684\u5173\u7cfb\u8868\u5f62\u5f0f\u5b58\u50a8\uff0c\u4eab\u53d7\u5173\u7cfb\u578b\u6a21\u578b\u7684\u9ad8\u6548\u3001\u4e00\u81f4\u4e0e\u4f4e\u5197\u4f59\u4f18\u52bf\uff1b\u540c\u65f6\uff0c\u901a\u8fc7\u53cc\u6816\u89c6\u56fe\u5c06\u5173\u7cfb\u6570\u636e\u81ea\u52a8\u7ec4\u88c5\u4e3aJSON\u6587\u6863\uff0c\u8ba9\u5e94\u7528\u7a0b\u5e8f\u53ef\u4ee5\u901a\u8fc7JSON\u8bed\u6cd5\u3001REST API\u7b49\u65b9\u5f0f\u4fbf\u6377\u8bbf\u95ee\uff0c\u65e0\u9700\u590d\u6742\u7684\u5bf9\u8c61-\u5173\u7cfb\u6620\u5c04\uff08ORM\uff09\u5c42\u3002<br \/>\n\u4f5c\u4e3a\u539f\u751f\u7279\u6027\uff0c\u65e0\u9700\u989d\u5916\u90e8\u7f72\u7b2c\u4e09\u65b9\u5de5\u5177\uff0c\u53ea\u9700\u521b\u5efa\u53cc\u6816\u89c6\u56fe\uff0c\u5373\u53ef\u5b9e\u73b0\u5173\u7cfb\u6570\u636e\u4e0eJSON\u6587\u6863\u7684\u53cc\u5411\u4ea4\u4e92\u3002<\/p>\n<h3>1. \u73af\u5883\u51c6\u5907\u4e0e\u5e95\u5c42\u8868\u521b\u5efa<\/h3>\n<p>\u9996\u5148\u521b\u5efa\u7535\u5546\u8ba2\u5355\u76f8\u5173\u7684\u5e95\u5c42\u5173\u7cfb\u8868\uff0c\u5305\u542b\u8ba2\u5355\u8868\u3001\u8ba2\u5355\u9879\u8868\u3001\u5ba2\u6237\u8868\uff0c\u7528\u4e8e\u5b58\u50a8\u89c4\u8303\u5316\u7684\u8ba2\u5355\u6570\u636e\uff1a<\/p>\n<pre><code class=\"language-sql\">-- \u521b\u5efa\u5ba2\u6237\u8868\nCREATE TABLE customer (\n    cust_id NUMBER PRIMARY KEY,\n    cust_name VARCHAR2(100) NOT NULL,\n    phone VARCHAR2(20),\n    email VARCHAR2(100)\n);\n\n-- \u521b\u5efa\u8ba2\u5355\u8868\nCREATE TABLE orders (\n    order_id NUMBER PRIMARY KEY,\n    cust_id NUMBER REFERENCES customer(cust_id),\n    order_date DATE DEFAULT SYSDATE,\n    total_amount NUMBER(10,2) NOT NULL,\n    order_status VARCHAR2(20) NOT NULL\n);\n\n-- \u521b\u5efa\u8ba2\u5355\u9879\u8868\nCREATE TABLE order_item (\n    item_id NUMBER PRIMARY KEY,\n    order_id NUMBER REFERENCES orders(order_id),\n    product_name VARCHAR2(100) NOT NULL,\n    quantity NUMBER NOT NULL,\n    unit_price NUMBER(10,2) NOT NULL\n);\n\n-- \u63d2\u5165\u6d4b\u8bd5\u6570\u636e\nINSERT INTO customer VALUES (1, &#039;\u5f20\u4e09&#039;, &#039;13800138000&#039;, &#039;zhangsan@example.com&#039;);\nINSERT INTO orders VALUES (1001, 1, SYSDATE, 599.00, &#039;\u5df2\u4ed8\u6b3e&#039;);\nINSERT INTO order_item VALUES (10001, 1001, &#039;\u624b\u673a&#039;, 1, 499.00);\nINSERT INTO order_item VALUES (10002, 1001, &#039;\u624b\u673a\u58f3&#039;, 1, 100.00);\nCOMMIT;<\/code><\/pre>\n<h3>2. \u521b\u5efaJSON-\u5173\u7cfb\u53cc\u6816\u89c6\u56fe<\/h3>\n<p>\u4f7f\u7528\u7c7bGraphQL\u8bed\u6cd5\u521b\u5efa\u53cc\u6816\u89c6\u56fe\uff0c\u5c06\u5ba2\u6237\u3001\u8ba2\u5355\u3001\u8ba2\u5355\u9879\u7684\u5173\u8054\u6570\u636e\u7ec4\u88c5\u4e3a\u5c42\u7ea7JSON\u6587\u6863\uff1a<\/p>\n<pre><code class=\"language-sql\">-- \u521b\u5efaJSON-\u5173\u7cfb\u53cc\u6816\u89c6\u56fe\uff0c\u7ec4\u88c5\u8ba2\u5355\u5b8c\u6574\u4fe1\u606fJSON\nCREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_duality_view AS\n  SELECT \n    o.order_id AS &quot;orderId&quot;,\n    o.order_date AS &quot;orderDate&quot;,\n    o.total_amount AS &quot;totalAmount&quot;,\n    o.order_status AS &quot;orderStatus&quot;,\n    c.cust_id AS &quot;customer.custId&quot;,\n    c.cust_name AS &quot;customer.custName&quot;,\n    c.phone AS &quot;customer.phone&quot;,\n    COLLECTION(\n      SELECT \n        oi.item_id AS &quot;itemId&quot;,\n        oi.product_name AS &quot;productName&quot;,\n        oi.quantity AS &quot;quantity&quot;,\n        oi.unit_price AS &quot;unitPrice&quot;\n      FROM order_item oi\n      WHERE oi.order_id = o.order_id\n    ) AS &quot;orderItems&quot;\n  FROM orders o\n  JOIN customer c ON o.cust_id = c.cust_id;<\/code><\/pre>\n<h3>3. \u6570\u636e\u8bbf\u95ee\u4e0e\u64cd\u4f5c\u5b9e\u6218<\/h3>\n<p>\u521b\u5efa\u53cc\u6816\u89c6\u56fe\u540e\uff0c\u5373\u53ef\u901a\u8fc7\u591a\u79cd\u65b9\u5f0f\u8bbf\u95ee\u548c\u64cd\u4f5c\u6570\u636e\uff0c\u5b9e\u73b0\u5173\u7cfb\u6570\u636e\u4e0eJSON\u6587\u6863\u7684\u65e0\u7f1d\u4ea4\u4e92\u3002<\/p>\n<ul>\n<li>\u573a\u666f1\uff1a\u4ee5JSON\u683c\u5f0f\u67e5\u8be2\u8ba2\u5355\u6570\u636e\n<pre><code class=\"language-sql\">-- \u901a\u8fc7\u53cc\u6816\u89c6\u56fe\u67e5\u8be2JSON\u683c\u5f0f\u7684\u8ba2\u5355\u6570\u636e\nSELECT JSON_VALUE(data, &#039;$.orderId&#039;) AS order_id,\n   JSON_VALUE(data, &#039;$.customer.custName&#039;) AS cust_name,\n   JSON_VALUE(data, &#039;$.totalAmount&#039;) AS total_amount\nFROM order_duality_view;\n-- \u67e5\u8be2\u5b8c\u6574JSON\u6587\u6863\nSELECT data FROM order_duality_view WHERE JSON_VALUE(data, &#039;$.orderId&#039;) = 1001;<\/code><\/pre>\n<p>\u6267\u884c\u540e\uff0c\u4f1a\u8fd4\u56de\u5305\u542b\u5ba2\u6237\u4fe1\u606f\u3001\u8ba2\u5355\u9879\u7684\u5c42\u7ea7JSON\u6587\u6863\uff0c\u65e0\u9700\u624b\u52a8\u5173\u8054\u591a\u8868\uff0c\u76f4\u63a5\u9002\u914d\u524d\u7aef\u5e94\u7528\u7684JSON\u6570\u636e\u9700\u6c42\u3002<\/p>\n<\/li>\n<li>\u573a\u666f2\uff1a\u901a\u8fc7JSON\u683c\u5f0f\u66f4\u65b0\u6570\u636e\n<pre><code class=\"language-sql\">\n-- \u901a\u8fc7\u53cc\u6816\u89c6\u56fe\u66f4\u65b0JSON\u6587\u6863\uff0c\u540c\u6b65\u66f4\u65b0\u5e95\u5c42\u5173\u7cfb\u8868\nUPDATE order_duality_view\nSET data = JSON_MERGEPATCH(data, &#039;{&quot;orderStatus&quot;: &quot;\u5df2\u53d1\u8d27&quot;, &quot;totalAmount&quot;: 699.00}&#039;)\nWHERE JSON_VALUE(data, &#039;$.orderId&#039;) = 1001;\nCOMMIT;\n-- \u9a8c\u8bc1\u5e95\u5c42\u5173\u7cfb\u8868\u6570\u636e\u662f\u5426\u540c\u6b65\u66f4\u65b0\nSELECT order_status, total_amount FROM orders WHERE order_id = 1001;\n\u66f4\u65b0JSON\u6587\u6863\u540e\uff0c\u5e95\u5c42orders\u8868\u4e2d\u7684order_status\u548ctotal_amount\u5b57\u6bb5\u4f1a\u81ea\u52a8\u540c\u6b65\u66f4\u65b0\uff0c\u786e\u4fdd\u6570\u636e\u4e00\u81f4\u6027\u3002<\/code><\/pre>\n<\/li>\n<li>\u573a\u666f3\uff1a\u901a\u8fc7REST API\u8bbf\u95eeJSON\u6570\u636e<br \/>\nOracle 23ai\u652f\u6301\u901a\u8fc7REST API\u8bbf\u95ee\u53cc\u6816\u89c6\u56fe\u7684JSON\u6570\u636e\uff0c\u65e0\u9700\u989d\u5916\u5f00\u53d1\u63a5\u53e3\uff0c\u76f4\u63a5\u914d\u7f6eREST\u7aef\u70b9\u5373\u53ef\uff1a<\/p>\n<pre><code class=\"language-sql\">-- \u542f\u7528REST\u670d\u52a1\nBEGIN\nDBMS_REST.SET_ENABLED(TRUE);\nEND;\n\/\n-- \u4e3a\u53cc\u6816\u89c6\u56fe\u521b\u5efaREST\u7aef\u70b9\nBEGIN\nDBMS_REST.CREATE_RESOURCE(\nresource_path => '\/order-api',\nsource_type => 'VIEW',\nsource_name => 'ORDER_DUALITY_VIEW',\nmethod => 'GET'\n);\nEND;\n\/<\/code><\/pre>\n<\/li>\n<\/ul>\n<p>\u914d\u7f6e\u5b8c\u6210\u540e\uff0c\u5373\u53ef\u901a\u8fc7HTTP\u8bf7\u6c42\u8bbf\u95eeJSON\u6570\u636e\uff0c\u4f8b\u5982\uff1aGET <a href=\"http:\/\/localhost:8080\/ords\/rag_user\/order-api?orderId=1001\uff0c\u8fd4\u56deJSON\u683c\u5f0f\u7684\u8ba2\u5355\u8be6\u60c5\">http:\/\/localhost:8080\/ords\/rag_user\/order-api?orderId=1001\uff0c\u8fd4\u56deJSON\u683c\u5f0f\u7684\u8ba2\u5355\u8be6\u60c5<\/a>\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle 23ai\u7684JSON-\u5173\u7cfb\u53cc\u6816\uff0c\u6838\u5fc3\u662f<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57,58],"tags":[],"class_list":["post-2162","post","type-post","status-publish","format-standard","hentry","category-oracle-23ai","category--oracle-23c"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2162"}],"version-history":[{"count":4,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2162\/revisions"}],"predecessor-version":[{"id":2166,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2162\/revisions\/2166"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2162"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}