Oracle 中oci 关于null 的处理方法

转载自：http://www.linuxidc.com/Linux/2011-02/32564p2.htm

官方文档中的描述

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