Part of my work is writing functions that test if a user is part of a
population (set math) The database I work with in the grand scheme is not
particular large, but large in its own right. Some rows can be in the millions.
Being me, instead of doing real work, plus having the experience of a Moodle
plugin designer who’s module would crash computers and tell me that it worked
for him on his laptop, I looked up how to produce a function that is
efficient.. I have found two ways to do this. One from stackoverflow, the
other method that is used a lot in Ellucian Banner.
I am going to list a function that I wrote in both ways. The logic suited the need
of my employer.
StackOverflow method:
FUNCTION is_enrolled (pin_pidm SATURN.sfrstcr.sfrstcr_pidm%TYPE) RETURN VARCHAR2 AS
l_count NUMBER;
BEGIN
select count(1) into l_count
FROM sfrstcr
INNER JOIN stvrsts ON sfrstcr_rsts_code = stvrsts_code
WHERE sfrstcr_term_code in (
SELECT stvterm_code from stvterm where stvterm_code <> '999999' AND stvterm_end_date >= SYSDATE)
and sfrstcr_pidm = pin_pidm;
IF l_count > 0
THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;
END is_enrolled;
Banner Method:
-- Ellucian prefers to return boolian, I need to return (Y/N). They also like cursors
FUNCTION is_enrolled (pin_pidm SATURN.sfrstcr.sfrstcr_pidm%TYPE) RETURN VARCHAR2 IS
student_found VARCHAR2(1);
student_exists VARCHAR2(1);
CURSOR found_student_c
IS
select 'X'
FROM sfrstcr
INNER JOIN stvrsts ON sfrstcr_rsts_code = stvrsts_code
WHERE sfrstcr_term_code in (
SELECT stvterm_code from stvterm where stvterm_code <> '999999' AND stvterm_end_date >= SYSDATE)
and sfrstcr_pidm = pin_pidm;
BEGIN
OPEN found_student_c;
FETCH found_student_c INTO student_found;
CLOSE found_student_c;
IF student_found = "X" THEN
THEN
student_exists := 'Y';
ELSE
student_exists := 'N';
END IF;
RETURN student_exists;
END is_enrolled;
Personally, I am not sure which is better, but, I might as well do it the Banner
way, instead of always trying to reinvent the wheel or borrow some else's wheel.
