※ 本記事は、Martin Bachによる”Working with JSON in Multilingual Engine/JavaScript in Oracle Database 23c Free“を翻訳したものです。
2023年12月20日
JSONは、主要なデータ交換形式ではないとしても、最も重要なデータの格納方法の1つとなっています。この記事では、MLE/JavaScriptを使用すると、JSONの操作がかなり簡単であることを示しています。結局のところ、JSONはJavaScript Object Notationの略ですので、これは必ずしも驚くべきことではありません …
背景
この例では、リレーショナル・コンテキストでJSONを使用する方法を確認できます。つまり、JSONは、Oracle Database 21cで最初に導入されたJSONデータ型を使用してリレーショナル表に格納されます。フォローする場合は、『Oracle Database JSON開発者ガイド』の第4章に進んでください。
この例は、Linux x86-64上のOracle Database 23c Freeで作成およびテストされています。
ここでは、この記事で多くを見ることになる発注の例を示します:
{
"PONumber": 1600,
"Reference": "ABULL-20140421",
"Requestor": "Alexis Bull",
"User": "ABULL",
"CostCenter": "A50",
"ShippingInstructions": {
"name": "Alexis Bull",
"Address": {
"street": "200 Sporting Green",
"city": "South San Francisco",
"state": "CA",
"zipCode": 99236,
"country": "United States of America"
},
"Phone": [
{
"type": "Office",
"number": "909-555-7307"
},
{
"type": "Mobile",
"number": "415-555-1234"
}
]
},
"Special Instructions": null,
"AllowPartialShipment": true,
"LineItems": [
{
"ItemNumber": 1,
"Part": {
"Description": "One Magic Christmas",
"UnitPrice": 19.95,
"UPCCode": 13131092899
},
"Quantity": 9.0
},
{
"ItemNumber": 2,
"Part": {
"Description": "Lethal Weapon",
"UnitPrice": 19.95,
"UPCCode": 85391628927
},
"Quantity": 5.0
}
]
}
データベース内JavaScriptとJSONが一致
この仮説的なビジネスによってプロモーションが実行されると仮定します。クリスマスに近づくにつれて、一定額以上の支出をしている顧客はフットバッグを手に入れることになります。フットバッグは楽しい小さなプレゼントであり、モビリティと調整を奨励します。
次に、無料アイテムをオーダーに追加する方法の例を示します。購買オーダーがドキュメント(前述の例)で示されているように定義されている場合、次のコードがあります:
create or replace mle module purchase_order_module
language javascript as
/**
* A simple function accepting a purchase Order (as per chapter 4 in the Oracle Database
* JSON Developer's Guide) and checking whether its value is high enough to merit the
* addition of a free item
*
* @param {object} po the purchase order to be checked
* @param {object} freeItem which item to add to the order free of charge
* @param {number} threshold the minimum order value before a free item can be added
* @param {boolean} a flag indicating whether the free item was successfully added
* @returns {object} the potentially updated purchaseOrder
* @throws exceptions in case
* - any of the mandatory parameters is null
* - in the absence of line items
* - if the free item has already been added to the order
*/
export function addFreeItem(po, freeItem, threshold, itemAdded) {
// sanity checking
if (po == null || freeItem == null || threshold == null) {
throw new Error(`mandatory parameter either not provided or null`);
}
// ensure there are line items provided by the purchase order
if (po.LineItems === undefined) {
throw new Error(`PO number ${po.PONumber} does not contain any line items`);
}
// bail out if the free Item has already been added to the purchase order
if (po.LineItems.find(({ Part }) => Part.Description === freeItem.Part.Description)) {
throw new Error(`${freeItem.Part.Description} has already been added to order ${po.PONumber}`);
}
// In, Out, and InOut Parameters are implemented using special interfaces, see section
// 5.2 in the MLE JavaScript Developers Guide
itemAdded.value = false;
// get the total order value
const poValue = po.LineItems
.map(x => x.Part.UnitPrice * x.Quantity)
.reduce(
(accumulator, currentValue) => accumulator + currentValue, 0
);
// add a free item to the purchase order if its value
// exceeds the threshold
if ( poValue > threshold ) {
// update the ItemNumber
freeItem.ItemNumber = (po.LineItems.length + 1)
po.LineItems.push(freeItem);
itemAdded.value = true;
}
return po;
}
/
SQLまたはPL/SQL(あるいはその両方)でaddFreeItem()を使用する場合は、コール仕様を作成する必要があります。PL/SQLパッケージを使用したコール仕様の例を次に示します:
create or replace package purchase_order_pkg as
function add_free_item(
p_po IN JSON,
p_free_item IN JSON,
p_threshold IN NUMBER,
p_item_added OUT BOOLEAN
)
return json as
mle module purchase_order_module
signature 'addFreeItem(any, any, number, Out)';
-- additional code
end purchase_order_pkg;
/
あなたの製品に300以上を費やしている各顧客に、素敵で楽しいフットバッグを提供することを決定したと仮定すると、これはそれを行うための1つの方法です:
-- let's list the number of line items per purchase order
select
p.id,
p.po_document.LineItems.size() as numItemsBefore
from
j_purchaseorder p;
次の出力が表示されます:
SQL> select
2 p.id,
3 p.po_document.LineItems.size() as numItemsBefore
4 from
5* j_purchaseorder p;
ID NUMITEMSBEFORE
___________________________________ _________________
09BA7F7EB40F034DE063020011ACD4A2 2
09BA7F7EB410034DE063020011ACD4A2 3
各発注の適格性を確認する前に、次の点に注意してください:
set serveroutput on long 100000 pages 100 lines 120 longchunk 120
declare
l_free_item JSON;
l_updated_po JSON;
l_item_added BOOLEAN;
-- stub to simulate error handling ... this isn't the way to do it
-- but should act as a good-enough approximation for this article
procedure log_error(
p_id j_purchaseorder.id%type,
p_error varchar2)
as
begin
dbms_output.put_line('an error occurred processing ID ' || p_id);
dbms_output.put_line(p_error);
end;
begin
-- this would have typically been selected from the database,
-- not in this example though, to keep it simple
-- note how ItemNumber is initialised to -1, it's updated by
-- add_free_item()
l_free_item := JSON('{
"ItemNumber": -1,
"Part":
{
"Description": "Super Fun Footbag",
"UPCCode": 1234567890,
"UnitPrice": 0.00
},
"Quantity": 1
}');
-- iterate over all purchase orders
for po in (select id, po_document from j_purchaseorder) loop
begin
l_item_added := false;
-- add a free item for purchases > 300
l_updated_po := purchase_order_pkg.add_free_item(
p_po => po.po_document,
p_free_item => l_free_item,
p_threshold => 300,
p_item_added => l_item_added
);
-- persist the updated order in the database, skip the operation
-- if the original PO has not been updated
if ( l_item_added ) then
update j_purchaseorder
set
po_document = l_updated_po
where
id = po.id;
end if;
exception when others then
log_error( po.id, sqlerrm );
end;
end loop;
end;
/
幸運な人がいるかどうか見てみましょう:
select
p.id,
p.po_document.LineItems.size() as numItemsafter
from
j_purchaseorder p;
ID 09BA7F7EB410034DE063020011ACD4A2のライン・アイテムの数が1増えました:
SQL> select
2 p.id,
3 p.po_document.LineItems.size() as numItemsafter
4 from
5* j_purchaseorder p;
ID NUMITEMSAFTER
___________________________________ ________________
09BA7F7EB40F034DE063020011ACD4A2 2
09BA7F7EB410034DE063020011ACD4A2 4
幸せなお客様がいらっしゃるようです:
select
json_serialize(
p.po_document.LineItems[3]
pretty
) as freebee
from
j_purchaseorder p
where
id = '09BA7F7EB410034DE063020011ACD4A2';
FREEBEE
_____________________________________________________
{
"ItemNumber" : 4,
"Part" :
{
"Description" : "Super Fun Footbag",
"UPCCode" : 1234567890,
"UnitPrice" : 0
},
"Quantity" : 1
}
同じPL/SQLブロックをもう一度実行しても、フットバッグは2回追加されません。JavaScriptコードでは、それらのうち1つのみが許可されます。
SQL> l
1 declare
2 l_free_item JSON;
3 l_updated_po JSON;
4 l_item_added BOOLEAN;
5
6 -- stub to simulate error handling ... this isn't the way to do it
7 -- but should act as a good-enough approximation for this article
8 procedure log_error(
9 p_id j_purchaseorder.id%type,
10 p_error varchar2)
11 as
12 begin
13 dbms_output.put_line('an error occurred processing ID ' || p_id);
14 dbms_output.put_line(p_error);
15 end;
16 begin
17 -- this would have typically been selected from the database,
18 -- not in this example though, to keep it simple
19 -- note how ItemNumber is initialised to -1, it's updated by
20 -- add_free_item()
21 l_free_item := JSON('{
22 "ItemNumber": -1,
23 "Part":
24 {
25 "Description": "Super Fun Footbag",
26 "UPCCode": 1234567890,
27 "UnitPrice": 0.00
28 },
29 "Quantity": 1
30 }');
31
32 -- iterate over all purchase orders
33 for po in (select id, po_document from j_purchaseorder) loop
34
35 begin
36 l_item_added := false;
37
38 -- add a free item for purchases > 300
39 l_updated_po := purchase_order_pkg.add_free_item(
40 p_po => po.po_document,
41 p_free_item => l_free_item,
42 p_threshold => 300,
43 p_item_added => l_item_added
44 );
45
46 -- persist the updated order in the database, skip the operation
47 -- if the original PO has not been updated
48 if ( l_item_added ) then
49
50 update j_purchaseorder
51 set
52 po_document = l_updated_po
53 where
54 id = po.id;
55 end if;
56 exception when others then
57 log_error( po.id, sqlerrm );
58 end;
59 end loop;
60* end;
SQL> /
an error occurred processing ID 09BA7F7EB410034DE063020011ACD4A2
ORA-04161: Error: Super Fun Footbag has already been added to order 672
ORA-04171: at addFreeItem (MARTIN.PURCHASE_ORDER_MODULE:30:15)
PL/SQL procedure successfully completed.
まとめ
この記事の目的は、JSONの操作に関して、データベース内JavaScriptの使いやすさを示すことでした。