Oracle variable binding

Use of ORACLE variable binding

In ORACLE, binding variables can reduce hard parsing and improve system performance (note that, normally, not in any situation ).

Take the table tabletest as an example to see how to bind a variable. The table structure of tabletest is as follows:

Field1 number (10)

Field2 number (10)

Field3 number (10)

Field4 number (10)

Field5 number (10)

Variable binding can be considered as a placeholder, for example:

Declare

I number;

J number;

Sqlstr varchar2 (200 );

Begin

I: = 1;

J: = 2;

Sqlstr: = 'insert into test table (field1, field2, field3, field4, field5) values (: X,: x,: y,: x,: x)';
Execute immediate sqlstr using I, I, j, I, I;

end;

In such a piece of code, I, I, j, I, correspond to: x,: x,: y,: x,: x. This code is correct, but if we assume that sqlstr contains only the bind variables: x,: y, If you change the statement execute immediate sqlstr using I, I, j, I; To execute immediate sqlstr using I, j;, the number of variables bound to the statement is insufficient.

After the preceding correct code is executed, the data inserted from field1 to field5 should be, 1.

If we change execute immediate sqlstr using I, I, j, I; To execute immediate sqlstr using I, j, j, I, I;, we can check the inserted records, the inserted records are 1, 2, 2, 1, and 1.

As shown above, Variable binding only acts as a placeholder, Bind variables with the same name does not mean they are the same During the transfer, you must consider the counterpoint of the sequence between the passed value and the bound variable, rather than the variable name.

The ORACLE system can bind variables, for example, the following code:

Declare

I number;

Begin

For I in 1 .. 1000 loop

Insert into test table ( I, I + 1, I * 1, I * 2, I-1)

End loop;

End;

This Code does not need to bind variables to improve efficiency. ORACLE automatically binds the variables.

We can understand this as follows: This code executes the insert into test table (I, I + 1, I * 1, I * 2, I-1) Statement 1000 times, each time the issued statement is the same.

If you change this code to the following:

Declare

I number;

Sqlstr varchar2 (200 );

Begin

For I in 1 .. 1000 loop

Sqlstr: = 'insert into test table ('| to_char (I) |', '| to_char (I) |' + 1, '| to_char (I) | '* 1,' | to_char (I) | '* 2,' | to_char (I) | '-1 )';

Execute immediate sqlstr;

End loop;

End;

This Code also executes 1000 insert statements, However, each statement is different. Therefore, ORACLE will hard resolve each statement once, which is much less efficient than the previous one.. To improve efficiency, You can bind the variable to change the statement in the loop

Sqlstr: = 'insert into test table (: I,: I + 1,: I * 1,: I * 2,: i-1)';

Execute immediate sqlstr using I, I;

The execution efficiency is much higher.

I tried to bind a variable to replace the table name, process name, and field name. The result is a statement error., The conclusion is that the bound variable cannot be used as an embedded string, but only as a variable in the statement.

From the perspective of efficiency, because oracle10G gave up RBO and introduced CBO in all aspects, the efficiency of using variable binding in 10g is more obvious than that in 9i.

Finally, we mentioned that variable binding can improve efficiency in general situations, What are the unusual situations?

The answer is: When fields (including field sets) are indexed and the set of fields (sets) has a very large potential (that is, there is a particularly large proportion of values in the field ),, Bind variables may cause query plan errors, which may result in low query efficiency.. In this case, it is best not to bind variables.