データの挿入、変更および削除
表で、様々なデータ操作を実行できます。データを追加したり、既存のデータを変更したり、データを削除できます。
例のとおりに操作する場合は、スクリプトbaggageschema_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。
KVSTOREまたはKVLiteを起動し、SQL.shellを開きます。
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreloadコマンドを使用して、スクリプトを実行します。load -file baggageschema_loaddata.sqlデータの挿入
INSERT文は、新しい行を作成して、指定した表に追加するために使用します。表名の後にオプションの列を指定できます。このリストには、表の列のサブセットの列名が含まれます。このサブセットには、すべての主キー列が含まれている必要があります。列リストが存在しない場合、デフォルトの列リストは、表のすべての列がCREATE TABLE文で指定された順序で含まれる列リストです。
列リストの列は、VALUES句の後にリストされた式(またはDEFAULTキーワード)に1対1で対応します(式/DEFAULTの数が列数と一致しない場合はエラーが発生します)。これらの式/DEFAULTによって、新しい行の関連する列の値が計算されます。式が複数の項目を返す場合は、エラーが発生します。式で結果が返されない場合は、NULLがその式の結果として使用されます。式ではなく、DEFAULTキーワードがVALUESリストに表示される場合は、関連付けられた列のデフォルト値が、新しい行のその列の値として使用されます。デフォルト値は、列リストの列の数が表の列の合計数より少ない場合、欠落している列にも使用されます。
例1:
BaggageInfo表へのすべての列値を提供する行の挿入:INSERT INTO BaggageInfo VALUES(
1762392196147,
"Birgit Naquin",
"M",
"165-742-5715",
"QD1L0T",
[ {
"id" : "7903989918469",
"tagNum" : "17657806240229",
"routing" : "JFK/MAD",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MAD",
"flightLegs" : [ {
"flightNo" : "BM495",
"flightDate" : "2019-03-07T07:00:00Z",
"fltRouteSrc" : "JFK",
"fltRouteDest" : "MAD",
"estimatedArrival" : "2019-03-07T14:00:00Z",
"actions" : [ {
"actionAt" : "MAD",
"actionCode" : "Offload to Carousel at MAD",
"actionTime" : "2019-03-07T13:54:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "ONLOAD to MAD",
"actionTime" : "2019-03-07T07:00:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "BagTag Scan at JFK",
"actionTime" : "2019-03-07T06:53:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "Checkin at JFK",
"actionTime" : "2019-03-07T05:03:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
"bagArrivalDate" : "2019-03-07T13:51:00Z"
} ]
)例2: DEFAULT句を指定して、INSERT文の実行時に一部のデータをスキップします。
DEFAULTを指定すると、一部の列のデータをスキップできます。
INSERT INTO BaggageInfo VALUES(
1762397286805,
"Bonnie Williams",
DEFAULT,
DEFAULT,
"CZ1O5I",
[ {
"id" : "79039899129693",
"tagNum" : "17657806216554",
"routing" : "SFO/ORD/FRA",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "FRA",
"flightLegs" : [ {
"flightNo" : "BM572",
"flightDate" : "2019-03-02T05:00:00Z",
"fltRouteSrc" : "SFO",
"fltRouteDest" : "ORD",
"estimatedArrival" : "2019-03-02T09:00:00Z",
"actions" : [ {
"actionAt" : "SFO",
"actionCode" : "ONLOAD to ORD",
"actionTime" : "2019-03-02T05:24:00Z"
}, {
"actionAt" : "SFO",
"actionCode" : "BagTag Scan at SFO",
"actionTime" : "2019-03-02T04:52:00Z"
}, {
"actionAt" : "SFO",
"actionCode" : "Checkin at SFO",
"actionTime" : "2019-03-02T03:28:00Z"
} ]
}, {
"flightNo" : "BM582",
"flightDate" : "2019-03-02T05:24:00Z",
"fltRouteSrc" : "ORD",
"fltRouteDest" : "FRA",
"estimatedArrival" : "2019-03-02T13:24:00Z",
"actions" : [ {
"actionAt" : "FRA",
"actionCode" : "Offload to Carousel at FRA",
"actionTime" : "2019-03-02T13:20:00Z"
}, {
"actionAt" : "ORD",
"actionCode" : "ONLOAD to FRA",
"actionTime" : "2019-03-02T12:54:00Z"
}, {
"actionAt" : "ORD",
"actionCode" : "OFFLOAD from ORD",
"actionTime" : "2019-03-02T12:30:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-02T13:18:00Z",
"bagArrivalDate" : "2019-03-02T13:18:00Z"
} ]
)例3: INSERT文で列名を指定し、列をスキップします。
表の一部の列のみのデータがある場合は、INSERT句で列の名前を指定し、VALUES句で対応する値を指定できます。
INSERT INTO BaggageInfo(ticketNo, fullName,confNo,bagInfo) VALUES(
1762355349471,
"Bryant Weber",
"LI7N1W",
[ {
"id" : "79039899149056",
"tagNum" : "17657806234185",
"routing" : "MEL/LAX/MIA",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MIA",
"flightLegs" : [ {
"flightNo" : "BM114",
"flightDate" : "2019-03-01T12:00:00Z",
"fltRouteSrc" : "MEL",
"fltRouteDest" : "LAX",
"estimatedArrival" : "2019-03-02T02:00:00Z",
"actions" : [ {
"actionAt" : "MEL",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-03-01T12:20:00Z"
}, {
"actionAt" : "MEL",
"actionCode" : "BagTag Scan at MEL",
"actionTime" : "2019-03-01T11:52:00Z"
}, {
"actionAt" : "MEL",
"actionCode" : "Checkin at MEL",
"actionTime" : "2019-03-01T11:43:00Z"
} ]
}, {
"flightNo" : "BM866",
"flightDate" : "2019-03-01T12:20:00Z",
"fltRouteSrc" : "LAX",
"fltRouteDest" : "MIA",
"estimatedArrival" : "2019-03-02T16:21:00Z",
"actions" : [ {
"actionAt" : "MIA",
"actionCode" : "Offload to Carousel at MIA",
"actionTime" : "2019-03-02T16:18:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MIA",
"actionTime" : "2019-03-02T16:12:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-03-02T16:02:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-02T16:09:00Z",
"bagArrivalDate" : "2019-03-02T16:09:00Z"
} ]
)データの更新
UPDATE文を使用して、表の行を更新できます。
SET句は、ターゲット式と新しい値式の2つの式で構成されます。ターゲット式は、更新する項目を返します。新しい値式は、ゼロ以上の項目を返すことができます。空の結果が返された場合、SETは何も処理を行いません。複数の項目が返された場合、項目は新しく作成された配列の中に格納されます(これは、SELECT句が選択リスト内の複数値式を処理する方法と同じです)。そのため、実質的には、新しい値式の結果には、最大で1つの項目が含まれます。WHERE句では、どの行を更新するかを指定します。現在の実装では、単一行の更新のみが許可されるため、WHERE句で完全な主キーを指定する必要があります。- オプションの
RETURNING句は、SELECT句と同じように機能します。つまり、"*"を指定でき、その場合は更新された完全な行が返されます。または、返される必要があるものを指定する式のリストを指定できます。 - さらに、
WHERE条件を満たす行がない場合、UPDATE文は空の結果を返します。
例1: 列値を変更する簡単な例。
指定したチケット番号の一部の列値を更新します。
UPDATE BaggageInfo
SET contactPhone = "823-384-1964",
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810例2: 行データを更新し、RETURNING句を使用して値をフェッチします。
UPDATE句の実行後に、RETURNING句を使用してデータをフェッチできます。
UPDATE BaggageInfo
SET contactPhone = "823-384-1964",
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810 RETURNING *出力:
{"ticketNo":1762344493810,"fullName":"Adam Phillips","gender":"M","contactPhone":"823-384-1964",
"confNo":"LE6J4Y",
"bagInfo":{"bagInfo":[{"bagArrivalDate":"2019.02.02 at 03:13:00 AEDT","flightLegs":
[{"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019.02.01 at 01:13:00 EST"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019.02.01 at 00:47:00 EST"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019.01.31 at 23:38:00 EST"}],
"estimatedArrival":"2019.02.01 at 03:00:00 PST","flightDate":"2019.02.01 at 01:00:00 EST",
"flightNo":"BM604","fltRouteDest":"LAX","fltRouteSrc":"MIA"},{"actions":
[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019.02.02 at 03:15:00 AEDT"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019.02.01 at 07:35:00 PST"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019.02.01 at 07:18:00 PST"}],
"estimatedArrival":"2019.02.02 at 03:15:00 AEDT","flightDate":"2019.01.31 at 22:13:00 PST",
"flightNo":"BM667","fltRouteDest":"MEL","fltRouteSrc":"LAX"}],"id":"79039899165297",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
"lastSeenTimeGmt":"2019.02.02 at 03:13:00 AEDT","routing":"MIA/LAX/MEL","tagNum":"17657806255240"}]}}JSONデータの変更
JSONデータの更新中に、
WHERE、SETおよびRETURNING句に加えて、次の句を使用できます。
ADD句は、1つ以上の配列に新しい要素を追加するために使用します。通常は1つ以上の配列項目を返す必要があるターゲット式、新規要素を配置する各配列内の位置を指定するオプションの位置式、挿入する新規要素を返す新規要素式で構成されます。PUT句は、主に新しいフィールドをJSONドキュメントに追加する場合に使用します。これはターゲット式で構成され、通常、この式は、ターゲットJSONドキュメントに挿入される1つ以上のフィールドを返します。REMOVE句は、削除する項目を計算する単一のターゲット式で構成されます。
例1: 表を更新し、JSONオブジェクトにデータを追加します
乗客の特定の飛行区間のアクション配列に(特定の配列要素で)要素を追加します。デフォルトでは、要素は末尾に追加されます。数値が指定されている場合は、その位置に挿入されます。次の例では、新しい要素を2番目の位置に追加するとします。
UPDATE BaggageInfo bag
ADD bag.bagInfo[0].flightLegs[0].actions 2 {"actionAt" : "LAX",
"actionCode" : "WAITING at LAX",
"actionTime" : "2019-02-01T06:13:00Z"}
WHERE ticketNo=1762344493810
RETURNING *例2: 表を更新し、JSONオブジェクトからのデータを更新します。
SET句を使用して、JSONオブジェクトからのデータを更新できます。ここで、actions配列の2番目の要素は、指定されたチケット番号の新しい値で更新されます。
UPDATE BaggageInfo bag
SET bag.bagInfo[0].flightLegs[0].actions[2]=
{"actionAt" : "LAX",
"actionCode" : "STILL WAITING at LAX",
"actionTime" : "2019-02-01T06:15:00Z"}
WHERE ticketNo=1762344493810 RETURNING *例3: 表を更新し、JSONオブジェクトのデータを削除します。
REMOVE句を使用して、特定の要素を配列から削除できます。配列の索引を使用して、削除する必要がある配列の要素を指定する必要があります。UPDATE BaggageInfo bag
REMOVE bag.bagInfo[0].flightLegs[0].actions[1]
WHERE ticketNo=1762344493810
RETURNING *データの削除
DELETE文は、条件を満たす行セットを表から削除するために使用されます。条件は、SELECT式と同じように動作するWHERE句に指定されます。DELETE文の結果は、RETURNING句が存在するかどうかによって異なります。RETURNING句がない場合、DELETEは削除された行数を返します。それ以外の場合、削除された行ごとに、RETURNING句の後の式がSELECT句と同じ方法で計算され、結果がアプリケーションに返されます。
例1: 単純なWHERE句を使用して、表からデータを削除します。
指定したフルネームを持つユーザーに対応するデータを削除します。
DELETE FROM BaggageInfo
WHERE fullName = "Bonnie Williams"例2: RETURNING句を使用して、表からデータを削除します。
RETURNING句は、削除する行の詳細をフェッチします。次の例では、削除されるチケット番号に対応するフルネームおよび確認番号をフェッチしています。
DELETE FROM BaggageInfo
WHERE ticketNo = 1762392196147
RETURNING fullName,confNo出力:
{"fullName":"Birgit Naquin","confNo":"QD1L0T"}ノート:
DELETE文の実行中にエラーが発生した場合は、一部の行が削除され、一部の行は削除されない可能性があります。システムでは、どの行が削除され、どの行が削除されていないかは追跡されません。これは、Oracle NoSQL Databaseでは低レイテンシ操作に重点が置かれているためです。シャードをまたいで長時間実行される操作は、2フェーズ・コミットおよびロック・メカニズムを使用して調整されません。このような場合は、アプリケーションでDELETE文を再実行することをお薦めします。