Nodejs in Visual Studio Code 07.学习Oracle

1.开始

Node.js：https://nodejs.org

OracleDB: https://github.com/oracle/node-oracledb/blob/master/INSTALL.md#instwin

　　　　　 https://github.com/oracle/node-oracledb/blob/master/doc/api.md#resultsethandling

2.OracleDB安装

• Python 2.7

　　下载安装即可，略

• C Compiler with support for C++ 11 (Xcode, gcc, Visual Studio or similar)

　　打开Visual Studio的安装文件，查看C编译器是否安装，见下图

• The small, free Oracle Instant Client "basic" and "SDK" packages if your database is remote. Or use a locally installed database such as the free Oracle XE release

　　打开Oracle Instant Client，免费下载basic和sdk两个压缩包，绿色软件无须安装

　　instantclient-basic-windows.x64-12.1.0.2.0.zip  69MB

　　instantclient-sdk-windows.x64-12.1.0.2.0.zip   2.62MB

　　将两个ZIP文件解压到同一个目录中Z:SoftsOracleClient12GX64

• Set OCI_LIB_DIR and OCI_INC_DIR during installation if the Oracle libraries and headers are in a non-default location

　　打开我的电脑->属性->高级属性->环境变量，新增两个环境变量ORACLE_HOME64,OCI_LIB_DIR 和 OCI_INV_DIR

　　ORACLE_HOME64 : Z:SoftsOracleClient12GX64

　　OCI_LIB_DIR : %ORACLE_HOME64%sdklibmsvc

　　OCI_INV_DIR : %ORACLE_HOME64%sdkinclude

　　将Z:SoftsOracleClient12GX64这个路径%ORACLE_HOME64%加到Path中。

• 执行CMD命令

$ npm install oracledb

3.OracleDB普通查询

dbconfig.js 配置数据库连接字符串

module.exports = {
  user          : process.env.NODE_ORACLEDB_USER || "test",

  // Instead of hard coding the password, consider prompting for it,
  // passing it in an environment variable via process.env, or using
  // External Authentication.
  password      : process.env.NODE_ORACLEDB_PASSWORD || "test",

  // For information on connection strings see:
  // https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connectionstrings
  connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING || "192.168.1.100/orcl",

  // Setting externalAuth is optional.  It defaults to false.  See:
  // https://github.com/oracle/node-oracledb/blob/master/doc/api.md#extauth
  externalAuth  : process.env.NODE_ORACLEDB_EXTERNALAUTH ? true : false
};

app.js执行一个简单的查询语句

var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');

//打开一个链接
oracledb.getConnection(
  {
    user          : dbConfig.user,
    password      : dbConfig.password,
    connectString : dbConfig.connectString
  },
  function(err, connection)
  {
    if (err) {
      console.error(err.message);
      return;
    }
    //执行查询语句
    connection.execute(
      "SELECT department_id, department_name " +
        "FROM departments " +
        "WHERE manager_id < :id",
      [110],  // bind value for :id
      { maxRows: 10 },  // a maximum of 10 rows will be returned.  Default limit is 100
      function(err, result)
      {
        if (err) {
          console.error(err.message);
          doRelease(connection);
          return;
        }
        console.log(result.metaData);
        console.log(result.rows);
        //查询结束后记得释放链接资源
        doRelease(connection);
      });
  });

function doRelease(connection)
{
  connection.release(
    function(err) {
      if (err) {
        console.error(err.message);
      }
    });
}

　　

4.OracleDB ResultSet查询

　　 普通查询，默认最大返回100条数据，如果需要查询更多的数据那么需要建立一个DataReader和数据库保持连接然后一行一行的读取数据，这个在nodejs oracledb里面就叫ResultSet查询。

你可以这样使用ResultSet，每次返回一行数据

connection.execute(
  "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
  [], // no bind variables
  { resultSet: true }, // return a result set.  Default is false
  function(err, result)
  {
    if (err) { . . . }
    fetchOneRowFromRS(connection, result.resultSet);
  });
});

. . .

function fetchOneRowFromRS(connection, resultSet)
{
  resultSet.getRow( // get one row
    function (err, row)
    {
      if (err) {
         . . .           // close the result set and release the connection
      } else if (!row) { // no rows, or no more rows
        . . .            // close the result set and release the connection
      } else {
        console.log(row);
        fetchOneRowFromRS(connection, resultSet);  // get next row
      }
    });
}

当然也可以每次返回多行数据，请使用numRows参数

var numRows = 10;  // number of rows to return from each call to getRows()

connection.execute(
  "SELECT employee_id, last_name FROM employees ORDER BY employee_id",
  [], // no bind variables
  { resultSet: true }, // return a result set.  Default is false
  function(err, result)
  {
    if (err) { . . . }
    fetchRowsFromRS(connection, result.resultSet, numRows);
  });
});

. . .

function fetchRowsFromRS(connection, resultSet, numRows)
{
  resultSet.getRows( // get numRows rows
    numRows,
    function (err, rows)
    {
      if (err) {
         . . .                        // close the result set and release the connection
      } else if (rows.length == 0) {  // no rows, or no more rows
        . . .                         // close the result set and release the connection
      } else if (rows.length > 0) {
        console.log(rows);
        fetchRowsFromRS(connection, resultSet, numRows);  // get next set of rows
      }
    });
}