Oracle Database Development (7). The thinking of the OCI example .
Vert Melon
Jun 26,2007
The central content in this article is about the common operations with Oracle . Just
follow the instructions and enrich the relevant knowledge by the OCI official document .
I'd like to copy many sentences from OCI official document . It would save me many
trouble and these sentences are more exactly .
1. Connection
Connection is surely a first step done with the database . OCI provides two mode of
connection , which is Single User, Single Connection and Multiple Sessions or Connections.
The example also gave the both implement .
a. Single User, Single Connection
-----------------------------------------------------------------------------
An application maintains only a single user session for each database connection
at any time .
The corresponding functions in the example listed as follows :
OCIDB::Single_Conn()
OCIDB::Single_Disc()
The calling order of routines :
OCIEnvCreate
OCIHandleAlloc <ERROR>
OCILogon
OCILogoff
OCIHandleFree <ERROR>
OCIHandleFree <ENV>
An application of OCI must have one environment handle , which is created by OCIEnvCreate()
here . The environment handle defines a context in which all OCI functions are invoked .
It is the base of almost all of the other handles which would be seen in future.
OCIEnvCreate() creates and initializes an environment for OCI functions to work under .
This call should be invoked before any other OCI call and should be used instead of
the OCIInitialize() and OCIEnvInit() calls. OCIInitialize() and OCIEnvInit() calls will be
supported for backward compatibility. But if you are writing a DLL or a shared library
using OCI library then this call should definitely be used instead of OCIInitialize() and
OCIEnvInit() call . OCIInitialize() and OCIEnvInit() call is used in OCIDB::Multiple_Conn() ,
we'll cover it soon.
Almost all OCI calls include in their parameter list one or more handles. A handle is an
opaque pointer to a storage area allocated by the OCI library. You use a handle to store
context or connection information, (for example, an environment or service context handle),
or it may store information about OCI functions or data (for example, an error or describe
handle). Handles can make programming easier, because the library, rather than the application,
maintains this data.
Either OCIEnvCreate() or ( OCIInitialize() and OCIEnvInit() ) allocates a environment
handle . I defined a private member variable named m_pOCIEnv to store it . You can find
the both routines return a address as environment handle .
There is also a very important handle , which appears in mostly OCI calls . That is the
error handle .The error handle maintains information about errors that occur during an OCI
operation. If an error occurs in a call, the error handle can be passed to OCIErrorGet() to
obtain additional information about the error that occurred.
Go and look at OCIException::CheckError() which shows a integral error management .
After the preparation of Environment and Error , we will come to the actual code about
the connection . OCILogon() and OCILogoff() is quite easy to us . Notice that a service
context handle will be created during the time processing OCILogon().
A service context handle defines attributes that determine the operational context for
OCI calls to a server. The service context contains three handles as its attributes, that
represent a server connection, a user session, and a transaction. OCILogon() call also
implicitly allocates server and user session handles associated with the session.
Is is a very good custom that release the resource allocated at first in an order when
the program comes the end . OCIDB::Single_Disc() does it explicitly .
b. Multiple Sessions or Connections
-----------------------------------------------------------------------------
If an application needs to maintain multiple user sessions on a database connection,
the application requires a different set of calls to set up the sessions and connections.
The corresponding functions in the example listed as follows :
OCIDB::Multiple_Conn()
OCIDB::Multiple_Disc()
The calling order of routines :
OCIInitialize
OCIEnvInit
OCIHandleAlloc <Error>
OCIHandleAlloc <Server Context >
OCIHandleAlloc <Server>
OCIAttrSet <Set Server Into ServerContext>
OCIServerAttach <AttachServer>
OCIHandleAlloc <Session>
OCIAttrSet <Set Session Into ServerContext>
OCISessionBegin
...
Compare with OCILogon() it becomes more complex . Each handle is allocated by
hand here .
There is someting new . All OCI handles have attributes associated with them. These
attributes represent data stored in that handle. You can read handle attributes using the
attribute get call, OCIAttrGet(), and you can change them with the attribute set call,
OCIAttrSet(). OCIServerAttach() creates an access path to the data server for OCI
operations. OCISessionBegin() establishes a session for a user against a particular server.
This call is required for the user to be able to execute any operation on the server.
Notice that the calling order of OCIAttrSet() or OCIServerAttach() is random . As the
same , either call OCIAttrSet() before OCISessionBegin() or call OCISessionBegin()
before OCIAttrSet() is OK .
You can change the content of the file Main.cpp to implement the different operation .
Some code for test is listed below :
#include "OCIDB.h"
int main() {
OCIDB db;
db.Single_Conn();
db.Single_Disc();
}
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.Multiple_Disc();
}
2. Execute non-query SQL
Non-query SQL means that SQL doesn't return any data , such as create , insert , delete
and so on . These SQL statements also could be comprehanded as DDL . OCI also support
the bind for extern values and we will discuss it in next section. Please search the relevant
code in the example. The function OCIDB::ExecuteSql() covers it .
At first you should allocate a statement handle in the envionment handle. A statement
handle is the context that identifies a SQL or PL/SQL statement and its associated attributes.
Every SQL statement must be prepared for execution by OCIStmtPrepare() then. This is a
completely local call, requiring no round trip to the server. No association is made at this
point between the statement and a particular server.
After finished the steps above , call OCIStmtExecute() to execute the statement. For DDL
statements, no further steps are necessary.
You may use these code statements in Main.cpp as the following :
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.ExecuteSql("update liwei_test set id =123 where note='test' ");
db.Multiple_Disc();
}
3. Bind variable
Most DML statements, and some queries (such as those with a WHERE clause), require a
program to pass data to Oracle as part of a SQL or PL/SQL statement. Such data can be constant
or literal data, known when your program is compiled.
insert into liwei_test (id,name,note)values(1,'aABD','cadsf')
This statement is a simple one to insert some data known. When you prepare a SQL statement
or PL/SQL block that contains input data to be supplied at runtime, placeholders in the SQL statement
or PL/SQL block mark where data must be supplied. For example, the following SQL statement
contains three placeholders, indicated by the leading colons (for example, :id), that show where
input data must be supplied by the program.
insert into liwei_test (id,name,note)values(:id,:name,:note)
In this example I also made two different way using the bind . Look at this content of Main.cpp :
A. One-off bind with a predefine structure
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.BindAddVar(":id", 19809);
db.BindAddVar(":name", "liwei");
db.BindAddVar(":note", "test");
db.BindSql("insert into liwei_test (id,name,note)values(:id,:name,:note) ");
db.BindAddVar(":id", 169);
db.BindAddVar(":name", "sstem ch");
db.BindSql("insert into liwei_test (id,name)values(:id,:name) ");
db.Multiple_Disc();
}
OCIDB::BindAddVar() add the user parameters into the share variable which name
is m_BindVars . It has several overrided patterns .
OCIDB::BindSql() execute the SQL statement binding the structural variable . All of
the OCI operation needed is called in an order .
Notice : OCIDB::BindAddVar() allocates memory automaticly as the bind variable need .
B. Bind the variable step by step consistent with the defined order of OCI
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.UserPrepare("insert into liwei_test (id,name,note)values(:id,:name,:note)");
db.UserBind(":id",10701);
db.UserBind(":name", "liweitest");
db.UserBind(":note", "asdfasdf");
db.UserExecute();
db.UserCommit();
db.UserFree();
db.Multiple_Disc();
}
The process had been divided into several steps here . In this case you can extend some
further functions more easily .
Notice : The class function starts with "User" is a series which performs a common template .
Both the two methods above follow this calling order :
OCIHandleAlloc <stmt>
OCIStmtPrepare
OCIBindByName/OCIBindByPos
OCIStmtExecute
OCITransCommit/OCITransRollback
OCIHandleFree <stmt>
4. Get the Recordset from a SQL Select statement
In this section i'd like to introduce a simple query example about the select statement which
select-list cols is known before execution.
Query statements return data from the database to your application. When processing a query,
you must define an output variable or an array of output variables for each item in the select-list
from which you want to retrieve data.
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.UserSelect("select id,name,note,value from liwei_test where note='test'");
while(db.UserFetch()==0) {
printf("id:%f\n", db.UserGetFloat("id"));
printf("name:%s\n", db.UserGetString("name"));
printf("note:%s\n", db.UserGetString("note"));
printf("value:%f\n\n", db.UserGetFloat("value"));
}
db.UserSelectFree();
}
As you see , a simple one is gave now , which still seems a bit complex . Firstly i defined
a structure "TSelectVar" to store select-list . "TSelectVar" had a union to store values in
different type . Then i encapsuled the select operation into the four parts .
UserSelect
UserFetch
UserGet
UserSelectFree
So let's have a look at the first part -- OCIDB::UserSelect() .
OCIHandleAlloc() , OCIStmtPrepare(), OCIStmtExecute() and then the OCIAttrGet() !
I want to know the column count of the select-list , so used OCIAttrGet() there . For each
column , i called OCIParamGet() to get a OCI parameter and then used OCIAttrGet() to
fetch lots of useful imformation such as name, size, presision and so on .
After the get of attribute , you should define the content of the select-list using OCIDefineByPos()
or OCIDefineByName() then.
The purpose of these Get() and Define() calls is to describe the select-list for the following
action which is get . OCIDB::UserFetch() is easily comprehanded , for only one call OCIStmtFetch
in it .
Pay attention to the member function like OCIDB::UserGet , these functions is interface
to exterior user . It is overrided also .
int UserGetInt(int index);
int UserGetInt(char * name);
char * UserGetString(int index);
char * UserGetString(char * name);
float UserGetFloat(int index);
float UserGetFloat(char * name);
Peruse the code patiently and you will gain much knowledge .
5.The Last
This example is just for a demonstration or a accidence . You mush not stop here only .
Take this way for more info by practice and online help. Obviousely there are many points
which are not very standard in the article , for a exam i didn't use CONST while passing a
value . This is a important aspect to improve .
For you may not acclimatize youself to the variety of the OCI type , take it easy , try to
get together with the OCI Function Reference and force a type conversion .
-------------------------------------------------------------------------------------------------------
Oracle数据库开发(七).OCI示例开发说明
草木瓜
2007-6-26
这篇文章的主要内容是介绍的常见的OCI数据操作。可以顺着文章思路结合OCI
相关Oracle文档去理解。这里从原文档中复制了不少原句，这个省了很多麻烦而且
这些表达更为准确。
一、数据库连接
数据库连接是操作数据库的第一步。OCI提供了两种模式的连接，即单用户
单连接和多用户多连接。我这里的OCI示例也分别提供了两种实现方式。
a.单用户，单连接
-----------------------------------------------------------------------------
应用程序对于某一数据库连接仅支持单用户进程。
例子中对应的函数过程：
OCIDB::Single_Conn()
OCIDB::Single_Disc()
OCI内部过程的调用顺序如下：
OCIEnvCreate
OCIHandleAlloc <ERROR>
OCILogon
OCILogoff
OCIHandleFree <ERROR>
OCIHandleFree <ENV>
OCI应用程序必须有一个环境句柄，在这里由 OCIEnvCreate()创建。环境句柄相当
于定义一个容器，包含了以后的所有OCI句柄，这是OCI调用的基础。
OCIEnvCreate()创建并初始化了一个OCI函数的工作环境，必须先于其他OCI函数
之前调用。一般来说，从兼容性方面考虑，最好使用OCIInitialize() 和 OCIEnvInit()替代
OCIEnvCreate()。不过如果你在写dll或者共享库之类的东西，最好还是用OCIEnvCreate()。
OCIDB::Multiple_Conn()中就使用了OCIInitialize()和OCIEnvInit() ，我们一会会提到。
绝大多数的OCI调用都使用了一个或多个句柄。句柄指向由OCI库自动分配的内存
空间。可以存储连接环境信息(如，数据库连接环境或服务环境句柄) ，OCI函数执行
过程中相关信息(如，错误句柄或者描述句柄)。OCI句柄让开发工作变得简单起来，
这些相关信息是由OCI库所管理，而不是用户的应用程序。
不管是OCIEnvCreate()还是(OCIInitialize()和OCIEnvInit())都分配了一个数据库环境句
柄。我这里定义了私有成员变量m_pOCIEnv用来存储环境句柄。
OCI中还有一个很重要的句柄，经常出现在OCI调用过程中。这就是错误句柄。错误
句柄管理在OCI数据操作中出现的各类错误，如果在调用中出错，可以把错误句柄
传递给OCIErrorGet()来获取更多的错误信息。
OCIException::CheckError() 是一个完整的错误处理示例。
准备好环境句柄和错误句柄后，该到数据库连接的实际代码了。这里的OCILogon()
和 OCILogoff() 理解起来并不难。要注意OCILogon()执行过程中返回了一个服务环境
句柄。
服务环境句柄定义一些重要属性，直接决定了连接数据库的方式方法。服务环境
句柄包含三个属性，其实就是另外独立的三个句柄，即服务连接，用户会话和事务。
OCILogon()执行过程中其实也声明了服务连接和用户会话的相关句柄，只不过是隐
式声明。
在程序结束时手工释放资源是很好的习惯，可参见OCIDB::Single_Disc()。
b.多用户，多连接
-----------------------------------------------------------------------------
如果应用程序对单一数据库连接需要维护多个用户会话，就需要使用另一种方式了。
例子中对应的函数过程：
OCIDB::Multiple_Conn()
OCIDB::Multiple_Disc()
OCI内部过程的调用顺序如下：
OCIInitialize
OCIEnvInit
OCIHandleAlloc <Error>
OCIHandleAlloc <Server Context >
OCIHandleAlloc <Server>
OCIAttrSet <Set Server Into ServerContext>
OCIServerAttach <AttachServer>
OCIHandleAlloc <Session>
OCIAttrSet <Set Session Into ServerContext>
OCISessionBegin
...
与OCILogon()相比，变的有些复杂，每个句柄在这里都是手工去创建。需要提出
的是，所有的OCI句柄都有其相关属性，这些属性存储了一些有用的数据。可以使
用OCIAttrGet()获取对应信息，也可以通过OCIAttrSet()进行修改。OCIServerAttach()
创建了一个OCI操作的数据服务访问路径，OCISessionBegin() 确立用户会话连接。
这里完成后，才可以进行实际的数据操作。
注意这里的OCIAttrSet()和OCIServerAttach()的调用顺序不是固定的，类似的 OCISessionBegin()
和 OCIAttrSet() 先后顺序也是可以互换的。
你可以自行更改Main.cpp内容以完成不同的数据操作。
#include "OCIDB.h"
int main() {
OCIDB db;
db.Single_Conn();
db.Single_Disc();
}
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.Multiple_Disc();
}
二、执行无返回值的SQL语句
这类语句一般包括create , insert和delete等等，无返回值语句一般也可以理解为DDL
语句。OCI也支持对SQL语句进行变量绑定，下节会专门讨论。
例子中相关的函数是OCIDB::ExecuteSql() 。
首先你需要在环境句柄中声明一个语句句柄。语句句柄包含了SQL或者PL/SQL
语句及其相关属性，每个SQL语句在执行前必须要使用OCIStmtPrepare()进行预处理。
这个是本地化调用，不会向服务器端发送请求。
完成上面步骤后，就可以调用OCIStmtExecute() 执行SQL语句了。
代码语句如下：
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.ExecuteSql("update liwei_test set id =123 where note='test' ");
db.Multiple_Disc();
}
三、变量绑定
大多数DML语句或者一些查询(带where条件的)需要向SQL传递一些数据，下面的例子
是在程序编译时就已经知道要传递的数值。
insert into liwei_test (id,name,note)values(1,'aABD','cadsf')
这是个简单的例子，当预处理一个SQL语句或者PL/SQL块，其中有些数值是需要在运
行中才能确定的。这时就需要在运行动态去绑定变量了，我们在SQL语句或者PL/SQL块
中使用:id之类的符号，表示可以做为变量绑定。如：
insert into liwei_test (id,name,note)values(:id,:name,:note)
这个例子中，我使用了两种途径来绑定变量。
A.通过预定义的结构体一次性绑定
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.BindAddVar(":id", 19809);
db.BindAddVar(":name", "liwei");
db.BindAddVar(":note", "test");
db.BindSql("insert into liwei_test (id,name,note)values(:id,:name,:note) ");
db.BindAddVar(":id", 169);
db.BindAddVar(":name", "sstem ch");
db.BindSql("insert into liwei_test (id,name)values(:id,:name) ");
db.Multiple_Disc();
}
OCIDB::BindAddVar() 将相关的用户变量参数添加到m_BindVars变量中，这个函数
有不同的重载形式。
OCIDB::BindSql() 绑定结构体定义的变量，并执行SQL语句。
注意：OCIDB::BindAddVar() 根据绑定的变量值自动分配内存
B. 根据OCI内部顺序一步一步绑定
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.UserPrepare("insert into liwei_test (id,name,note)values(:id,:name,:note)");
db.UserBind(":id",10701);
db.UserBind(":name", "liweitest");
db.UserBind(":note", "asdfasdf");
db.UserExecute();
db.UserCommit();
db.UserFree();
db.Multiple_Disc();
}
这里把整个过程划分成多步，可以方便以后扩展功能。
注意：User打头的这些系列函数，其实是比较常用的一套模板。
这两种方式调用OCI内部函数的顺序都是一样的：
OCIHandleAlloc <stmt>
OCIStmtPrepare
OCIBindByName/OCIBindByPos
OCIStmtExecute
OCITransCommit/OCITransRollback
OCIHandleFree <stmt>s
四、从Select语句获取记录集
这节主要介绍一个简单的查询例子，例子中的查询列在执行前已经知道。
查询语句返回数据库中所需数据，执行查询时必须要定义外部的输出变量或者变
量数组来存储你所需要检索的数据。
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.UserSelect("select id,name,note,value from liwei_test where note='test'");
while(db.UserFetch()==0) {
printf("id:%f\n", db.UserGetFloat("id"));
printf("name:%s\n", db.UserGetString("name"));
printf("note:%s\n", db.UserGetString("note"));
printf("value:%f\n\n", db.UserGetFloat("value"));
}
db.UserSelectFree();
}
上面就是一个小例子。首先我先定义了一个结构体TSelectVar(与绑定变量的结构体
类似)准备存储返回的结果集。TSelectVar 中使用了Union存储不同类型的数值。然后
我把整个OCI Select过程封装成如下四部分：
UserSelect
UserFetch
UserGet
UserSelectFree
我们先看第一部分 OCIDB::UserSelect() 。
OCIHandleAlloc() , OCIStmtPrepare(), OCIStmtExecute() 然后是OCIAttrGet() 。
OCIAttrGet()主要是为了获取返回结果集的列数。对于每一列，使用了OCIParamGet()
先获取整列的OCIParm，然后再使用OCIAttrGet()依次获取象列名，列大小，精度等
有用的信息。
获取这些属性后，就需要调用OCIDefineByPos()或OCIDefineByName为结果集定义
内容了。
其实这些Get()和Define()就是为了描述Select执行后返回的结果集，方便我们获取其中
数据。OCIDB::UserFetch() 里面只有个OCIStmtFetch()，比较好理解，说白了就是控制
游标。
注意象OCIDB::UserGet的这些个成员函数，是对外的数据接口，以下是不同的重载形
式：
int UserGetInt(int index);
int UserGetInt(char * name);
char * UserGetString(int index);
char * UserGetString(char * name);
float UserGetFloat(int index);
float UserGetFloat(char * name);
这里没有什么好说的，读读代码就会清楚。
五、写在最后
这个例仅做演示。不过不必仅停留于此，如要提高，结合在线文档多练即可。
这个例子的代码在某些方面显然不够规范，如未在传值中使用const，这也是以后
要提高的方面。
另外，刚接触OCI时，可能会对内部的一堆类型不太适应，这里多看看函数的参考，
尽量做一些强制性的转换。