未满标18/24小时通知（存储过程）

BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE number VARCHAR(40) character set gbk;
DECLARE id int;
DECLARE c int;
DECLARE d datetime;
DECLARE money decimal(20,2) DEFAULT 0.00;
DECLARE Textdata1 text character set gbk;
DECLARE Textdata2 text character set gbk;
DECLARE Exdate datetime;
DECLARE Time INT;
DECLARE cc int;
DECLARE telwq varchar(20);
DECLARE telhb varchar(20);
DECLARE telsj varchar(20);
DECLARE rs CURSOR FOR SELECT a.a AS number,a.b AS id,a.c AS c,a.d AS d,sum(a.f-b.b) AS money FROM  (SELECT a.F25 AS a,a.F01 AS b,a.F08 AS c,a.F22 AS d,b.F11 AS e,a.F05 AS f FROM S62.T6230 a,S62.T6231 b,S62.T6250 c WHERE a.F01=b.F01 AND a.F01=c.F01 AND b.F11 IS NULL AND HOUR(TIMEDIFF(DATE_ADD(a.F22,INTERVAL a.F08 DAY),NOW()))<=24 )a
LEFT JOIN
(SELECT F02 AS a,sum(F04) AS b FROM S62.T6250 GROUP BY F02) b
ON a.b=b.a
GROUP BY a.b;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
SET Exdate = (SELECT DATE_ADD(NOW(),INTERVAL 30 MINUTE));
SET telwq = 13760836670;
SET telhb = 15920149039;
SET telsj = 13808870034;
IF (SELECT COUNT(*)  FROM  (SELECT a.F25 AS a,a.F01 AS b,a.F08 AS c,a.F22 AS d,b.F11 AS e,a.F05 AS f FROM S62.T6230 a,S62.T6231 b,S62.T6250 c WHERE a.F01=b.F01 AND a.F01=c.F01 )a
LEFT JOIN S62.T6231 b
ON a.b=b.F01
WHERE HOUR(TIMEDIFF(DATE_ADD(a.d,INTERVAL a.c DAY),NOW()))<=24 AND b.F11 IS NULL)>0
THEN
OPEN rs;
FETCH NEXT FROM rs INTO number,id,c,d,money;
REPEAT
SET Time =HOUR(TIMEDIFF(NOW(),DATE_ADD(d,INTERVAL c DAY)));
IF (Time <= 24 && Time >= 23)
THEN
SET Textdata1 = (SELECT CONCAT('标的',id,'，编号',number,'，剩余筹款时24小时，剩余筹款金额',money,'元。'));
INSERT INTO
S10._1040(F02,F03,F04,F05,F06)
VALUES(0,Textdata1,NOW(),'W',Exdate);
SET cc =(SELECT @@IDENTITY);
INSERT INTO
S10._1041(F01,F02)
VALUES (cc,telwq);
INSERT INTO
S10._1040(F02,F03,F04,F05,F06)
VALUES(0,Textdata1,NOW(),'W',Exdate);
SET cc =(SELECT @@IDENTITY);
INSERT INTO
S10._1041(F01,F02)
VALUES (cc,telhb);
INSERT INTO
S10._1040(F02,F03,F04,F05,F06)
VALUES(0,Textdata1,NOW(),'W',Exdate);
SET cc =(SELECT @@IDENTITY);
INSERT INTO
S10._1041(F01,F02)
VALUES (cc,telsj);
END IF;
IF (Time <= 18 && Time >= 17)
THEN
SET Textdata2 = (SELECT CONCAT('标的',id,'，编号',number,'，剩余筹款时18小时，剩余筹款金额',money,'元。'));
INSERT INTO
S10._1040(F02,F03,F04,F05,F06)
VALUES(0,Textdata2,NOW(),'W',Exdate);
SET cc =(SELECT @@IDENTITY);
INSERT INTO
S10._1041 (F01,F02)
VALUES(cc,telwq);
INSERT INTO
S10._1040(F02,F03,F04,F05,F06)
VALUES(0,Textdata2,NOW(),'W',Exdate);
SET cc =(SELECT @@IDENTITY);
INSERT INTO
S10._1041(F01,F02)
VALUES (cc,telhb);
INSERT INTO
S10._1040(F02,F03,F04,F05,F06)
VALUES(0,Textdata2,NOW(),'W',Exdate);
SET cc =(SELECT @@IDENTITY);
INSERT INTO
S10._1041(F01,F02)
VALUES (cc,telsj);
END IF;
FETCH NEXT FROM rs INTO number,id,c,d,money;
UNTIL Done END REPEAT;
CLOSE rs;
END IF;
END

来自为知笔记(Wiz)