Efficient data exists in table Oracle PL/SQL
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.
