将过程赋值给变量

Question

我希望使用此过程选择任意随机值。

delimiter $$
CREATE PROCEDURE randomdigit(troy INT)
     BEGIN
          select troy; 
     END$$
delimiter ;

要使用它，我调用call randomdigit(n);

但是，当我尝试将该过程赋值给触发器中的变量时，我会得到以下错误

/* SQL错误(1064)：您的SQL语法出现错误；请检查与MySQL服务器版本对应的手册，以获得使用接近“调用随机数字(1)”的正确语法；在第11行*/设置the_class_id =(从e中选择examination_class_id )

这是我的扳机

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;
    DECLARE lesrandom INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    SET lesrandom = call randomdigit(1);
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id) VALUES (( select cs_id from class_students where cs_class_id = 1 AND cs_year_id = 1 ),lesrandom);

END //
DELIMITER ;

用这种方式将过程赋值给变量是正确的吗？

Answer 1

阿基尔的答案是一个可能的解决办法。如果需要存储过程，则必须使用OUT参数。

delimiter $$
CREATE PROCEDURE randomdigit(IN troy INT, OUT result INT)
     BEGIN
          set result = troy; 
     END$$
delimiter ;

就这样叫它：

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;
    DECLARE lesrandom INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    call randomdigit(1, lesrandom);
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id) VALUES (( select cs_id from class_students where cs_class_id = 1 AND cs_year_id = 1 ),lesrandom);

END //
DELIMITER ;

Answer 2

将其作为一个函数进行更改

delimiter $$
CREATE function randomdigit(troy INT) returns int
     BEGIN
          return troy; 
     END$$
delimiter ;

并按以下方式更改触发器

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;
    DECLARE lesrandom INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    SET lesrandom = randomdigit(1);
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id) VALUES (( select cs_id from class_students where cs_class_id = 1 AND cs_year_id = 1 ),lesrandom);

END //
DELIMITER ;

希望这样可以