Oracle Pro * C/C + + (7) : embedded SQL syntax | August more challenges

C language development process, need to operate the database, use Oracle database generally will use Oracle Pro*C/C++ precompiler.

This article introduces the Oracle Pro*C/C++ precompiler in detail, learns its role in developing applications that manipulate Oracle data, and learns what it enables your applications to do. Specific as use.

Host variable syntax

Oracle uses host variables to pass data and state information to your program; The program uses host variables to pass data to Oracle.

Output and input host variables

Host variables are called output host variables or input host variables depending on how they are used.

The primary variables in the INTO clause of a SELECT or FETCH statement are called output host variables because they hold the column values output by Oracle. Oracle assigns column values to the corresponding output host variable in the INTO clause.

All other host variables in the SQL statement are called input host variables because the program enters their values into Oracle. For example, input host variables are used in the VALUES clause of an INSERT statement and in the SET clause of an UPDATE statement. They are also used in WHERE, HAVING, and FOR clauses. Input host variables can appear anywhere in an SQL statement where values or expressions are allowed.

EXEC SQL SELECT ename, empno INTO :name,:number FROM emp ORDER BY ename;
Copy the code

You cannot use input host variables to provide SQL keywords or names of database objects. Therefore, you cannot use input main variables in data definition statements such as ALTER, CREATE, and DROP. In the following example, the DROP TABLE statement is invalid:

char table_name[30]; 
 
printf("Table name? "); gets(table_name); EXEC SQL DROP TABLE :table_name; Host variables are not allowedCopy the code

Use dynamic SQL if you need to change the database object name at run time. Before Oracle executes an SQL statement containing the input main variables, the program must assign them values. Here’s an example:

int     emp_number; 
char    temp[20];
VARCHAR emp_name[20]; 

/* chong */ 
printf("Employee number? "); 
gets(temp);
emp_number = atoi(temp);
printf("Employee name? "); 
gets(emp_name.arr); 
emp_name.len = strlen(emp_name.arr); 
 
EXEC SQL INSERT INTO EMP (EMPNO, ENAME) 
    VALUES (:emp_number, :emp_name); 
Copy the code

Notice that the input host variable in the VALUES clause of the INSERT statement is prefixed with a colon.

Index variable syntax

Any host variable can be associated with an optional indicator variable. Each time a host variable is used in an SQL statement, the resulting code is stored in its associated indicator variable. Therefore, indicator variables can monitor host variables.

You can assign NULL to input host variables using indicator variables in VALUES or SET clauses. Use indicator variables in the INTO clause to detect NULL or truncated values in output host variables.

When the input

The values that your program can assign to indicator variables have the following meanings:

In the output

The values that Oracle can assign to indicator variables have the following meanings:

Remember that indicator variables must be defined as 2-byte integers and, in SQL statements, must be prefixed with a colon and must immediately follow their main variable.

Insert a NULL

You can use indicator variables to insert NULL. Before the INSERT, set the appropriate indicator variable to -1 for each column to be NULL, as shown in the following example:

set ind_comm = - 1; 
 
EXEC SQL INSERT INTO emp (empno, comm) 
     VALUES (:emp_number, :commission:ind_comm); 
Copy the code

The indicator variable IND_comm specifies that NULL will be stored in the COMM column.

NULL can be hardcoded as follows instead:

EXEC SQL INSERT INTO emp (empno, comm) 
     VALUES (:emp_number, NULL); 
Copy the code

While this is less flexible, it may be more readable. In general, NULL is inserted conditionally, as shown in the following example:

printf("Enter employee number or 0 if not available: "); 
scanf("%d", &emp_number); 
 
if (emp_number == 0) 
    ind_empnum = - 1; 
else 
    ind_empnum = 0; 
 
EXEC SQL INSERT INTO emp (empno, sal) 
     VALUES (:emp_number:ind_empnum, :salary); 
Copy the code

Returns NULL

You can also use indicator variables to manipulate NULL returns, as shown in the following example:

EXEC SQL SELECT ename, sal, comm 
    INTO :emp_name, :salary, :commission:ind_comm 
    FROM emp 
    WHERE empno = :emp_number; 
 if (ind_comm == - 1) 
    pay = salary;   /* commission is NULL; ignore it */ 
else 
    pay = salary + commission; 
Copy the code

Get a NULL

When DBMS=V7 or DBMS=V8, Oracle will issue the following error message if you SELECT or FETCH NULL into a primary variable that is independent of the indicator variable:

ORA- 01405.: fetched column value is NULL 
Copy the code

Test the NULL

NULL can be tested using indicator variables in the WHERE clause, as shown in the following example:

EXEC SQL SELECT ename, sal 
INTO :emp_name, :salary 
FROM emp 
WHERE :commission INDICATOR :ind_comm IS NULL.Copy the code

However, NULL cannot be compared with each other or with other values using relational operators. For example, if the COMM column contains one or more NULL, the following SELECT statement will fail:

EXEC SQL SELECT ename, sal 
INTO :emp_name, :salary 
FROM emp 
WHERE comm = :commission; 
Copy the code

The next example shows how to compare values for equality when some of them may be NULL:

EXEC SQL SELECT ename, sal 
     INTO :emp_name, :salary 
     FROM emp 
     WHERE (comm = :commission) OR ((comm IS NULL) AND 
          (:commission INDICATOR :ind_comm IS NULL)); 
Copy the code