Utilisation des API pour modifier ou supprimer des données de table
Voyez comment mettre à jour et supprimer des données de table Oracle NoSQL Database Cloud Service à l'aide d'API.
Cet article contient les informations suivantes :
Rubriques connexes
Utilisation de l'API pour mettre à jour les données
Vous pouvez utiliser la commande SQL UPSERT dans la demande d'interrogation pour mettre à jour ou insérer des données.
Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query().
/*Upsert data*/
private static void upsertRows(NoSQLHandle handle,String sqlstmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
QueryIterableResult results = handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}
String upsert_row = "UPSERT INTO stream_acct VALUES("+
"1,"+
"\"AP\","+
"\"2023-10-18\","+
"{\"firstName\": \"Adam\","+
"\"lastName\": \"Phillips\","+
"\"country\": \"Germany\","+
"\"contentStreamed\": [{"+
"\"showName\" : \"At the Ranch\","+
"\"showId\" : 26,"+
"\"showtype\" : \"tvseries\","+
"\"genres\" : [\"action\", \"crime\", \"spanish\"],"+
"\"numSeasons\" : 4,"+
"\"seriesInfo\": [ {"+
"\"seasonNum\" : 1,"+
"\"numEpisodes\" : 2,"+
"\"episodes\": [ {"+
"\"episodeID\": 20,"+
"\"episodeName\" : \"Season 1 episode 1\","+
"\"lengthMin\": 70,"+
"\"minWatched\": 70,"+
"\"date\" : \"2022-04-18\""+
"},"+
"{"+
"\"episodeID\": 30,"+
"\"lengthMin\": 60,"+
"\"episodeName\" : \"Season 1 episode 2\","+
"\"minWatched\": 60,"+
"\"date\" : \"2022-04-18\""+
"}]"+
"},"+
"{"+
"\"seasonNum\": 2,"+
"\"numEpisodes\" : 2,"+
"\"episodes\": [{"+
"\"episodeID\": 40,"+
"\"episodeName\" : \"Season 2 episode 1\","+
"\"lengthMin\": 40,"+
"\"minWatched\": 40,"+
"\"date\" : \"2022-04-25\""+
"},"+
"{"+
"\"episodeID\": 50,"+
"\"episodeName\" : \"Season 2 episode 2\","+
"\"lengthMin\": 45,"+
"\"minWatched\": 30,"+
"\"date\" : \"2022-04-27\""+
"}"+
"]"+
"},"+
"{"+
"\"seasonNum\": 3,"+
"\"numEpisodes\" : 2,"+
"\"episodes\": [{"+
"\"episodeID\": 60,"+
"\"episodeName\" : \"Season 3 episode 1\","+
"\"lengthMin\": 50,"+
"\"minWatched\": 50,"+
"\"date\" : \"2022-04-25\""+
"},"+
"{"+
"\"episodeID\": 70,"+
"\"episodeName\" : \"Season 3 episode 2\","+
"\"lengthMin\": 45,"+
"\"minWatched\": 30,"+
"\"date\" : \"2022-04-27\""+
"}"+
"]"+
"}"+
"]"+
"},"+
"{"+
"\"showName\": \"Bienvenu\","+
"\"showId\": 15,"+
"\"showtype\": \"tvseries\","+
"\"genres\" : [\"comedy\", \"french\"],"+
"\"numSeasons\" : 2,"+
"\"seriesInfo\": ["+
"{"+
"\"seasonNum\" : 1,"+
"\"numEpisodes\" : 2,"+
"\"episodes\": ["+
"{"+
"\"episodeID\": 20,"+
"\"episodeName\" : \"Bonjour\","+
"\"lengthMin\": 45,"+
"\"minWatched\": 45,"+
"\"date\" : \"2022-03-07\""+
"},"+
"{"+
"\"episodeID\": 30,"+
"\"episodeName\" : \"Merci\","+
"\"lengthMin\": 42,"+
"\"minWatched\": 42,"+
"\"date\" : \"2022-03-08\""+
"}"+
"]"+
"}"+
"]"+
"}"+
"]}) RETURNING *";
System.out.println("Upsert data ");
upsertRows(handle,upsert_row);Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query().
#upsert data
def upsert_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
result = handle.query(request)
print('Upsert data')
for r in result.get_results():
print('\t' + str(r))upsert_row = '''
UPSERT INTO stream_acct VALUES
(
1,
"AP",
"2023-10-18",
{
"firstName": "Adam",
"lastName": "Phillips",
"country": "Germany",
"contentStreamed": [{
"showName": "At the Ranch",
"showId": 26,
"showtype": "tvseries",
"genres": ["action", "crime", "spanish"],
"numSeasons": 4,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Season 1 episode 1",
"lengthMin": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"date": "2022-04-25"
},
{
"episodeID": 50,
"episodeName": "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022-04-27"
}]
},
{
"seasonNum": 3,
"numEpisodes": 2,
"episodes": [{
"episodeID": 60,
"episodeName": "Season 3 episode 1",
"lengthMin": 20,
"minWatched": 20,
"date": "2022-04-25"
},
{
"episodeID": 70,
"episodeName": "Season 3 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022 - 04 - 27 "
}]
}]
},
{
"showName": "Bienvenu",
"showId": 15,
"showtype": "tvseries",
"genres": ["comedy", "french"],
"numSeasons": 2,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date": "2022-03-07"
},
{
"episodeID": 30,
"episodeName": "Merci",
"lengthMin": 42,
"minWatched": 42,
"date": "2022-03-08"
}]
}]
}]
}
) RETURNING *
'''
upsert_data(handle,upsert_row) Pour exécuter une interrogation, utilisez la fonction Client.Query.
//upsert data in the table
func upsertRows(client *nosqldb.Client, err error,
tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Upsert failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
}upsert_data := `UPSERT INTO stream_acct VALUES(
1,
"AP",
"2023-10-18",
{
"firstName": "Adam",
"lastName": "Phillips",
"country": "Germany",
"contentStreamed": [
{
"showName": "At the Ranch",
"showId": 26,
"showtype": "tvseries",
"genres": [
"action",
"crime",
"spanish"
],
"numSeasons": 4,
"seriesInfo": [
{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [
{
"episodeID": 20,
"episodeName": "Season 1 episode 1",
"lengthMin": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}
]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [
{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"date": "2022-04-25"
},
{
"episodeID": 50,
"episodeName": "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022-04-27"
}
]
},
{
"seasonNum": 3,
"numEpisodes": 2,
"episodes": [
{
"episodeID": 60,
"episodeName": "Season 3 episode 1",
"lengthMin": 20,
"minWatched": 20,
"date": "2022-04-25"
},
{
"episodeID": 70,
"episodeName": "Season 3 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022 - 04 - 27 "
}
]
}
]
},
{
"showName": "Bienvenu",
"showId": 15,
"showtype": "tvseries",
"genres": [
"comedy",
"french"
],
"numSeasons": 2,
"seriesInfo": [
{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [
{
"episodeID": 20,
"episodeName": "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date": "2022-03-07"
},
{
"episodeID": 30,
"episodeName": "Merci",
"lengthMin": 42,
"minWatched": 42,
"date": "2022-03-08"
}
]
}
]
}
]
}) RETURNING *`
upsertRows(client, err,tableName,upsert_data)Vous pouvez utiliser la commande SQL UPSERT dans la demande d'interrogation pour mettre à jour ou insérer des données. Pour exécuter une interrogation, utilisez la méthode query.
/*upserts data in the table*/
async function upsertData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}async function upsertData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}const upsert_row = `UPSERT INTO stream_acct VALUES
(
1,
"AP",
"2023-10-18",
{
"firstName": "Adam",
"lastName": "Phillips",
"country": "Germany",
"contentStreamed": [{
"showName": "At the Ranch",
"showId": 26,
"showtype": "tvseries",
"genres": ["action", "crime", "spanish"],
"numSeasons": 4,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Season 1 episode 1",
"lengthMin": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"date": "2022-04-25"
},
{
"episodeID": 50,
"episodeName": "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022-04-27"
}]
},
{
"seasonNum": 3,
"numEpisodes": 2,
"episodes": [{
"episodeID": 60,
"episodeName": "Season 3 episode 1",
"lengthMin": 20,
"minWatched": 20,
"date": "2022-04-25"
},
{
"episodeID": 70,
"episodeName": "Season 3 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022 - 04 - 27 "
}]
}]
},
{
"showName": "Bienvenu",
"showId": 15,
"showtype": "tvseries",
"genres": ["comedy", "french"],
"numSeasons": 2,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date": "2022-03-07"
},
{
"episodeID": 30,
"episodeName": "Merci",
"lengthMin": 42,
"minWatched": 42,
"date": "2022-03-08"
}]
}]
}]
}) RETURNING *`
await upsertData(handle,upsert_row);
console.log("Upsert data into table");Vous pouvez utiliser la commande SQL UPSERT dans la demande d'interrogation pour mettre à jour ou insérer des données. Pour exécuter une interrogation, vous pouvez utiliser la méthode QueryAsync ou GetQueryAsyncEnumerable et effectuer une itération sur l'énumération asynchrone obtenue.
private static async Task upsertData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}private const string upsert_row = @"UPSERT INTO stream_acct VALUES
(
1,
""AP"",
""2023-10-18"",
{
""firstName"": ""Adam"",
""lastName"": ""Phillips"",
""country"": ""Germany"",
""contentStreamed"": [{
""showName"": ""At the Ranch"",
""showId"": 26,
""showtype"": ""tvseries"",
""genres"": [""action"", ""crime"", ""spanish""],
""numSeasons"": 4,
""seriesInfo"": [{
""seasonNum"": 1,
""numEpisodes"": 2,
""episodes"": [{
""episodeID"": 20,
""episodeName"": ""Season 1 episode 1"",
""lengthMin"": 75,
""minWatched"": 75,
""date"": ""2022-04-18""
},
{
""episodeID"": 30,
""lengthMin"": 60,
""episodeName"": ""Season 1 episode 2"",
""minWatched"": 40,
""date"": ""2022 - 04 - 18""
}]
},
{
""seasonNum"": 2,
""numEpisodes"": 2,
""episodes"": [{
""episodeID"": 40,
""episodeName"": ""Season 2 episode 1"",
""lengthMin"": 40,
""minWatched"": 30,
""date"": ""2022-04-25""
},
{
""episodeID"": 50,
""episodeName"": ""Season 2 episode 2"",
""lengthMin"": 45,
""minWatched"": 30,
""date"": ""2022-04-27""
}]
},
{
""seasonNum"": 3,
""numEpisodes"": 2,
""episodes"": [{
""episodeID"": 60,
""episodeName"": ""Season 3 episode 1"",
""lengthMin"": 20,
""minWatched"": 20,
""date"": ""2022-04-25""
},
{
""episodeID"": 70,
""episodeName"": ""Season 3 episode 2"",
""lengthMin"": 45,
""minWatched"": 30,
""date"": ""2022 - 04 - 27""
}]
}]
},
{
""showName"": ""Bienvenu"",
""showId"": 15,
""showtype"": ""tvseries"",
""genres"": [""comedy"", ""french""],
""numSeasons"": 2,
""seriesInfo"": [{
""seasonNum"": 1,
""numEpisodes"": 2,
""episodes"": [{
""episodeID"": 20,
""episodeName"": ""Bonjour"",
""lengthMin"": 45,
""minWatched"": 45,
""date"": ""2022-03-07""
},
{
""episodeID"": 30,
""episodeName"": ""Merci"",
""lengthMin"": 42,
""minWatched"": 42,
""date"": ""2022-03-08""
}]
}]
}]
}
) RETURNING *";
await upsertData(client,upsert_row);
Console.WriteLine("Upsert data in table");Utilisation de l'API pour mettre à jour les données
Vous pouvez utiliser la commande UPDATE SQL dans la demande d'interrogation pour mettre à jour les données.
Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query().
//Update data
private static void updateRows(NoSQLHandle handle,String sqlstmt) throws Exception {
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
handle.query(queryRequest);
System.out.println("Updated table " + tableName);
}/* update non-JSON data*/
String upd_stmt ="UPDATE stream_acct SET account_expiry=\"2023-12-28T00:00:00.0Z\" WHERE acct_Id=3";
updateRows(handle,upd_stmt);Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query().
#update data
def update_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
result = handle.query(request)
print('Data Updated in table: stream_acct')# update non-JSON data
upd_stmt ='''UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3'''
update_data(handle,upd_stmt)Pour exécuter une interrogation, utilisez la fonction Client.Query.
//update data in the table
func updateRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Upsert failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
fmt.Printf("Updated data in the table: \n")
}updt_stmt := "UPDATE stream_acct SET account_expiry='2023-12-28T00:00:00.0Z' WHERE acct_Id=3"
updateRows(client, err,tableName,updt_stmt)Pour exécuter une interrogation, utilisez la méthode query.
/*updates data in the table*/
async function updateData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}async function updateData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}const updt_stmt = 'UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3'
await updateData(handle,updt_stmt);
console.log("Data updated in the table");Vous pouvez utiliser la commande UPDATE SQL dans la demande d'interrogation pour mettre à jour les données. Pour exécuter une interrogation, vous pouvez appeler la méthode QueryAsync ou appeler la méthode GetQueryAsyncEnumerable et effectuer une itération sur l'énumération asynchrone obtenue.
private static async Task updateData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}private const string updt_stmt =
@"UPDATE stream_acct SET account_expiry =""2023-12-28T00:00:00.0Z"" WHERE acct_Id=3";
await updateData(client,updt_stmt);
Console.WriteLine("Data updated in the table");Utilisation de l'API pour mettre à jour les données JSON
Vous pouvez utiliser la commande UPDATE SQL pour ajouter et supprimer des données dans un objet JSON de votre table.
Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query().
//Update data
private static void updateRows(NoSQLHandle handle,String sqlstmt) throws Exception {
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
handle.query(queryRequest);
System.out.println("Updated table " + tableName);
}/* update JSON data and add a node*/
String upd_json_addnode="UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes
{\"date\" : \"2022-04-26\","+
"\"episodeID\" : 43,"+
"\"episodeName\" : \"Season 2 episode 2\","+
"\"lengthMin\" : 45,"+
"\"minWatched\" : 45} WHERE acct_Id=2 RETURNING *";
updateRows(handle,upd_json_addnode);
/* update JSON data and remove a node*/
String upd_json_delnode="UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] WHERE acct_Id=2 RETURNING *";
updateRows(handle,upd_json_delnode);Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query().
#update data
def update_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
result = handle.query(request)
print('Data Updated in table: stream_acct')# update JSON data and add a node
upd_json_addnode = '''UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
"date" : "2022-04-26",
"episodeID" : 43,
"episodeName" : "Season 2 episode 2",
"lengthMin" : 45,
"minWatched" : 45} WHERE acct_Id=2 RETURNING *'''
update_data(handle,upd_json_addnode)
# update JSON data and delete a node
upd_json_delnode = '''UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1] WHERE acct_Id=2 RETURNING *'''
update_data(handle,upd_json_delnode)Pour exécuter une interrogation, utilisez la fonction Client.Query.
//update data in the table
func updateRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Upsert failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
fmt.Printf("Updated data in the table: \n")
}upd_json_addnode := `UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
"date" : "2022-04-26",
"episodeID" : 43,
"episodeName" : "Season 2 episode 2",
"lengthMin" : 45,
"minWatched" : 45} WHERE acct_Id=2 RETURNING *`
updateRows(client, err,tableName,upd_json_addnode)
upd_json_delnode := `UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1]
WHERE acct_Id=2 RETURNING *`
updateRows(client, err,tableName,upd_json_delnode)Pour exécuter une interrogation, utilisez la méthode query.
/*updates data in the table*/
async function updateData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}async function updateData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}const upd_json_addnode =
`UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
"date" : "2022-04-26",
"episodeID" : 43,
"episodeName" : "Season 2 episode 2",
"lengthMin" : 45,
"minWatched" : 45} WHERE acct_Id=2 RETURNING *`
await updateData(handle,upd_json_addnode);
console.log("New data node added in the table");
const upd_json_delnode =
'UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1]
WHERE acct_Id=2 RETURNING *'
await updateData(handle,upd_json_delnode);
console.log("New Data node removed from the table");Vous pouvez utiliser la commande UPDATE SQL pour ajouter et supprimer des données dans un objet JSON de votre table. Pour exécuter une interrogation, vous pouvez appeler la méthode QueryAsync ou appeler la méthode GetQueryAsyncEnumerable et effectuer une itération sur l'énumération asynchrone obtenue.
private static async Task updateData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}private const string upd_json_addnode =
@"UPDATE stream_acct acct1 ADD acct1.acct_data.contentStreamed.seriesInfo[1].episodes {
""date"" : ""2022-04-26"",
""episodeID"" : 43,
""episodeName"" : ""Season 2 episode 2"",
""lengthMin"" : 45,
""minWatched"" : 45} WHERE acct_Id=2 RETURNING *";
await updateData(client,upd_json_addnode);
Console.WriteLine("New data node added in the table");
private const string upd_json_delnode =
"UPDATE stream_acct acct1 REMOVE acct1.acct_data.contentStreamed.seriesInfo[1].episodes[1]
WHERE acct_Id=2 RETURNING *";
await updateData(client,upd_json_delnode);
Console.WriteLine("New Data node removed from the table");Utilisation de l'API d'interrogation pour supprimer des données
Vous pouvez utiliser l'API QueryRequest et supprimer une ou plusieurs rangées d'une table NoSQL qui satisfont à une condition de filtre.
Vous pouvez utiliser la commande DELETE SQL dans la demande d'interrogation pour supprimer des données. Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query().
//delete rows based on a filter condition
private static void deleteRows(NoSQLHandle handle, String sqlstmt) throws Exception {
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
handle.query(queryRequest);
System.out.println("Deleted row(s) from table " + tableName);
}
String del_stmt ="DELETE FROM stream_acct acct1 WHERE
acct1.acct_data.firstName=\"Adelaide\" AND acct1.acct_data.lastName=\"Willard\"";
/*delete rows based on a filter condition*/
deleteRows(handle,del_stmt);Vous pouvez utiliser la commande DELETE SQL dans la demande d'interrogation pour supprimer des données. Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query().
#del row(s) with a filter condition
def delete_rows(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
result = handle.query(request)
print('Deleted data from table: stream_acct')# delete data based on a filter condition
del_stmt ='''DELETE FROM stream_acct acct1 WHERE
acct1.acct_data.firstName="Adelaide" AND acct1.acct_data.lastName="Willard"'''
delete_rows(handle,del_stmt)Vous pouvez utiliser la commande DELETE SQL dans la demande d'interrogation pour supprimer des données. Pour exécuter une interrogation, utilisez la fonction Client.Query.
//delete rows based on a filter condition
func deleteRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Upsert failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
fmt.Printf("Deleted data from the table: %v\n",tableName)
}delete_stmt := `DELETE FROM stream_acct acct1 WHERE
acct1.acct_data.firstName="Adelaide" AND acct1.acct_data.lastName="Willard"`
deleteRows(client, err,tableName,delete_stmt)Vous pouvez utiliser la commande DELETE SQL dans la demande d'interrogation pour supprimer des données. Pour exécuter une interrogation, utilisez la méthode query.
/*deletes data based on a filter conditioin */
async function deleteRows(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}await deleteRows(handle,del_stmt);
console.log("Rows deleted");async function deleteRows(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}await deleteRows(handle,del_stmt);
console.log("Rows deleted");Vous pouvez utiliser la commande DELETE SQL dans la demande d'interrogation pour supprimer des données. Pour exécuter une interrogation, vous pouvez appeler la méthode QueryAsync ou appeler la méthode GetQueryAsyncEnumerable et effectuer une itération sur l'énumération asynchrone obtenue.
private static async Task deleteRows(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}await deleteRows(client,del_stmt);
Console.WriteLine("Rows removed from the table");Utilisation de l'API pour supprimer une seule rangée
Vous pouvez utiliser l'API DeleteRequest et supprimer une seule rangée à l'aide d'une clé primaire.
DeleteRequest peut être utilisée pour effectuer des suppressions inconditionnelles et conditionnelles.
- Supprimer une rangée existante. Il s'agit de la valeur par défaut.
- N'aboutissez au succès que si la rangée existe et que sa version correspond à une version spécifique. Utilisez
setMatchVersionpour ce cas.
//delete row based on primary KEY
private static void delRow(NoSQLHandle handle, MapValue m1) throws Exception {
DeleteRequest delRequest = new DeleteRequest().setKey(m1).setTableName(tableName);
DeleteResult del = handle.delete(delRequest);
if (del.getSuccess()) {
System.out.println("Delete succeed");
}
else {
System.out.println("Delete failed");
}
}
/*delete a single row*/
MapValue m1= new MapValue();
m1.put("acct_Id",1);
delRow(handle,m1);
Des rangées uniques sont supprimées à l'aide de borneo.DeleteRequest à l'aide d'une valeur de clé primaire.
#del row with a primary KEY
def del_row(handle,table_name):
request = DeleteRequest().set_key({'acct_Id': 1}).set_table_name(table_name)
result = handle.delete(request)
print('Deleted data from table: stream_acct')
# delete row based on primary key
del_row(handle,'stream_acct')
DeleteRequest est utilisé pour supprimer une rangée d'une table. La rangée est identifiée à l'aide d'une clé primaire spécifiée dans DeleteRequest.Key.
//delete with primary key
func delRow(client *nosqldb.Client, err error, tableName string)(){
key := &types.MapValue{}
key.Put("acct_Id",1)
delReq := &nosqldb.DeleteRequest{
TableName: tableName,
Key: key,
}
delRes, err := client.Delete(delReq)
if err != nil {
fmt.Printf("failed to delete a row: %v", err)
return
}
if delRes.Success {
fmt.Println("Delete succeeded")
}
}
delRow(client, err,tableName)
Utilisez la méthode delete pour supprimer une rangée d'un tableau. Pour plus de détails sur la méthode, voir la classe NoSQLClient.
Vous devez transmettre le nom de la table et la clé primaire de la rangée. En outre, vous pouvez rendre l'opération de suppression conditionnelle en spécifiant une valeur RowVersion de la rangée précédemment retournée par les méthodes get ou put.
/*delete row based on primary key*/
async function delRow(handle) {
try {
/* Unconditional delete, should succeed.*/
var result = await handle.delete(TABLE_NAME, { acct_Id: 1 });
/* Expected output: delete succeeded*/
console.log('delete ' + result.success ? 'succeeded' : 'failed');
} catch(error) {
console.error(' Error: ' + error.message);
}
}await delRow(handle);
console.log("Row deleted based on primary key");
interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}/*delete row based on primary key*/
async function delRow(handle: NoSQLClient) {
try {
/* Unconditional delete, should succeed.*/
var result = await handle.delete<StreamInt>(TABLE_NAME, { acct_Id: 1 });
/* Expected output: delete succeeded*/
console.log('delete ' + result.success ? 'succeeded' : 'failed');
} catch(error) {
console.error(' Error: ' + error.message);
}
}await delRow(handle);
console.log("Row deleted based on primary key");
Pour supprimer une rangée, utilisez la méthode DeleteAsync. Transmettez-lui le nom de la table et la clé primaire de la rangée à supprimer. Cette méthode utilise la clé primaire comme MapValue. Les noms de champ doivent être identiques aux noms de colonne de clé primaire de la table.
Les méthodes DeleteAsync et DeleteIfVersionAsync retournent Task<DeleteResult<RecordValue>>. L'instance DeleteResult contient le statut de réussite de l'opération de suppression. L'opération de suppression peut échouer si la rangée avec la clé primaire indiquée n'existe pas ou s'il s'agit d'une suppression conditionnelle et que la version de rangée fournie ne correspond pas à la version de rangée existante.
private static async Task delRow(NoSQLClient client){
var primaryKey = new MapValue
{
["acct_Id"] = 1
};
// Unconditional delete, should succeed.
var deleteResult = await client.DeleteAsync(TableName, primaryKey);
// Expected output: Delete succeeded.
Console.WriteLine("Delete {0}.",deleteResult.Success ? "succeeded" : "failed");
}
await delRow(client);
Console.WriteLine("Row deleted based on primary key");Utilisation de l'API pour supprimer plusieurs rangées
Vous pouvez utiliser l'API MultiDeleteRequest et supprimer plusieurs rangées d'une table NoSQL.
Vous pouvez utiliser MultiDeleteRequest pour supprimer plusieurs rangées d'une table dans une opération atomique. La clé utilisée peut être partielle mais doit contenir tous les champs de la clé de partition. Un intervalle peut être spécifié pour supprimer un intervalle de clés. Comme cette opération peut dépasser la quantité maximale de données pouvant être modifiées en une seule opération, une clé de continuation peut être utilisée pour poursuivre l'opération.
Si la clé primaire d'une table est <AAAAAMM, horodatage> et que sa clé de partition est AAAAMM, tous les enregistrements ayant atteint le même mois seront dans la même partition horizontale. Il est possible de supprimer un intervalle de valeurs d'horodatage pour un mois spécifique à l'aide de la classe MultiDeleteRequest.
Voir Informations de référence sur l'API de la trousse SDK Java pour Oracle NoSQL pour plus de détails sur les différentes classes et méthodes.
//Delete multiple rows from the table
private static void delMulRows(NoSQLHandle handle,int pinval) throws Exception {
MapValue key = new MapValue().put("pin", 1234567);
MultiDeleteRequest multiDelRequest = new MultiDeleteRequest()
.setKey(key)
.setTableName(tableName);
MultiDeleteResult mRes = handle.multiDelete(multiDelRequest);
System.out.println("MultiDelete result = " + mRes);
}
/*delete multiple rows using shard key*/
delMulRows(handle,1234567);Vous pouvez utiliser la classe borneo.MultiDeleteRequest pour effectuer plusieurs suppressions en une seule opération atomique.
Voir Informations de référence sur l'API de la trousse SDK Python pour Oracle NoSQL pour plus de détails sur les différentes classes et méthodes.
#delete multiple rows
def multirow_delete(handle,table_name,pinval):
request = MultiDeleteRequest().set_table_name(table_name).set_key({'pin': pinval})
result = handle.multi_delete(request)
)/*delete multiple rows using shard key*/
multirow_delete(handle,'examplesAddress',1234567)Vous pouvez utiliser la méthode MultiDelete pour supprimer plusieurs rangées d'une table en une seule opération atomique.
Voir Informations de référence sur l'API de la trousse SDK Go pour Oracle NoSQL pour plus de détails sur les différentes classes et méthodes.
//delete multiple rows
func delMulRows(client *nosqldb.Client, err error, tableName string,pinval int)(){
shardKey := &types.MapValue{}
shardKey.Put("pin", pinval)
multiDelReq := &nosqldb.MultiDeleteRequest{
TableName: tableName,
Key: shardKey,
}
multiDelRes, err := client.MultiDelete(multiDelReq)
if err != nil {
fmt.Printf("failed to delete multiple rows: %v", err)
return
}
fmt.Printf("MultiDelete result=%v\n", multiDelRes)
}/*delete multiple rows using shard key*/
delMulRows(client, err,tableName,1234567)Vous pouvez supprimer plusieurs rangées ayant la même clé de partition en une seule opération atomique à l'aide de la méthode deleteRange.
//deletes multiple rows
async function mulRowDel(handle,pinval){
try {
/* Unconditional delete, should succeed.*/
var result = await handle.deleteRange(TABLE_NAME, { pin: pinval });
/* Expected output: delete succeeded*/
console.log('delete ' + result.success ? 'succeeded' : 'failed');
} catch(error) {
console.error(' Error: ' + error.message);
}
}/*delete multiple rows using shard key*/
await mulRowDel(handle,1234567);interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}//deletes multiple rows
async function mulRowDel(handle: NoSQLClient,pinVal: Integer){
try {
/* Unconditional delete, should succeed.*/
var result = await handle.deleteRange<StreamInt>(TABLE_NAME, { pin: pinval });
/* Expected output: delete succeeded*/
console.log('delete ' + result.success ? 'succeeded' : 'failed');
} catch(error) {
console.error(' Error: ' + error.message);
}
}/*delete multiple rows using shard key*/
await mulRowDel(handle,1234567);Vous pouvez supprimer plusieurs rangées ayant la même clé de partition en une seule opération atomique à l'aide de la méthode DeleteRangeAsync.
//delete multiple rows
private static async Task mulDelRows(NoSQLClient client,int pinval){
var parKey = new MapValue {["pin"] = pinval};
var options = new DeleteRangeOptions();
do
{
var result = await client.DeleteRangeAsync(TableName,parKey,options);
Console.WriteLine($"Deleted {result.DeletedCount} row(s)");
options.ContinuationKey = result.ContinuationKey;
} while(options.ContinuationKey != null);
}/*delete multiple rows using shard key*/
await mulDelRows(client,1234567);Utilisation des API pour modifier ou supprimer des données de table