如何使用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屋！