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.

SBYC Wet Wednesday 2020 Fun 1

Sail Log

2020 Personal Sail Log Summary

Date

March 11, 2020

Vessel Name/Type/Model

Escape J/70

Course/Sea Miles

Sailed dock to dock: 7.62 mi

Course: K2 St-Kp-Gt-Bp-Fin 2.54 nm

Notes

Who sailed or my position on the vessel

  1. Boat Owner/Skipper
    1. Me

  2. Crew
    1. Jenn

    2. Max

    3. Erik

Weather

FZUS56 KLOX 110918
CWFLOX
Expires:202003111645;;306406

Coastal Waters Forecast
National Weather Service Los Angeles/Oxnard CA
218 AM PDT Wed Mar 11 2020

Point Piedras Blancas to San Mateo Point CA out 60 NM including the
Channel Islands National Marine Sanctuary


PZZ650-111645-
East Santa Barbara Channel from Pt. Conception to Pt. Mugu CA
including Santa Cruz Island-
218 AM PDT Wed Mar 11 2020

.TODAY...SE winds 5 to 10 kt. Wind waves 2 ft or less. W swell 2 to
3 ft at 9 seconds. S swell 2 ft. Chance of showers and slight chance
of tstms.
.TONIGHT...NW winds 10 kt in the evening, becoming variable 10 kt or
less. Wind waves 2 ft or less. W swell 2 ft at 12 seconds. S swell
2 ft. Slight chance of tstms in the evening.

Sail Trace (Strava)

Video/Pictures

SBYC Openning Day 2020 on J70 Escape

Talk about wind. There were gusts just before the race of 33 mph! We played it safe with the kite and still hit above 15.5 km/h or on my Garmin Fenix 6, 18.3 mph surfing a wave downwind. Sailed course A8 or St-Ap-Gt-Bp-Xp-Dp-Fin …..6.65

SBYC Opening Day 2020

Sail Log

Date

Vessel Name/Type/Model

Course/Sea Miles

Coure

Total Sailed: 14.78 mi

Notes

Very windy. Winds close to 20 mi and gusts close to 30mi. We has one wave surf that got us up to 15+ nm/hr with just jib and main.

We made it around with no damage or harm.

Who sailed or my position on the vessel

  1. Boat Owner/Skipper
    1. Me

  2. Crew
    1. Jenn

    2. Mike F.

    3. Max

    4. Nicole

Weather

FZUS56 KLOX 011708
CWFLOX
Expires:202003012315;;331955

Coastal Waters Forecast
National Weather Service Los Angeles/Oxnard CA
908 AM PST Sun Mar 1 2020

Point Piedras Blancas to San Mateo Point CA out 60 NM including the
Channel Islands National Marine Sanctuary


PZZ650-012315-
East Santa Barbara Channel from Pt. Conception to Pt. Mugu CA
including Santa Cruz Island-
908 AM PST Sun Mar 1 2020

...GALE WARNING IN EFFECT THROUGH LATE TONIGHT...

.TODAY...W winds 25 to 30 kt with gusts to 35 kt. Combined seas 8 to
9 ft dominant period 10 seconds.
.TONIGHT...NW winds 20 to 30 kt with local gusts to 35 kt, becoming
15 to 20 kt with local gusts to 25 kt after midnight. Strongest
western portion. Combined seas 9 to 11 ft dominant period 11 seconds,
subsiding to 6 to 8 ft dominant period 11 seconds after midnight.
Slight chance of showers in the evening.

Sail Trace (Strava)

Video/Pictures

SBSC Opening Day 2020

Sail Log

Date

Feb. 29th 2020

Vessel Name/Type/Model

Course/Sea Miles

5 short windward/leewards

Got 4-4-3-4-1

The the first race we should have gotten a 2nd. I did not stear properly for the takedown for our team. The last race was awesome. Second around the first mark. Bunched up at the last mark. The mexican was ughly but very effective. Nothing like a first to finish a day. I hope my team remembers how it feels to win against good sailors.

Notes

Who sailed or my position on the vessel

  1. Boat Owner/Skipper
    1. Me

  2. Crew
    1. Jenn

    2. Mike F.

    3. Galin

Weather

FZUS56 KLOX 291118
CWFLOX
Expires:202002291800;;270350

Coastal Waters Forecast
National Weather Service Los Angeles/Oxnard CA
318 AM PST Sat Feb 29 2020

Point Piedras Blancas to San Mateo Point CA out 60 NM including the
Channel Islands National Marine Sanctuary


PZZ650-291800-
East Santa Barbara Channel from Pt. Conception to Pt. Mugu CA
including Santa Cruz Island-
318 AM PST Sat Feb 29 2020

...SMALL CRAFT ADVISORY IN EFFECT FROM THIS AFTERNOON THROUGH LATE
TONIGHT...
...GALE WARNING IN EFFECT FROM LATE TONIGHT THROUGH SUNDAY EVENING...

.TODAY...SW winds 5 to 10 kt, becoming W 10 to 20 kt in the
afternoon, except 15 to 25 kt western portion. Combined seas 2 to 3
ft dominant period 12 seconds, building to 3 to 5 ft dominant period
12 seconds in the afternoon. Patchy fog in the morning.

Sail Trace (Strava)

Video/Pictures

Obituary: Marilyn Joyce (Mepham) Ghens

My loving mother, Marilyn Joyce Mepham Ghens, passed away after a long illness on Feb. 13, 2020 at Cottage Hospital, Santa Barbara. I miss her so, her devotion to family, her love for animals and children, her sense of beauty. I treasure the memories of our times together.

A lifelong Catholic, Marilyn often attended mass at the Santa Barbara Mission. Born in 1940 in Long Beach, her family moved to Santa Barbara in 1951.

Her father, George Mepham (1906-2007), was a plumber who built a family home in Samarkand, where he lived with Marilyn's loving mother, Lucille (Panchiere) Mepham (1907-1993), a jazz pianist.

My Mom graduated from Santa Barbara High School in 1958 where she was known for her love for the ocean, water skiing, and softball.

In college at San Jose State University, my Mom conceived me with classmate Warren Ghens. As chronicled in the book "The Girls Who Went Away," 80 percent of children of single mothers at that time were put up for adoption. At a maternity home, my Mom worked hard cleaning many a floor and swallowed her pride to make the case to keep me. My parents were briefly married. Mom lived most of her life in the family home with her parents; all three of them raised me.

Mom later graduated from UCSB in business economics and earned her law degree from Santa Barbara College of Law. Marilyn, an only child herself, stood by her own parents through their illnesses at the end of life. She was a loving woman.

I invite friends and family to join me for my Mom's graveside service at Santa Barbara Cemetery Association Mausoleum in the Pines on Thurs, Feb. 27, 11 a.m.

SBYC CHRF Spring #3 2020

Sail Log

Date

Feburary 23, 2020

Vessel Name/Type/Model

Escape J/70

Course/Sea Miles

H D5 St-Dp-Gt-Cp-Fin 4.89 nm

10.66 mi sailed

Notes

Who sailed or my position on the vessel

  1. Boat Owner/Skipper
    1. Me

  2. Crew
    1. Jenn

    2. Matt

    3. Issac

    4. Max

Weather

9 to 12 miles. SW

Sail Trace (Strava)

Video/Pictures