Oracle 绑定变量给出错误
SET SERVEROUTPUT ON
VARIABLE dept_id NUMBER
DECLARE
max_deptno NUMBER(3);
dept_name departments.department_name%TYPE :='Education';
BEGIN
SELECT MAX(department_id)
INTO max_deptno
FROM departments;
DBMS_OUTPUT.PUT_LINE ('The maximum department no is : ' || max_deptno);
:dept_id:=(max_deptno+10);
INSERT INTO departments (department_name, department_id,location_id)
VALUES(dept_name, :dept_id, NULL);
DBMS_OUTPUT.PUT_LINE ('The number of rows affected : ' || SQL%ROWCOUNT);
END;
/
错误报告： ORA-01400: 无法将 NULL 插入 ("SYSTEM"."DEPARTMENTS"."DEPARTMENT_ID") ORA-06512: 在第 10 行 01400. 00000 - “无法将 NULL 插入 (%s)” *原因：
*行动： 最大部门号是：190
我在尝试执行 oracle 语句中的绑定变量时收到此错误。但是如果我输入一些值而不是绑定变量，我就会得到正确的插入语句。我在这里做错了什么？
SET SERVEROUTPUT ON
VARIABLE dept_id NUMBER
DECLARE
max_deptno NUMBER(3);
dept_name departments.department_name%TYPE :='Education';
BEGIN
SELECT MAX(department_id)
INTO max_deptno
FROM departments;
DBMS_OUTPUT.PUT_LINE ('The maximum department no is : ' || max_deptno);
:dept_id:=(max_deptno+10);
INSERT INTO departments (department_name, department_id,location_id)
VALUES(dept_name, :dept_id, NULL);
DBMS_OUTPUT.PUT_LINE ('The number of rows affected : ' || SQL%ROWCOUNT);
END;
/
Error report:
ORA-01400: cannot insert NULL into ("SYSTEM"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 10
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:
*Action:
The maximum department no is : 190
I am getting this error while trying to execute the bind variable in oracle statment. But if i put some value instead of bind variable, i get this insert statement right. What am I doing wrong here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为绑定变量的值仅在 pl/sql 块完成时设置。它可能必须正常终止。
一种解决方案是在插入中使用
max_deptno+10来代替:dept_if。更好的解决方案是创建另一个 pl/sql 变量并在插入语句中使用它。您还必须更改 INSERT 语句：
I think the value of the bind variable is only set when the pl/sql block is finished. And it probably has to terminate normally.
One solution is to use
max_deptno+10in the insert insead of:dept_if. A better solution is to create another pl/sql variable and use that in the insert statement.You also have to change the INSERT statement:
我认为出现此错误是因为您使用绑定变量而没有在启动程序中使用
set autoprint on。I think this error is obtained because you use bind variable without using
set autoprint onin start program.