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/<span9987879><a href="javascript:void(0);" style="color:#000FFF;text-decoration:underline" id="Y9987879S9">orcl</a></span9987879>";
- 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 |
>=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的值,表示取出的值的字节大小超过定义的每个值的字节大小,取出的值被截断,返回的值是被截断前的字节的大小。