Handling exceptions by adding select clause embedded inside in PL-SQL(Oracle functions)

Question

I have to design a function(date in, number out) in oracle which executes the following three cases given the conditions:

case1: If input_date matches monday_date then execute query1(select from where.. and..) with monday_date as input param.

case2: If query from case(1) returns no rows, then create new variable max_input_date1 = input_date+1 and then execute query2(with ..select from table1 left join table2 ..) with monday_date and max_input_date1 as input param.

case3: If query from case(2) returns no rows, then create new variable max_input_date2 = input_date+4 and then execute query2( with..select from table1 left join table2 ..) with monday_date and max_input_date2 as input param.

I am trying to use exception - NO_DATA_FOUND to move to each case, but it looks like exception clause in the oracle functions do not execute any "select" statements - I am not completely sure, but it did not work for me, or I am doing something wrong. nothing is being returned after the function enters the exception clause.

I am looking for suggestions on how to get this to work, either with or without use of exceptions. Please help. This is how my function looks so far

CREATE OR
    REPLACE FUNCTION get_dept_no(input_date string)
    RETURN NUMBER
    IS
    dept_no NUMBER;

    max_input_date1 date ;
    max_input_date2 date ;

    delta1 integer := 1;
    delta2 integer := 4;

BEGIN
    SELECT num
    INTO dept_no
    FROM table1
    WHERE aperiod = 201910
      AND trunc(monday_date) = to_date(input_date, 'yyyy-mm-dd');
    RETURN dept_no;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
            max_input_date1 := to_date(input_date, 'yyyy-mm-dd') + delta1;

            WITH max as (
                SELECT *
                FROM table2 adm
                GROUP BY aperiod, num
                )

                SELECT
                       ma.num INTO dept_no
                FROM max ma
                LEFT JOIN table1 yw
                  ON ma.aperiod = yw.aperiod
                  AND ma.num = yw.num
                WHERE trunc(ma.DOJ_date) >= to_date(input_date, 'yyyy-mm-dd')
                  AND trunc(ma.DOJ_date) <= to_date(max_input_date1, 'yyyy-mm-dd');
            raise;
            WHEN NO_DATA_FOUND THEN
                max_input_date2 := to_date(input_date, 'yyyy-mm-dd') + delta2;
                WITH max as (
                SELECT *
                FROM table2 adm
                GROUP BY aperiod, num
                )

                SELECT
                       ma.num INTO dept_no
                FROM max ma
                LEFT JOIN table1 yw
                  ON ma.aperiod = yw.aperiod
                  AND ma.num = yw.num
                WHERE trunc(ma.DOJ_date) >= to_date(input_date, 'yyyy-mm-dd')
                  AND trunc(ma.DOJ_date) <= to_date(max_input_date2, 'yyyy-mm-dd');

return dept_no;
END get_dept_no;

Answer 1

I tend to want to write very small functions that do exactly 1 thing; that sometimes surprisingly involves more code, but removes complexity - seems like a contradiction but not really. In this case I would break it 3 inner functions, and a simple main. (Yes, you can write a function inside on a function).

create or replace function get_dept_no(input_date IN string)
    return number
is
    dept_no number;

    max_input_date1 date;
    max_input_date2 date;

    delta1 integer := 1;
    delta2 integer := 4;

    function get_dept_q1
      return number
    is
        dept_q1_result number;
    begin 
        select num
          into dept_q1_result
          from table1
         where aperiod = 201910
           and trunc(monday_date) = to_date(input_date, 'yyyy-mm-dd');
        return dept_q1_result;
    exception 
       when no_data_found then 
            return null; 
    end get_dept_q1;  

    function get_dept_q2
      return number
    is
        dept_q2_result number;
    begin 
      max_input_date1 := to_date(input_date, 'yyyy-mm-dd') + delta1;

      with max as (
           select *
             from table2 adm
            group by aperiod, num
            )
       select ma.num  
        into dept_no
        from max ma
        left join table1 yw
             on  ma.aperiod = yw.aperiod
             and ma.num = yw.num
         where trunc(ma.doj_date) >= to_date(input_date, 'yyyy-mm-dd')
           and trunc(ma.doj_date) <= to_date(max_input_date1, 'yyyy-mm-dd');
        return dept_q2_result;         
    exception 
       when no_data_found then 
            return null; 
    end get_dept_q2;  

    function get_dept_q3
      return number
    is
        dept_q3_result number;
    begin 
      max_input_date1 := to_date(input_date, 'yyyy-mm-dd') + delta1;

      with max as (
           select *
             from table2 adm
            group by aperiod, num
            )
       select ma.num  
        into dept_q3_result
        from max ma
        left join table1 yw
              on  ma.aperiod = yw.aperiod
             and ma.num = yw.num
         where trunc(ma.doj_date) >= to_date(input_date, 'yyyy-mm-dd')
           and trunc(ma.doj_date) <= to_date(max_input_date1, 'yyyy-mm-dd');
         return dept_q1_result;
    exception 
       when no_data_found then 
            return null; 
    end get_dept_q3;       

-- MAIN    
begin
   dept_no := get_dept_q1;

   if dept_no is null 
   then 
      dept_no := get_dept_q2;
   end if;

   if dept_nun is null 
   then 
      dept_no := get_dept_q3;
   end if;                                                     

   if dept_num is null 
   then 
      raise no_data_found; 
   end if;

   return dept_no;
end get_dept_no; 

NOTE: I did not evaluate the queries, just restructured the original function.