oracle 如何获取自定义函数中删除的记录的总数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6986756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get a total count for records deleted in custom function
提问by James Shin
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 WHEN SQL%ROWCOUNT = 0;
END LOOP;
RETURN counter;
END;
结尾;
This is how I call the function on sql_plus
这就是我在 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.
当我打印 OUT_NUMBER 时，我什么也没看到。
Thanks for your help!
谢谢你的帮助！
回答by Justin Cave
You're not initializing the COUNTERvariable 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 COUNTERto 0.
您没有初始化COUNTER变量，因此它以 NULL 开始。将值添加到 NULL 将导致 NULL，而不是值。因此，您至少需要初始化COUNTER为 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.
- 程序应该做诸如删除数据之类的事情。函数不应该修改数据，它们应该只是进行计算。使用 OUT 参数创建过程比将其声明为函数更有意义。
- 在这样的循环中删除数据没有多大意义。简单地执行一个删除所有需要删除的数据的 DELETE 语句会更有效率。
- 向循环添加临时提交会减慢代码速度，并增加遇到 ORA-01555 错误的可能性。很少有像这样的临时提交真正有意义的情况。
回答by Danny
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.
否则，您正在尝试将行数添加到 null。这将始终为空。
