Big Oracle Procedure as an Example

PROCEDURE GetMapDetails(argMemberId IN Member.MemberId%TYPE, argProvinceId IN Member.POSTALCODEPROVINCEID%TYPE, argMapDetails OUT refCursorType)
IS

totalMemPoints1 NUMBER;
totalMemPoints2 NUMBER;
totalMemPoints NUMBER;
totalProvPoints1 NUMBER;
totalProvPoints2 NUMBER;
totalProvPoints NUMBER;
totalConPoints1 NUMBER;
totalConPoints2 NUMBER;
totalConPoints NUMBER;

BEGIN

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (COMMITMENT.ENDDATE - COMMITMENT.STARTDATE)) INTO totalMemPoints1
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE COMMITMENT.MEMBERID = argMemberId AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND MEMBER.MEMBERID = argMemberId AND MEMBER.POSTALCODEPROVINCEID = ACTION_X_PROVINCE.PROVINCEID
AND COMMITMENT.ENDDATE < sysdate AND COMMITMENT.STARTDATE < sysdate AND COMMITMENT.ENDDATE > COMMITMENT.STARTDATE;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (round((sysdate - COMMITMENT.STARTDATE)-0.5))) INTO totalMemPoints2
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE COMMITMENT.MEMBERID = argMemberId AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND MEMBER.MEMBERID = argMemberId AND MEMBER.POSTALCODEPROVINCEID = ACTION_X_PROVINCE.PROVINCEID
AND COMMITMENT.ENDDATE > sysdate AND COMMITMENT.STARTDATE < sysdate;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (COMMITMENT.ENDDATE - COMMITMENT.STARTDATE)) INTO totalProvPoints1
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE MEMBER.POSTALCODEPROVINCEID = argProvinceId AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = argProvinceId
AND COMMITMENT.ENDDATE < sysdate AND COMMITMENT.STARTDATE < sysdate AND COMMITMENT.ENDDATE > COMMITMENT.STARTDATE;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (round((sysdate - COMMITMENT.STARTDATE)-0.5))) INTO totalProvPoints2
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE MEMBER.POSTALCODEPROVINCEID = argProvinceId AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = argProvinceId
AND COMMITMENT.ENDDATE > sysdate AND COMMITMENT.STARTDATE < sysdate;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (COMMITMENT.ENDDATE - COMMITMENT.STARTDATE)) INTO totalConPoints1
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER
WHERE MEMBER.COUNTRYID = 1 AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID
AND COMMITMENT.ENDDATE < sysdate AND COMMITMENT.STARTDATE < sysdate AND COMMITMENT.ENDDATE > COMMITMENT.STARTDATE;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (round((sysdate - COMMITMENT.STARTDATE)-0.5))) INTO totalConPoints2
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE MEMBER.COUNTRYID = 1 AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID
AND COMMITMENT.ENDDATE > sysdate AND COMMITMENT.STARTDATE < sysdate;

IF (totalMemPoints1 > 0 AND totalMemPoints2 > 0) THEN
SELECT totalMemPoints1 + totalMemPoints2 INTO totalMemPoints FROM dual;
ELSIF (totalMemPoints1 > 0) THEN
SELECT totalMemPoints1 INTO totalMemPoints FROM dual;
ELSIF (totalMemPoints2 > 0) THEN
SELECT totalMemPoints2 INTO totalMemPoints FROM dual;
ELSE
SELECT '-1' INTO totalMemPoints FROM dual;
END IF;

IF (totalProvPoints1 > 0 AND totalProvPoints2 > 0) THEN
SELECT totalProvPoints1 + totalProvPoints2 INTO totalProvPoints FROM dual;
ELSIF (totalProvPoints1 > 0) THEN
SELECT totalProvPoints1 INTO totalProvPoints FROM dual;
ELSIF (totalProvPoints2 > 0) THEN
SELECT totalProvPoints2 INTO totalProvPoints FROM dual;
ELSE
SELECT '-1' INTO totalProvPoints FROM dual;
END IF;

IF (totalConPoints1 > 0 AND totalConPoints2 > 0) THEN
SELECT totalConPoints1 + totalConPoints2 INTO totalConPoints FROM dual;
ELSIF (totalConPoints1 > 0) THEN
SELECT totalConPoints1 INTO totalConPoints FROM dual;
ELSIF (totalConPoints2 > 0) THEN
SELECT totalConPoints2 INTO totalConPoints FROM dual;
ELSE
SELECT '-1' INTO totalConPoints FROM dual;
END IF;

OPEN argMapDetails FOR SELECT '&' || PROVINCE.PROVINCECODE || 'Members=' || COUNT(MEMBER.MEMBERID) AS AllCanada
FROM MEMBER,PROVINCE
WHERE MEMBER.POSTALCODEPROVINCEID = PROVINCE.PROVINCEID AND PROVINCE.PROVINCECODE IS NOT NULL
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Members=0'
FROM MEMBER,PROVINCE
WHERE PROVINCE.PROVINCECODE NOT IN (SELECT PROVINCE.PROVINCECODE FROM MEMBER,PROVINCE WHERE MEMBER.POSTALCODEPROVINCEID = PROVINCE.PROVINCEID AND PROVINCE.PROVINCECODE IS NOT NULL GROUP BY PROVINCE.PROVINCECODE)
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Name=' || PROVINCE.NAME FROM PROVINCE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Actions=' || COUNT(COMMITMENT.ACTIONID)
FROM PROVINCE, COMMITMENT,MEMBER
WHERE PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID AND MEMBER.MEMBERID = COMMITMENT.MEMBERID AND PROVINCE.PROVINCECODE IS NOT NULL
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Actions=0'
FROM PROVINCE, COMMITMENT, MEMBER
WHERE PROVINCE.PROVINCECODE NOT IN (SELECT PROVINCE.PROVINCECODE FROM PROVINCE, COMMITMENT, MEMBER WHERE PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID AND MEMBER.MEMBERID = COMMITMENT.MEMBERID AND PROVINCE.PROVINCECODE IS NOT NULL GROUP BY PROVINCE.PROVINCECODE)
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&YourPersonalSavings=' || TO_CHAR(ROUND(totalMemPoints,2)) FROM DUAL
UNION
SELECT '&YourProvince=' || PROVINCE.NAME FROM PROVINCE, MEMBER WHERE PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID AND MEMBER.MEMBERID = argMemberId
UNION
SELECT '&YourProvincialSavings=' || TO_CHAR(ROUND(totalProvPoints,2)) FROM DUAL
UNION
SELECT '&TotalCanadaSavings=' || TO_CHAR(ROUND(totalConPoints)) FROM DUAL
UNION
SELECT '&TotalGreenhouse=' || TO_CHAR(ROUND(totalConPoints)) FROM DUAL
UNION
SELECT '&TotalCanidians=' || TO_CHAR(COUNT(MEMBER.MEMBERID)) FROM MEMBER WHERE COUNTRYID = 1
UNION
SELECT '&DataLoaded=Done' FROM DUAL;

END GetMapDetails;