oracle 获取删除的记录数sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31507423/
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
Get number of records deleted sql
提问by user525146
I want to get the row count of how many records have been deleted. The below query returns the number of records that will be affected, but
我想获取已删除记录的行数。下面的查询返回将受到影响的记录数，但
SELECT COUNT(*) FROM suppliers
WHERE EXISTS
( SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id
AND customer_id > 25 );
I need something like this, after the rows have been deleted, I can show the number of records that have been deleted on the front end.
我需要这样的东西，行被删除后，我可以在前端显示已删除的记录数。
SELECT COUNT(*) FROM suppliers(
DELETE from supplier(
WHERE EXISTS
( SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id
AND customer_id > 25 ));
I don't want to run the query twice, one to get the number of records that will be deleted, if its greater than 0, then I run the second query to delete the records.
我不想运行两次查询，一次是获取将被删除的记录数，如果它大于 0，那么我运行第二个查询来删除记录。
回答by Gordon Linoff
You can use the RETURNING INTOclause to get all the rows being deleted -- and then count those.
您可以使用该RETURNING INTO子句来获取所有被删除的行——然后计算这些行。
An easier way for just the count is SQL%ROWCOUNTin PL/SQL code. Something like this:
一种更简单的计数方法是SQL%ROWCOUNT在 PL/SQL 代码中。像这样的东西：
BEGIN
DELETE from supplier(
WHERE EXISTS
( SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id
AND customer_id > 25 );
dbms_output.put_line(SQL%ROWCOUNT);
END;
回答by cutit
You can use declare @countDelete int set nocount on delete tablename where .. select @@ROWCOUNT
您可以使用declare @countDelete int set nocount on delete tablename where .. select @@ROWCOUNT
