根据状态个数来决定总设备的状态（主要是循环loop）

  PROCEDURE TEST_STATE(P_MAC VARCHAR2) IS
    V_STENUM_0 NUMBER; --设备异常的总数
    V_STENUM_1 NUMBER; --设备正常的总数
    V_STENUM_2 NUMBER; --设备故障的总数
    V_STENUM_3 NUMBER; --设备未知的总数
    V_SID VARCHAR2(20); --场景编号
    V_OUT NUMBER;
    CURSOR C_CUR IS
      SELECT T.S_ID FROM TB_GRA_RELATION T WHERE T.MAC_NUM = P_MAC;
  BEGIN
 
    OPEN C_CUR;
    LOOP
      FETCH C_CUR
        INTO V_SID;
      EXIT WHEN C_CUR%NOTFOUND;
   
      SELECT SUM(CASE
                   WHEN T1.EQU_STATE = 1 THEN
                    1
                   ELSE
                    0
                 END),
             SUM(CASE
                   WHEN T1.EQU_STATE = 0 THEN
                    1
                   ELSE
                    0
                 END),
             SUM(CASE
                   WHEN T1.EQU_STATE = 2 THEN
                    1
                   ELSE
                    0
                 END),
             SUM(CASE
                   WHEN T1.EQU_STATE = 3 THEN
                    1
                   ELSE
                    0
                 END)
        INTO V_STENUM_1, V_STENUM_0, V_STENUM_2, V_STENUM_3
     
        FROM V_REAL_ALL T1, TB_GRA_RELATION T2
       WHERE T1.EQU_MAC = T2.MAC_NUM
         AND T2.S_ID = V_SID;
      ----关联运行状态（1正常 0异常、2故障、3未知）
   
      IF V_STENUM_2 > 0 THEN
        V_OUT := 2;
      ELSE
        IF V_STENUM_0 > 0 THEN
          V_OUT := 0;
        ELSE
          IF V_STENUM_3 > 0 THEN
            V_OUT := 3;
          ELSE
            V_OUT := 1;
          END IF;
        END IF;
      END IF;
     
     
      UPDATE TB_GRA_SIGHT T SET T.STAT = V_OUT WHERE T.S_ID = V_SID;
      COMMIT;
     
    END LOOP;
    CLOSE C_CUR;
  /*
    --根据CP MAC查询出关联几个场景
    select COUNT(t.s_id)
      INTO V_CP_NUM
      from tb_gra_relation t
     where t.mac_num = P_MAC;
    --该CP MAC下只有一个场景时
    IF V_CP_NUM = 1 THEN
      --查询出该场景下异常（state=0）状态的设备数
      SELECT T.COUNT_NUM
        INTO V_STENUM_0
        FROM (
              --查询出该场景下各种状态的次数
              select COUNT(t2.EQU_STATE) COUNT_NUM, t2.EQU_STATE
                from tb_gra_relation t1, v_real_all t2
               where t1.s_id in (select t.s_id
                                   from tb_gra_relation t
                                  where t.mac_num = P_MAC)
                 and t1.mac_num = t2.EQU_MAC
               GROUP BY t2.EQU_STATE) T
       WHERE T.EQU_STATE = 0;
      --查询出该场景下异常（state=1）状态的设备数
      SELECT T.COUNT_NUM
        INTO V_STENUM_1
        FROM (
              --查询出该场景下各种状态的次数
              select COUNT(t2.EQU_STATE) COUNT_NUM, t2.EQU_STATE
                from tb_gra_relation t1, v_real_all t2
               where t1.s_id in (select t.s_id
                                   from tb_gra_relation t
                                  where t.mac_num = P_MAC)
                 and t1.mac_num = t2.EQU_MAC
               GROUP BY t2.EQU_STATE) T
       WHERE T.EQU_STATE = 1;
      --查询出该场景下异常（state=2）状态的设备数
      SELECT T.COUNT_NUM
        INTO V_STENUM_2
        FROM (
              --查询出该场景下各种状态的次数
              select COUNT(t2.EQU_STATE) COUNT_NUM, t2.EQU_STATE
                from tb_gra_relation t1, v_real_all t2
               where t1.s_id in (select t.s_id
                                   from tb_gra_relation t
                                  where t.mac_num = P_MAC)
                 and t1.mac_num = t2.EQU_MAC
               GROUP BY t2.EQU_STATE) T
       WHERE T.EQU_STATE = 2;
   
      --查询出该场景下异常（state=3）状态的设备数
      SELECT T.COUNT_NUM
        INTO V_STENUM_3
        FROM (
              --查询出该场景下各种状态的次数
              select COUNT(t2.EQU_STATE) COUNT_NUM, t2.EQU_STATE
                from tb_gra_relation t1, v_real_all t2
               where t1.s_id in (select t.s_id
                                   from tb_gra_relation t
                                  where t.mac_num = P_MAC)
                 and t1.mac_num = t2.EQU_MAC
               GROUP BY t2.EQU_STATE) T
       WHERE T.EQU_STATE = 3;
      --如果异常状态数>0则该修改该场景的关联状态为异常
      IF V_STENUM_0 > 0 THEN
        UPDATE TB_GRA_SIGHT T
           SET T.STAT = 0
         WHERE T.S_ID IN
               (select t.s_id from tb_gra_relation t where t.mac_num = P_MAC);
        --如果故障状态数>0则该修改该场景的关联状态为故障
      ELSIF V_STENUM_0 = 0 AND V_STENUM_2 > 0 THEN
        UPDATE TB_GRA_SIGHT T
           SET T.STAT = 2
         WHERE T.S_ID IN
               (select t.s_id from tb_gra_relation t where t.mac_num = P_MAC);
        --如果未知状态数>0则该修改该场景的关联状态为未知
      ELSIF V_STENUM_0 = 0 AND V_STENUM_2 = 0 AND V_STENUM_3 > 0 THEN
        UPDATE TB_GRA_SIGHT T
           SET T.STAT = 3
         WHERE T.S_ID IN
               (select t.s_id from tb_gra_relation t where t.mac_num = P_MAC);
        --所有设备都正常
      ELSE
        UPDATE TB_GRA_SIGHT T
           SET T.STAT = 1
         WHERE T.S_ID IN
               (select t.s_id from tb_gra_relation t where t.mac_num = P_MAC);
      END IF;
   
      --ELSIF V_NUM > 1 THEN
   
    END IF;
  */
  END;