mysql - 将过程分配给变量

Question

我想使用此过程选择我想要的任何随机值

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

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

但是，当我尝试使用将过程分配给触发器中的变量时出现此错误

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call randomdigit(1); SET the_class_id = (select examination_class_id from e' at line 11 */

这是我的触发器

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

Akhil 的回答是一个可能的解决方案。如果您需要存储过程，则必须使用 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 ;