oracle 绑定变量的使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5209981/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
use of bind variable
提问by jasmeet
Can we use a bind variable in oracle inside a procedure or function ?
我们可以在 oracle 中的过程或函数中使用绑定变量吗？
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;
Will I able to do this? It is showing me bad variable v_bind
我能做到吗？它向我展示了坏变量v_bind
回答by Luke Woodward
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.
您不能创建带有绑定变量的过程，因为存储过程是服务器端对象，而绑定变量仅存在于客户端。
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.
假设我正在使用 SQL*Plus，并且我已经创建了一些绑定变量。一旦我退出 SQL*Plus，我创建的任何绑定变量就不再存在。但是，存储过程必须保留在数据库中，因此它们不能引用在客户端上创建然后销毁的任何内容。
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'
You can, however, pass a bind variable as an OUTparameter for a procedure. The procedure can then assign a value to the OUTparameter, and this value will then be stored in your bind variable.
但是，您可以将绑定变量作为过程的OUT参数传递。然后该过程可以为该OUT参数分配一个值，然后该值将存储在您的绑定变量中。
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
回答by CoderSteve
No, you cannot do what you are asking. Bind variables in plsql are handled transparently. You do not explicitly code bind variables unless you are going to use 'execute immediate' to run the code outside of plsql like this:
不，你不能做你所要求的。plsql 中的绑定变量是透明处理的。除非您打算使用“立即执行”在 plsql 之外运行代码，否则您不会显式编码绑定变量，如下所示：
declare
v_bind number := 1;
begin
execute immediate 'select * from table where x = :v_bind';
end;`
The following code uses bind variables as well, but it is handled transparently by plsql:
以下代码也使用绑定变量，但由 plsql 透明处理：
declare
v_bind number := 1
y number;
begin
select count(*) into y from table where x = v_bind;
end;
回答by Rohan Sadale
You can't bind a sqlplus variable in a session to a function/procedure. It will give you error of "Bad bind variable". You can actually just pass bind variable from your oracle session to any procedure.
您不能将会话中的 sqlplus 变量绑定到函数/过程。它会给你“错误的绑定变量”的错误。您实际上可以将绑定变量从您的 oracle 会话传递到任何过程。
Let's see a example
让我们看一个例子
variable v1 NUMBER;
begin
select salary into :v1 from employees where employee_id = 100;
dbms_output.put_line(:v1);
end;
/
And if you run the above example by enclosing in procedure/function it will show you error.
如果您通过包含在过程/函数中来运行上面的示例，它将显示错误。
create or replace procedure proc is
begin
select salary into :v1 from employees where employee_id = 100;
dbms_output.put_line(:v1);
end;
/
Error -
错误 -
PROCEDURE proc compiled
Warning: execution completed with warning
3/20 PLS-00049: bad bind variable 'V1'
4/22 PLS-00049: bad bind variable 'V1'
Thus, it is not possible to use session-level bind variables in procedures/functions. In below example t2 is a bind variable
因此，不可能在过程/函数中使用会话级绑定变量。在下面的例子中 t2 是一个绑定变量
create or replace procedure proc is
t2 NUMBER;
begin
select salary into t2 from employees where employee_id = 100;
dbms_output.put_line(t2);
end;
/
You can call this procedure from sqlplus as
你可以从 sqlplus 调用这个过程作为
exec proc;
