MAXIMO查询所有菜单

CREATE OR REPLACE PROCEDURE searchMenuByUserId(inuserid IN varchar,
                                               menucur  OUT sys_refcursor,
                                               errorMsg OUT varchar) IS
BEGIN
  errorMsg := '';
  OPEN menucur FOR
    select *
      from (
           --查询一级菜单
            (select t.position APP,
                    'MODULE' appType,
                    '' APPURL,
                    mod.description,
                    '' mainTbName,
                    '5' maxAppsId,
                    t.position ORDERID,
                    NULL PARENT
               from maxmenu t, maxmodules mod
              where t.keyvalue = mod.module
                and t.menutype = 'MODULE'
                and t.elementtype = 'MODULE'
                and t.visible = 1
                and t.moduleapp in
                    (select m.moduleapp
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.elementtype = 'APP'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = inuserid)
                                and a.optionname = 'READ'))) union all

           --查询二级菜单
            (select t.position app,
                    'MODULE' appType,
                    '' APPURL,
                    t.headerdescription description,
                    '' mainTbName,
                    '5' maxAppsId,
                    t.position ORDERID,
                    m.position parent
               from maxmenu t,
                    (select t.position, mod.module
                       from maxmenu t, maxmodules mod
                      where t.keyvalue = mod.module
                        and t.menutype = 'MODULE'
                        and t.elementtype = 'MODULE'
                        and t.visible = 1
                        and t.moduleapp in
                            (select m.moduleapp
                               from maxmenu m
                              where m.menutype = 'MODULE'
                                and m.elementtype = 'APP'
                                and m.keyvalue in
                                    (select distinct a.app
                                       from applicationauth a
                                      where a.groupname in
                                            (select g.groupname
                                               from groupuser g
                                              where g.userid = inuserid)
                                        and a.optionname = 'READ'))) m,
                          (select POSITION,MODULEAPP,COUNT(1) CONUNTNUM from maxmenu m2,maxapps app where m2.keyvalue=app.app and m2.Elementtype='APP'
                          and M2.keyvalue in
                          (select m.keyvalue
                             from maxmenu m
                            where m.menutype = 'MODULE'
                              and m.keyvalue in
                                  (select distinct a.app
                                     from applicationauth a
                                    where a.groupname in
                                          (select g.groupname
                                             from groupuser g
                                            where g.userid = inuserid)
                                      and a.optionname = 'READ'))
                           GROUP BY POSITION,MODULEAPP HAVING COUNT(1)>0) CON
              where t.menutype = 'MODULE'
                and t.elementtype = 'HEADER'
                and t.moduleapp = m.module
                AND t.position=CON.POSITION
                AND T.moduleapp=CON.moduleapp
                and t.visible = 1
                and t.moduleapp in
                    (select m.moduleapp
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = inuserid)
                                and a.optionname = 'READ')))

            union all

           --查询二级应用
            (select t.position app,
                    'APP' appType,
                    p.app APPURL,
                    p.description,
                    '' mainTbName,
                    '5' maxAppsId,
                    t.position ORDERID,
                    m.position parent
               from maxmenu t,
                    maxapps p,
                    (select t.position, mod.module
                       from maxmenu t, maxmodules mod
                      where t.keyvalue = mod.module
                        and t.menutype = 'MODULE'
                        and t.elementtype = 'MODULE'
                        and t.visible = 1
                        and t.moduleapp in
                            (select m.moduleapp
                               from maxmenu m
                              where m.menutype = 'MODULE'
                                and m.elementtype = 'APP'
                                and m.keyvalue in
                                    (select distinct a.app
                                       from applicationauth a
                                      where a.groupname in
                                            (select g.groupname
                                               from groupuser g
                                              where g.userid = inuserid)
                                        and a.optionname = 'READ'))) m
              where t.keyvalue = p.app
                and t.menutype = 'MODULE'
                and t.elementtype = 'APP'
                and t.moduleapp = m.module
                and t.subposition = 0
                and t.visible = 1
                and t.moduleapp in
                    (select m.moduleapp
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = inuserid)
                                and a.optionname = 'READ')))

            union all

           --查询三级菜单
            (select t.subposition app,
                    'APP' appType,
                    p.app APPURL,
                    p.description,
                    '' mainTbName,
                    '5' maxAppsId,
                    t.subposition ORDERID,
                    pa.position parent
               from maxmenu t,
                    maxapps p,
                    (select t.position, M.module
                       from maxmenu t,
                            (select t.position, mod.module
                               from maxmenu t, maxmodules mod
                              where t.keyvalue = mod.module
                                and t.menutype = 'MODULE'
                                and t.elementtype = 'MODULE'
                                and t.visible = 1
                                and t.moduleapp in
                                    (select m.moduleapp
                                       from maxmenu m
                                      where m.menutype = 'MODULE'
                                        and m.elementtype = 'APP'
                                        and m.keyvalue in
                                            (select distinct a.app
                                               from applicationauth a
                                              where a.groupname in
                                                    (select g.groupname
                                                       from groupuser g
                                                      where g.userid = inuserid)
                                                and a.optionname = 'READ'))) m
                      where t.menutype = 'MODULE'
                        and t.elementtype = 'HEADER'
                        and t.moduleapp = m.module
                        and t.visible = 1
                        and t.moduleapp in
                            (select m.moduleapp
                               from maxmenu m
                              where m.menutype = 'MODULE'
                                and m.keyvalue in
                                    (select distinct a.app
                                       from applicationauth a
                                      where a.groupname in
                                            (select g.groupname
                                               from groupuser g
                                              where g.userid = inuserid)
                                        and a.optionname = 'READ'))) PA
              where t.keyvalue = p.app
                and t.menutype = 'MODULE'
                and t.elementtype = 'APP'
                and t.moduleapp = PA.module
                AND T.position = PA.position
                and t.visible = 1
                and t.keyvalue in
                    (select m.keyvalue
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = inuserid)
                                and a.optionname = 'READ')))) menu
     order by app
     ;
     EXCEPTION WHEN OTHERS THEN errorMsg := sqlerrm; -- sqlcode是异常编号，sqlerrm是异常的详细信息
END searchMenuByUserId;