Distinct in the cursor does not work...?

Prompt, something I can not understand, distinct in the cursor does not work or what? have a cursor with a distinct, which I get around in a loop .. If the request from the cursor to perform separately, then returns 1 record, if distinct clean, there will be 2 entries. A loop through the cursor with distinct runs 2 times.

DECLARE cur CURSOR FOR
 开发者_运维技巧  SELECT DISTINCT t.DATPR, A.ACCOUNT_NO
   FROM postgkh.tt_krd t
   INNER JOIN postgkh.account A ON t.LS = A.ACCOUNT_NO
   INNER JOIN postgkh.resid RID USING(ACCOUNT_ID)
   INNER JOIN postgkh.reseller R USING(RESELLER_ID)
   WHERE r.RESELLER_ID = RID
   ORDER BY A.ACCOUNT_ID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur;
  REPEAT
 FETCH cur INTO PR_DATE,ACCID, ACCNO;
 SET LIM_FP = postgkh.GET_ACCOUNT_FP(ACCID) - INTERVAL 1 DAY;
 IF(LIM_FP>PR_DATE) THEN
  IF(TMP IS NULL OR TMP<>ACCNO) THEN
   SET TMP = ACCNO;
   SET ERR = CONCAT(ERR,', ',ACCNO);
  END IF;
 END IF;
  UNTIL done END REPEAT;
  CLOSE cur;

The cursor construct in your code isn't formed correctly. The last row the cursor will read is being read twice. The cursor should be closed after it reaches the last row.

What is happening here is that the cursor traversal reaches the last row then the error handler for SQLSTATE is triggered, then done = 1. It is not yet closed at that point. The cursor is left open to traverse the same last row again until it hits the UNTIL done END REPEAT.

I suggest you do not use the REPEAT-UNTIL construct for the cursor. Use LOOP instead:

OPEN cur;
curLoop: LOOP
FETCH cur INTO PR_DATE,ACCID, ACCNO;
 IF (done)
 THEN
  CLOSE cur;
  LEAVE curLoop;
 END IF;

 ...

END LOOP curLoop;

This snippet may help: http://mysql-0v34c10ck.blogspot.com/2011/05/general-purpose-dynamic-cursor-part-3.html