Efficient data exists in table Oracle PL/SQL

Michael Ghens

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.

Comments