如果我的 PLSQL 块中有多个 DML 查询，我如何计算聚合受影响的行?

2021-12-30 00:00:00 sql oracle11g oracle plsql

我有一个场景，我的 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语句，把对应的变量加到log count里，很痛苦.

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.

相关文章