如何使用 Node.js 在 Oracle DB 中插入多条记录 [英] How to insert multiple records into Oracle DB using Node.js

问题描述

我可以在表中插入一条记录，但我想一次向表中插入多条记录-

I am able to insert one record into a table, but i want to insert multiple records at once into the table-

我的代码如下-

var doinsert_autocommit = function (conn, cb) {
var query="INSERT INTO test VALUES (:id,:name)";
var values=[{1,'rate'},{5,'ratee'}]; 

如果我使用 [1,'rat']- 它适用于插入一行.

If i use [1,'rat']- its working for inserting one row.

conn.execute(

"INSERT INTO test VALUES (:id,:name)",
values, // Bind values
{ autoCommit: true},  // Override the default non-autocommit behavior
function(err, result)
{
  if (err) {
    return cb(err, conn);
  } else {
    console.log("Rows inserted: " + result.rowsAffected);  // 1
    return cb(null, conn);
  }
});

};

推荐答案

2019/04/25 更新:

Update 2019/04/25:

该驱动程序自 2.2 版起就内置了对批处理 SQL 执行的支持.在可能的情况下使用 connection.executeMany() .它以较低的复杂性提供所有性能优势.有关更多详细信息，请参阅文档的批处理语句执行部分:https://oracle.github.io/node-oracledb/doc/api.html#batchexecution

The driver, since version 2.2, has built-in support for batch SQL execution. Use connection.executeMany() for this when possible. It offers all of the performance benefits with less complexity. See the Batch Statement Execute section of the documentation for more details: https://oracle.github.io/node-oracledb/doc/api.html#batchexecution

上一个答案:

目前，驱动程序仅支持与 PL/SQL 的数组绑定，不支持直接 SQL.我们希望在未来改进这一点.现在，您可以执行以下操作...

Currently, the driver only supports array binds with PL/SQL, not direct SQL. We hope to improve this in the future. For now, you can do the following...

鉴于此表:

create table things (
  id   number not null,
  name varchar2(50) not null
)
/

以下应该有效:

var oracledb = require('oracledb');
var config = require('./dbconfig');
var things = [];
var idx;

function getThings(count) {
  var things = [];

  for (idx = 0; idx < count; idx += 1) {
    things[idx] = {
      id: idx,
      name: "Thing number " + idx
    };
  }

  return things;
}

// Imagine the 'things' were fetched via a REST call or from a file.
// We end up with an array of things we want to insert.
things = getThings(500);

oracledb.getConnection(config, function(err, conn) {
  var ids = [];
  var names = [];
  var start = Date.now();

  if (err) {throw err;}

  for (idx = 0; idx < things.length; idx += 1) {
    ids.push(things[idx].id);
    names.push(things[idx].name);
  }

  conn.execute(
    ` declare
        type number_aat is table of number
          index by pls_integer;
        type varchar2_aat is table of varchar2(50)
          index by pls_integer;

        l_ids   number_aat := :ids;
        l_names varchar2_aat := :names;
      begin
        forall x in l_ids.first .. l_ids.last
          insert into things (id, name) values (l_ids(x), l_names(x));
      end;`,
    {
      ids: {
        type: oracledb.NUMBER,
        dir: oracledb.BIND_IN,
        val: ids
      }, 
      names: {
        type: oracledb.STRING,
        dir: oracledb.BIND_IN,
        val: names
      }
    },
    {
      autoCommit: true
    },
    function(err) {
      if (err) {console.log(err); return;}

      console.log('Success. Inserted ' + things.length + ' rows in ' + (Date.now() - start) + ' ms.');
    }
  );
});

这将插入 500 行，单次往返数据库.另外，数据库中 SQL 和 PL/SQL 引擎之间的单个上下文切换.

That will insert 500 rows with a single roundtrip to the database. Plus, a single context switch between the SQL and PL/SQL engines in the DB.

如您所见，数组必须单独绑定(您不能绑定对象数组).这就是为什么该示例演示了如何将它们分解为单独的数组以进行绑定.随着时间的推移，这一切都应该变得更加优雅，但目前有效.

As you can see, the arrays have to be bound in separately (you can't bind an array of objects). That's why the example demonstrates how to break them up into separate arrays for binding purposes. This should all get more elegant over time, but this works for now.

这篇关于如何使用 Node.js 在 Oracle DB 中插入多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！