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時，可能會對內部的一堆類型不太適應，這里多看看函數的參考，
盡量做一些強制性的轉換。