我正在尝试通过node.js动态地对oracle表发出补丁请求
以下是我的设置：
在我的router.js文件中，我有：

const express = require('express');
const router = new express.Router();
const employees = require('../controllers/employees.js');
const smiCats = require('../controllers/smi/smiCats.js');
const auth = require('../controllers/auth.js');

router.route('/login/:id?')
    .post(auth.getToken);

router.route('/ams/:id?')
    .get(auth.verifyToken, employees.get)
    .post(auth.verifyToken, employees.post)
    .put(auth.verifyToken, employees.put)
    .delete(auth.verifyToken, employees.delete)
    .patch(auth.verifyToken, employees.patch);

router.route('/smi/cats/:id?')
    .get(auth.verifyToken, smiCats.get)
    .post(auth.verifyToken, smiCats.post)
    .put(auth.verifyToken, smiCats.put)
    .patch(auth.verifyToken, smiCats.patch);

module.exports = router;

然后调用我的控制器，该控制器具有我的补丁函数，并得到净化。

//sanitizer
function sanitizeCats(req) {
    const cats = {
        cat_desc: req.body.cat_desc,
        msg_for: req.body.msg_for,
        msg_user_owner: req.body.msg_user_owner || 0,
        msg_realtor_owner: req.body.msg_realtor_owner || 0
    };

    return cats;
}
async function patch(req, res, next) {
    try {
        let category = sanitizeCats(req);
        category.cat_id = parseInt(req.params.id, 10);

        const success = await smiCats.patch(category);

        if (success) {
            res.status(204).end();
        } else {
            res.status(404).end();
        }
    } catch (err) {
        next(err);
    }
}

module.exports.patch = patch;

当它被执行时，它调用我的db\uapi模块，该模块组装sql语句
（下一个代码部分是我的问题的来源）

const database = require('../../services/database.js');
const oracledb = require('oracledb');
const patchSql =
    `BEGIN
   DECLARE
   BEGIN

  IF nvl(:cat_desc,'zzz') != 'zzz' THEN  
  UPDATE smi_contact_cats
     SET cat_desc = :cat_desc
   WHERE cat_id = :cat_id;
  END IF;

  IF nvl(:msg_for,'zzz') != 'zzz' THEN
  UPDATE smi_contact_cats
     SET msg_for = :msg_for
   WHERE cat_id = :cat_id;
  END IF;

  IF nvl(:msg_user_owner,-1) > -1 THEN
  UPDATE smi_contact_cats
     SET msg_user_owner = :msg_user_owner
   WHERE cat_id = :cat_id;
  END IF;

  IF nvl(:msg_realtor_owner,-1) > -1 THEN
  UPDATE smi_contact_cats
     SET msg_realtor_owner = :msg_realtor_owner
   WHERE cat_id = :cat_id;
  END IF;

  :rowcount := sql%rowcount;
  END;
  END;`;

async function patch(cats) {
    const category = Object.assign({}, cats);
    //add binds
    category.rowcount = {
        dir: oracledb.BIND_OUT,
        type: oracledb.NUMBER
    };

    const result = await database.simpleExecute(patchSql, category);
    return result.outBinds.rowcount === 1;
}

module.exports.patch = patch;

然后调用数据库函数来实际执行和组装带有绑定变量的sql：

const oracledb = require('oracledb');
const dbConfig = require('../config/database.js');

async function initialize() {
    const pool = await oracledb.createPool(dbConfig.beta);
}

module.exports.initialize = initialize;

async function close() {
    await oracledb.getPool().close();
}

module.exports.close = close;

function simpleExecute(statement, binds = [], opts = {}) {
    return new Promise(async (resolve, reject) => {
        let conn;

        opts.outFormat = oracledb.OBJECT;
        opts.autoCommit = true;

        try {
            conn = await oracledb.getConnection();
            const result = await conn.execute(statement, binds, opts);

            resolve(result);
        } catch (err) {
            reject(err);
        } finally {
            if (conn) { // conn assignment worked, need to close
                try {
                    await conn.close();
                } catch (err) {
                    console.log(err);
                }
            }
        }
    });
}

module.exports.simpleExecute = simpleExecute;

所以所有这些工作。。。但它的动态性不足以让我构建我们公司的api。如何在node.js中生成一个更动态的补丁请求，而不必键入每一列并在其周围放置一个nvl来检查它是否存在。作为一方，如果没有更好的方法来动态清理，我洗耳恭听，但主要的问题是如何更好地动态构建补丁请求。