如何添加{在}列表Oracle数据库节点JS

Question

我有这个疑问

select * from foo where id in (:ListOfIds) 

我把这种方法很多次，但每次通话例如具有不同的值

select * from foo where id in (1,2,5) 
    select * from foo where id in (3,4,6) 

那么如何我可以将列表传递给此查询吗？

Answer 1

你在单个字符串中有逗号分隔的数字列表还是你有一个数组数组？

下面是一个例子，说明如何使用自定义类型和表函数处理以逗号分隔的数字列表。首先，在使用像SQL开发人员工具数据库运行此：

create or replace type number_ntt as table of number; 
/

create or replace function string_to_number_ntt(
    p_string in varchar2, 
    p_separator in varchar2 := ',' 
) 

    return number_ntt 

is 

    l_elements apex_application_global.vc_arr2; 
    l_retval number_ntt := number_ntt(); 

begin 

    l_elements := apex_util.string_to_table(p_string, p_separator); 

    for x in 1 .. l_elements.count 
    loop 
     l_retval.extend(); 
     l_retval(l_retval.count) := l_elements(x); 
    end loop; 

    return l_retval; 

end string_to_number_ntt; 
/

注意函数利用这可能已经安装在DB的apex_util包。

一旦这项工作到位，你可以按如下方式使用它：

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

oracledb.getConnection(config) 
    .then((c) => { 
    conn = c; 

    return conn.execute(
    `select employee_id, 
     last_name, 
     department_id 
     from employees 
     where department_id in (
     select column_value 
     from table(string_to_number_ntt(:depts)) 
    )`, 
     { 
     depts: '30, 60, 90' 
     }, 
     { 
      outFormat: oracledb.OBJECT 
     } 
    ); 
    }) 
    .then(result => { 
    console.log(result.rows); 
    }) 
    .catch(err => { 
    console.log(err); 
    }) 
    .then(() => { 
    if (conn) { // conn assignment worked, must close 
     return conn.close(); 
    } 
    }) 
    .catch(err => { 
    console.log(); 
    }); 

示例结果：

$ node my-script.js 
[ { EMPLOYEE_ID: 114, LAST_NAME: 'Raphaely', DEPARTMENT_ID: 30 }, 
    { EMPLOYEE_ID: 115, LAST_NAME: 'Khoo', DEPARTMENT_ID: 30 }, 
    { EMPLOYEE_ID: 116, LAST_NAME: 'Baida', DEPARTMENT_ID: 30 }, 
    { EMPLOYEE_ID: 117, LAST_NAME: 'Tobias', DEPARTMENT_ID: 30 }, 
    { EMPLOYEE_ID: 118, LAST_NAME: 'Himuro', DEPARTMENT_ID: 30 }, 
    { EMPLOYEE_ID: 119, LAST_NAME: 'Colmenares', DEPARTMENT_ID: 30 }, 
    { EMPLOYEE_ID: 103, LAST_NAME: 'Hunold', DEPARTMENT_ID: 60 }, 
    { EMPLOYEE_ID: 104, LAST_NAME: 'Ernst', DEPARTMENT_ID: 60 }, 
    { EMPLOYEE_ID: 105, LAST_NAME: 'Austin', DEPARTMENT_ID: 60 }, 
    { EMPLOYEE_ID: 106, LAST_NAME: 'Pataballa', DEPARTMENT_ID: 60 }, 
    { EMPLOYEE_ID: 107, LAST_NAME: 'Lorentz', DEPARTMENT_ID: 60 }, 
    { EMPLOYEE_ID: 100, LAST_NAME: 'King', DEPARTMENT_ID: 90 }, 
    { EMPLOYEE_ID: 101, LAST_NAME: 'Kochhar', DEPARTMENT_ID: 90 }, 
    { EMPLOYEE_ID: 102, LAST_NAME: 'De Haan', DEPARTMENT_ID: 90 } ] 

如果你有一个数字的数组，你可以将它们转换为一个逗号分隔的列表数字如下：

[30, 60, 90].join(',') 

下面是你的执行语句的例子：

return conn.execute(
    `select * 
    from foo 
    where id in (
     select column_value 
     from table(string_to_number_ntt(:ListOfIds)) 
    )`, 
    { 
    ListOfIds: ListOfIds.join(',') 
    }, 
    { 
     outFormat: oracledb.OBJECT 
    } 
); 

Answer 2

在该名单中的最大尺寸是已知的特殊情况下，也不宜太大，这将是更容易使用，在每个潜在列表项绑定变量。对于应用程序不知道的任何值，绑定一个null值。该SQL将是：

sql = 'select * from foo where id in (:v1, :v2, :v3, :v4)'; 

然后，如果你只有3个数据项，你会绑定：

binds = [30, 60, 90, null]; 
connection.execute(sql, binds, options, executeCbFunc); 

另一种解决方案是使用绑定（针对安全），但建立确切的SQL字符串像：

binds = ['Christopher', 'Hazel', 'Samuel']; 
sql = "select first_name, last_name from employees where first_name in ("; 
for (var i=0; i < binds.length; i++) sql += (i > 0) ? ", :" + i : ":" + i; 
sql += ")"; // select first_name, last_name from employees where first_name in (:0, :1, :2) 

但是，这取决于多久这个查询被执行，并绑定值的数量如何变化的，你可以有很多“独一无二”的查询字符串的结束。所以你可能不会获得执行一个固定的SQL语句给出的语句缓存好处。 [关于JS，上次我查了一下，这个简单的for循环比map/join解决方案快。]