oracle 如何在plsql中使用绑定变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8190015/
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 use bind variable in plsql?
提问by bluebyte
Simple task
简单的任务
variable dept_id NUMBER
DECLARE
max_deptno NUMBER;
dept_name departments.department_name%TYPE := 'Education';
BEGIN
select max(department_id)
into max_deptno
from departments;
:dept_id := max_deptno + 10;
insert into departments (department_id, department_name, location_id)
values (:dept_id, dept_name, null);
DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
END;
max_deptno is not NULL. Why dept_id is NULL after assignment? What am I doing wrong?
max_deptno 不是 NULL。为什么赋值后 dept_id 为 NULL？我究竟做错了什么？
script output:
脚本输出：
MAX(DEPARTMENT_ID)
------------------
520
Error starting at line 10 in command:
DECLARE
max_deptno NUMBER;
dept_name departments.department_name%TYPE := 'Education1';
BEGIN
select max(department_id)
into max_deptno
from departments;
:dept_id := max_deptno + 10;
insert into departments (department_id, department_name, location_id)
values (:dept_id, dept_name, null);
DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
END;
Error report:
错误报告：
ORA-01400: невозможно вставить NULL в ("ANDKOM"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: на line 9
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:
*Action:
回答by Justin Cave
The host variable DEPT_IDis not NULL after the assignment. The problem is that your INSERTstatement is using the local variable NEW_DPTin the INSERTand that local variable has not been assigned a value. If you really want to use a host variable in PL/SQL (you would essentially never do this in real life) you would need to use that in your PL/SQL block
DEPT_ID赋值后宿主变量不为NULL。问题是您的INSERT语句正在使用中的局部变量NEW_DPT，INSERT而该局部变量尚未赋值。如果你真的想在 PL/SQL 中使用一个宿主变量（你在现实生活中基本上永远不会这样做）你需要在你的 PL/SQL 块中使用它
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 max_deptno NUMBER;
3 new_dpt NUMBER;
4 dept_name departments.department_name%TYPE := 'Education';
5 BEGIN
6 select max(department_id)
7 into max_deptno
8 from departments;
9 :dept_id := max_deptno + 10;
10 insert into departments (department_id, department_name, location_id)
11 values (:dept_id, dept_name, null);
12 DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
13 DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
14* END;
SQL> /
The maximum department id is 270
Rows made by insert: 1
PL/SQL procedure successfully completed.
SQL> select :dept_id from dual;
:DEPT_ID
----------
280
More realistic, however, would be to ditch the substitution variable entirely
然而，更现实的是完全抛弃替代变量
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 max_deptno NUMBER;
3 new_dpt NUMBER;
4 dept_name departments.department_name%TYPE := 'Education';
5 BEGIN
6 select max(department_id)
7 into max_deptno
8 from departments;
9 new_dpt := max_deptno + 10;
10 insert into departments (department_id, department_name, location_id)
11 values (new_dpt, dept_name, null);
12 DBMS_OUTPUT.PUT_LINE('The maximum department id is ' || max_deptno);
13 DBMS_OUTPUT.PUT_LINE('Rows made by insert: ' || SQL%ROWCOUNT);
14* END;
SQL> /
The maximum department id is 280
Rows made by insert: 1
PL/SQL procedure successfully completed.
回答by bluebyte
I've asked the same question in oracle forum and got the answer:
我在oracle论坛上问过同样的问题并得到了答案：
Hi,
This is a known issue and is dependent on a JDBC bug which has never been resolved: https://forums.oracle.com/forums/thread.jspa?messageID=9456891�
If your specific case does not lend itself to setting a bind variable in one anonymous block and reading it in another, then the only workaround (which is also considered good coding practice) is to replace the SQL*Plus variable with a PL/SQL variable.
Regards, Gary SQL Developer Team
你好，
这是一个已知问题，并且依赖于从未解决的 JDBC 错误：https: //forums.oracle.com/forums/thread.jspa?messageID=9456891䳻
如果您的具体情况不适合在一个匿名块中设置绑定变量并在另一个块中读取它，那么唯一的解决方法（这也被认为是良好的编码实践）是用 PL/SQL 变量替换 SQL*Plus 变量.
问候， Gary SQL 开发团队
