游标中的PL / SQL变量与绑定参数是否有效？ [英] Are PL/SQL variables in cursors effectively the same as bind parameters?

问题描述

我听说使用绑定变量是（可以）更有效率，因为对于具有不同绑定值的后续调用，查询本身仍然相同，因此不需要再解析。

我明白为什么固定值的情况。在下面的游标中，值固定为1.如果我有一个不同的游标是相同的，除了1变成2，它是一个不同的查询。清除到目前为止。

 声明
游标C_CURSOR是
 select * from TESTTABLE pt其中pt.ID = 1; 
  

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

我搜索的范围很广，但无处不在，我在上面的例子中找到了文字，使用PL / SQL变量。

换句话说，下面两个片段中，第二个可能更有效，或者它们本质上是相同的吗？

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

  declare 
 V_TEST integer：= 1; 
 
光标C_CURSOR是
 select * 
从
 TESTTABLE pt 
其中
 pt.ID = V_TEST; 
 
开始
在C_CURSOR循环中的r 
 null; 
 end loop; 
 end; 
  

使用绑定变量：

  declare 
 V_TEST int：= 1; 
 
光标C_CURSOR（B_TEST int）是
 select * 
从
 TESTTABLE pt 
其中
 pt.ID = B_TEST; 
 
开始
 for C在C_CURSOR（V_TEST）循环
 null; 
 end loop; 
 end; 
  

解决方案

首先，好问题。

我想提一个小报价：

每次引用PL / SQL变量

这样说，

strong> PL / SQL本身处理大多数与绑定变量有关的问题，直到你编写的大多数代码已经使用绑定变量，而你不知道。例如，以下位PL / SQL：

 创建或替换过程dsal（p_empno数）
 as 
 begin 
 update emp 
 set sal = sal * 2 
其中empno = p_empno; 
 commit; 
 end; 
 / 
  

现在你可能会认为你必须将p_empno替换为绑定变量。但是，好消息是，每个对PL / SQL变量的引用实际上是一个绑定变量。

来源

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.

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;

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.

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?

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;

With a bind variable:

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;

解决方案

First of all, good question.

I would like to make a small quote:

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

Having said that,

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;
/

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.

Source

这篇关于游标中的PL / SQL变量与绑定参数是否有效？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！