游标中的 PL/SQL 变量是否与绑定参数有效相同?

2021-12-24 00:00:00 oracle parameterbinding plsql cursor bind-variables

我听说使用绑定变量(可以)更有效，因为对于具有不同绑定值的后续调用，查询本身仍然相同，因此不再需要对其进行解析.

I've heard that using bind variables is (can be) more efficient, because for subsequent calls with a different bind value, the query itself is still the same, so it doesn't need to be parsed anymore.

我理解为什么固定值会出现这种情况.在下面的游标中，该值固定为 1.如果我有一个相同的不同游标，除了 1 变为 2，这是一个不同的查询.到目前为止清除.

I understand why this is the case for fixed values. In the cursor below, the value is fixed on 1. If I have a different cursor that is the same, except the 1 becomes 2, it is a diffent query. Clear so far.

declare
  cursor C_CURSOR is 
    select * from TESTTABLE pt where pt.ID = 1;

但我想知道在游标内使用 PL/SQL 变量时是否也是这种情况.它们是否被扩展为一个固定值，或者它们是否被解释为绑定变量.

But I wondered if this is also the case when using PL/SQL variables inside the cursor. Are they expanded as if it's a fixed value, or are they interpreted as bind variables.

我进行了广泛的搜索，但到处都可以找到关于文字的示例，就像上面的例子一样，但没有明确解释 PL/SQL 变量的使用.

I've searched far and wide, but everywhere I find examples about literals, as in the case above, but no clear explanation about the use of PL/SQL variables.

换句话说，在下面的两个片段中，第二个可能更有效，还是本质上相同?

In other words, of the two snippets below, is the second one potentially more efficient, or are they essentially the same?

直接在游标中使用 PL/SQL 变量:

With a PL/SQL variable directly in the cursor:

declare
  V_TEST integer := 1;

  cursor C_CURSOR is 
    select * 
    from 
      TESTTABLE pt
    where
      pt.ID = V_TEST;

begin
  for r in C_CURSOR loop
    null;
  end loop;
end;

使用绑定变量:

declare
  V_TEST int := 1;

  cursor C_CURSOR(B_TEST int) is 
    select * 
    from 
      TESTTABLE pt
    where
      pt.ID = B_TEST;

begin
  for r in C_CURSOR(V_TEST) loop
    null;
  end loop;
end;

推荐答案

首先，好问题.

我想引用一下:

对 PL/SQL 变量的每个引用实际上都是一个绑定变量.

Every reference to a PL/SQL variable is in fact a bind variable.

话虽如此，

PL/SQL 本身处理了与绑定变量有关的大部分问题，以至于您编写的大多数代码已经在您不知情的情况下使用了绑定变量.以下面的 PL/SQL 代码为例:

PL/SQL itself takes care of most of the issues to do with bind variables, to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL:

create or replace procedure dsal(p_empno in number)
as
  begin
    update emp
    set sal=sal*2
    where empno = p_empno;
    commit;
  end;
/

现在您可能认为必须用绑定变量替换 p_empno.然而，好消息是每个对 PL/SQL 变量的引用实际上都是一个绑定变量.

Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.

来源

相关文章