sql删除表中的重复记录
来源：未知 责任编辑：责任编辑 发表时间:2014-01-25 11:37 点击:次
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;
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
