sql删除表中的反复记录[MSSQL防范]

　　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;

　　作者 Zhijie.Geng