得到PLS-00382错误，尽管结构不不同

Question

由于某种原因，当试图编译触发器以更新StudentLastName时，我在第31行上得到上述错误，但它与我的其他IF块匹配相同的结构。有人知道是什么引起的吗?

--Alex Regimbald 040605887
CREATE OR REPLACE TRIGGER SCSP_VIEW_UPDATE
INSTEAD OF UPDATE ON SCSP_VIEW FOR EACH ROW
BEGIN
IF :OLD.SectionName <> :NEW.SectionName THEN
UPDATE SECTION_AR_5887 SET SectionName = :NEW.SectionName
WHERE SectionID = :OLD.SectionID;
END IF;
IF :OLD.CourseName <> :NEW.CourseName THEN
UPDATE COURSE_AR_5887 SET CourseName = :NEW.CourseName
WHERE CourseID = :OLD.CourseID;
END IF;
IF :OLD.ProfessorFirstName <> :NEW.ProfessorFirstName
AND :NEW.ProfessorFirstName <> NULL THEN
UPDATE PROFESSOR_AR_5887 SET ProfessorFirstName = :NEW.ProfessorFirstName
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
IF :OLD.ProfessorLastName <> :NEW.ProfessorLastName
AND :NEW.ProfessorLastName <> NULL THEN
UPDATE PROFESSOR_AR_5887 SET ProfessorLastName = :NEW.ProfessorLastName
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
IF :OLD.SectionName <> :NEW.SectionName THEN
UPDATE SECTION_AR_5887 SET SectionName = :NEW.SectionName
WHERE SectionID = :OLD.SectionID;
END IF;
IF :OLD.StudentFirstName <> :NEW.StudentFirstName THEN
UPDATE STUDENT_AR_5887 SET StudentFirstName = :NEW.StudentFirstName
WHERE StudentID = :OLD.StudentID;
END IF;
IF :OLD.StudentLastName <> :NEW.StudentLastName THEN
UPDATE STUDENT_AR_5887 SET StudentLastName = :NEW.StudentLastName
WHERE StudentID = :OLD.StudentID;
END IF;
IF :NEW.ProfessorLastName AND :NEW.ProfessorFirstName = NULL THEN
UPDATE SECTION_AR_5887 SET ProfessorID = NULL
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
END;

Answer 1

调整以下行以更正语法错误:

IF :NEW.ProfessorLastName IS NULL AND :NEW.ProfessorFirstName IS NULL THEN

其余的是与使用NULL相关的逻辑误解。

将其他NULL测试更改为使用IS NULL或IS NOT NULL。

像x = NULL和x <> NULL这样的测试永远不会成立。

相反,使用:

• x IS NULL
• x IS NOT NULL

另外，一些与NULL相关的测试是不必要的。例如:

IF :OLD.ProfessorFirstName <> :NEW.ProfessorFirstName
AND :NEW.ProfessorFirstName IS NOT NULL THEN

这个NULL测试是不必要的，因为如果:NEW.ProfessorFirstName为NULL，第一个表达式:OLD.ProfessorFirstName <> :NEW.ProfessorFirstName永远不会为真。所以这里的NULL测试是不必要的。

下面的语句足以说明变量/引用不是NULL:

IF :OLD.ProfessorFirstName <> :NEW.ProfessorFirstName

小提琴测试用例:

CREATE TABLE SCSP (
SectionID    varchar2(20)
, SectionName  varchar2(20)
, CourseID     varchar2(20)
, CourseName   varchar2(20)
, ProfessorID  varchar2(20)
, ProfessorFirstName varchar2(20)
, ProfessorLastName  varchar2(20)
, StudentID        varchar2(20)
, StudentFirstName varchar2(20)
, StudentLastName  varchar2(20)
);
CREATE OR REPLACE VIEW SCSP_VIEW AS
SELECT * FROM SCSP
;
CREATE TABLE SECTION_AR_5887 (
SectionID    varchar2(20)
, SectionName  varchar2(20)
, ProfessorID  varchar2(20)
);
CREATE TABLE COURSE_AR_5887 (
CourseID   varchar2(20)
, CourseName varchar2(20)
);
CREATE TABLE PROFESSOR_AR_5887 (
ProfessorID        varchar2(20)
, ProfessorFirstName varchar2(20)
, ProfessorLastName  varchar2(20)
);
CREATE TABLE STUDENT_AR_5887 (
StudentID        varchar2(20)
, StudentFirstName varchar2(20)
, StudentLastName  varchar2(20)
);
CREATE OR REPLACE TRIGGER SCSP_VIEW_UPDATE
INSTEAD OF UPDATE ON SCSP_VIEW FOR EACH ROW
BEGIN
IF :OLD.SectionName <> :NEW.SectionName THEN
UPDATE SECTION_AR_5887 SET SectionName = :NEW.SectionName
WHERE SectionID = :OLD.SectionID;
END IF;
IF :OLD.CourseName <> :NEW.CourseName THEN
UPDATE COURSE_AR_5887 SET CourseName = :NEW.CourseName
WHERE CourseID = :OLD.CourseID;
END IF;
IF :OLD.ProfessorFirstName <> :NEW.ProfessorFirstName THEN
UPDATE PROFESSOR_AR_5887 SET ProfessorFirstName = :NEW.ProfessorFirstName
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
IF :OLD.ProfessorLastName <> :NEW.ProfessorLastName THEN
UPDATE PROFESSOR_AR_5887 SET ProfessorLastName = :NEW.ProfessorLastName
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
IF :OLD.SectionName <> :NEW.SectionName THEN
UPDATE SECTION_AR_5887 SET SectionName = :NEW.SectionName
WHERE SectionID = :OLD.SectionID;
END IF;
IF :OLD.StudentFirstName <> :NEW.StudentFirstName THEN
UPDATE STUDENT_AR_5887 SET StudentFirstName = :NEW.StudentFirstName
WHERE StudentID = :OLD.StudentID;
END IF;
IF :OLD.StudentLastName <> :NEW.StudentLastName THEN
UPDATE STUDENT_AR_5887 SET StudentLastName = :NEW.StudentLastName
WHERE StudentID = :OLD.StudentID;
END IF;
IF :NEW.ProfessorLastName IS NULL AND :NEW.ProfessorFirstName IS NULL THEN
UPDATE SECTION_AR_5887 SET ProfessorID = NULL
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
END;
/