Proc The basic knowledge of the indicator variable variable

• Indicator variables are a class of SQL variables associated with host variables that are used to monitor and manage host variables associated with them, and each host variable can define an indicator variable. The specific effects of indicator variables are as follows:
(1) Enter a null value into the database table column
(2) to check whether the data selected from the database table column is a null value, or whether a truncation problem is occurring
• Indicates that the variable must be defined as a 2-byte integer type (short)
• SQL statement, if the indicator keyword is not specified, indicates that the variable must be immediately after the host variable with which it is associated
• If the host variable is declared using declare section, the associated indicator variable must also be declared with declare section
• Before you indicate a variable in an SQL statement, you also need to add the ":" Indicator
: host_variable indicator:indicator_variable
is equivalent to
: host_variable:indicator_ Variable
• Indicates the value of a variable, and its meaning is as follows:
The ø0 operation succeeded
ø-1 the host variable corresponding to the indicated variable returned or inserted, updated to a null value
Ø-2 data is very long when it is stored from the database to the corresponding host variable, and It is not possible to infer how many bytes have been truncated
ø>0 is truncated in the corresponding host variable when the data is FETHC or select, indicating that the variable holds the corresponding column length
· Oracle will report a 1405 error if you do not have to instruct a variable to assign a null value to a host variable in a SELECT or FETCH statement. To avoid this situation, there are two possible solutions:
Ø Use the indicator variable
Ø to adjust the command-line arguments of the Pro*c/c++ precompiled program proc, setting the Mode=oracle,unsafe_null=yes

Reference to indicator variable:
In an SQL statement, the indicator variable name should be preceded by a colon and must be followed by the associated host variable. In C, indicator variables are used as stand-alone as C variables, without having to prefix colons or following related host variables. For example:
EXEC SQL SELECT EMPN ()
Into::emp_number:ind_num
From EMP
WHERE ename =: emp_name;
If (Ind_num =-1)
{
Printf ("Employlee number is NULL. ”）；
}
To enhance readability, you can add indicator keywords between the host variable and its indicator variable. Such as:
EXEC SQL SELECT EMPNO
Into:emp_number Indicator:ind_num
From EMP
WHERE ename =: emp_name;

Examples of application of indicator variables:
Example 1: Inserting a null value into a database table column
Printf ("Enter department number and name");
SCANF ("%d%s", &dept_number, Dept_name);
If (dept_number = 0)
{
Ind_num =-1;
}
Else
{
Ind_num = 0;
}
EXEC SQL INSERT into DEPT (deptno,dname) VALUES (:d ept_number:ind_num,:d ept_name)
Where Ind_num is an indicator variable of dept_number, when the value of the input dept_number is 0, the null value is inserted into the Deptno column of the Dept table

Example 2: Check if the value retrieved from the database table column is null
EXEC SQL BEGIN DECLARE section;
int emp_number;
float salary, commission;
Short Ind_comm; /* Indicator Variable * *
EXEC SQL end DECLARE section;

EXEC SQL SELECT SAL, COMM
Into:salary,: Commission:ind_comm
From EMP
WHERE EMPNO =: emp_number;
if (-1 = ind_comm)/* Commission variable is null */
Pay = salary;
Else
Pay = salary + commission;