JavaScript嵌套异步函数无法返回Oracle查询结果问题

你的DBQueryJDE函数虽然标记为async，但内部使用的是回调式API，没有将异步数据库操作包装成Promise返回。函数执行时会直接跳过异步回调，默认返回undefined，这就是外层PromiseScrap.then里拿到result为undefined的原因。从控制台输出顺序也能看出来：先打印JDE Data:undefined，之后才打印RESULTSET，说明外层then回调先执行了，根本没等Oracle查询完成。

将DBQueryJDE改为使用Promise封装异步操作，或者直接用oracledb的Promise API（推荐，代码更简洁）。同时修复函数内未定义的response变量，把错误处理交给上层接口处理。

重构后的DBQueryJDE函数 #

async function DBQueryJDE(sqlquery) {
  let connection;
  try {
    console.log('Oracle SQL:\n' + sqlquery);
    const oracledb = require('oracledb');
    const connectionProperties = {
      user: process.env.DBAAS_USER_NAME || "SomeUserID",
      password: process.env.DBAAS_USER_PASSWORD || "SomePassword",
      connectString: "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db92IF.bherp.local)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME= DB92IF.bherp.local)))"
    };
    // 使用Promise形式获取连接
    connection = await oracledb.getConnection(connectionProperties);
    // 使用Promise形式执行查询
    const result = await connection.execute(sqlquery);
    console.log("RESULTSET:" + JSON.stringify(result));
    return result;
  } catch (error) {
    console.log('DBErrorJDE: ' + error.message + '\n Query:' + sqlquery);
    throw error; // 抛出错误让上层catch处理
  } finally {
    // 无论成功失败都释放连接
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err.message);
      }
    }
  }
};

优化接口代码（使用async/await替代嵌套then） #

这样代码结构更清晰，避免回调地狱：

app.get('/api/Scrap/:ParmLocation/:ParmDepartment', async (req, res) => {
  const ParmLocation = req.params.ParmLocation;
  const ParmDepartment = req.params.ParmDepartment;
  console.log('/api/Scrap called for ' + ParmLocation + ' Department ' + ParmDepartment + ' @ ' + Date());

  try {
    // 获取Planners
    const queryPlanners = `SELECT Planners FROM DeptPlanners where Department = '${ParmDepartment}'`;
    const resultPlanners = await db.DBQueryBHBrowser(queryPlanners);
    let Planners = '';
    resultPlanners.recordset.forEach(row => {
      Planners = Object.values(Object.values(row))[0];
    });
    console.log('/api/Scrap Planners completed @ ' + Date());
    console.log('Planners: ' + Planners);

    // 执行Oracle查询
    const queryScrap = `SELECT TO_CHAR(TO_DATE(SUBSTR(ILTRDJ,-5), 'YYDDD'),'WW') AS Week, SUM(ILTRQT / 1000 * -1) AS TOTQTY, SUM(ILPAID / 100 * -1) AS TOTAMT
    FROM PRODDTA.F4111
    JOIN PRODDTA.F4102 ON ILITM = IBITM AND IBMCU = ILMCU AND IBANPL NOT IN (50030002,50030039)
    JOIN PRODDTA.F4101 ON IMLITM = ILLITM
    JOIN PRODCTL.F5500001 On ILTRDJ = DCGRDT
    LEFT JOIN PRODCTL.F0005 ON ILRCD= LTRIM(F0005.Drky)
    And DRSY='42' AND DRRT='RC'
    WHERE ILMCU = '       50010' AND ILDCT IN('ID', 'IS')
    AND ILTRDJ BETWEEN  123001 AND 123365
    AND IBANPL IN (${Planners})
    AND ILRCD NOT IN('517','509') AND ILUSER <> 'HIPPK'
    GROUP BY TO_CHAR(TO_DATE(SUBSTR(ILTRDJ,-5), 'YYDDD'),'WW')
    ORDER BY Week`;

    const resultScrap = await db.DBQueryJDE(queryScrap);
    console.log(`/api/Scrap ${ParmLocation} ${ParmDepartment}  completed @ ${Date()}`);
    console.log(`JDE Data:` + JSON.stringify(resultScrap));
    res.send(resultScrap);
  } catch (err) {
    console.error("API Error:", err.message);
    res.status(500).send("Server error");
  }
});

• 避免SQL注入：当前代码用字符串拼接生成SQL语句（比如Department = '${ParmDepartment}'），存在SQL注入风险。建议使用参数绑定，MSSQL和Oracle都支持占位符传参，具体可以参考对应数据库驱动的文档。
• 统一错误处理：将数据库操作的错误统一抛到上层接口处理，不要在DBQuery函数里直接操作response，保持职责单一。

内容的提问来源于stack exchange，提问作者Steve Grody