SQL Oracle 如何计算插入 plsql 块的总行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37563557/
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
Oracle How to count total number of rows inserted in a plsql block
提问by Utsav
I want to count no of inserts I make into the database
我想计算我在数据库中插入的次数
The below query returns one(1) although 2 rows did update
下面的查询返回一（1）虽然 2 行确实更新
begin
Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS)
values (1000000386,'BB','Branch Budgets','I'));
Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS)
values (1000000257,'TIME','Timesheets','I'));
dbms_output.put_line('No Of rows'||sql%Rowcount);
end;
回答by Utsav
dbms_output.put_line('No Of rows'||sql%Rowcount);
This will give you total number of rows updated by laststatement. So even if you have 10 insert statements this way, you will always get 1as sql%rowcount.
这将为您提供最后一条语句更新的总行数。因此，即使您以这种方式有 10 个插入语句，您也将始终获得1as sql%rowcount.
Either use 2 output statements, one each after insertstatements, or use a variable and add number of rows updated to it, and then display it at last.
要么使用 2 个输出语句，每个insert语句后面一个，要么使用一个变量并添加更新的行数，然后最后显示它。
declare
v_count integer;
begin
v_count:=0;
Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS)
values (1000000386,'BB','Branch Budgets','I');
v_count:= sql%Rowcount;
Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS)
values (1000000257,'TIME','Timesheets','I');
v_count:= v_count+ sql%Rowcount;
dbms_output.put_line('No Of rows '||v_count);
commit;
end;
OR
If you are inserting data to same table, use a combined insertstatement like this.
This will return 2 rows.
或 如果您要向同一个表中插入数据，请使用这样的组合insert语句。这将返回 2 行。
begin
INSERT ALL
into APPLICATIONS (ID,ALIAS,NAME,STATUS)
values (1000000386,'BB','Branch Budgets','I')
into APPLICATIONS (ID,ALIAS,NAME,STATUS)
values (1000000257,'TIME','Timesheets','I')
SELECT * FROM dual;
dbms_output.put_line('No Of rows '||sql%Rowcount);
commit;
end;
