PLSQL Bind变量和Host变量有什么区别

问题描述

关于PLSQL，主机变量"和绑定变量"之间有什么区别?

With respect to PLSQL, what is the difference between a 'Host Variable' and a 'Bind Variable'?

此链接来自askTom的说法是"plsql的区别很模糊-它非常接近sql".那么那微小的模糊"区别是什么?

This link from askTom says that "the distinction blurs in plsql -- its very close to sql". Then what is that tiny, 'blurry' difference?

声明1:

SELECT 1 FROM dual WHERE dummy = :B1;

声明2:

SELECT 1 FROM dual WHERE dummy = v_var;

在这两个语句中，语句1代表绑定变量，语句2代表主机变量，对吗?

In these two statements, Statement 1 represents a bind variable and Statement 2 represents a Host variable, am I correct?

推荐答案

考虑以下C#代码段:

int    v_empno = 7369;
string v_ename;

OracleCommand cmd = con.CreateCommand();
cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, v_empno, ParameterDirection.Input);
cmd.CommandText = "select e.ename from scott.emp e where e.empno = :1";
v_ename = cmd.ExecuteScalar().ToString();

v_empno和v_ename是主机变量.在这里，您可以显式创建绑定变量，以在语句中用作:1.

v_empno and v_ename are host variables. Here you explicitly create your bind variable for use as :1 in your the statement.

考虑以下PL/SQL代码段:

Consider this snippet of PL/SQL:

declare
   v_empno  number := 7369;
   v_ename  varchar2(10);
begin
   select e.ename
     into v_ename
     from scott.emp e
    where e.empno = v_empno;
   dbms_output.put_line(v_ename);
end;
/

再次声明的变量v_empno和v_ename可以被认为是主机变量，但是当它们在PL/SQL代码中的静态SQL中使用时，它们会被PL/SQL编译器/引擎自动转换为绑定变量. -您不必像C#示例中那样手动创建绑定变量.如果您检查由这部分PL/SQL实际执行的SQL，它将看起来像这样:

Again the declared variables v_empno and v_ename can be considered host variables, but when they are used in static SQL within the PL/SQL code, they are automatically turned into bind variables by the PL/SQL compiler/engine - you do not have to manually create your bind variable like in the C# example. If you examine the SQL that is actually executed by this piece of PL/SQL, it will look something like this:

   select e.ename
     from scott.emp e
    where e.empno = :B1

这是为您的v_empno PL/SQL变量自动创建:B1绑定变量的PL/SQL编译器.这就是Tom Kyte的意思，即您实际上无法在PL/SQL中真正区分主机变量和绑定变量.当您编写PL/SQL时，在PL/SQL代码中使用时，变量是宿主变量，在嵌入式SQL代码中使用时，它们同时是绑定变量.您无需在PL/SQL中进行区分，编译器会为您解决这个问题.

That is the PL/SQL compiler that automatically has created :B1 bind variable for your v_empno PL/SQL variable. And that is what Tom Kyte means that you cannot really make a proper distinction between host variable and bind variable in PL/SQL. When you write PL/SQL the variables are host variables when used in PL/SQL code and at the same time they are bind variables when used in the embedded SQL code. You do not need to make a distinction in PL/SQL, the compiler takes care of it for you.

这篇关于PLSQL Bind变量和Host变量有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！