创建随机全日菜单存储过程报错：遇符号IN，需合法标识符

错误原因分析 #

1. 参数声明语法错误：根据报错提示，你使用的数据库（如Oracle/DB2）不支持IN 参数名 类型的写法，正确的参数传入方向声明应放在参数名称之后，即参数名 IN 类型；若使用MySQL，原参数写法本身合法，但必须先设置DELIMITER，否则存储过程内的分号会被当作语句结束符引发解析错误。
2. INSERT语句不完整：代码末尾的INSERT INTO MC_90_DAYS_MENU (Day_Number)缺少值输入部分，无法完成数据插入。

修复后的代码 #

针对Oracle/DB2环境 #

CREATE OR REPLACE PROCEDURE MC_CREATE_RANDOM_MENU(P_DAY_NUMBER IN INT)
BEGIN
   DELETE FROM MC_90_DAYS_MENU WHERE Day_Number = P_DAY_NUMBER;
   DELETE FROM MC_90_DAYS_MENU_ITEM WHERE Day_Number = P_DAY_NUMBER;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Breakfast', Item FROM Breakfast ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Breakfast', Item FROM "Coffee & Tea" ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Lunch', Item FROM Salads ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Lunch', Item FROM 
       (SELECT * FROM "Beef & Pork" UNION SELECT * FROM "Chicken & Fish") AS T
       ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Lunch', Item FROM Beverages ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   IF DBMS_RANDOM.VALUE() < 0.5 THEN
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Lunch', Item FROM Desserts ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   END IF;
   IF DBMS_RANDOM.VALUE() < 0.5 THEN
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Afternoon Snack', Item FROM "Smoothies & Shakes" ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   ELSE
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Afternoon Snack', Item FROM "Coffee & Tea" ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Afternoon Snack', Item FROM "Snacks & Sides" ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   END IF;
   -- Dinner
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Dinner', Item FROM 
       (SELECT * FROM "Beef & Pork" UNION SELECT * FROM "Chicken & Fish") AS T
       ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Dinner', Item FROM Beverages ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   IF DBMS_RANDOM.VALUE() < 0.5 THEN
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Dinner', Item FROM 
           (SELECT * FROM Desserts UNION SELECT * FROM "Coffee & Tea") AS T
           ORDER BY DBMS_RANDOM.VALUE() FETCH FIRST 1 ROWS ONLY;
   END IF;
   INSERT INTO MC_90_DAYS_MENU (Day_Number) VALUES(P_DAY_NUMBER);
END;
/

针对MySQL环境 #

DELIMITER //
CREATE OR REPLACE PROCEDURE MC_CREATE_RANDOM_MENU(IN P_DAY_NUMBER INT)
BEGIN
   DELETE FROM MC_90_DAYS_MENU WHERE Day_Number = P_DAY_NUMBER;
   DELETE FROM MC_90_DAYS_MENU_ITEM WHERE Day_Number = P_DAY_NUMBER;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Breakfast', Item FROM Breakfast ORDER BY RAND() LIMIT 1;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Breakfast', Item FROM `Coffee & Tea` ORDER BY RAND() LIMIT 1;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Lunch', Item FROM Salads ORDER BY RAND() LIMIT 1;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Lunch', Item FROM 
       (SELECT * FROM `Beef & Pork` UNION SELECT * FROM `Chicken & Fish`) AS T
       ORDER BY RAND() LIMIT 1;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Lunch', Item FROM Beverages ORDER BY RAND() LIMIT 1;
   IF RAND() < 0.5 THEN
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Lunch', Item FROM Desserts ORDER BY RAND() LIMIT 1;
   END IF;
   IF RAND() < 0.5 THEN
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Afternoon Snack', Item FROM `Smoothies & Shakes` ORDER BY RAND() LIMIT 1;
   ELSE
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Afternoon Snack', Item FROM `Coffee & Tea` ORDER BY RAND() LIMIT 1;
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Afternoon Snack', Item FROM `Snacks & Sides` ORDER BY RAND() LIMIT 1;
   END IF;
   -- Dinner
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Dinner', Item FROM 
       (SELECT * FROM `Beef & Pork` UNION SELECT * FROM `Chicken & Fish`) AS T
       ORDER BY RAND() LIMIT 1;
   INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
   SELECT P_DAY_NUMBER, 'Dinner', Item FROM Beverages ORDER BY RAND() LIMIT 1;
   IF RAND() < 0.5 THEN
       INSERT INTO MC_90_DAYS_MENU_ITEM (Day_Number, Meal_Type, Item)
       SELECT P_DAY_NUMBER, 'Dinner', Item FROM 
           (SELECT * FROM Desserts UNION SELECT * FROM `Coffee & Tea`) AS T
           ORDER BY RAND() LIMIT 1;
   END IF;
   INSERT INTO MC_90_DAYS_MENU (Day_Number) VALUES(P_DAY_NUMBER);
END //
DELIMITER ;

额外说明 #

• Oracle/DB2环境中，用DBMS_RANDOM.VALUE()替代MySQL的RAND()生成随机数，用FETCH FIRST 1 ROWS ONLY替代LIMIT 1限制返回行数。
• MySQL环境必须设置DELIMITER，避免存储过程内的分号被数据库误解析为语句结束符。

内容的提问来源于stack exchange，提问作者Georgia