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.
0	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的值，表示取出的值的字节大小超过定义的每个值的字节大小，取出的值被截断，返回的值是被截断前的字节的大小。