How would I solve this function assignment in PL/SQL?

Question

So the assignments goes like this:

Make a PL/SQL function that takes in 2 parameters (exmaple p1 and p2). The function then has to calculate the sum of all the numbers between the range of p1 and p2. P1 doesn't always have to be smaller than P2.

The function returns the sum of the range.

The problem is that I don't understand the syntax of PL/SQL that well.

I have something like this but this only compares the numbers and returns the bigger number

DECLARE  
   a number;  
   b number;  
   c number;  
FUNCTION findMax(x IN number, y IN number)   
RETURN number  
IS  
    z number;  
BEGIN  
   IF x > y THEN
    Z := x;
   ELSE  
      Z:= y;  
   END IF;  
  
   RETURN z;  
END;   
BEGIN  
   a:= 23;  
   b:= 45;  
  
   c := findMax(a, b);  
   dbms_output.put_line(' The sum of all the numbers between  (23,45): ' || c);  
END;
/

Answer 1

Function you posted (that returns larger of two numbers) is already built-in, its name is greatest:

SQL> select greatest (23, 45) result from dual;

    RESULT
----------
        45

SQL>

---

Opposite to it is function named least; both of them can be used in your assignment so that loop would always go from smaller to larger bound. Something like this:

SQL> create or replace function f_sum_between (par_a in int, par_b in int)
  2    return int
  3  is
  4    retval number := 0;
  5  begin
  6    for i in least(par_a, par_b) .. greatest(par_a, par_b) loop
  7      retval := retval + i;
  8    end loop;
  9    return retval;
 10  end;
 11  /

Function created.

A test or two:

SQL> select f_sum_between(2, 5) result,
  2         2 + 3 + 4 + 5 verify
  3  from dual;

    RESULT     VERIFY
---------- ----------
        14         14

SQL>
SQL> select f_sum_between(-2, -5) result,
  2         -2 + -3 + -4 + -5 verify
  3  from dual;

    RESULT     VERIFY
---------- ----------
       -14        -14

SQL>

Answer 2

Since this is a homework assignment, the syntax for CREATE FUNCTION is documented here.

In the signature of the function, you would need to declare two IN arguments, p1 and p2, that both have the number data type and you need to RETURN a NUMBER data type.

In the declare section of the function, you would need to declare a variable to hold the total of the values with a NUMBER data type and initialise it to zero.

In the body of the function, you would need to use a loop to iterate from the least of p1 and p2 up to the greatest of p1 and p2 and at each iteration of the loop add the loop value to the previously declared total variable.

Finally, when the loop has terminated, you need to RETURN the total.