如何获取teradata过程中受最后几个sql语句影响的行数

Question

那么让我在过程中有3个merge语句:

CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT)
BEGIN
    MERGE INTO t1
         USING t2
    ...;
    MERGE INTO a1
         USING b2
    ...;
    MERGE INTO c1
         USING d2
    ...;
END;

如何获得过程中受影响的行数?我知道ACTIVE_COUNT，但我可以使用它吗?是num_rows = ACTIVE_COUNT将工作，还是需要将其添加到变量几次并初始设置为0?

Answer 1

您需要初始化变量为零，然后在每次执行DML语句后添加计数(使用ACTIVITY_COUNT或GET DIAGNOSTICS ROW_COUNT):

CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT)
BEGIN
   DECLARE ac BIGINT DEFAULT 0;
    MERGE INTO t1
         USING t2
    ...;
   SET ac = ac + ACTIVITY_COUNT;
    MERGE INTO a1
         USING b2
    ...;
   SET ac = ac + ACTIVITY_COUNT;
    MERGE INTO c1
         USING d2
    ...;
   SET ac = ac + ACTIVITY_COUNT;
   SET num_rows = ac;
END;

Answer 2

您可以在每次合并后插入以下部分。

Merge INTO t1
      USING t2
.....;
SET lv_activity_count = activity_count;

SET lv_message = ' Number of rows merged in table1 is  '|| lv_activity_count ;
..

请初始化并声明变量