mysql - 1172 - 结果由 mysql 中的多行组成

Question

如何解决这个问题(结果在 mysql 中包含不止一行)

 DROP PROCEDURE IF EXISTS `doMarksApplication`;
 CREATE PROCEDURE `doMarksApplication`(
 in  kuser varchar(20),
 out idpro int(11))
 SP:BEGIN
   declare no_more_rows  int  default FALSE;
   declare total_marks   decimal(10,2) default 0;
   declare idfor  int(11)       default 0; 
   declare sskod  int(5)        default getCurSession();
   declare bdata  int(5)        default 0;
   declare nopmh  varchar(20);
   # Data PB [Permohonan Baru] DM [Proses Pemarkahan] 
   declare cur1 cursor for
   select ind_nopmh from pinduk 
   left join pprses on pro_nopmh = ind_nopmh
   where ind_sskod = sskod and
   concat(pro_stats,pro_statp) in ('PB','DM') and
   not exists (select mar_idnum from pmrkah where mar_nopmh = ind_nopmh)
   order by ind_nopmh;
   declare continue handler for not found set no_more_rows = TRUE;

   begin
        select count(ind_nopmh) into bdata 
        from pinduk 
        left join pprses on pro_nopmh = ind_nopmh
        where ind_sskod = sskod and
              concat(pro_stats,pro_statp) in ('PB','DM') and
              not exists (select mar_idnum from pmrkah where mar_nopmh = ind_nopmh);
      end;

      begin
        select count(for_idnum) into idfor from xkod_markah_00_formula
        where for_stats = 'A' and
              curdate() between for_tkhdr and for_tkhhg;
      end;

      if idfor = 1 and sskod <> 0 then
        begin
          select for_idnum into idfor from xkod_markah_00_formula
          where for_stats = 'A' and
                curdate() between for_tkhdr and for_tkhhg;
        end;

        begin
          insert into pprmar
          (pma_tkmla,pma_msmla,pma_puser,pma_sskod,pma_idfor,pma_bdata)
          values
          (curdate(),curtime(),kuser,sskod,idfor,bdata);
        end;

        begin
          select last_insert_id() into idpro;
        end;

        open cur1;    
        LOOP1:loop    
          fetch cur1 into nopmh;

          if no_more_rows then
            close cur1;
            leave LOOP1;
          end if; 

          begin
            call getMarksAnakPerak(nopmh,@total_perak);
            call getMarksAkademik(nopmh,@total_akdmk);
            call getMarksSosioekonomi(nopmh,@total_sosio);
          end;

          set total_marks = @total_perak + @total_akdmk + @total_sosio;

          begin
            insert into pmrkah
            (mar_idpro,mar_nopmh,mar_idfor,mar_perak,mar_akdmk,mar_sosio,mar_total)
            values
            (idpro,nopmh,idfor,@total_perak,@total_akdmk,@total_sosio,total_marks);
          end;

          begin
            update pprses 
            set pro_stats = 'D',
                pro_statp = 'M',
                pro_tkmsk = curdate(),
                pro_msmsk = curtime(),
                pro_kuser = kuser
            where pro_nopmh = nopmh;
          end;

        end loop; 

        begin
          update pprmar
          set pma_tktmt = curdate(),
              pma_mstmt = curtime()
          where pma_idnum = idpro;
        end;
      end if;
 END;

Answer 1

我已经使用 mysql 编程 15 年了，这无疑是我见过的最令人困惑的存储过程。

尽管如此，您的问题可能出现在此处

select for_idnum into idfor from xkod_markah_00_formula
  where for_stats = 'A' and
        curdate() between for_tkhdr and for_tkhhg;

我知道这似乎不是原因，但在不知道您调用的其他三个存储过程的内容的情况下，这是唯一的候选者。您应该为它添加一个 limit 1，并为从表中读取的每个 select into 语句(即不是 sum() 或 count() 等...)添加一个限制，因为这总是有可能导致您的错误看见。

select for_idnum into idfor from xkod_markah_00_formula
  where for_stats = 'A' and
        curdate() between for_tkhdr and for_tkhhg limit 1;

此外，您应该注释掉三个存储过程调用并查看错误是否消失。我的猜测是，问题出在这些存储过程中，因为与上面类似的 select into 在结果集中有不止一行，但没有使用 limit 1 并且没有正确过滤。