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數(shù)據(jù)庫(kù)開發(fā)(七).OCI示例開發(fā)說(shuō)明
草木瓜
2007-6-26
這篇文章的主要內(nèi)容是介紹的常見的OCI數(shù)據(jù)操作。可以順著文章思路結(jié)合OCI
相關(guān)Oracle文檔去理解。這里從原文檔中復(fù)制了不少原句，這個(gè)省了很多麻煩而且
這些表達(dá)更為準(zhǔn)確。
一、數(shù)據(jù)庫(kù)連接
數(shù)據(jù)庫(kù)連接是操作數(shù)據(jù)庫(kù)的第一步。OCI提供了兩種模式的連接，即單用戶
單連接和多用戶多連接。我這里的OCI示例也分別提供了兩種實(shí)現(xiàn)方式。
a.單用戶，單連接
-----------------------------------------------------------------------------
應(yīng)用程序?qū)τ谀骋粩?shù)據(jù)庫(kù)連接僅支持單用戶進(jìn)程。
例子中對(duì)應(yīng)的函數(shù)過(guò)程：
OCIDB::Single_Conn()
OCIDB::Single_Disc()
OCI內(nèi)部過(guò)程的調(diào)用順序如下：
OCIEnvCreate
OCIHandleAlloc <ERROR>
OCILogon
OCILogoff
OCIHandleFree <ERROR>
OCIHandleFree <ENV>
OCI應(yīng)用程序必須有一個(gè)環(huán)境句柄，在這里由 OCIEnvCreate()創(chuàng)建。環(huán)境句柄相當(dāng)
于定義一個(gè)容器，包含了以后的所有OCI句柄，這是OCI調(diào)用的基礎(chǔ)。
OCIEnvCreate()創(chuàng)建并初始化了一個(gè)OCI函數(shù)的工作環(huán)境，必須先于其他OCI函數(shù)
之前調(diào)用。一般來(lái)說(shuō)，從兼容性方面考慮，最好使用OCIInitialize() 和 OCIEnvInit()替代
OCIEnvCreate()。不過(guò)如果你在寫dll或者共享庫(kù)之類的東西，最好還是用OCIEnvCreate()。
OCIDB::Multiple_Conn()中就使用了OCIInitialize()和OCIEnvInit() ，我們一會(huì)會(huì)提到。
絕大多數(shù)的OCI調(diào)用都使用了一個(gè)或多個(gè)句柄。句柄指向由OCI庫(kù)自動(dòng)分配的內(nèi)存
空間。可以存儲(chǔ)連接環(huán)境信息(如，數(shù)據(jù)庫(kù)連接環(huán)境或服務(wù)環(huán)境句柄) ，OCI函數(shù)執(zhí)行
過(guò)程中相關(guān)信息(如，錯(cuò)誤句柄或者描述句柄)。OCI句柄讓開發(fā)工作變得簡(jiǎn)單起來(lái)，
這些相關(guān)信息是由OCI庫(kù)所管理，而不是用戶的應(yīng)用程序。
不管是OCIEnvCreate()還是(OCIInitialize()和OCIEnvInit())都分配了一個(gè)數(shù)據(jù)庫(kù)環(huán)境句
柄。我這里定義了私有成員變量m_pOCIEnv用來(lái)存儲(chǔ)環(huán)境句柄。
OCI中還有一個(gè)很重要的句柄，經(jīng)常出現(xiàn)在OCI調(diào)用過(guò)程中。這就是錯(cuò)誤句柄。錯(cuò)誤
句柄管理在OCI數(shù)據(jù)操作中出現(xiàn)的各類錯(cuò)誤，如果在調(diào)用中出錯(cuò)，可以把錯(cuò)誤句柄
傳遞給OCIErrorGet()來(lái)獲取更多的錯(cuò)誤信息。
OCIException::CheckError() 是一個(gè)完整的錯(cuò)誤處理示例。
準(zhǔn)備好環(huán)境句柄和錯(cuò)誤句柄后，該到數(shù)據(jù)庫(kù)連接的實(shí)際代碼了。這里的OCILogon()
和 OCILogoff() 理解起來(lái)并不難。要注意OCILogon()執(zhí)行過(guò)程中返回了一個(gè)服務(wù)環(huán)境
句柄。
服務(wù)環(huán)境句柄定義一些重要屬性，直接決定了連接數(shù)據(jù)庫(kù)的方式方法。服務(wù)環(huán)境
句柄包含三個(gè)屬性，其實(shí)就是另外獨(dú)立的三個(gè)句柄，即服務(wù)連接，用戶會(huì)話和事務(wù)。
OCILogon()執(zhí)行過(guò)程中其實(shí)也聲明了服務(wù)連接和用戶會(huì)話的相關(guān)句柄，只不過(guò)是隱
式聲明。
在程序結(jié)束時(shí)手工釋放資源是很好的習(xí)慣，可參見OCIDB::Single_Disc()。
b.多用戶，多連接
-----------------------------------------------------------------------------
如果應(yīng)用程序?qū)我粩?shù)據(jù)庫(kù)連接需要維護(hù)多個(gè)用戶會(huì)話，就需要使用另一種方式了。
例子中對(duì)應(yīng)的函數(shù)過(guò)程：
OCIDB::Multiple_Conn()
OCIDB::Multiple_Disc()
OCI內(nèi)部過(guò)程的調(diào)用順序如下：
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()相比，變的有些復(fù)雜，每個(gè)句柄在這里都是手工去創(chuàng)建。需要提出
的是，所有的OCI句柄都有其相關(guān)屬性，這些屬性存儲(chǔ)了一些有用的數(shù)據(jù)。可以使
用OCIAttrGet()獲取對(duì)應(yīng)信息，也可以通過(guò)OCIAttrSet()進(jìn)行修改。OCIServerAttach()
創(chuàng)建了一個(gè)OCI操作的數(shù)據(jù)服務(wù)訪問(wèn)路徑，OCISessionBegin() 確立用戶會(huì)話連接。
這里完成后，才可以進(jìn)行實(shí)際的數(shù)據(jù)操作。
注意這里的OCIAttrSet()和OCIServerAttach()的調(diào)用順序不是固定的，類似的 OCISessionBegin()
和 OCIAttrSet() 先后順序也是可以互換的。
你可以自行更改Main.cpp內(nèi)容以完成不同的數(shù)據(jù)操作。
#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();
}
二、執(zhí)行無(wú)返回值的SQL語(yǔ)句
這類語(yǔ)句一般包括create , insert和delete等等，無(wú)返回值語(yǔ)句一般也可以理解為DDL
語(yǔ)句。OCI也支持對(duì)SQL語(yǔ)句進(jìn)行變量綁定，下節(jié)會(huì)專門討論。
例子中相關(guān)的函數(shù)是OCIDB::ExecuteSql() 。
首先你需要在環(huán)境句柄中聲明一個(gè)語(yǔ)句句柄。語(yǔ)句句柄包含了SQL或者PL/SQL
語(yǔ)句及其相關(guān)屬性，每個(gè)SQL語(yǔ)句在執(zhí)行前必須要使用OCIStmtPrepare()進(jìn)行預(yù)處理。
這個(gè)是本地化調(diào)用，不會(huì)向服務(wù)器端發(fā)送請(qǐng)求。
完成上面步驟后，就可以調(diào)用OCIStmtExecute() 執(zhí)行SQL語(yǔ)句了。
代碼語(yǔ)句如下：
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.ExecuteSql("update liwei_test set id =123 where note='test' ");
db.Multiple_Disc();
}
三、變量綁定
大多數(shù)DML語(yǔ)句或者一些查詢(帶where條件的)需要向SQL傳遞一些數(shù)據(jù)，下面的例子
是在程序編譯時(shí)就已經(jīng)知道要傳遞的數(shù)值。
insert into liwei_test (id,name,note)values(1,'aABD','cadsf')
這是個(gè)簡(jiǎn)單的例子，當(dāng)預(yù)處理一個(gè)SQL語(yǔ)句或者PL/SQL塊，其中有些數(shù)值是需要在運(yùn)
行中才能確定的。這時(shí)就需要在運(yùn)行動(dòng)態(tài)去綁定變量了，我們?cè)赟QL語(yǔ)句或者PL/SQL塊
中使用:id之類的符號(hào)，表示可以做為變量綁定。如：
insert into liwei_test (id,name,note)values(:id,:name,:note)
這個(gè)例子中，我使用了兩種途徑來(lái)綁定變量。
A.通過(guò)預(yù)定義的結(jié)構(gòu)體一次性綁定
#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() 將相關(guān)的用戶變量參數(shù)添加到m_BindVars變量中，這個(gè)函數(shù)
有不同的重載形式。
OCIDB::BindSql() 綁定結(jié)構(gòu)體定義的變量，并執(zhí)行SQL語(yǔ)句。
注意：OCIDB::BindAddVar() 根據(jù)綁定的變量值自動(dòng)分配內(nèi)存
B. 根據(jù)OCI內(nèi)部順序一步一步綁定
#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();
}
這里把整個(gè)過(guò)程劃分成多步，可以方便以后擴(kuò)展功能。
注意：User打頭的這些系列函數(shù)，其實(shí)是比較常用的一套模板。
這兩種方式調(diào)用OCI內(nèi)部函數(shù)的順序都是一樣的：
OCIHandleAlloc <stmt>
OCIStmtPrepare
OCIBindByName/OCIBindByPos
OCIStmtExecute
OCITransCommit/OCITransRollback
OCIHandleFree <stmt>s
四、從Select語(yǔ)句獲取記錄集
這節(jié)主要介紹一個(gè)簡(jiǎn)單的查詢例子，例子中的查詢列在執(zhí)行前已經(jīng)知道。
查詢語(yǔ)句返回?cái)?shù)據(jù)庫(kù)中所需數(shù)據(jù)，執(zhí)行查詢時(shí)必須要定義外部的輸出變量或者變
量數(shù)組來(lái)存儲(chǔ)你所需要檢索的數(shù)據(jù)。
#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();
}
上面就是一個(gè)小例子。首先我先定義了一個(gè)結(jié)構(gòu)體TSelectVar(與綁定變量的結(jié)構(gòu)體
類似)準(zhǔn)備存儲(chǔ)返回的結(jié)果集。TSelectVar 中使用了Union存儲(chǔ)不同類型的數(shù)值。然后
我把整個(gè)OCI Select過(guò)程封裝成如下四部分：
UserSelect
UserFetch
UserGet
UserSelectFree
我們先看第一部分 OCIDB::UserSelect() 。
OCIHandleAlloc() , OCIStmtPrepare(), OCIStmtExecute() 然后是OCIAttrGet() 。
OCIAttrGet()主要是為了獲取返回結(jié)果集的列數(shù)。對(duì)于每一列，使用了OCIParamGet()
先獲取整列的OCIParm，然后再使用OCIAttrGet()依次獲取象列名，列大小，精度等
有用的信息。
獲取這些屬性后，就需要調(diào)用OCIDefineByPos()或OCIDefineByName為結(jié)果集定義
內(nèi)容了。
其實(shí)這些Get()和Define()就是為了描述Select執(zhí)行后返回的結(jié)果集，方便我們獲取其中
數(shù)據(jù)。OCIDB::UserFetch() 里面只有個(gè)OCIStmtFetch()，比較好理解，說(shuō)白了就是控制
游標(biāo)。
注意象OCIDB::UserGet的這些個(gè)成員函數(shù)，是對(duì)外的數(shù)據(jù)接口，以下是不同的重載形
式：
int UserGetInt(int index);
int UserGetInt(char * name);
char * UserGetString(int index);
char * UserGetString(char * name);
float UserGetFloat(int index);
float UserGetFloat(char * name);
這里沒(méi)有什么好說(shuō)的，讀讀代碼就會(huì)清楚。
五、寫在最后
這個(gè)例僅做演示。不過(guò)不必僅停留于此，如要提高，結(jié)合在線文檔多練即可。
這個(gè)例子的代碼在某些方面顯然不夠規(guī)范，如未在傳值中使用const，這也是以后
要提高的方面。
另外，剛接觸OCI時(shí)，可能會(huì)對(duì)內(nèi)部的一堆類型不太適應(yīng)，這里多看看函數(shù)的參考，
盡量做一些強(qiáng)制性的轉(zhuǎn)換。