oracle层级关系按列存储_oracle_存储过程_有参数_获取部门装置层级树

create or replace procedure P_UTIL_TREE(P_APPL_NAME in VARCHAR2,

P_HIERARCHY_TYP in VARCHAR2,

TREETYPE in varchar2,

USERCODE in VARCHAR2,

P_CUR OUT SYS_REFCURSOR) is

begin

OPEN P_CUR FOR

with v_util_tree as

(select DISTINCT H.PARENT_EQ_NAME,

H.EQ_NAME,

E.DSCR,

H.SEQ,

W.EDITABLE,

E.TYP

from ip_equip_hierarchy h,

ip_eq E,

(SELECT U.ROLE_ID, T.HIERARCHY_TYP, T.EQ_NAME, T.EDITABLE

FROM A_PT_ROLES_PURVIEW T, A_PT_ROLES_USERS U

WHERE T.HIERARCHY_TYP = P_HIERARCHY_TYP

AND T.ROLE_ID = U.ROLE_ID

AND upper(U.USER_ID) = upper(USERCODE)) w

WHERE H.HIERARCHY_TYP = P_HIERARCHY_TYP

AND H.EQ_NAME = E.EQ_NAME

and e.typ <> 'TANK'

and e.typ <> 'LGTANK'

and e.typ <> 'POST'

AND H.HIERARCHY_TYP = W.HIERARCHY_TYP(+)

AND H.EQ_NAME = W.EQ_NAME(+)

start with H.EQ_NAME in (SELECT T.EQ_NAME

FROM A_PT_ROLES_PURVIEW T, A_PT_ROLES_USERS U

WHERE T.HIERARCHY_TYP = P_HIERARCHY_TYP

AND T.ROLE_ID = U.ROLE_ID

AND upper(U.USER_ID) = upper(USERCODE))

CONNECT BY PRIOR H.PARENT_EQ_NAME = H.EQ_NAME

AND H.HIERARCHY_TYP = P_HIERARCHY_TYP

UNION ALL

SELECT '0',EQ_NAME, DSCR, 0 AS SEQ, '', TYP

FROM V_IP_EQ

WHERE EQ_NAME = '$ASSET'

ORDER BY SEQ )

SELECT DSCR as TEXT, EQ_NAME as ID, PARENT_EQ_NAME, TYP

FROM v_util_tree A;

end P_UTIL_TREE;