how to get a total count for records deleted in custom function

Can someone please help me writing a custom function to delete old records and return a total count at the same time? Here is what I have currently. The delete portion works good, but somehow return counter is nothing.

CREATE OR REPLACE FUNCTION DELETE_OLD(delete_date IN DATE) 
RETURN NUMBER IS
counter NUMBER;
BEGIN
  LOOP
    DELETE FROM MY_TEST_TABLE WHERE TEST_DATA_LOAD_DATE < delete_date AND ROWNUM   <= 100;
    counter := counter + SQL%ROWCOUNT;
COMMIT;
EXIT W开发者_Go百科HEN SQL%ROWCOUNT = 0;
  END LOOP;
  RETURN counter;

END;

This is how I call the function on sql_plus

SQL> VAR OUT_NUMBER
SQL> call DELETE_OLD(TO_DATE('20-05-2011', 'dd-MM-yyyy')) into :OUT_NUMBER;

and when I print OUT_NUMBER, I see nothing.

Thanks for your help!

You're not initializing the COUNTER variable so it starts off as NULL. Adding values to NULL will result in a NULL, not a value. At a minimum, therefore, you'd want to initialize COUNTER to 0.

CREATE OR REPLACE FUNCTION DELETE_OLD(delete_date IN DATE) 
RETURN NUMBER IS
  counter NUMBER := 0;
BEGIN
  LOOP
    DELETE FROM MY_TEST_TABLE 
     WHERE TEST_DATA_LOAD_DATE < delete_date 
       AND ROWNUM   <= 100;
    counter := counter + SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN SQL%ROWCOUNT = 0;
  END LOOP;
  RETURN counter;
END;

That said, I would be very concerned about this general approach

• Procedures should do things like delete data. Functions should not modify data, they should just make computations. It would make much more sense to create a procedure with an OUT parameter than to declare this as a function.
• It doesn't make a lot of sense to delete data in a loop like this. It will be much more efficient to simply execute a single DELETE statement that deletes all the data you need to delete.
• Adding interim commits to loops slows code down and can increase the probability of hitting an ORA-01555 error. There are very few cases where it really makes sense to have interim commits like this.

You need to initialize the counter to a value if you are going to try to add to it

COUNTER NUMBER :=0;

otherwise you are trying to add the rowcount to null. Which will always be null.