一道面试题,要求写成存储过程且使用游标,当时胡乱写了一个,编程依赖GUI环境,很多单词拼写错误,巨汗!回来后整理一个!

CREATE OR REPLACE PROCEDURE DELETE_REPEAT

/*****************************************************

  FUNCTION : 删除表Hero_repeat表中c_code,c_name重复的且剩一条  

  AUTHOR   : Hero  QQ:305334565

  TOOL     : PL/SQL Developer 7.1.5.1398

  DATE     : 02/14/2011

  MODIFIED  (MM/DD/YYYY)   NOTE

  *******************************************************/

(OUT_MSG OUT NUMBER) --返回 0成功,-1失败

 IS

  -- define cursor

  CURSOR CUR_DELETE_REP IS

    SELECT C_CODE, C_NAME, MIN(ROWID) RN

      FROM HERO_REPEAT HR

     GROUP BY HR.C_CODE, HR.C_NAME

    HAVING COUNT(*) > 1;

  R CUR_DELETE_REP%ROWTYPE;

BEGIN

  --方法一:根据游标取出重复的值,删除除了rowid最小的重复数据

  --open cursor

  OPEN CUR_DELETE_REP;

  LOOP

    FETCH CUR_DELETE_REP

      INTO R;

    DELETE FROM HERO_REPEAT HR

     WHERE ROWID <> R.RN

       AND HR.C_CODE = R.C_CODE

       AND HR.C_NAME = R.C_NAME;

    EXIT WHEN CUR_DELETE_REP%NOTFOUND;

  END LOOP;

  CLOSE CUR_DELETE_REP;

  --方法二:使用for loop删除

  /*  FOR X IN (SELECT C_CODE, C_NAME, MIN(ROWID) RN

                FROM HERO_REPEAT HR

               GROUP BY HR.C_CODE, HR.C_NAME

              HAVING COUNT(*) > 1) LOOP

      DELETE FROM HERO_REPEAT HR

       WHERE ROWID <> X.RN

         AND HR.C_CODE = X.C_CODE

         AND HR.C_NAME = X.C_NAME;

    END LOOP;

  */

  OUT_MSG := 0; --成功

EXCEPTION

  WHEN OTHERS THEN

    OUT_MSG := -1; --失败

    --记录异常信息

    --PRC_ERROR_LOG('DELETE_REPEAT', '出现了未知的错误'); 

    RETURN;

END;

  /*

  --create test data; 

  DROP TABLE HERO_REPEAT;

  CREATE TABLE hero_repeat AS

  SELECT '01' C_CODE, 'abc' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '01' C_CODE, 'abc' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '01' C_CODE, 'abc' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '01' C_CODE, 'abc' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '02' C_CODE, 'efg' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '02' C_CODE, 'efg' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '02' C_CODE, 'efg' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '02' C_CODE, 'fg' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '03' C_CODE, 'abc' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '03' C_CODE, 'abc' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '03' C_CODE, 'hjk' C_NAME

    FROM DUAL

  UNION ALL

  SELECT '03' C_CODE, 'hjk' C_NAME FROM DUAL;

  */