分享一篇mysql存储过程实例
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE NSRSBH VARCHAR(30);
DECLARE NSRYF VARCHAR(30);
DECLARE NN INT DEFAULT 0;
DECLARE CC INT DEFAULT 0;
DECLARE rs CURSOR FOR select gf_nsrsbh nn, left(kprq,7) ss from invoice
where kprq > left(date_sub(now(), interval 12 month), 10) group by gf_nsrsbh ,left(kprq, 7) ORDER BY GF_NSRSBH;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO NSRSBH, NSRYF;
REPEAT
SELECT NSRYF;
IF NOT Done THEN
/* REPEAT
SET CC = CC + 1;*/
SELECT count(*) N into NN FROM TEST WHERE ID = NSRSBH AND YF = NSRYF;
IF NN>0 THEN
SELECT CC;
SET NN = 0;
UPDATE TEST SET SE = (SELECT SUM(SE) SE FROM INVOICE
WHERE GF_NSRSBH = NSRSBH AND LEFT(KPRQ,7) = NSRYF), UPDATE_DATE = NOW();
ELSE
SELECT CC;
INSERT INTO TEST(ID,YF,SE) SELECT GF_NSRSBH,NSRYF YF, SUM(SE) SE FROM INVOICE
WHERE GF_NSRSBH = NSRSBH AND LEFT(KPRQ,7) = NSRYF GROUP BY GF_NSRSBH, LEFT(KPRQ,7);
END IF;
/*UNTIL CC = 12
END REPEAT;*
/*REPEAT
SET CC = CC + 1;
delete FROM test where ID = NSRSBH AND YF = left(MON,7);
SELECT CC;
INSERT INTO TEST(ID,YF,SE) SELECT GF_NSRSBH,left(MON,7) YF, SUM(SE) SE FROM INVOICE
WHERE GF_NSRSBH = NSRSBH AND LEFT(KPRQ,7) = left(MON,7) GROUP BY GF_NSRSBH, LEFT(KPRQ,7);
SET MON = DATE_SUB(MON, INTERVAL 1 MONTH);
SELECT MON;
UNTIL CC = 12
END REPEAT;
SET MON = NOW();*/
END IF;
FETCH NEXT FROM rs INTO NSRSBH, NSRYF;
UNTIL Done END REPEAT;
CLOSE rs;
END