UPDATE 重複記錄

最新推荐文章于 2022-08-02 02:42:21 发布

原创最新推荐文章于 2022-08-02 02:42:21 发布 · 535 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table

ORACLE數據庫专栏收录该内容

60 篇文章

订阅专栏

/*
@ Jay
@ 09/01/20
@ Update重複數據
*/
PROCEDURE P_UPD
(
    V_TABLE VARCHAR2,
    V_COLUMN VARCHAR2
)
AS
    O_CUR1 MYTYPE;
    V_NUM VARCHAR2(20);
    SQLTXT VARCHAR2(200);
    V_VAR VARCHAR2(1);
BEGIN
    /*取單引號*/
    SELECT '''' INTO V_VAR FROM DUAL;
    /*查找有重複的記錄*/
    OPEN O_CUR1 FOR 'SELECT '||V_COLUMN||'
                     FROM
                     (
                       SELECT '||V_COLUMN||',COUNT(*) CNT FROM '||V_TABLE||' GROUP BY NAME
                      )
                     WHERE CNT >1';
    LOOP
      FETCH O_CUR1 INTO V_NUM;
      V_NUM := V_VAR||V_NUM||V_VAR;
      EXIT WHEN O_CUR1%NOTFOUND;
      SQLTXT := 'UPDATE '||V_TABLE||' SET '||V_COLUMN||'='||V_COLUMN||'||ROWNUM WHERE '||V_COLUMN||' = '||V_NUM;
      EXECUTE IMMEDIATE SQLTXT;
    END LOOP;
END;