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安装
下载安装即可，略
- 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_DIRandOCI_INC_DIRduring 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命令
|
1
|
$ npm install oracledb |
3.OracleDB普通查询
dbconfig.js 配置数据库连接字符串
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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执行一个简单的查询语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
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，每次返回一行数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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参数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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 } });} |
http://www.cnblogs.com/mengkzhaoyun/p/5405912.html