自己写的存储过程 sql树 删除表中重复记录

CREATE OR REPLACE PROCEDURE getUserMenuResource(roid IN Varchar2)
AS
BEGIN
  DELETE FROM BOCO_TEMP_MENURESOURCE;
  COMMIT;
  --读出所有角色表所对应的菜单ID
  DECLARE CURSOR records IS SELECT a.funcid FROM boco_rolemenurelation a WHERE a.roleid=roid ORDER BY a.funcid;
  BEGIN
    FOR cur IN records LOOP
        --SELECT a.displayname,a.url,a.pid,a.icon,a.disorder,a.code,a.isshow INTO BOCO_TEMP_MENURESOURCE(displayname,url,pid,icon,disorder,code,isshow) FROM boco_menuresource a START WITH a.CODE =cur.funcid CONNECT BY a.PID = PRIOR a.code  ;
        --Dbms_Output.put_line(cur.funcid);
         Dbms_Output.put_line(cur.funcid );
        --遍历所有根菜单下的所有子菜单项，将其写入 BOCO_TEMP_MENURESOURCE 表
        INSERT INTO  BOCO_TEMP_MENURESOURCE(DISPLAYNAME,
                              URL,
                              PID,
                              ICON,
                              DISORDER,
                              CODE,
                              ISSHOW)
SELECT A.DISPLAYNAME, A.URL, A.PID, A.ICON, A.DISORDER, A.CODE, A.ISSHOW FROM BOCO_MENURESOURCE A START WITH A.CODE =cur.funcid CONNECT BY A.PID = PRIOR A.code  ;
    END
    LOOP;
    EXCEPTION
        WHEN OTHERS THEN
             Dbms_Output.put_line(SQLERRM);
  END;
  --删除表中重复菜单记录项  
  DELETE FROM boco_temp_menuresource
  WHERE code IN (SELECT code FROM 
  boco_temp_menuresource GROUP BY code HAVING COUNT(code)>1)
  AND ROWID NOT IN (SELECT MIN(ROWID) FROM boco_temp_menuresource GROUP BY code HAVING COUNT(code)>1);

  --SELECT * FROM BOCO_TEMP_MENURESOURCE;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE GETUSERMENURESOURCE(ORGCODE IN VARCHAR2) AS
  V_SQL VARCHAR2(1000); --查询字符串
  V_PID NVARCHAR2(20); --父节点
  TYPE CUR_TYPE IS REF CURSOR;
  CUR_FIELDLIST CUR_TYPE;

  V_FUNCID NVARCHAR2(10); --菜单CODE  

BEGIN

  DBMS_OUTPUT.PUT_LINE('***********执行开始...');
  DBMS_OUTPUT.PUT_LINE('***********orgCode：' || ORGCODE);
  DBMS_OUTPUT.PUT_LINE('************');

  --删除临时表数据
  DELETE FROM T_TYBOOK_BACK_TMP_MENURESOURCE;
  COMMIT;
  --查询PID项，看是否要根节点
  SELECT PID
    INTO V_PID
    FROM T_TYBOOK_BACK_ORGANIZE A
   WHERE A.CODE = ORGCODE;

  IF V_PID IS NULL THEN
    --查询所有菜单 
    V_SQL := 'SELECT code  FROM T_TYBOOK_BACK_MENURESOURCE';
  ELSE
    --规则为二级组织和一级组织拥有同样权限
    SELECT PID
      INTO V_PID
      FROM T_TYBOOK_BACK_ORGANIZE A
     WHERE A.CODE = V_PID;
    IF V_PID IS NULL THEN
      V_SQL := 'SELECT code  FROM T_TYBOOK_BACK_MENURESOURCE';
    ELSE
      --查询包含子菜单在内所有上级组织所对应的菜单
      --v_sql:='SELECT M.FUNCID FROM (SELECT * FROM t_tybook_back_organize a START WITH a.code =''' || orgCode || '''  CONNECT BY PRIOR a.pid = a.code) T    JOIN t_tybook_back_orgmenu M ON T.CODE=M.ORG_CODE' ;
    
      --查询直属菜单
      V_SQL := 'SELECT M.Funcid FROM  (SELECT * FROM T_TYBOOK_BACK_ORGANIZE A  WHERE A.CODE = ''' ||
               ORGCODE || ''' UNION ';
      V_SQL := V_SQL ||
               ' SELECT * FROM T_TYBOOK_BACK_ORGANIZE B  WHERE B.CODE = (SELECT pid FROM T_TYBOOK_BACK_ORGANIZE A  WHERE A.CODE = ''' ||
               ORGCODE ||
               ''')) T INNER JOIN t_tybook_back_orgmenu M ON T.CODE=M.ORG_CODE ';
    
    END IF;
  END IF;

  DBMS_OUTPUT.PUT_LINE('************步骤1：查询直属菜单');
  DBMS_OUTPUT.PUT_LINE('------------' || V_SQL);

  --打开游标 SQL 集合
  OPEN CUR_FIELDLIST FOR V_SQL;
  DBMS_OUTPUT.PUT_LINE('************打开游标成功：cur_fieldlist');
  DBMS_OUTPUT.PUT_LINE('************');
  DBMS_OUTPUT.PUT_LINE('************执行循环遍历');
  DBMS_OUTPUT.PUT_LINE('************步骤2：取出包含当前菜单在内的下级的所有子菜单');
  LOOP
    --遍历游标数据，将每个菜单下的所有子菜单项写时临时表中            
    FETCH CUR_FIELDLIST
      INTO V_FUNCID;
    DBMS_OUTPUT.PUT_LINE('************功能菜单编码：' || V_FUNCID);
  
    EXIT WHEN CUR_FIELDLIST%NOTFOUND;
  
    --在菜单表中以 v_funcid 为根，查找根下面的所有子菜单项，将其写入临时表中
    INSERT INTO T_TYBOOK_BACK_TMP_MENURESOURCE
      (DISPLAYNAME, URL, PID, ICON, DISORDER, CODE, ISSHOW)
      SELECT A.DISPLAYNAME,
             A.URL,
             A.PID,
             A.ICON,
             A.DISORDER,
             A.CODE,
             A.ISSHOW
        FROM T_TYBOOK_BACK_MENURESOURCE A
       START WITH A.CODE = V_FUNCID
      CONNECT BY A.PID = PRIOR A.CODE;
    DBMS_OUTPUT.PUT_LINE('************' ||
                         'INSERT INTO  T_TYBOOK_BACK_TMP_MENURESOURCE(DISPLAYNAME, URL,PID,ICON,DISORDER,CODE,ISSHOW) SELECT A.DISPLAYNAME, A.URL, A.PID, A.ICON, A.DISORDER, A.CODE, A.ISSHOW FROM T_TYBOOK_BACK_MENURESOURCE A START WITH A.CODE =''' ||
                         V_FUNCID || '''  CONNECT BY A.PID = PRIOR A.code');
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('************');
  DBMS_OUTPUT.PUT_LINE('************删除表重复记录');

  --删除表重复记录
  DELETE FROM T_TYBOOK_BACK_TMP_MENURESOURCE
   WHERE CODE IN (SELECT CODE
                    FROM T_TYBOOK_BACK_TMP_MENURESOURCE
                   GROUP BY CODE
                  HAVING COUNT(CODE) > 1)
     AND ROWID NOT IN (SELECT MIN(ROWID)
                         FROM T_TYBOOK_BACK_TMP_MENURESOURCE
                        GROUP BY CODE
                       HAVING COUNT(CODE) > 1);

  DBMS_OUTPUT.PUT_LINE('************');
  DBMS_OUTPUT.PUT_LINE('************继承菜单项：');

  --此表中URL做为特殊处理字段，如果为非 orgCode 下所拥有的菜单，则 "_+URL" 来标识为继承权限， 更新临时表数据URL
  --查询自身菜单所对应的菜单权限
  --DECLARE CURSOR records IS SELECT a.funcid FROM  t_tybook_back_orgmenu a WHERE a.org_code=orgCode; 

  --查询包含子菜单在内所有上级组织所对应的菜单
  DECLARE
    CURSOR RECORDS IS
      SELECT T.CODE
        FROM (SELECT *
                FROM T_TYBOOK_BACK_ORGANIZE A
               START WITH A.CODE = ORGCODE
              CONNECT BY PRIOR A.PID = A.CODE) T;
  BEGIN
    FOR CUR IN RECORDS LOOP
      IF CUR.CODE != ORGCODE THEN
        DBMS_OUTPUT.PUT_LINE('************CODE:' || CUR.CODE);
        --查询所有组织对应的菜单
        DECLARE
          CURSOR RS IS
            SELECT B.FUNCID
              FROM T_TYBOOK_BACK_ORGMENU B
             WHERE B.ORG_CODE = CUR.CODE;
        BEGIN
          FOR R IN RS LOOP
            --更新选择状态
            UPDATE T_TYBOOK_BACK_TMP_MENURESOURCE A
               SET A.DESCRIPTION = '*',A.DISPLAYNAME= CASE WHEN instr(A.Displayname,'上)',1)>0 THEN A.DISPLAYNAME ELSE '(上)'||A.DISPLAYNAME END   
             WHERE A.CODE = R.FUNCID;
 
          END LOOP;
        END;
      
      END IF;
    
    END LOOP;
  END;

  --更新orgCode所对应的菜单权限 
  DECLARE
    CURSOR RES IS
      SELECT B.FUNCID
        FROM T_TYBOOK_BACK_ORGMENU B
       WHERE B.ORG_CODE = ORGCODE;
  BEGIN
    FOR RE IN RES LOOP
      UPDATE T_TYBOOK_BACK_TMP_MENURESOURCE A
         SET A.DESCRIPTION = '_'
       WHERE A.CODE = RE.FUNCID;
    END LOOP;
  END;

  DBMS_OUTPUT.PUT_LINE('************');
  DBMS_OUTPUT.PUT_LINE('***********执行结束...');
  CLOSE CUR_FIELDLIST;
  COMMIT;

END;