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:\Softs\OracleClient\12GX64
- Set
OCI_LIB_DIR
andOCI_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命令
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