mysql - 存储过程问题，如果为 NULL，则不包括变量

Question

需要一些关于 MYSQL 存储过程脚本的建议。该过程从一年和一个学期复制数据并将其粘贴到另一个，并在中间使用来自yearsemestersectionaffix 表（yssa）的新年和学期的FK 更新YearSemester 外键。

我认为，问题“似乎”与以下两个陈述之一有关，或者两者兼而有之。

以下语句是初始临时表构建的一部分，如果表单中提供了学期，则在其中选择要复制的学期（行）。如果不包括一个学期（NULL），那么它应该只复制当年的所有内容

/*  Where the year matches the input year, and if not null the semester matches the semester  */
WHERE sy.year = FromYear
    AND (sy.semesterID = copySemester OR copySemester IS NULL);

或者在这篇文章中，在 UPDATE 语句中，YSSA 表中的 sectionAffixID 被匹配为第三个匹配列（前两个是 YEAR 和 SEMESTERID）

AND sectionAffixID = (SELECT sectionAffixID FROM sched_YSSA AS sy3 WHERE sy3.ID = sched_courses_copy.yearSemesterID));

问题是有些年份和学期没有包括部分词缀，主要是夏天。

    DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Copy_CoursesAndComponents`(IN `FromYear` INT(4), IN `ToYear` INT(4), IN `copySemester` INT(1), IN `copyRoom` BOOLEAN, IN `copyInstructors` BOOLEAN, IN `copyComments` BOOLEAN, IN `StatusClass` INT(1))
    NO SQL
    SQL SECURITY INVOKER
BEGIN
/*  Drops temp table if it exists  */
DROP TABLE IF EXISTS Sched_courses_copy;
/*  Creates temp table anew  */
CREATE TABLE sched_courses_copy
/*  Selects for temp table values to be copied from the YEAR and SEMESTER selected */
SELECT sc.yearSemesterID, 
sc.courseID, sc.sectionID, sc.sectionNumber,
case copyRoom
        when '1' then sc.roomID
        when '0' then NULL
    end as RoomID,
case copyInstructors
        when '1' then sc.primaryInstructor_ID
        when '0' then NULL
    end as primaryInstructor_ID,
case copyInstructors
        when '1' then sc.secondaryInstructor_ID
        when '0' then NULL
    end as secondaryInstructor_ID,
NULL AS numberOfStudents,
case copyComments
        when '1' then sc.comment
        when '0' then NULL
    end as Comment,
sc.class_status_classStatusID
FROM sched_course AS sc INNER JOIN sched_yssa AS sy ON sy.ID = sc.yearSemesterID
/*  Where the year matches the input year, and if not null the semester matches the semester  */
WHERE sy.year = FromYear
    AND (sy.semesterID = copySemester OR copySemester IS NULL);
    

/*  !!NEED UPDATE statement here to change YearSemesterID FOR EACH ROW to the proper matching FK from YSSA table  */
UPDATE Sched_courses_copy
SET Sched_courses_copy.yearSemesterID = (SELECT sy.ID FROM Sched_Yssa AS sy WHERE year = ToYear AND semesterID = (SELECT semesterID FROM sched_YSSA AS sy2 WHERE sy2.ID = sched_courses_copy.yearSemesterID) AND sectionAffixID = (SELECT sectionAffixID FROM sched_YSSA AS sy3 WHERE sy3.ID = sched_courses_copy.yearSemesterID));


/*  Deletes duplicates from the dataset before inserting them  */
DELETE scc
FROM Sched_courses_copy AS scc, Sched_course AS sc
WHERE 
    scc.yearSemesterID = sc.yearSemesterID
    AND scc.courseID = sc.courseID
    AND scc.sectionID = sc.sectionID
    AND scc.sectionNumber = sc.sectionNumber;

/* Inserts new data into the sched course table   */
INSERT INTO Sched_course (yearSemesterID,courseID,sectionID,sectionNumber,roomID,primaryInstructor_ID,secondaryInstructor_ID,numberOfStudents,comment,class_status_classStatusID)

SELECT yearSemesterID,courseID,sectionID,sectionNumber,roomID,primaryInstructor_ID,secondaryInstructor_ID,numberOfStudents,comment,class_status_classStatusID
FROM Sched_courses_copy AS scc;

/* DROPS temp table   */
DROP TABLE Sched_courses_copy; 

DROP TABLE IF EXISTS sched_components_copy;

CREATE TABLE sched_components_copy

SELECT *
FROM (SELECT t1.classID AS oldclassID, t1.year AS oldyear, t1.semesterID AS oldSemesterID, t1.sectionAffixID AS oldsectionAffixID, t1.yearSemesterID AS oldyearSemesterID, t1.courseID AS oldcourseID, t1.sectionID AS oldsectionID, t1.sectionNumber as oldsectionNumber, t2.classID AS newclassID, t2.year AS newyear, t2.semesterID AS newsemesterID, t2.sectionAffixID AS newsectionAffixID, t2.yearSemesterID AS newyearSemesterID, t2.courseID AS newcourseID, t2.sectionID as newsectionID, t2.sectionNumber AS newsectionNumber

FROM (SELECT * FROM sched_course AS sc INNER JOIN sched_YSSA AS sy ON sc.yearSemesterID = sy.ID WHERE sy.year = FromYear AND sy.SemesterID = copySemester) AS t1 JOIN (SELECT * FROM sched_course AS sc2 INNER JOIN sched_YSSA AS sy2 ON sc2.yearSemesterID = sy2.ID WHERE sy2.year = ToYear AND sy2.SemesterID = copySemester) AS t2 ON t1.semesterID = t2.semesterID AND t1.sectionAffixID = t2.sectionAffixID AND t1.courseID = t2.courseID AND t1.sectionID = t2.sectionID AND t1.sectionNumber = t2.sectionNumber) AS t3 INNER JOIN sched_components ON oldclassID = sched_Components.classID;


INSERT INTO Sched_components (classID, ComponentID, dayOfWeekID, startTime, endTime, totalHrs)
SELECT newclassID, ComponentID, dayOfWeekID, startTime, endTime, totalHrs
FROM Sched_components_copy;

DROP TABLE Sched_components_copy;

END$$
DELIMITER ;

标签： mysqlstored-proceduresinsert-update