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