首先用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
