mysql在触发器里使用游标

wgs7909

BEGIN
    DECLARE QRCODE VARCHAR(32);
    DECLARE DTALFID VARCHAR(32);
    DECLARE FINDEX VARCHAR(10);
    DECLARE PHONEQRCODE VARCHAR(32);
    DECLARE PHONECLASSID VARCHAR(32);
    DECLARE SEQUENCENO VARCHAR(32);
    DECLARE STATEINFO VARCHAR(64);
    DECLARE STATEINFOKO  VARCHAR(64);
    DECLARE UUID VARCHAR(32);
    DECLARE IS_FOUND INTEGER DEFAULT 1;
    /*定义一个游标，把查询的结果临时放在游标变量里。*/
    DECLARE  DTL CURSOR FOR SELECT FID, FPHONEQRCODE, FPHONECLASSID, FSEQUENCENO, FSTATEINFO, FSTATEINFOKO, FFINDEX FROM  PH_INVOICEDETAIL  WHERE FMASTERID = NEW.FID ;
    /*在MYSQL里，定义完游标后，一定要给游标增加一个句柄，当游标下一条为空时触发，并且可以给IS_FOUND重新赋值*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET IS_FOUND=0;
    IF( NEW.fInvocieType='收购单' and (NEW.FISUNAUDIT='反审核完成' or NEW.FISINWH='是')) THEN
/*当游标被打开时，执行SELECT查询*/
    OPEN DTL;
/*取游标的第一条记录，把值赋给变量*/
        FETCH DTL INTO DTALFID, PHONEQRCODE, PHONECLASSID, SEQUENCENO, STATEINFO, STATEINFOKO, FINDEX;
        WHILE( IS_FOUND = 1 ) DO
            IF( FINDEX = 1 ) THEN
              UPDATE PH_PHONEINFO INFO SET INFO.fPhoneClassID=PhoneClassID, INFO.fSequenceNo=SequenceNo, INFO.fStateInfo=StateInfo, INFO.fStateInfoKO=StateInfoKO,fIndexNo=fIndexNo+1
              WHERE INFO.FPHONEQRCODE = PHONEQRCODE;
            ELSE
              SET UUID := REPLACE( UUID(), '-', '');
              UPDATE PH_INVOICEDETAIL SET FPHONEID = UUID,FFINDEX = '1' WHERE FID = DTALFID;
              INSERT INTO PH_PHONEINFO (FID, VERSION, FPHONECLASSID, FINDEXNO, FSEQUENCENO, FPHONEQRCODE, FSTATEINFO, FSTATEINFOKO, FISDEL, FINVOICESNUMBER, FPHONESTATUS, FPOSITIONSTATE,FCREATETIME)
                                                 VALUES(UUID, 0, PHONECLASSID, '999', SEQUENCENO, PHONEQRCODE, STATEINFO, STATEINFOKO, 0, OLD.FINVOICESNUMBER, '未销售', OLD.FPOSITIONSTATE, CURRENT_TIMESTAMP());
           END IF; /*取下一条记录，把值赋给变量*/
           FETCH DTL INTO DTALFID, PHONEQRCODE, PHONECLASSID, SEQUENCENO, STATEINFO, STATEINFOKO, FINDEX;
        END WHILE;
/*游标使用完以后，一定要关闭*/
    CLOSE DTL;
  END IF;
END