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:\Softs\OracleClient\12GX64

• 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:\Softs\OracleClient\12GX64

　　OCI_LIB_DIR : %ORACLE_HOME64%\sdk\lib\msvc

　　OCI_INV_DIR : %ORACLE_HOME64%\sdk\include

　　將Z:\Softs\OracleClient\12GX64這個路徑%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      }    });}

 

Nodejs in Visual Studio Code 07.學習Oracle