时间戳

首页 > 解决方案 > Oracle 触发器错误：错误的绑定变量“新”

sql - Oracle 触发器错误：错误的绑定变量“新”

问题描述

我正在尝试创建一个触发器来检查容量、2 个函数的输入get_max_capacity并get_current_capacity具有 OBJECT_U5 类型（我的代码如下）

CREATE OR REPLACE TYPE object_u5 AS OBJECT (
        student_id  VARCHAR2(1000),
        course_id   VARCHAR2(1000),
        sec_id      VARCHAR2(1000),
        semester    VARCHAR2(1000),
        year        NUMBER(4,0),
        grade       VARCHAR2(3)
);
CREATE OR REPLACE FUNCTION get_current_capacity (
        course_register IN object_u5
) RETURN NUMBER IS
        current_capacity NUMBER(38);
BEGIN
        SELECT
                COUNT(*)
        INTO current_capacity
        FROM
                takes
        WHERE
                        course_id = course_register.course_id
                AND sec_id = course_register.sec_id
                AND year = course_register.year
                AND semester = course_register.semester;
END;
CREATE OR REPLACE TRIGGER check_capacity BEFORE
        INSERT ON takes
        FOR EACH ROW
ENABLE DECLARE
        max_capacity  NUMBER(4, 0);
        current_capacity  NUMBER(4, 0);
BEGIN
        max_capacity := get_max_capacity(:new);
        current_capacity := get_current_capacity(:new);
        IF ( max_capacity > cur_capacity ) THEN
                dbms_output.put_line('Insert successfully');
        ELSE
                dbms_output.put_line('The classroom is full! Choose the others');
        END IF;
END;

返回的 2 个错误：PLS-00049: bad bind variable 'NEW'

标签： sqloracleplsqltriggers

解决方案

确切地; :new什么？应该是例如

    current_capacity := get_current_capacity
          (object_u5(:new.student_id, 
                     :new.course_id, 
                     :new.sec_id,
                     :new.semester, 
                     :new.year, 
                     :new.grade));

修复后，触发器编译：

SQL> CREATE OR REPLACE TRIGGER check_capacity BEFORE
  2          INSERT ON takes
  3          FOR EACH ROW
  4  ENABLE DECLARE
  5          max_capacity  NUMBER(4, 0);
  6          current_capacity  NUMBER(4, 0);
  7  BEGIN
  8          max_capacity :=
  9            get_max_capacity(object_u5(:new.student_id,
 10                                       :new.course_id,
 11                                       :new.sec_id,
 12                                       :new.semester,
 13                                       :new.year,
 14                                       :new.grade));
 15          current_capacity :=
 16            get_current_capacity(object_u5(:new.student_id,
 17                                       :new.course_id,
 18                                       :new.sec_id,
 19                                       :new.semester,
 20                                       :new.year,
 21                                       :new.grade));
 22
 23          IF ( max_capacity > current_capacity ) THEN
 24                  dbms_output.put_line('Insert successfully');
 25          ELSE
 26                  dbms_output.put_line('The classroom is full! Choose the others');
 27          END IF;
 28  END;
 29  /

Trigger created.

SQL>

推荐阅读