javascript - Node-oracledb:只有最后一个值在循环中多次插入

Question

我正在读取 XLSX 文件并将记录插入 ORACLE 数据库。 XLSX 包含以下值

H
H
H
H
JK

但是只有JK被插入了5次。下面是使用的代码

var XLSX = require('xlsx')
var workbook = XLSX.readFile('Accounts.xlsx');
var sheet_name_list = workbook.SheetNames;
var xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
var connection;
var oracledb = require('oracledb');
oracledb.autoCommit = true;
var dbConfig = require(__dirname + '/dbconfig.js');
var cnt;


 oracledb.getConnection(
    dbConfig,
     function(err, connection) {
        if (err) throw err;

        for (i in xlData) 
        {

            var act_fam = xlData[i].ACCOUNT_FAMILY;



               connection.execute(
                `SELECT * FROM TFAMCORGDS_TEST WHERE MNEFAMCOR='`+ act_fam + `'`,
                function(err, results) {
                    if (err) throw err;
                    cnt = results.rows.length;

                    if (cnt === 0)
                    {
                         connection.execute(
                `INSERT INTO TFAMCORGDS_TEST (CODFAMCOR,MNEFAMCOR,DATMOD,DATFINACT) VALUES (SCORGDS.NEXTVAL,'`+ act_fam + `',SYSDATE,NULL)`,
                function(err, results) {
                    if (err) throw err;
                    console.log('Rows Inserted: ', results.rowsAffected);
                    //do work on the rows here


                }
            );
                    }
             });







        }
    }
);

而且我也无法在 connection.execute 函数之外使用变量 "cnt" 值，尽管它是全局变量。

Answer 1

我将把任务隔离在不同的函数中，并将它们包装在 Promise 中，而不是在循环中调用回调。代码看起来像这样

var XLSX = require("xlsx");
var workbook = XLSX.readFile("Accounts.xlsx");
var sheet_name_list = workbook.SheetNames;
var xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
var connection;
var oracledb = require("oracledb");
oracledb.autoCommit = true;
var dbConfig = require(__dirname + "/dbconfig.js");
var cnt;

function getConnection() {
  return new Promise((resolve, reject) => {
    oracledb.getConnection(dbConfig, function(err, connection) {
      if (err) {
        reject(err);
      } 
      resolve(connection);
    });
  });
}

function get(connection, act_fam) {
  return new Promise((resolve, reject) => {
    connection.execute(`SELECT * FROM TFAMCORGDS_TEST WHERE MNEFAMCOR='` + act_fam + `'`, function(err, results) {
      if (err) {
        reject(err);
      } 
      resolve(results);
    });
  });
}

function insert(connection, act_fam) {
  return new Promise((resolve, reject) => {
    connection.execute(
      `INSERT INTO TFAMCORGDS_TEST (CODFAMCOR,MNEFAMCOR,DATMOD,DATFINACT) VALUES (SCORGDS.NEXTVAL,'` +
        act_fam +
        `',SYSDATE,NULL)`, function(err, results) {
      if (err) {
        reject(err);
      } 
      resolve(results);
    });
  });
}

async function main() {
  const connection = await getConnection();
  for (i in xlData) 
  {
      var act_fam = xlData[i].ACCOUNT_FAMILY;
      const results = await get(connection, act_fam);
      cnt = results.rows.length;
      if (cnt === 0) {
        const insertResult = await insert(connection, act_fam);
        console.log('Rows Inserted: ', insertResult.rowsAffected);
      }
  }

}

希望这有帮助