使用绑定变量 [英] use of bind variable

问题描述

我们可以在Oracle的过程或函数中使用绑定变量吗?

Can we use a bind variable in oracle inside a procedure or function ?

我正在尝试更新过程中的绑定变量.在任何情况下都可以这样做吗?

I'm trying to update a bind variable inside my procedure. Can I do so in any case?

if (condition) then
    :v_bind:=10;
end if;

我可以在过程或函数中执行上述操作吗??

Can I do the above thing inside a procedure or function..?

variable v_bind number; 
create procedure abc as v_one 
BEGIN 
  select count(a) into v_one from ab; 
  if(v_one<>0) then 
     :v_bind:=10; 
  end if; 

我可以做到吗?它向我显示了错误的变量v_bind

Will I able to do this? It is showing me bad variable v_bind

推荐答案

您无法创建其中包含绑定变量的过程，因为存储过程是服务器端对象，并且绑定变量仅存在于客户端.

You can't create a procedure with a bind variable in it because stored procedures are server-side objects and bind variables only exist on the client side.

假设我正在使用SQL * Plus，并且已经创建了一些绑定变量.一旦退出SQL * Plus，我创建的任何绑定变量都将不复存在.但是，存储过程必须保留在数据库中，因此它们无法引用在客户端上创建然后销毁的任何内容.

Suppose I'm using SQL*Plus, and that I've created some bind variables. Once I exit SQL*Plus, any bind variables I created don't exist any more. However, stored procedures have to persist in the database, and hence they can't have any reference to anything that was created and then destroyed on the client.

下面的示例显示您无法创建引用绑定变量的过程:

Here's an example showing that you can't create a procedure that references a bind variable:

SQL> variable i number
SQL> exec :i := 0;    

PL/SQL procedure successfully completed.

SQL> print :i

         I
----------
         0

SQL> create or replace procedure test_proc
  2  as
  3  begin
  4    :i := 9;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors procedure test_proc;
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PLS-00049: bad bind variable 'I'

但是，您可以将绑定变量作为过程的OUT参数传递.然后，该过程可以为OUT参数分配一个值，然后将该值存储在绑定变量中.

You can, however, pass a bind variable as an OUT parameter for a procedure. The procedure can then assign a value to the OUT parameter, and this value will then be stored in your bind variable.

假设我们执行以下过程:

Suppose we have the following procedure:

CREATE OR REPLACE PROCEDURE do_stuff (
  p_output    OUT INTEGER
)
AS
BEGIN
  p_output := 6;
END;

我们可以使用它来设置绑定变量，如下所示:

We can use this to set a bind variable as follows:

SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL> print :i

         I
----------
         0

SQL> exec do_stuff(:i);

PL/SQL procedure successfully completed.

SQL> print :i

         I
----------
         6

这篇关于使用绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！