oracle - PL/SQL 中同一过程更新和插入的行数

问题描述

我有一个过程，INSERT INTO然后UPDATE是某些字段（都在同一个过程中），我正在使用@Clive的这个答案Number of rows are an UPDATE in PL/SQL to know the amount of data that has been更新以放入日志，但它给我带来了总行数，而不仅仅是已更新的记录。

这是正确的认识方式吗？

我需要知道从中插入INSERT STATEMENT了多少行以及从中更新了多少行UPDATE STATEMENT。

我的查询：

CREATE OR REPLACE PROCEDURE OWNER.TABLE_NAME
AS

 -- VARIABLE
 v_qtd_regs  number := 0;  
 v_code      number;
 v_errm      VARCHAR2(500);
 start_time  pls_integer;
 end_time    pls_integer;
 elapse_time number;
 proc_name   varchar2(100);
 i NUMBER;

BEGIN
   proc_name := 'PRDWBI_CGA_D_COLUMNS';
   start_time := dbms_utility.get_time;
   DS_FUNCESP.PRDSBI_GRAVA_LOG( 'I', 'DataWarehouse', proc_name, 'Início Carga' );

   -- INSERT INTO TABLE:
   INSERT INTO OWNER.TABLE_NAME
   (COLUMN_ID, COLUMNS_NAME, COLUMN_NAME2)
   (SELECT 1 AS COLUMN_ID, 'TEST' AS COLUMN_NAME, SYSDATE AS COLUMN_NAME2 FROM DUAL);

   COMMIT;

  -- UPDATE SOME COLUMNS I NEED
   UPDATE OWNER.TABLE_NAME y
   SET (y.COLUMNS_NAME, y.COLUMN_NAME2) = 
               (SELECT 'TEST2' AS COLUMN_NAME, SYSDATE AS COLUMN_NAME2 FROM DUAL x WHERE x.COLUMN_ID = y.COLUMN_ID)
   WHERE EXISTS (SELECT 'TEST2' AS COLUMN_NAME, SYSDATE AS COLUMN_NAME2 FROM DUAL x WHERE x.COLUMN_ID = y.COLUMN_ID);

   -- TO KNOW HOW MANY ROWS WERE UPDATED
   i := SQL%rowcount;     

   COMMIT;  

   --dbms_output.Put_line(i);

   SELECT COUNT(1) INTO v_qtd_regs FROM OWNER.TABLE_NAME where LinData >= TRUNC(SYSDATE);
   end_time := dbms_utility.get_time;
   elapse_time := ((end_time - start_time)/100);
   v_errm := SUBSTR(SQLERRM, 1 , 500);
   DS_FUNCESP.PRDSBI_GRAVA_LOG('T', 'DataWarehouse', proc_name, v_errm, v_qtd_regs, elapse_time );

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 500);
      DS_FUNCESP.PRDSBI_GRAVA_LOG('E', 'Error', proc_name, v_errm);

END;

编辑问题以显示一个真实的例子：

我创建了一个从“SYS.DBA_TAB_COLUMNS”获取数据的表，仅用作示例，如下所示：

  CREATE TABLE "DW_FUNCESP"."D_TEST"
    (
        "ID_COLUMN" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
        START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
        "NM_OWNER"  VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "NM_TABLE"  VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "CD_COLUMN" NUMBER(20,0) NOT NULL ENABLE                             ,
        "NM_COLUMN" VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "DS_COLUMN" VARCHAR2(500 CHAR) NOT NULL ENABLE                       ,
        "LINDATE"   DATE DEFAULT SYSDATE NOT NULL ENABLE                     ,
        "LINORIGIN" VARCHAR2(100 CHAR) NOT NULL ENABLE
    )

然后我创建了一个过程来识别插入和更新的记录，如下所示：

CREATE OR REPLACE PROCEDURE DW_FUNCESP.PRDWBI_CGA_D_TEST
AS

 -- variaveis de suporte as informações que deve gravar
 rows_inserted integer;
 rows_updated  integer;

BEGIN

   -- Insert Into table
   INSERT INTO DW_Funcesp.D_TEST
   (NM_OWNER, NM_TABLE, CD_COLUMN, NM_COLUMN, DS_COLUMN, LINDATE, LINORIGIN)
   (SELECT
      NVL(x.NM_OWNER ,'NOT FOUND')      AS NM_OWNER     ,
      NVL(x.NM_TABLE ,'NOT FOUND')      AS NM_TABLE     ,
      NVL(x.CD_COLUMN ,-1)              AS CD_COLUMN    ,
      NVL(x.NM_COLUMN ,'NOT FOUND')     AS NM_COLUMN    ,
      NVL(x.DS_COLUMN ,x.NM_COLUMN)     AS DS_COLUMN    ,
      SYSDATE                           AS LINDATE      ,
      'SYS.DBA_TAB_COLUMNS'             AS LINORIGIN
    FROM
      (
        SELECT
          d.OWNER       AS NM_OWNER ,
          d.TABLE_NAME  AS NM_TABLE ,
          d.COLUMN_ID   AS CD_COLUMN,
          d.COLUMN_NAME AS NM_COLUMN,
          e.COMMENTS    AS DS_COLUMN
        FROM SYS.DBA_TAB_COLUMNS d
        LEFT JOIN SYS.DBA_COL_COMMENTS e
          ON e.OWNER           = d.OWNER
          AND e.TABLE_NAME  = d.TABLE_NAME
          AND e.COLUMN_NAME = d.COLUMN_NAME
        WHERE d.OWNER = 'DW_FUNCESP'
       ) x
    LEFT JOIN DW_FUNCESP.D_TEST y
      ON  y.NM_OWNER = x.NM_OWNER
      AND y.NM_TABLE = x.NM_TABLE
      AND y.NM_COLUMN = x.NM_COLUMN
    WHERE y.ID_COLUMN IS NULL);

    rows_inserted := sql%rowcount;

  -- Update the table
   UPDATE DW_FUNCESP.D_TEST z
   SET (z.NM_COLUMN, z.DS_COLUMN, z.LINDATE) = 
                  (SELECT
                      NVL(x.NM_COLUMN ,'NOT FOUND')     AS NM_COLUMN    ,
                      NVL(x.DS_COLUMN ,x.NM_COLUMN)     AS DS_COLUMN    ,
                      SYSDATE                           AS LINDATE      
                    FROM
                      (
                        SELECT
                          d.OWNER       AS NM_OWNER ,
                          d.TABLE_NAME  AS NM_TABLE ,
                          d.COLUMN_ID   AS CD_COLUMN,
                          d.COLUMN_NAME AS NM_COLUMN,
                          e.COMMENTS    AS DS_COLUMN
                        FROM SYS.DBA_TAB_COLUMNS d
                        LEFT JOIN SYS.DBA_COL_COMMENTS e
                          ON e.OWNER           = d.OWNER
                          AND e.TABLE_NAME  = d.TABLE_NAME
                          AND e.COLUMN_NAME = d.COLUMN_NAME
                        WHERE d.OWNER = 'DW_FUNCESP'
                       ) x
                    WHERE z.NM_OWNER = x.NM_OWNER
                      AND z.NM_TABLE = x.NM_TABLE
                      AND z.CD_COLUMN = x.CD_COLUMN)
   WHERE EXISTS (SELECT
                      NVL(x.NM_COLUMN ,'NOT FOUND')     AS NM_COLUMN    ,
                      NVL(x.DS_COLUMN ,x.NM_COLUMN)     AS DS_COLUMN    ,
                      SYSDATE                           AS LINDATE      
                    FROM
                      (
                        SELECT
                          d.OWNER       AS NM_OWNER ,
                          d.TABLE_NAME  AS NM_TABLE ,
                          d.COLUMN_ID   AS CD_COLUMN,
                          d.COLUMN_NAME AS NM_COLUMN,
                          e.COMMENTS    AS DS_COLUMN
                        FROM SYS.DBA_TAB_COLUMNS d
                        LEFT JOIN SYS.DBA_COL_COMMENTS e
                          ON e.OWNER           = d.OWNER
                          AND e.TABLE_NAME  = d.TABLE_NAME
                          AND e.COLUMN_NAME = d.COLUMN_NAME
                        WHERE d.OWNER = 'DW_FUNCESP'
                       ) x
                    WHERE z.NM_OWNER = x.NM_OWNER
                      AND z.NM_TABLE = x.NM_TABLE
                      AND z.CD_COLUMN = x.CD_COLUMN);

   rows_updated := sql%rowcount; 

   dbms_output.Put_line('inserted=>' || to_char(rows_inserted) || ', updated=>' || to_char(rows_updated));

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      RAISE;

END;

所以我的第一个插入输出是：

插入=>2821，更新=>2821

所以我选择了一个要更改的数据并对其进行了更新，我进行了以下选择以选择应更新哪些数据以再次引入 DBMS 输出：

SELECT * FROM DW_FUNCESP.D_TEST WHERE NM_TABLE = 'D_TEST';

如图所示，我在列中进行了评论，以引入更新：

COMMENT ON COLUMN DW_FUNCESP.D_TEST.LINORIGIN IS 'The origin of the data';

我再次运行该程序，输出为：

插入=>0，更新=>2821

该更新的结果：

你不应该只在输出中带来 1 个更新的数据，因为只有 1 个更新？而不是所有的行？

例如：插入=>0，更新=>1

所以我的问题仍然存在，我问对了吗？是否有可能在相同的程序中获得此结果？是不是更新不正确（尽管已经更新了数据）？

标签： oracleplsqloracle-sqldeveloper

oracle - PL/SQL 中同一过程更新和插入的行数

问题描述

解决方案

推荐阅读