Как использовать переменную связывания в plsql?

Простая задача

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 не равно NULL. Почему после присваивания dept_id имеет значение NULL? Что я делаю не так?

вывод сценария:

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;

Отчет об ошибке:

ORA-01400: невозможно вставить NULL в ("ANDKOM"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: на  line 9
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:    
*Action:
0
задан Sathyajith Bhat 21 November 2011 в 07:40
поделиться