将Mysql CASE select语句结果存储在变量中

Question

我有以下MySQL触发器：

CREATE TRIGGER `upd_interim_final` AFTER INSERT ON `oee_main_interim`
 FOR EACH ROW INSERT INTO `oee_main_interim_final` (id,NAME,ts,Left_IO,Left_NIO,Recovery,Right_IO,Right_NIO,RunMode,S_TYPE,Shift,STD,curr_S_Type) 
VALUES(NULL, New.NAME, New.TS, NEW.Left_IO, New.Left_NIO,  New.Recovery, New.Right_IO, New.Right_NIO, New.RunMode, New.S_TYPE, 


 ( Select

  (Case
    When ((CurTime() > oee_machinenames.Shift1) And
    (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
    When ((CurTime() > oee_machinenames.Shift2) And
    (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
    When ((CurTime() > oee_machinenames.Shift3) Or
    (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) As curr_Shift
From
  oee_machinenames
  where 
  oee_machinenames.ID = New.NAME

Group By
  oee_machinenames.ID),


  (Select
    `STD` From `oee_variant` Where `Machine_ID` = New.NAME And `S_TYPE` = 

(Select
  `S_TYPE`

From
  `v_getmaxid`
Where
  `NAME` = New.Name And
  v_getmaxid.Max_id In (Select
    Max(v_getmaxid.Max_id) As Max_Max_id
  From
    `v_getmaxid`
  Where
    `NAME` = New.Name))

     And `oee_variant`.`Operators` = 
    (Select `Operators` from `oee_machinenames` where `ID` = New.NAME)),


(Select
  `S_TYPE`

From
  `v_getmaxid`
Where
  `NAME` = New.Name And
  v_getmaxid.Max_id In (Select
    Max(v_getmaxid.Max_id) As Max_Max_id
  From
    `v_getmaxid`
  Where
    `NAME` = New.Name))

  )

我尝试将此CASE子句的结果存储在一个变量中，以便稍后在触发器中使用它：

 ( Select

      (Case
        When ((CurTime() > oee_machinenames.Shift1) And
        (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
        When ((CurTime() > oee_machinenames.Shift2) And
        (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
        When ((CurTime() > oee_machinenames.Shift3) Or
        (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) As curr_Shift
    From
      oee_machinenames
      where 
      oee_machinenames.ID = New.NAME

    Group By
      oee_machinenames.ID),

我尝试将以下内容添加到触发器的开头，但没有成功：

DECLARE Shifts TEXT;
SET @Shifts := ( Select

      (Case
        When ((CurTime() > oee_machinenames.Shift1) And
        (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
        When ((CurTime() > oee_machinenames.Shift2) And
        (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
        When ((CurTime() > oee_machinenames.Shift3) Or
        (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) As curr_Shift
    From
      oee_machinenames
      where 
      oee_machinenames.ID = New.NAME

    Group By
      oee_machinenames.ID);

任何帮助都将不胜感激。谢谢

Answer 1

@Shifts是一个9.4. User-Defined Variables和Shifts one 13.6.4.1. Local Variable DECLARE Syntax，是不同的变量。

下面是一个例子：

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test`$$

CREATE PROCEDURE `sp_test`(`p_parm1` BOOL, `p_parm2` BOOL)
BEGIN
    DECLARE `shifts` CHAR(6);
    SET `shifts` := (SELECT CASE WHEN `p_parm1` AND `p_parm2` THEN 'Shift1'
                                 WHEN `p_parm1` AND TRUE THEN 'Shift2'
                                 WHEN TRUE AND `p_parm2` THEN 'Shift3'
                                 ELSE 'Shift0'
                            END
                     FROM DUAL
                     WHERE TRUE = TRUE);
    SELECT `shifts`;
END$$

DELIMITER ;