Example Oracle Procedure with Cursors to loop inside and Table Types

PROCEDURE UpdateLeader(argMemId IN ChallengeMember.MemberId%TYPE) IS

CURSOR userChallenges IS
SELECT DISTINCT(CHALLENGEMEMBER.CHALLENGEID)
FROM CHALLENGEMEMBER, CHALLENGE
WHERE CHALLENGEMEMBER.MEMBERID = argMemId AND CHALLENGEMEMBER.CHALLENGEID = CHALLENGE.CHALLENGEID AND (CHALLENGE.CHALLENGESTATUSID = 2 OR CHALLENGE.CHALLENGESTATUSID = 3);

CURSOR ChallengeMembers (varCHALLENGEID CHALLENGEMEMBER.CHALLENGEID%TYPE) ISSELECT CHALLENGEMEMBER.MEMBERID AS MEMBERID,CHALLENGE.STARTDATE AS STARTDATE, CHALLENGE.ENDDATE AS ENDDATE
FROM CHALLENGEMEMBER, CHALLENGE
WHERE CHALLENGE.CHALLENGEID = varCHALLENGEID AND
CHALLENGE.CHALLENGEID = CHALLENGEMEMBER.CHALLENGEID AND
CHALLENGEMEMBER.MEMBERID <> 0;

vMemberPoints CHALLENGEMEMBER.POINTS%TYPE;
varMaxPoints CHALLENGEMEMBER.POINTS%TYPE;

varNotifyRequired NUMBER;

TYPE MAX_POINTS_REC IS RECORD
(MEMBER CHALLENGEMEMBER.MEMBERID%TYPE);

TYPE MAX_POINTS_TAB_TYPE IS TABLE OF MAX_POINTS_REC
INDEX BY BINARY_INTEGER;

MAX_POINTS_TAB MAX_POINTS_TAB_TYPE;
i NUMBER;
LEADER_CHANGE NUMBER;
VAR_ISLEADER NUMBER;
ISLEADER_RECS NUMBER;

BEGIN

FOR Challenge_REC IN userChallenges

LOOP
varMaxPoints := 0;
i := 1;

MAX_POINTS_TAB.DELETE;
LEADER_CHANGE := 1;

FOR Mem_REC IN ChallengeMembers(Challenge_REC.CHALLENGEID)

LOOP
vMemberPoints := 0;
vMemberPoints := nvl(CHALLENGEMEMBER_PKG.GetGHGByChallengeMem(Mem_REC.MEMBERID, Mem_REC.STARTDATE, Mem_REC.ENDDATE), 0);

IF (vMemberPoints > varMaxPoints) THEN
varMaxPoints := vMemberPoints;

MAX_POINTS_TAB.DELETE;
MAX_POINTS_TAB(1).MEMBER := Mem_REC.MEMBERID;

ELSIF (vMemberPoints = varMaxPoints AND vMemberPoints > 0) THEN
i := i +1;
MAX_POINTS_TAB(i).MEMBER := Mem_REC.MEMBERID;

END IF;
END LOOP;

SELECT NVL(COUNT(ISLEADER),0) INTO ISLEADER_RECS
FROM CHALLENGEMEMBER
WHERE CHALLENGEID = Challenge_REC.CHALLENGEID AND
ISLEADER = 1 ;

IF ISLEADER_RECS <> MAX_POINTS_TAB.COUNT THEN

UPDATE CHALLENGEMEMBER
SET UPDATENOTIFIED = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

ELSE
FOR x IN 1 .. MAX_POINTS_TAB.COUNT
LOOP
SELECT ISLEADER INTO VAR_ISLEADER
FROM CHALLENGEMEMBER
WHERE CHALLENGEID = Challenge_REC.CHALLENGEID AND
MEMBERID = MAX_POINTS_TAB(x).MEMBER;

IF VAR_ISLEADER <> 1 THEN
UPDATE CHALLENGEMEMBER
SET UPDATENOTIFIED = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;
END IF;

END LOOP;

END IF;

UPDATE CHALLENGEMEMBER
SET ISLEADER = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

FOR x IN 1 .. MAX_POINTS_TAB.COUNT
LOOP
UPDATE CHALLENGEMEMBER
SET ISLEADER = 1
WHERE CHALLENGEMEMBER.MEMBERID = MAX_POINTS_TAB(x).MEMBER AND
CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

END LOOP;
END LOOP;

END UpdateLeader;