oracle 中oci 關于null 的處理方法

oci中處理null，必須通過 Indicator 來完成。

使用的oracle 版本 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

#include <stdlib.h>

#include <stdio.h>

#include <string.h>

#include "include/oci.h"

#include "include/ociextp.h"

#define ERRGOTO(Recode) do /

{ /

if (Recode!=0) /

{ /

goto ERR; /

} /

} while (0)

void main()

{

int a = 0 ;

OCIDefine* defhp1 = 0;

OCIDefine* defhp2 = 0;

int blength = 40;

char b[40];

sb2 indicator;

text *select_sql = (text *)"select a,b from teststmt2";

dvoid *tmp;

OCIEnv *envhp;

OCIServer *srvhp;

OCIError *errhp;

OCISvcCtx *svchp;

OCISession *usrhp;

OCIStmt *stmthp;

char serName1[30] ="10.0.4.161:1521/orcl";

char userName1[30] = "cxy";

char pwd1[30] = "cxy";

//!如果沒有資料可以使用這個測試用例建立資料表。

//test_BindArrayOfStruct();

memset(b, 0, blength);

//!初始化換環境句柄

ERRGOTO(OCIEnvCreate(&(envhp), OCI_DEFAULT,

NULL, NULL, NULL, NULL, 0, NULL));

//!初始化錯誤句柄

ERRGOTO(OCIHandleAlloc((dvoid *)envhp,(dvoid **)&errhp,OCI_HTYPE_ERROR,64,(dvoid **) &tmp));

//!配置設定服務上下文句柄和服務句柄

ERRGOTO(OCIHandleAlloc((dvoid *)envhp,(dvoid **)&srvhp,OCI_HTYPE_SERVER, 64,(dvoid **) &tmp));

ERRGOTO(OCIHandleAlloc((dvoid *)envhp,(dvoid **)&svchp,OCI_HTYPE_SVCCTX, 64,(dvoid **) &tmp));

//!初始化伺服器句柄

ERRGOTO(OCIServerAttach( srvhp, errhp, (text *)serName1, (sb4) strlen(serName1), (ub4) OCI_DEFAULT));

//!/将伺服器句柄連接配接到服務上下文句柄

ERRGOTO(OCIAttrSet ((dvoid *)svchp, OCI_HTYPE_SVCCTX,(dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, errhp));

//!配置設定設定會話句柄，并向裡填充使用者名和密碼

ERRGOTO(OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&usrhp,OCI_HTYPE_SESSION, 0, (dvoid **) 0));

ERRGOTO(OCIAttrSet ((dvoid *)usrhp, OCI_HTYPE_SESSION,(dvoid *)userName1, (ub4)strlen(userName1),OCI_ATTR_USERNAME, errhp));

ERRGOTO(OCIAttrSet ((dvoid *)usrhp, OCI_HTYPE_SESSION,(dvoid *)pwd1, (ub4)strlen(pwd1),OCI_ATTR_PASSWORD, errhp));

//!建立會話

ERRGOTO(OCISessionBegin (svchp, errhp, usrhp,OCI_CRED_RDBMS, OCI_DEFAULT));

//!将會話句柄連接配接到服務上下文句柄

ERRGOTO( OCIAttrSet ( (dvoid *)svchp, OCI_HTYPE_SVCCTX,(dvoid *)usrhp, (ub4) 0, OCI_ATTR_SESSION, errhp));

//!配置設定語句句柄

ERRGOTO(OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0));

//!開始查詢并獲得結果

text *delete_table = (text *)"delete from teststmt2";

ERRGOTO(OCIStmtPrepare(stmthp,errhp,(text *)delete_table,strlen((char *)delete_table),OCI_NTV_SYNTAX,OCI_DEFAULT));

ERRGOTO(OCIStmtExecute(svchp,stmthp,errhp,1,0,0,0,OCI_DEFAULT));

int aa = 8;

char bb[] = "bbbbbb";

indicator = -1;

text *insert_sql = (text *)"INSERT INTO teststmt2(a,b) values(:1,:2)";

OCIBind* bindhp = 0;

//!準備資料，綁定參數

ERRGOTO(OCIStmtPrepare(stmthp,errhp,(text *)insert_sql,strlen((char *)insert_sql),OCI_NTV_SYNTAX,OCI_DEFAULT));

ERRGOTO(OCIBindByPos(stmthp,&bindhp,errhp,1, (dvoid *)&aa,4, SQLT_INT, (void*)&indicator, NULL, NULL,0,0,0));

//OCIBindArrayOfStruct(bindhp,errhp,sizeof(int),0,0,0);

ERRGOTO(OCIBindByPos(stmthp,&bindhp,errhp,2,(dvoid *)&bb, 6, SQLT_CHR,NULL,NULL,NULL,0,0,0));

//OCIBindArrayOfStruct(bindhp,errhp,2,0,0,0);

//！執行語句

ERRGOTO(OCIStmtExecute(svchp,stmthp,errhp,1,0,0,0,0));

indicator = -1;

//!開始查詢并獲得結果

ERRGOTO(OCIStmtPrepare(stmthp,errhp,(text *)select_sql,strlen((char *)select_sql),OCI_NTV_SYNTAX,OCI_DEFAULT));

//!綁定以一個列

ERRGOTO(OCIDefineByPos(stmthp,&defhp1,errhp,1,&a,sizeof(a),SQLT_INT, (void*)&indicator,0,0,OCI_DEFAULT));

//ERRGOTO(OCIDefineArrayOfStruct(defhp1,errhp,sizeof(int),0,4,0));

//!綁定以二個列

indicator = -2;

ERRGOTO(OCIDefineByPos(stmthp,&defhp2,errhp,2,&b, 20, SQLT_CHR, (void*)&indicator, 0, 0, OCI_DEFAULT));

//ERRGOTO(OCIDefineArrayOfStruct(defhp2,errhp,2,0,2,0));

//!執行語句

ERRGOTO(OCIStmtExecute(svchp,stmthp,errhp,1,0,0,0,OCI_DEFAULT));

//ERRGOTO(OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,0));

printf("%d, %s, %d", a, b, indicator);

ERRGOTO(OCITransCommit(svchp,errhp,0));

//!釋放各個資源

//ERRGOTO(OCIHandleFree(srvhp,OCI_HTYPE_SERVER));

//ERRGOTO(OCIHandleFree(errhp,OCI_HTYPE_ERROR));

//ERRGOTO(OCIHandleFree(envhp,OCI_HTYPE_ENV));

return ;

ERR:

sb4 errcod=0;

OraText msgerr[200]="";

OraText msgstat[200]="";

OCIErrorGet(errhp,1,msgstat,&errcod,msgerr,200,OCI_HTYPE_ERROR);

printf((char *)msgerr);

return;

}

官方文檔中的描述

Input

For input host variables, the OCI application can assign the following values to an indicator variable:

Table 2-7 Input Indicator Values

Input Indicator Value	Action Taken by Oracle
-1	Oracle assigns a NULL to the column, ignoring the value of the input variable.
>=0	Oracle assigns the value of the input variable to the column.

Output

On output, Oracle can assign the following values to an indicator variable:

Table 2-8 Output Indicator Values

Output Indicator Value	Meaning
-2	The length of the item is greater than the length of the output variable; the item has been truncated. Additionally, the original length is longer than the maximum data length that can be returned in the sb2 indicator variable.
-1	The selected value is null, and the value of the output variable is unchanged.
Oracle assigned an intact value to the host variable.
>0	The length of the item is greater than the length of the output variable; the item has been truncated. The positive value returned in the indicator variable is the actual length before truncation.

Indicator Variables for Named Data Types and REFs

Indicator variables for most new (after release 8.0) datatypes function as described above. The only exception is SQLT_NTY (a named datatype). Data of type SQLT_REF uses a standard scalar indicator, just like other variable types. For data of type SQLT_NTY, the indicator variable must be a pointer to an indicator structure.

When database types are translated into C struct representations using the Object Type Translator (OTT), a null indicator structure is generated for each object type. This structure includes an atomic null indicator, plus indicators for each object attribute.

sb2 indicator ;

對于in

indicator = -1;

OCIBindByPos(stmthp,&bindhp,errhp,1, (dvoid *)&aa,4, SQLT_INT, (void*)&indicator, NULL, NULL,0,0,0))

當indicator = -1;無論綁定的是任何值，插入資料庫的全為null

對于out

 初始

indicator = 0；

OCIDefineByPos(stmthp,&defhp1,errhp,1,&a,sizeof(a),SQLT_INT, (void*)&indicator,0,0,OCI_DEFAULT);

執行完成，當indicator = -1;如資料庫中的值為null，則綁定的值不修改，并且不報錯，否則替換成資料庫中的值，

如果沒傳入indicator參數，而隻是指定一個空指針的話，報錯，說提取的列為null,

-2 和 >0時，測試發現差不多，都是當綁定值小于資料庫的值時，把indicator 置為資料庫中的實際長度，用處不大

綜上所述主要就是indicator = -1，可以在資料庫中插入null值，或判斷資料庫中的是否為null

參數indp是訓示符緩沖區，也是一個數組，每個元素是一個sb2類型的值。一般作輸入用，如果此項動态參數會被輸出，則也作輸出用。在輸入時，元素值為-1時表示NULL值輸入，大于或等于0時是普通得值輸入。在輸出時，和函數OCIDefineByPos()裡的訓示符緩沖區作用相同，其值将會在提取資料後填入。其值标志着取到的數值的特殊資訊。-2表示值的長度過大，且超過sb4類型的最大值，取出的值被截斷，是部分值。-1表示值為NULL，因為C/C++沒有NULL這個類型，是以這是判斷取出的字段值是否是NULL的唯一方法。0表示資料被完整取出。大于0的值，表示取出的值的位元組大小超過定義的每個值的位元組大小，取出的值被截斷，傳回的值是被截斷前的位元組的大小。