【node.js】node連接配接oracle——oracledb

oracledb為Oracle官方提供的node連接配接Oracle的第三方包，github：https://github.com/oracle/node-oracledb，oracle-api：https://oracle.github.io/node-oracledb/doc/api.html

一、安裝oracledb

yarn add oracledb

cnpm/npm i oracledb -D

二、使用oracledb

首先需要引入

oracledb

定義資料庫使用者名、密碼、位址等資訊

const DB_CONFIG = {
  user: '123', 
  password: '123',
  connectString: '127.0.0.1:1521/orcl' // 資料庫位址：{IP:PORT/資料庫名稱}
}

開始連接配接資料庫，其中可通過連接配接池或直接進行連接配接，下面是兩種方式的具體實作：

// 使用連接配接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();
      result = await connection.execute(`SELECT * FROM SYS_USER`);
    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    throw err;
  } finally {
    await pool.close();
  }
}
// 直接連接配接
async function run() {
  let connection;
    try {
      connection = await pool.getConnection(DB_CONFIG);
      result = await connection.execute(`SELECT * FROM SYS_USER`);
    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
}
run()

其中

connection.execute(sql,params,options)

是用來執行sql的方法，它可以接受三個參數

sql,params,options

：

sql

是需要執行的sql語句，

params

用來設定sql中一些綁定屬性或是傳回值，

options

控制執行的sql的可選參數，如

autoCommit

是否自動送出事務。

2.1params

params

中的常用屬性有

dir,maxArraySize,maxSize,type,val

：

描述
`dir`	綁定的方向，可選值： `oracledb.BIND_IN，oracledb.BIND_INOUT，oracledb.BIND_OUT` 。 `oracledb.BIND_IN` ：作為變量輸入； `oracledb.BIND_OUT` ：作為變量輸出。預設 `oracledb.BIND_IN`
`type`	資料在oracle中的資料類型： `oracledb.STRING,oracledb.NUMBER,oracledb.CLOB,oracledb.BLOB,oracledb.DATE` 等等
`val`	綁定變量的值

例：

const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_AGE = :0 and USER_NAME = :1`,
	[12, '李四']
)
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_AGE = :age and USER_NAME = :name`,
	{
		name: {dir: oracledb.BIND_IN, val: '李四', type: oracledb.NUMBER}
		age: 12
	}
)
// 模糊查詢無法使用:
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_NAME like '%:name%'`, //報錯
	{name: '李四'}
)
// 可使用以下方式代替或使用instr代替like，注意使用instr第二個參數如果為’‘是查不到資料的
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE USER_NAME like '%${name}%'`
)
// instr
const result = await connection.execute(
	`SELECT * FROM SYS_USER WHERE INSTR(USER_ANME, :name)>0`,
	{name: '李'}
)

2.2 options

options

常用屬性：

屬性	描述
`autoCommit: Boolean`	自動送出事務
`bindDefs: Object`	對象定義綁定變量的類型，大小和方向
`dmlRowCounts: Boolean`	允許輸出受每個輸入資料記錄影響的行數。隻能為 `INSERT，UPDATE，DELETE` 或 `MERGE` 語句設定為 `true` 。
`fetchInfo: Object`	定義如何在JavaScript中表示查詢列資料的對象

fetchInfo

使用示例:

const data = connection.execute(`select * from INTERFACE_IMMUTABLE`, null,
	{fetchInfo: {'INT_RESPONSE': { type : oracledb.STRING }}} // CLOB轉成字元串
)

關于

connection.execute()

傳回的資料結構如下(

metaData

傳回的為字段名，sql中可使用as替換，但傳回的都是大寫，

rows

是查詢的資料)：

{
    metaData: [
        {
            name: "ROLE_ID"
        },
        {
            name: "ROLE_NAME"
        }
    ],
    rows: [
        [
            10001,
            "管理者"
        ],
        [
            10002,
            "普通使用者"
        ],
        [
            10003,
            "更新角色名"
        ]
    ]
}

三、封裝oracledb工具類

const oracledb = require('oracledb')
const dateFormat = require('../util/date_format')

const DB_CONFIG = {
  user: '123', // 資料庫使用者名
  password: '123', // 資料庫密碼
  connectString: '127.0.0.1:1521/orcl' // 資料庫位址：{IP:PORT/資料庫名稱}
}

var globalPool = null

class Oracle{
  /**
   * 建立連接配接池
   * @return {Promise<*>}
   */
  async createPool() {
    let pool;
    try {
      pool = await oracledb.createPool(DB_CONFIG);
      globalPool = pool;
    } catch(e) {
      globalPool = null
      throw e;
    }
    return pool;
  }
  
  /**
   * 建立連接配接
   * @return {Promise<void>}
   */
  async createConnection(pool) {
    let connection;
    try {
      connection = await pool.getConnection();
    } catch (e) {
      throw e;
    }
    return connection;
  }
  
  /**
   * 查詢
   * @param sql
   * @param mapping
   * @param options
   * @return {Promise<void>}
   */
  query (sql, mapping, params = {}, options = {}) {
    return new Promise(async (resolve, reject) => {
      let pool;
      try {
        pool = await this.createPool();
        let connection;
        try {
          connection = await this.createConnection(pool);
          const result = await connection.execute(sql, params, options)
          // 轉換mapping
          const keys = Object.keys(mapping)
          const values = Object.values(mapping)
          // map: {資料庫列名：傳回字段名}
          const map = values.reduce((pre, curr, index) => {
            pre.set(curr.name, {name: keys[index], type: values[index].type})
            return pre
          }, new Map())
          resolve(result.rows.map(item => result.metaData.reduce((pre, key, index) => {
            let value = map.get(key.name)
            let curr = item[index]
            // 類型轉換
            if (value) {
              if (value.type === oracledb.DATE) {
                curr && (curr = dateFormat(curr))
              } else if (value.type === oracledb.NUMBER){
                curr && (curr = Number(curr))
              }
              // 無字段名與資料庫對應
              if (value.name === undefined) {
                pre[key.name] = curr
              } else {
                pre[value.name] = curr
              }
            } else {  // 無字段名與資料庫對應
              let {name} = key
              name = name.toLowerCase()
              // 下劃線轉駝峰
              name = name.replace(/\_(\w)/g, function(all, letter){
                return letter.toUpperCase()
              })
              pre[name] = curr
            }
            return pre
          }, {})));
        } catch (e) {
          throw e;
        } finally {
          if (connection) {
            try {
              await connection.close();
            } catch (e) {
              throw e
            }
          }
        }
      } catch (e) {
        reject(e);
        throw e;
      } finally {
        await pool.close();
      }
    });
  }
  
  /**
   * 插入操作
   * @param sql
   * @param params
   * @param options
   * @return {Promise<unknown>}
   */
  insert (sql, params = {}, options = {autoCommit: true}) {
    return new Promise(async (resolve, reject) => {
      let pool;
      try{
        pool = await this.createPool();
        let connection;
        try {
          connection = await this.createConnection(pool);
          const result = await connection.execute(sql, params, options);
          resolve(result);
        } catch (e) {
          throw e;
        } finally {
          if (connection) {
            try {
              await connection.close();
            } catch (e) {
              throw e;
            }
          }
        }
      } catch (e) {
        reject(e);
        throw e;
      } finally {
        await pool.close();
      }
    });
  }
  
  /**
   * 更新操作
   * @param sql
   * @param params
   * @param options
   * @return {Promise<unknown>}
   */
  update (sql, params = {}, options = {autoCommit: true}) {
   // ...同新增
  }
  
  /**
   * 删除操作
   * @param sql
   * @param params
   * @param options
   * @return {Promise<unknown>}
   */
  delete (sql, params = {}, options = {autoCommit: true}) {
    // ... 同新增
  }
  
  /**
   * 多sql事務控制
   * @param sqlParams [{sql, params, options, multi}] multi:是否執行多次
   * @return {Promise<[]>}
   */
  oracleTrans(sqlParams) {
    return new Promise(async (resolve, reject) => {
      let pool;
      try {
        pool = await this.createPool()
        let connection
        try {
          connection = await this.createConnection(pool)
          let results = []
          for (let i = 0, length = sqlParams.length; i < length; i++) {
            const {sql, params, multi = false, options = {}} = sqlParams[i]
            if (multi) results.push(await connection.executeMany(sql, params, options))
            else results.push(await connection.execute(sql, params, options))
          }
          // 送出事務
          await connection.commit()
          resolve(results)
        }catch (e) {
          // 事務復原
          if (connection) {
            try {
              await connection.rollback()
              throw e
            } catch (e) {
              throw e
            }
          }
        } finally {
          if (connection) {
            try {
              await connection.close()
            } catch (e) {
              throw e
            }
          }
        }
      }catch (e) {
        reject(e)
      } finally {
        await pool.close()
      }
    })
  }
  
  /**
   * sql執行多次
   * @param sql
   * @param binds []
   * @param options
   */
  multiSql (sql, binds = [], options) {
    return new Promise(async (resolve, reject) => {
      let pool
      try {
        pool = await this.createPool()
        let connection
        try {
          connection = await this.createConnection(pool)
          const data = await connection.executeMany(sql, binds, options).catch(async e => {
            if (connection) {
              await connection.rollback()
              throw e
            }
          })
          // 送出事務
          await connection.commit()
          resolve(data)
        } catch (e) {
          throw e
        } finally {
          if (connection) {
            try {
              await connection.close()
            } catch (e) {
              throw e
            }
          }
        }
      } catch (e) {
        throw e
      } finally {
        await pool.close()
      }
    })
  }
}

const oracle = new Oracle();
module.exports = {
  async query(sql, mapping, params, options) {
    return await oracle.query(sql, mapping, params, options);
  },
  async insert (sql, params, options) {
    return await oracle.insert(sql, params, options);
  },
  async update (sql, params, options) {
    return await oracle.update(sql, params, options);
  },
  async delete (sql, params, options) {
    return await oracle.delete(sql, params, options);
  },
  async oracleTrans (sqlParams) {
    return await oracle.oracleTrans(sqlParams)
  },
  async multiSql (sql, binds, options) {
    return await oracle.multiSql(sql, binds, options)
  },
  async pageSql(sql, page, pageNum, mapping, params, options) {
    const newParams = {...params,
      maxRow:page * pageNum,
      minRow:(page - 1) * pageNum + 1}
    return await oracle.query(
      `SELECT * FROM (SELECT a.*, ROWNUM ROW_ID
        FROM (${sql}) a
        WHERE ROWNUM <= :maxRow)
        WHERE ROW_ID >= :minRow`,
      mapping,
      newParams,
      options
    )
  }
}

上方工具包含了常用的CRUD操作及事務、分頁、一個sql執行多次等操作。上方中出現的

mapping

為js中的變量與資料庫中字段的映射，其資料類型如下：

const oracledb = require('oracledb')
// 角色資訊映射
const ROLE_MAPPING = {
  roleId: {
    type: oracledb.NUMBER,
    name: 'ROLE_ID'
  },
  roleName: {
    type: oracledb.STRING,
    name: 'ROLE_NAME'
  },
  roleDesc: {
    type: oracledb.STRING,
    name: 'ROLE_DESC'
  }
}

module.exports = ROLE_MAPPING

使用方法：

const db = require('../database/index')
...
const data = await db.pageSql(`select ROlE_ID,ROLE_NAME, ROLE_DESC from SYS_ROLE ${searchSql} order by ROLE_ID`,
      pageNo, pageSize, ROLE_MAPPING, sqlParams)
      .catch(e => {throw e})
console.log(data)
// 傳回的data結構
[
      {
        "roleId": 10001,
        "roleName": "管理者",
        "roleDesc": null,
        "rowId": 1
      },
      {
        "roleId": 10002,
        "roleName": "普通使用者",
        "roleDesc": null,
        "rowId": 2
      },
      {
        "roleId": 10003,
        "roleName": "更新角色名",
        "roleDesc": "更新角色描述資訊",
        "rowId": 3
      }
    ]

【node.js】node連接配接oracle——oracledb

一、安裝oracledb

二、使用oracledb

2.1params

2.2 options

三、封裝oracledb工具類

繼續閱讀

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

Docker上部署node項目Docker上部署node項目

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

手把手教你配置：Jenkins+Github+Webhook +Nginx自動化打包部署Vue項目

Oracle 批量查詢傳入List 傳回List