mysql - 无法获取 `` `IF ``` 语句来检查记录并提供错误

Question

我还不能使用图片，因为我没有足够的声誉，所以我只能输入创建表代码

CREATE TABLE course (
    `code` CHAR(3) NOT NULL,
    `name` VARCHAR(30) NOT NULL,
    credits TINYINT NOT NULL,
    CONSTRAINT credit_check CHECK (credits IN (50 , 75, 100)),
    CONSTRAINT pri_course PRIMARY KEY (`code`),
    CONSTRAINT uni_code UNIQUE (`name`)
);

CREATE TABLE module (
    `code` CHAR(2) NOT NULL,
    `name` VARCHAR(30) NOT NULL,
    cost DECIMAL(8 , 2 ) NOT NULL DEFAULT 0.0,
    credits TINYINT NOT NULL,
    course_code CHAR(3) NOT NULL,
    CONSTRAINT uni_name UNIQUE (`name`),
    CONSTRAINT pri_module PRIMARY KEY (`code`),
    CONSTRAINT for_module FOREIGN KEY (course_code)
        REFERENCES course (`code`)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT cred_check CHECK (credits IN (25 , 50))
);

INSERT INTO course (`code`, `name`, credits)
VALUES ('WSD', 'Web Systems & Development', 75);
INSERT INTO course (`code`, `name`, credits)  
    VALUES ('DDM', 'Database Design & Managment', 100);
INSERT INTO course (`code`, `name`, credits)
VALUES ('NSF', 'Network Security & Forensics', 75);

INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('A2', 'ASP.NET', 250, 25, 'WSD');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('A3', 'PHP', 250, 25, 'WSD');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('A4', 'JavaFX', 350, 25, 'WSD');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('B2', 'Oracle', 750, 50, 'DDM');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('B3', 'SQLS', 750, 50, 'DDM');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('C2', 'Law', 250, 25, 'NSF');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('C3', 'Forensics', 350, 25, 'NSF');
INSERT INTO module (`code`, `name`, cost, credits, course_code)
VALUES ('C4', 'Networks', 250, 25, 'NSF');

上面是创建表代码并插入我想要运行 IF 语句的 IF 语句将位于一个需要两个输入的过程中如果他们通过了声明，他们将被插入，输入将安排新的类(class)日期。我将把我的代码放在这里......

DELIMITER $$

CREATE PROCEDURE new_course (IN give_code CHAR(3), give_date DATE)
BEGIN
DECLARE com_mod BOOLEAN DEFAULT FALSE;
DECLARE new_code CHAR(2);
DECLARE loop_date DATE;
DECLARE new_course CHAR(3);
DECLARE mod_c CURSOR FOR 
    SELECT `code` FROM module WHERE course_code = give_code;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET com_mod = TRUE;


    IF (give_date < DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) OR (give_date <= CURDATE()) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Please Enter A Date Over One Month';
    ELSEIF (WEEKDAY(give_date) = 5 )THEN SET loop_date = DATE_ADD(give_date, INTERVAL 2 DAY);
    ELSEIF (WEEKDAY(give_date) = 6 )THEN SET loop_date = DATE_ADD(give_date, INTERVAL 1 DAY);
    ELSE SET loop_date = give_date;
    END IF;
    
   IF give_code <> ANY ( SELECT code FROM course ) THEN 
    SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Please Check Entered Course And Try Again'; 
END IF;
    
    OPEN mod_c;
    
    looper : LOOP
    
    FETCH NEXT FROM mod_c INTO new_code;
        IF com_mod THEN LEAVE looper;
        ELSE 
            INSERT INTO `session` (`code`, `date`) 
                VALUES (new_code, loop_date);
            IF (WEEKDAY(loop_date = 5))
                THEN SET loop_date = DATE_ADD(loop_date, INTERVAL 3 DAY);
                ELSE SET loop_date = DATE_ADD(loop_date, INTERVAL 1 DAY);
            END IF;
            END IF;
        END LOOP;
    CLOSE mod_c;

END $$
DELIMITER ;

这是我无法弄清楚的代码，

IF give_code <> ANY ( SELECT code FROM course ) THEN 
    SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Please Check Entered Course And Try Again'; 
END IF;

我希望它只插入类(class)表中的代码，但我不想对它们进行硬编码，因为我希望该过程能够在添加其他类(class)时成为 future 的证明。目前，我尝试插入的任何内容都会出现错误

Answer 1

你可以改变

IF give_code <> ANY ( SELECT code FROM course ) THEN 

至:

IF NOT EXISTS( SELECT 1 FROM course WHERE code = give_code) THEN