时间戳

首页 > 解决方案 > How to get total no of updated row count if we have multiple tables inside for loop in PLSQL?

sql - How to get total no of updated row count if we have multiple tables inside for loop in PLSQL?

问题描述

I have a situation, where the logic DML Statement can be changed at any given time, but other part will remain the same.

In short I have to create a template where I can add 1 or multiple DML Command and post that I need to calculate total No of rows affected by the DML Command.

For current situation I have DML command in For loop and there exist different table inside for loop for update action.

SQL Structure :

CREATE TABLE TEMP_A
  (NAME VARCHAR2(20), ID VARCHAR2(20)
  );
INSERT INTO TEMP_A VALUES
  ('SAM',100
  );
INSERT INTO TEMP_A VALUES
  ('SAM',100
  );
INSERT INTO TEMP_A VALUES
  ('SAM',100
  );
INSERT INTO TEMP_A VALUES
  ('JACK',200
  );
INSERT INTO TEMP_A VALUES
  ('JACK',200
  );
INSERT INTO TEMP_A VALUES
  ('JACK',200
  );
INSERT INTO TEMP_A VALUES
  ('HAMES',300
  );
INSERT INTO TEMP_A VALUES
  ('HAMES',300
  );
INSERT INTO TEMP_A VALUES
  ('HAMES',300
  );
COMMIT;


CREATE TABLE TEMP_C
  (NAME VARCHAR2(20), ID VARCHAR2(20)
  );
INSERT INTO TEMP_C VALUES
  ('SAM',100
  );
INSERT INTO TEMP_C VALUES
  ('RAM',100
  );
INSERT INTO TEMP_C VALUES
  ('KAM',200
  );
COMMIT;


CREATE TABLE TEMP_B
(ID VARCHAR2(20));

INSERT INTO TEMP_B VALUES(100);
INSERT INTO TEMP_B VALUES(200);
INSERT INTO TEMP_B VALUES(300);

COMMIT;

PLSQL Block :

    DECLARE 
   i number(2);
BEGIN
        FOR i in (select ID from TEMP_B) loop
        merge into  TEMP_A c
        using( select i.ID new_id from dual) t
        on
        (t.new_id = i.ID)
        when matched then update set
        c.id = 555;

    merge into  TEMP_C c
        using( select i.ID new_id from dual) t
        on
        (t.new_id = i.ID)
        when matched then update set
        c.id = 555;

        end loop;
   dbms_output.put_line('Total affecetd records are :' || SQL%ROWCOUNT);

END;
/

Output :

Total affected records are :3

Ideally, I was looking to get the output as 12 (Total no of affected rows from both the table inside for loop), but I am getting output for only last query what shall be done if I need to get the output of all rows affected inside for loop, the DML block can be changed at any given time so I cannot add custom SQL%ROWCOUNT statement and aggregate it I am looking for a generic approach if any.

标签： sqloracleplsql

解决方案

DECLARE
   I               NUMBER (2);
   AFFECTED_ROWS   NUMBER;
BEGIN
   AFFECTED_ROWS := 0;

   FOR I IN (SELECT ID FROM TEMP_B)
   LOOP
      MERGE INTO TEMP_A C
           USING (SELECT I.ID NEW_ID FROM DUAL) T
              ON (T.NEW_ID = I.ID)
      WHEN MATCHED
      THEN
         UPDATE SET C.ID = 555;

      AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;

      MERGE INTO TEMP_C C
           USING (SELECT I.ID NEW_ID FROM DUAL) T
              ON (T.NEW_ID = I.ID)
      WHEN MATCHED
      THEN
         UPDATE SET C.ID = 555;

      AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Total affecetd records are :' || AFFECTED_ROWS);
END;
/

AFFECTED_ROWS 存储每个语句的 SQL%ROWCOUNT 值的总和。

推荐阅读