ORACLE (PL/SQL), create function that would add number to array-CodePudding

I would like to create a function that would take an array and a number as paramaters, add that number to that list and return it.

Here's what I've made but it doesn't seem to work

CREATE OR REPLACE TYPE numList IS VARRAY(100) OF NUMBER;

CREATE OR REPLACE FUNCTION addNumToArray (v_var NUMBER, listOfNumbers IN OUT numList)
RETURN numList
IS
    v_count NUMBER:=listOfNumbers.COUNT;
BEGIN
    listOfNumbers :=numList();
    listOfNumbers.extend();
    listOfNumbers(v_count):= v_var;
RETURN listOfNumbers;
end;

When I call this function, it is always replacing the one number so the size is always one.

I tried to execute it like this but the result is always the last inserted number, in this case 17.

DECLARE
    ArrayNum numList := numList(10);
BEGIN
    ArrayNum:=addNumToArray(15,ArrayNum);
    ArrayNum:=addNumToArray(17,ArrayNum);
    FOR i in 1 .. ArrayNum.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(ArrayNum(i) || ' ' || ArrayNum.COUNT);
    END LOOP ;
end;

CodePudding user response：

Your function does this:

listOfNumbers :=numList();

which means you start with an empty list each time the function is called, and don't retain the old values; you just always put the new value into the otherwise-empty list.

You need to get rid of that, and also increment the counter after extending:

CREATE OR REPLACE FUNCTION addNumToArray (v_var NUMBER, listOfNumbers IN OUT numList)
RETURN numList
IS
    v_count NUMBER:=listOfNumbers.COUNT;
BEGIN
    listOfNumbers.extend();
    listOfNumbers(v_count   1):= v_var;
    RETURN listOfNumbers;
end;
/

or without the counter variable, just using listOfNumbers.COUNT - which will be incremented automatically by the extend():

CREATE OR REPLACE FUNCTION addNumToArray (v_var NUMBER, listOfNumbers IN OUT numList)
RETURN numList
IS
BEGIN
    listOfNumbers.extend();
    listOfNumbers(listOfNumbers.COUNT):= v_var;
    RETURN listOfNumbers;
end;
/

But as you have an IN OUT argument this can be a procedure instead:

CREATE OR REPLACE PROCEDURE addNumToArray (v_var NUMBER, listOfNumbers IN OUT numList)
IS
BEGIN
    listOfNumbers.extend();
    listOfNumbers(listOfNumbers.COUNT):= v_var;
end;
/

which is then called as:

DECLARE
    ArrayNum numList := numList(10);
BEGIN
    addNumToArray(15,ArrayNum);
    addNumToArray(17,ArrayNum);
    FOR i in 1 .. ArrayNum.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(ArrayNum(i) || ' ' || ArrayNum.COUNT);
    END LOOP ;
end;
/

All three versions get the output:

10 3
15 3
17 3

db<>fiddle