node.js查询oracle,nodejs操作oracle数据库示例

首先用SQLPLUS或其它工具在oracle数据库里创建表MY_USERS，用来此次测试：

CREATE TABLE MY_USERS( ID INTEGER, FIRSTNAME NVARCHAR2(20), LASTNAME NVARCHAR2(20) ) TABLESPACE  SYSTEM;

nodejs读写oracle的示例代码如下：

// 使用的oracle驱动：https://github.com/joeferner/node-oracle

var oracle = require('oracle');

// 需根据实际测试环境配置连接oracle数据库的参数

var oraConfig = {

hostname: "192.168.0.230",

port: 1521,

database: "orcl",    // oracle的SID

user: "system",    // 登录oracle的用户名

password: "password" // 请换为实际密码

}

或者：

var oraConnString = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.230)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))";

var oraConfig = { "tns": oraConnString, "user": "system", "password": "wangkong" };

// 插入1条数据，并返回被插入记录的I某个字段

oracle.connect(oraConfig, function(err, connection) {

if (err) {

console.log("Fail to connect oracle:", err);

return;

}

connection.setAutoCommit(true);

connection.execute("INSERT INTO MY_USERS(ID,FIRSTNAME,LASTNAME) VALUES (:1, :2, :3) RETURNING FIRSTNAME INTO :4", [2, '陈', '二', new oracle.OutParam(oracle.OCCISTRING)], function(err, result) {

if (err) {

console.log(err);

return;

}

console.log(JSON.parse(result));

connection.close();

});

});

返回结果为：

{

"updateCount": 1,

"returnParam": "陈"

}

// 批量插入记录

oracle.connect(oraConfig, function(err, connection) {

if (err) {

console.log("Fail to connect oracle:", err);

return;

}

connection.setAutoCommit(true);

function doInsert(sqlCmdOfInsert, records, callback) {

if (records.length > 0) {

sqlCmdOfInsert.execute(records.shift(), function(err, result) {

if (err) {

return callback(err);

}

if (result.updateCount !== 1) {

return callback(new Error("bad updateCount: " + result.updateCount));

}

// recurse with remaining records

doInsert(sqlCmdOfInsert, records, callback);

});

} else {

// job is finished

return callback();

}

}

var users = [ [ 3, "张", "三" ], [ 4, "李", "四" ], [ 5, "王", "五" ], [ 6, "赵", "六" ] ];

var sqlCmdOfInsert = connection.prepare("INSERT INTO MY_USERS(ID, FIRSTNAME, LASTNAME) VALUES(:1, :2, :3)");

doInsert(sqlCmdOfInsert, users, function(err) {

if (err) {

console.log(err);

return;

}

console.log("Done!");

});

});

// 查询一条记录：

oracle.connect(oraConfig, function(err, connection) {

if (err) {

console.log("Fail to connect oracle:", err);

return;

}

connection.execute("SELECT * FROM MY_USERS WHERE ID=:1", [5], function(err, results) {

if (err) {

console.log("Fail to query table:", err);

return;

}

console.log(JSON.parse(result));

connection.close();

});

});

// 查询多条记录，每次读取处理1条记录

oracle.connect(oraConfig, function(err, connection) {

if (err) {

console.log("Fail to connect oracle:", err);

return;

}

connection.setPrefetchRowCount(200);

var reader = connection.reader("SELECT * FROM MY_USERS WHERE ID>:1", [2]);

function doRead(cb) {

reader.nextRow(function(err, row) {

if (err) {

return cb(err);

}

if (row) {

// do something with row

console.log(JSON.stringify(row, "", "\t"));

// recurse to read next record

return doRead(cb)

} else {

// we are done

return cb();

}

})

}

doRead(function(err) {

if (err) {

console.log(err);

return;

}

console.log("Done!");

});

});

参考网址：

https://github.com/joeferner/node-oracle

https://www.npmjs.org/package/oracle

http://my.oschina.net/zenglingfan/blog/164390