oracle统计图形开发,oracle查询出树形结构显示，如以下截图形式

CREATE OR REPLACE FUNCTION F_GET_TRANS_FEEDER(BRANCHID NUMBER, MARK NUMBER)

RETURN VARCHAR2 IS

V_A VARCHAR2(200);

V_B NUMBER(10);

/*功能：　根据传入的branchid 以及列的位置　返回对应的线路名称

branchid ：台区所在的线路的ｄｅｖｉｄ

mark ：　　标志位　用于标志需要需要传入的列的位置 主线为0 支线为1 1级支线为2 所在的branchid 为3　*/

BEGIN

SELECT COUNT(1)

INTO V_B

FROM (SELECT T.DEVID,DEV_NAME,LEVEL

FROM NET_DF_BRANCH T

WHERE T.FLAGS IN (0, 10)

AND T.VER_FLAGS = 2

CONNECT BY PRIOR T.PARENTID = T.DEVID

START WITH T.DEVID = BRANCHID);

/*用于判定传入的branchiｄ的层级*/

IF V_B = 1 THEN

V_A := NULL;

/*对于只有主线的 返回的都为空*/

ELSIF V_B = 2 AND MARK = 1 THEN

SELECT N.DEV_NAME

INTO V_A

FROM NET_DF_BRANCH N

WHERE N.FLAGS IN (0, 10)

AND N.VER_FLAGS = 2

AND N.DEVID = BRANCHID;

ELSIF V_B = 2 AND MARK IN (2, 3) THEN

V_A := NULL;

/*对于有2层的 依次填入 第3列为空*/

ELSIF V_B = 3 AND MARK = 1 THEN

SELECT N1.DEV_NAME INTO V_A

FROM NET_DF_BRANCH N

INNER JOIN NET_DF_BRANCH N1

ON N.PARENTID = N1.DEVID

AND N1.VER_FLAGS = 2

AND N.FLAGS IN (0, 10)

WHERE N.FLAGS IN (0, 10)

AND N.VER_FLAGS = 2

AND N.DEVID = BRANCHID;

ELSIF V_B = 3 AND MARK = 2 THEN

SELECT DEV_NAME INTO V_A

FROM NET_DF_BRANCH T

WHERE T.FLAGS IN (0, 10)

AND T.VER_FLAGS = 2

AND LEVEL = 2

CONNECT BY PRIOR T.PARENTID = T.DEVID

START WITH T.DEVID = BRANCHID;

ELSIF V_B = 3 AND MARK = 3 THEN

V_A := NULL;

/*对于有3层的 其最后一列返回为空*/

ELSIF V_B > 3 AND MARK = 1 THEN

SELECT DEV_NAME

INTO V_A

FROM NET_DF_BRANCH T

WHERE T.FLAGS IN (0, 10)

AND T.VER_FLAGS = 2

AND LEVEL = 3

CONNECT BY PRIOR T.PARENTID = T.DEVID

START WITH T.DEVID = BRANCHID;

ELSIF V_B > 3 AND MARK = 2THEN

SELECT DEV_NAME

INTO V_A

FROM NET_DF_BRANCH T

WHERE T.FLAGS IN (0, 10)

AND T.VER_FLAGS = 2

AND LEVEL = 2

CONNECT BY PRIOR T.PARENTID = T.DEVID

START WITH T.DEVID = BRANCHID; ELSIF V_B > 3 AND MARK = 3

THEN

SELECT N.DEV_NAME

INTO V_A

FROM NET_DF_BRANCH N

WHERE N.FLAGS IN (0, 10)

AND N.VER_FLAGS = 2

AND N.DEVID = BRANCHID;

/*对于层级关系在4层已经4层以上 则取主线以及所在的branchid和branchid 的上一级 一级上两级*/

ELSE

v_a:=NULL;

/*对于传入错误的 返回为空*/

END IF;

RETURN V_A;

EXCEPTION

WHEN OTHERS THEN

RETURN -1;

/*异常则返回为-1*/

END;

/