如果我的PLSQL块中有多个DML查询，如何计算受影响的聚合行? [英] How can I calculate aggregate affected rows if there are Multiple DML query in my PLSQL block?

问题描述

在我的PLSQL块中可能存在多个DML语句的情况下，我一直在寻找一种通用方法，该方法可以计算出受此代码块影响的行总数.

I have a scenario where there may exist multiple DML statements inside my PLSQL Block, I was looking for some generic approach by using which I can calculate total no of rows affected using this block of code.

测试数据和结构以供参考:

Test Data and Structure for reference:

create table cust_temp_a
(Name varchar2(100), id varchar2(100));

insert into cust_temp_a VALUES
('Hasu','10');
insert into cust_temp_a VALUES
('Aasu','20');
insert into cust_temp_a VALUES
('Basu','30');
insert into cust_temp_a VALUES
('Casu','10');

commit;

create table cust_temp_b
(Name varchar2(100), id varchar2(100));

insert into cust_temp_b VALUES
('Hasu','10');
insert into cust_temp_b VALUES
('Aasu','20');
insert into cust_temp_b VALUES
('Basu','30');
insert into cust_temp_b VALUES
('Casu','20');

commit;

可能存在多个这样的表，

There may exist multiple tables like this,

下面是PLSQL块，具有不记录受影响的行的功能:

Below is the PLSQL Block with the capability of logging no of rows affected:

DECLARE 
   affected_count_a number;
   affected_count_b number;
   total_affected_count number;
PROCEDURE proc(affected_count_a OUT number,affected_count_b OUT number) IS 
BEGIN 
 update cust_temp_a set name = 'new_val' where id = 10;
 affected_count_a:=sql%rowcount;

 update cust_temp_b set name = 'new_val' where id = 20;
 affected_count_b:=sql%rowcount;
END;   
BEGIN 
   proc(affected_count_a,affected_count_b);
   total_affected_count:=affected_count_a+affected_count_b;
   dbms_output.put_line('total_affected_count : ' || total_affected_count ); 
   dbms_output.put_line('affected_count_a : ' || affected_count_a);
   dbms_output.put_line('affected_count_b : ' || affected_count_b );
END;
/
commit;

结果:

total_affected_count : 4
affected_count_a : 2
affected_count_b : 2

"proc"过程中可能存在多个DML语句，我想执行某种通用方法来记录每个DML语句的单独计数，最后汇总受"proc"影响的计数.

There may exist multiple DML statements inside the procedure "proc", and I wanted to perform some generic approach to log individual count of each DML statement and at last aggregate, count affected by the "proc".

每次都添加DML语句并向日志计数添加相应的变量是很痛苦的.

Adding DML Statement every time and adding the corresponding variable to log count is the pain.

推荐答案

您可以使用通用过程将计数记录到通用日志记录表中.

You may log the counts in a generic logging table using a generic procedure.

记录表

CREATE TABLE dml_logs (
     log_id      NUMBER PRIMARY KEY,
     step        VARCHAR2(200),
     row_count   NUMBER,
     log_date    DATE
);

ID序列

create sequence seq_dml_logs ;

记录过程

CREATE OR REPLACE PROCEDURE log_dml (
     p_step        VARCHAR2,
     p_row_count   NUMBER,
     p_log_date    DATE
) IS
     PRAGMA autonomous_transaction;
BEGIN
     INSERT INTO dml_logs (
          log_id,
          step,
          row_count,
          log_date
     ) VALUES (
          seq_dml_logs.NEXTVAL,
          p_step,
          p_row_count,
          p_log_date
     );
     COMMIT;
END;
/

具有DML的PL/SQL块

DECLARE
v_step dml_logs.step%TYPE;
BEGIN

  v_step := 'cust_temp_a_update';
   UPDATE cust_temp_a SET name = 'new_val' WHERE id = 10;

 log_dml(v_step,SQL%ROWCOUNT,SYSDATE);

 v_step := 'cust_temp_b_update';
   UPDATE cust_temp_b SET name = 'new_val' WHERE id = 20;

 log_dml(v_step,SQL%ROWCOUNT,SYSDATE);
END;
/

然后，聚合很简单.

select SUM(row_count) FROM dml_logs 
where step = ? and log_date = ? -- all the required conditions.

为了更好地识别记录是属于特定运行还是批次，您可以在dml_logs中添加另一列，称为 batch_number .记录此数字以标识dml的唯一运行，并且查询以获取汇总详细信息变得更加简单.

In order to better identify that the records belong to a particular run or a batch, you may add another column in the dml_logs called batch_number . Log this number to identify unique runs of your dmls and your query to get the aggregate details become much simpler.

这篇关于如果我的PLSQL块中有多个DML查询，如何计算受影响的聚合行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！