oracle 如何解决 SP2-0552：未声明绑定变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36114265/
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
How to solve SP2-0552: Bind variable not declared?
提问by Ionut
When I run this PL/SQL block:
当我运行这个 PL/SQL 块时：
SQL> set serveroutput on
SQL> declare
2 v_max_sal NUMBER(20)
3 begin
4 select max(sal) INTO :v_max_sal
5 from emp e, dept d
6 where e.deptno=d.deptno
7 and d.dname='SALES';
8 END;
9 /
it throws to me the next error: SP2-0552: Bind variable "V_MAX_SAL" not declared.What am I missing or doing wrong?
它向我抛出下一个错误：SP2-0552: Bind variable "V_MAX_SAL" not declared.我错过了什么或做错了什么？
回答by Alex Poole
If you definitely want a bind variable then you need to declare it outsidethe block:
如果你肯定想要一个绑定变量，那么你需要在块外声明它：
variable v_max_sal number;
begin
select max(sal)
into :v_max_sal
from dept d
join emp e
on e.deptno=d.deptno
where d.dname='SALES';
end;
/
print v_max_sal
Notice the SQL*Plus client variableand printcommands, and that there is no longer a declaresection in the block, as you don't now have or need a local PL/SQL variable. A local variable can act as a bind variable when it's used in a query - the parser sees it like that, and you'll see a placeholder in the query's plan - but it's not quite the same thing, as you usually want the bind variable to be referencable outside the Pl/SQL code.
请注意SQL*Plus 客户端variable和print命令，并且declare块中不再有一个部分，因为您现在没有或不需要本地 PL/SQL 变量。局部变量在查询中使用时可以充当绑定变量 - 解析器会这样看待它，并且您将在查询计划中看到占位符 - 但它并不完全相同，因为您通常需要绑定变量可在 PL/SQL 代码之外引用。
I've also used modern join syntax, though that isn't relevant to the problem.
我还使用了现代连接语法，尽管这与问题无关。
回答by Marmite Bomber
Note also, that a typical usage of bind variables covers the literal string in the WHERE predicate, so you may additionally replace the department name with it (to be able use the same query for all departments).
另请注意，绑定变量的典型用法涵盖 WHERE 谓词中的文字字符串，因此您可以另外用它替换部门名称（以便能够对所有部门使用相同的查询）。
VARIABLE v_max_sal NUMBER;
VARIABLE v_dname VARCHAR2(14);
begin
:v_dname := 'SALES';
select max(sal) INTO :v_max_sal
from scott.emp e, scott.dept d
where e.deptno=d.deptno
and d.dname = :v_dname;
END;
/
print v_max_sal;
You may also simple remove the colon before the variable and it will work too:
您也可以简单地删除变量前的冒号，它也可以工作：
SQL> set serveroutput on
SQL> declare
2 v_max_sal NUMBER(20);
3 begin
4 select max(sal) INTO v_max_sal
5 from scott.emp e, scott.dept d
6 where e.deptno=d.deptno
7 and d.dname='SALES';
8 dbms_output.put_line(v_max_sal);
9 END;
10 /
2850
Note that this variant and the solution proposed by @Alex lead to an identical query in the database:
请注意，此变体和@Alex 提出的解决方案导致数据库中出现相同的查询：
SELECT MAX(SAL) FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='SALES'
So in the database no bind variable is used.
所以在数据库中没有使用绑定变量。
The extended option in the begin of this answer leads to a query with a BV in the WHERE clause:
此答案开头的扩展选项导致查询在 WHERE 子句中带有 BV：
SELECT MAX(SAL) FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME = :B1
回答by GLRoman
Lose the :in front of v_max_salon your select ... intostatement:
失去了:前面v_max_sal上的select ... into发言：
SQL> set serveroutput on
SQL> declare
2 v_max_sal NUMBER(20)
3 begin
4 select max(sal) INTO v_max_sal
5 from emp e, dept d
6 where e.deptno=d.deptno
7 and d.dname='SALES';
8 END;
9 /
