idataparameter oracle,Oracle绑定变量在C#.NET中的应用及意义（The application and significance of Oracle binding va...

Oracle绑定变量在C#.NET中的应用及意义(The application and significance of Oracle binding variables in c #.net)

Oracle绑定变量在C#.NET中的应用及意义(The application and significance of Oracle binding variables in c #.net)

One, what is a binding variable

Bind variable:

Select * from emp where empno = : empno;

Is a placeholder in the user in the query, it will tell Oracle "I will then provide a value for this variable, we need to generate a plan now, but when I actually execute the statement, will provide the real value of" should be used.

The essence is to replace the substitution variables for constants in SQL statements. The binding variable can make the SQL statements that are submitted every time exactly the same.

Why use binding variables

Using binding variables can reduce hard parsing and optimize the use of Shared pools. In oracle, there are two optional parsing processes for a committed SQL statement, one called hard parsing and one called soft parsing.

When a SQL statement submitted, oracle will first check the Shared buffer pool have exactly the same with the statement, if there is one soft analysis can do only, or hard analysis.

A hard parsing requires many steps such as analysis, parsing, security checks, execution paths, optimal access plans, and so on. It consumes a lot of CPU and resources.

For example, to query children numbered 001, the following two methods can be implemented:

Select * from t_child where childid = '001'; / / no binding variables are used

Select * from t_child where childid = : childid / / use binding variables

However, in practice, it is often a child who has the number of 001, and may never use it again; Then you can query child '002', then query '003' and so on. So every time the query is a new query, it needs to be hard parsed;

While the second query provides the bind variables: childid, provided its value at query execution time, query after once compiled, query plan is stored in the Shared pool, which can be used to retrieve and reuse; In terms of performance and scalability, the difference between the two is huge, even surprising;

If the binding va