以前用SQL实现的机构职能树,再看看

时间:2023-09-20 13:49:56
DECLARE
CNT NUMBER;
TMP VARCHAR2(20);
vtmp VARCHAR2(22);
ltmp1 VARCHAR2(1000);
ltmp2 VARCHAR2(22);
ltmp3 VARCHAR2(22);
ltmp4 VARCHAR2(22);
NTMP NUMBER;
CURSOR CSR_TEST IS
SELECT O.SWJG_BM, O.SJ_SWJG_BM
FROM DB_NEU.T_XT_ORG O
WHERE O.SWJG_BM NOT IN (SELECT T.SWJG_BM FROM DB_NEU.T_XT_SWJG_ZN T);
R_TEST CSR_TEST%ROWTYPE; BEGIN
SELECT COUNT(*)
INTO CNT
FROM DB_NEU.T_XT_ORG O
WHERE O.SWJG_BM NOT IN (SELECT T.SWJG_BM FROM DB_NEU.T_XT_SWJG_ZN T);
DBMS_OUTPUT.PUT_LINE(CNT);
OPEN CSR_TEST;
FETCH CSR_TEST
INTO R_TEST;
tmp:=r_test.swjg_bm; /*保存当前需要处理的机关*/
WHILE CNT > 0 LOOP
/*DBMS_OUTPUT.PUT_LINE( r_test.swjg_bm);*/
SELECT COUNT(*)
INTO NTMP
FROM DB_NEU.T_XT_SWJG_ZN T
WHERE T.SWJG_BM = R_TEST.SJ_SWJG_BM;
DBMS_OUTPUT.PUT_LINE('ntmp:' || NTMP);
WHILE NTMP <> 1 LOOP /*父亲节点没在职能表中,那么需要取父亲的父亲*/
DBMS_OUTPUT.PUT_LINE('cnt' || CNT);
SELECT NVL(O.SJ_SWJG_BM, O.SWJG_BM) /**的话就取自己*/
INTO TMP
FROM T_XT_ORG O
WHERE O.SWJG_BM = R_TEST.SJ_SWJG_BM;
SELECT COUNT(*)
INTO NTMP
FROM DB_NEU.T_XT_SWJG_ZN T
WHERE T.SWJG_BM = tmp;
END LOOP;
/*DBMS_OUTPUT.PUT_LINE('cnt++' || CNT);*/
DBMS_OUTPUT.PUT_LINE('swjg_bm:' || tmp);
/*实际处理的代码begin*/
vtmp:=CHR(39)||tmp||CHR(39);
dbms_output.put_line('vtmp:'||vtmp);
SELECT /*O.SWJG_BM, O.SJ_SWJG_BM,*/ TTT.CC INTO ltmp1 /*, '00' AS ZN_DM*/
FROM T_XT_ORG O,
(SELECT CASE
WHEN TT.SWJG_BM =
(SELECT SWJG_BM
FROM (WITH TMP AS (SELECT O.*,
LEVEL LEV,
CONNECT_BY_ISLEAF LEAF
FROM T_XT_ORG O
START WITH O.SWJG_BM =
(SELECT SJ_SWJG_BM
FROM T_XT_ORG TA
WHERE TA.SWJG_BM =
vtmp)
CONNECT BY PRIOR
O.SWJG_BM = O.SJ_SWJG_BM)
SELECT * FROM TMP WHERE LEV = 1)
) THEN
CONCAT(CC, CHR(49))
WHEN LASTCODE < 56 THEN
CONCAT(SRC, CHR(LASTCODE + 1))
WHEN LASTCODE = 56 OR LASTCODE < 89 THEN
CONCAT(SRC, CHR(LASTCODE + 1))
WHEN LASTCODE = 89 OR LASTCODE < 121 THEN
CONCAT(SRC, CHR(LASTCODE + 1))
END CC,
vtmp AS SWJG
FROM (SELECT T.CC,
T.SWJG_BM,
SUBSTR(T.CC, -LENGTH(T.CC), LENGTH(T.CC) - 1) SRC,
ASCII(SUBSTR(T.CC, -1)) LASTCODE
FROM DB_NEU.T_XT_SWJG_ZN T
WHERE T.CC =
(SELECT MAX(ZZ.CC)
FROM DB_NEU.T_XT_SWJG_ZN ZZ
WHERE ZZ.SWJG_BM IN
(SELECT SWJG_BM
FROM (WITH TMP AS (SELECT O.*,
LEVEL LEV,
CONNECT_BY_ISLEAF LEAF
FROM T_XT_ORG O
START WITH O.SWJG_BM =
(SELECT SJ_SWJG_BM
FROM T_XT_ORG TA
WHERE TA.SWJG_BM =
vtmp)
CONNECT BY PRIOR O.SWJG_BM =
O.SJ_SWJG_BM)
SELECT *
FROM TMP
WHERE LEV =
(SELECT LEV
FROM TMP T
WHERE T.SWJG_BM = vtmp)
/*AND LEAF = 1*/
ORDER BY SWJG_BM ASC) TT
WHERE TT.SWJG_BM NOT IN (vtmp)
/***查出直系父亲**/
UNION ALL
SELECT SWJG_BM
FROM (WITH TMP AS (SELECT O.*,
LEVEL LEV,
CONNECT_BY_ISLEAF LEAF
FROM T_XT_ORG O
START WITH O.SWJG_BM =
(SELECT SJ_SWJG_BM
FROM T_XT_ORG TA
WHERE TA.SWJG_BM =
vtmp)
CONNECT BY PRIOR
O.SWJG_BM =
O.SJ_SWJG_BM)
SELECT *
FROM TMP
WHERE LEV = 1) ))) TT) TTT
WHERE O.SWJG_BM = TTT.SWJG;
/*end*/
/* dbms_output.putline('ltmp1:'||ltmp1||'ltmp2:'||ltmp2||'ltmp3:'||ltmp3||'ltmp4:'||ltmp4);*/
dbms_output.put_line('ltmp1:'||ltmp1);
CNT := CNT - 1;
/*END IF;*/
FETCH CSR_TEST
INTO R_TEST;
tmp:=r_test.swjg_bm;
END LOOP;
CLOSE CSR_TEST;
END;