请各位大虾写个存储过程,返回一个xml格式字符串, 谢谢了...有两张表,分别是如下2张
user
--id
--name
--dpmtid --部门iddepartment
--id
--name
--upid 上级部门id, 根目录为0-------------返回的xml格式字符串--------------------------- <?xml version="1.0" encoding="UTF-8" ?>
<department>
<member id="10000048">liuyl4</member>
<member id="10000048">liuyl4</member> <depart name="一级" id="57008604">
<member id="10000048">liuyl4</member>
<depart name="二级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
<depart name="三级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<depart name="四级" id="48913940">
<member id="10000044">820127</member>
<member id="10000045">liu</member>
</depart>
</depart>
<depart name="三级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
<depart name="四级" id="48913940">
<member id="10000044">820127</member>
<member id="10000045">liu</member>
</depart>
</depart>
</depart>
<depart name="二级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
<depart name="三级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
</depart>
</depart>
</depart> <depart name="一级" id="57008604">
<member id="10000048">liuyl4</member>
</depart>
</department>
user
--id
--name
--dpmtid --部门iddepartment
--id
--name
--upid 上级部门id, 根目录为0-------------返回的xml格式字符串--------------------------- <?xml version="1.0" encoding="UTF-8" ?>
<department>
<member id="10000048">liuyl4</member>
<member id="10000048">liuyl4</member> <depart name="一级" id="57008604">
<member id="10000048">liuyl4</member>
<depart name="二级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
<depart name="三级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<depart name="四级" id="48913940">
<member id="10000044">820127</member>
<member id="10000045">liu</member>
</depart>
</depart>
<depart name="三级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
<depart name="四级" id="48913940">
<member id="10000044">820127</member>
<member id="10000045">liu</member>
</depart>
</depart>
</depart>
<depart name="二级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
<depart name="三级" id="48913940">
<member id="10000044">820127</member>
<member id="10000049">liuyanling222</member>
<member id="10000045">liu</member>
</depart>
</depart>
</depart> <depart name="一级" id="57008604">
<member id="10000048">liuyl4</member>
</depart>
</department>
select LEVEL LEVLE_NUM,
t.*
from t_dep t
START WITH ID = 0
CONNECT BY PRIOR ID = UPID
C2
SELECT USR.*
USER USR
WHERE USR.DEPID = 上面取到的ID
用这两个SQl取数据for c1 loop
for c2 loop
出数据;
(</depart>输出的判断方法:
LEVLE_NUM连续增大,不输出
LEVLE_NUM变小 输出的个数 大的LEVLE_NUM - 小的LEVLE_NUM + 1
至于缩紧格式那就自己试试吧,反正是LEVLE_NUM*N(例如4)倍的空格就行了)
end loop
end loop
只是简单的结构,自己完善吧!
我认为没问题,应该能实现。