OCI介面學習筆記--基本理解（二）

一． Oracle oci工具包安裝：
$ORACLE_HOME\BIN:執行檔案和help檔案
$ORACLE_HOME\OCI\INCLUDE:標頭檔案
$ORACLE_HOME\OCI\LIB\BC:  for Borlanf C++的OCI庫
$ORACLE_HOME\OCI\LIB\MSVC:  for MS Visual C++的CI庫
如果是unix下，對於ORACLE8i，則OCI庫在$ORACLE_HOME/lib下,如果是9i,則在$ORACLE_HOME/lib32下，庫檔名一般為libclntsh.so

二、一個資料庫OCI操作一般流程

• 初始化OCI環境
• 申請控制程式碼
• 連線資料庫
• 建立會話
• 執行SQL操作（在這個過程中包括：(1)準備SQL語句。(2)在SQL語句中繫結需要輸入到SQL語句中的變數。(3)執行SQL語句。(4)獲取SQL中的輸出描述。(5)定義輸出變數。(6)獲取資料）
• 斷開會話
• 斷開伺服器
• 釋放資源

三、例項分析

先通過一段較為簡單的程式碼大致理解一下OCI程式設計：

1. #include <oci.h>
   
2. #include <iostream>
   
3. #include <string>
   
4. #include <string.h>
   
5. #include <stdlib.h>
   
6. using namespace std;
   
7. 
   
8. //存放查詢資料的結構體
   
9. struct result
   
10. {
    
11.     char ename[20];
    
12.     char cname[20];
    
13.     result()
    
14.     {
    
15.         memset(ename, '\0', sizeof(ename));
    
16.         memset(cname, '\0', sizeof(cname));
    
17.     }
    
18. };
    
19. 
    
20. int main()
    
21. {
    
22.     // 初始化 OCI 環境控制程式碼指標
    
23.     OCIEnv *envhpp = NULL;
    
24.     // 初始化伺服器控制程式碼
    
25.     OCIServer *servhpp = NULL;
    
26.     // 用於捕獲 OCI 錯誤資訊
    
27.     OCIError *errhpp = NULL;
    
28.     // 初始化會話控制程式碼
    
29.     OCISession *usrhpp = NULL;
    
30.     // 初始化服務上下文控制程式碼
    
31.     OCISvcCtx *svchpp = NULL;
    
32.     // 初始化表示式控制程式碼
    
33.     OCIStmt *stmthpp = NULL;
    
34. 
    
35.     string server="mydb";
    
36. 
    
37.     // 建立 OCI 環境 , 並設定環境控制程式碼。
    
38.     sword swResult = OCIEnvCreate(&envhpp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    
39.     if (swResult != OCI_SUCCESS && swResult != OCI_SUCCESS_WITH_INFO)
    
40.     {
    
41.         cout << "Oracle environment initialization error!" << endl;
    
42.         exit(1);
    
43.     }
    
44.     cout << "Oracle environment initialization success!" << endl;
    
45. 
    
46.     // 建立錯誤控制程式碼
    
47.     OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
    
48. 
    
49.     // 建立服務控制程式碼
    
50.     OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&servhpp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);
    
51. 
    
52.     // 連線伺服器，如果失敗則獲取錯誤碼
    
53.     if (OCIServerAttach(servhpp, errhpp, (text *)server.c_str(), strlen(server.c_str()), 0) != OCI_SUCCESS)
    
54.     {
    
55.         int errcno;
    
56.         char errbuf[512] = "";
    
57.         sb4 errcode;
    
58. 
    
59.         // 獲取錯誤指標和 OCI 錯誤程式碼
    
60.         OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
    
61.         errcno = errcode;
    
62. 
    
63.         cout << "Oracle server attach error:" << errbuf << endl;
    
64.         OCIHandleFree((dvoid *)envhpp,OCI_HTYPE_ENV);
    
65.         OCIHandleFree((dvoid *)servhpp,OCI_HTYPE_SERVER);
    
66.         OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
    
67.         exit(1);
    
68.     }
    
69.     cout << "Oracle server attach success!"<< endl;
    
70. 
    
71.     /***************** 連線資料庫 ****************/
    
72.     string user = "user";
    
73.     string pas = "passwd";
    
74.     errhpp = NULL;
    
75. 
    
76.     // 建立錯誤控制程式碼
    
77.     (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
    
78.     // 建立服務上下文控制程式碼
    
79.     (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&svchpp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **)0);
    
80.     // 設定屬性
    
81.     (void) OCIAttrSet((dvoid *)svchpp, OCI_HTYPE_SVCCTX, (dvoid *)servhpp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhpp);
    
82.     // 建立使用者連線控制程式碼
    
83.     (void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&usrhpp, (ub4)OCI_HTYPE_SESSION, (size_t) 0, (dvoid **)0);
    
84.     // 設定使用者名稱、密碼
    
85.     (void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)user.c_str(), (ub4)strlen(user.c_str()), (ub4)OCI_ATTR_USERNAME, errhpp);
    
86.     (void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pas.c_str(), (ub4)strlen(pas.c_str()), (ub4)OCI_ATTR_PASSWORD, errhpp);
    
87. 
    
88.     // 建立會話連線
    
89.     if(OCISessionBegin(svchpp, errhpp, usrhpp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
    
90.     {
    
91.         int errcno;
    
92.         char errbuf[512]={'\0'};
    
93.         sb4 errcode;
    
94.        
    
95.         // 獲取錯誤指標和 OCI 錯誤程式碼
    
96.         OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
    
97.         errcno = errcode;
    
98.         cout << "User session error:" << errbuf << endl;
    
99.         OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
    
100.         OCIHandleFree((dvoid *)usrhpp,OCI_HTYPE_SESSION);
     
101.         OCIHandleFree((dvoid *)svchpp,OCI_HTYPE_SVCCTX);
     
102.         exit(1);
     
103.     }
     
104.     cout << "user session success!" << endl;
     
105.    
     
106.     (void) OCIAttrSet((dvoid *)svchpp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)usrhpp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhpp);
     
107. 
     
108.     /*************** 執行 查詢SQL 語句 ******************/
     
109.     errhpp = NULL;
     
110. 
     
111.     // 建立一個表示式控制程式碼
     
112.     if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
     
113.     {
     
114.         cout << "Create STMT error !" << endl;
     
115.         exit(1);
     
116.     }
     
117.     cout << "Create stmt success !" << endl;
     
118.    
     
119.     // 建立錯誤控制程式碼
     
120.     OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
     
121.    
     
122.     // Select語句
     
123.     char sql[255] = "select col1, col2 from table1 ";
     
124.    
     
125.     if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
     
126.     {
     
127.          cout << "Create prepare error!" << sql << endl;
     
128.          exit(1);
     
129.     }
     
130.     cout << "Create prepare success!" << endl;
     
131. 
     
132.     /********* 繫結引數 ***********/
     
133.     // 申請繫結欄位的控制程式碼
     
134.     OCIDefine *bhp1 = NULL;
     
135.     OCIDefine *bhp2 = NULL;
     
136.    
     
137.     // 存放資料的結構
     
138.     struct result rst;
     
139.    
     
140.     // 指定提取資料長度
     
141.     ub2 datalen = 0;
     
142.     // 定義指示器變數 , 用於取可能存在空值的欄位
     
143.     char isnul[6] = "";
     
144.     // 定義輸出變數 ,
     
145.     OCIDefineByPos(stmthpp, &bhp1, errhpp, 1, (dvoid *)&rst.ename, sizeof(rst.ename), SQLT_CHR, NULL, &datalen, NULL, OCI_DEFAULT);
     
146.     OCIDefineByPos(stmthpp, &bhp2, errhpp, 2, (dvoid *)&rst.cname, sizeof(rst.cname), SQLT_STR, NULL, &datalen, NULL, OCI_DEFAULT);
     
147. 
     
148.     // 獲取 SQL 語句型別
     
149.     ub2 stmt_type;
     
150.     OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, errhpp);
     
151.    
     
152.     // 執行 SQL 語句
     
153.     OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)0, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
     
154. 
     
155.     // 獲取查詢資訊
     
156.     int rows_fetched;
     
157.     do
     
158.     {
     
159.         cerr << rst.ename<< " ";
     
160.         cerr << rst.cname<< " \n";
     
161.     }
     
162.     while(OCIStmtFetch2(stmthpp, errhpp, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT) != OCI_NO_DATA);
     
163. 
     
164.     // 獲得記錄條數
     
165.     OCIAttrGet((CONST void *)stmthpp, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched), OCI_ATTR_ROW_COUNT, errhpp);
     
166.     cout << " rows :" << rows_fetched << endl;
     
167. 
     
168.     /*************** 執行 新增SQL 語句 ******************/
     
169.     if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
     
170.     {
     
171.         cout << "Create STMT error !" << endl;
     
172.         exit(1);
     
173.     }
     
174.     cout << "Create stmt success !" << endl;
     
175. 
     
176.     OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
     
177. 
     
178.     // Insert語句
     
179.     char sql2[255] = "insert into table1 (col1, col2) values('testoci', 'testoci')";
     
180.    
     
181.     // 準備Sql語句
     
182.     if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql2, (ub4)strlen(sql2), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
     
183.     {
     
184.          cout << "Create prepare error!" << sql2 << endl;
     
185.          exit(1);
     
186.     }
     
187.     cout << "Create prepare success!" << endl;
     
188.    
     
189.     // 執行SQL 語句
     
190.     OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)1, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
     
191. 
     
192.     // 斷開使用者會話
     
193.     OCILogoff(svchpp, errhpp);
     
194.    
     
195.     // 斷開伺服器連線
     
196.     OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);
     
197.    
     
198.     // 釋放資源
     
199.     OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);
     
200.     OCIHandleFree((dvoid *) svchpp, OCI_HTYPE_SVCCTX);
     
201.     OCIHandleFree((dvoid *) servhpp, OCI_HTYPE_SERVER);
     
202.     OCIHandleFree((dvoid *) errhpp, OCI_HTYPE_ERROR);
     
203. 
     
204.     return 0;
     
205. }