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;
/

StackOverflow is not a site where people do your homework for you. Please [edit] the question to include a [MRE] with: **YOUR** attempt at a solution; and the issues/errors with **YOUR** attempt. — MT0, Feb 05 '22 at 13:58
If a problem is too difficult [find a simpler problem](https://ericlippert.com/2014/03/21/find-a-simpler-problem/). Forget about SQL, can you solve this with pen and paper? No? How about if P1 does have to be smaller than P2? Still can't get it? What if P1 is always zero? Still stuck? What if P2 was always 5? — Dour High Arch, Feb 05 '22 at 14:26
What is "number"? Real numbers has infinite cardinality (continuum) on every interval so you cannot calculate their sum. — astentx, Feb 06 '22 at 13:28

score 0 · Answer 1 · answered Feb 05 '22 at 13:53

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.

score 0 · Accepted Answer · answered Feb 05 '22 at 14:53

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>

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

2 Answers2