自己写的存储过程 sql树 删除表中重复记录
CREATE OR REPLACE PROCEDURE getUserMenuResource(roid IN Varchar2)ASBEGIN 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;