oracle存储过程解析json,存储过程解析表菜单并输出JSON格式字符串

最新推荐文章于 2024-05-22 17:56:31 发布

weixin_39862669

最新推荐文章于 2024-05-22 17:56:31 发布

阅读量691

点赞数

文章标签： SQL 存储过程 JSON 菜单结构权限查询

表结构如下：

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[T_4s_Menu](

[F_MenuID] [int] IDENTITY(1,1) NOT NULL,

[F_MenuName] [nvarchar](50) NULL,

[F_MenuUrl] [varchar](100) NULL,

[F_ParentID] [int] NULL,

[F_MenuCode] [varchar](10) NULL,

[F_Order] [int] NULL,

[F_IsMenu] [bit] NULL,

[F_IsWindow] [bit] NULL,

CONSTRAINT [PK_T_4s_Menu] PRIMARY KEY CLUSTERED

(

[F_MenuID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

存储过程如下：

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author:

-- Create date: <20130729>

-- Description:

-- =============================================

ALTER PROCEDURE [dbo].[pr_4sMenu2JSON] (@i_AdminID int,@i_GroupID int,@s_ReturnJson varchar(5000) OUTPUT)

BEGIN

SET NOCOUNT ON;

DECLARE @JSON varchar(5000);--要输出的JSON字符串

DECLARE @root_MenuCount int;--根菜单数量

DECLARE @root_MenuID int;--根菜单ID

DECLARE @root_MenuName nvarchar(50);--根菜单名称

DECLARE @root_MenuCode varchar(10);--根菜单编码

DECLARE @Return_Rules varchar(3000);--返回查询的权限

IF(@i_AdminID=-1 AND @i_GroupID=-1) --全部

BEGIN

SELECT @Return_Rules=',"Rules":"Null"';

END

ELSE

BEGIN

IF(@i_AdminID>-1)--指定4SID

BEGIN

SELECT @Return_Rules=',{'+[F_Rules]+'}' FROM [T_4s] WHERE [F_ID][email protected]_AdminID

END

ELSE --指定用户ID

BEGIN

SELECT @Return_Rules=',{'+[F_UserPower]+'}' FROM [T_4s_Users] WHERE [ID][email protected]_GroupID

END

SELECT @root_MenuCount=COUNT(0) FROM T_4s_Menu WHERE F_ParentID=1 AND F_MenuID<>1

SELECT @JSON='[{"count":'+CONVERT(varchar(10),@root_MenuCount)+',"list":[';

DECLARE @i int=1;--根菜单循环计数器，用来判断是不是读取最后一条记录

DECLARE cur_Root CURSOR FOR

SELECT [F_MenuID],[F_MenuName],[F_MenuCode] FROM T_4s_Menu WHERE F_ParentID=1 AND F_MenuID<>1 order by F_Order asc

OPEN cur_Root

FETCH NEXT FROM cur_Root INTO @root_MenuID, @root_MenuName,@root_MenuCode

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @MenuID int;--子菜单ID

DECLARE @MenuName nvarchar(50);--子菜单名称

DECLARE @MenuCode varchar(10);--子菜单编码

DECLARE @MenuUrl varchar(100);--子菜单URL

DECLARE @IsMenu bit;--子菜单是否显示

DECLARE @MenuCount int;--子菜单数量

DECLARE @j int=1;--子菜单循环计数器，用来判断是否是最后一条记录

SELECT @MenuCount=COUNT(0) FROM T_4s_Menu WHERE [email protected]_MenuID

DECLARE cur_Menu CURSOR FOR

SELECT [F_MenuID],[F_MenuName],[F_MenuCode],[F_MenuUrl],[F_IsMenu] FROM T_4s_Menu WHERE [email protected]_MenuID order by F_Order asc

OPEN cur_Menu

FETCH NEXT FROM cur_Menu INTO @MenuID, @MenuName,@MenuCode,@MenuUrl,@IsMenu

IF @[email protected]_MenuCount --最后一行数据

BEGIN

SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@root_MenuID)+',"MenuName":"'[email protected]_MenuName+'","MenuCode":"'[email protected]_MenuCode+'","MenuCount":'+CONVERT(varchar(10),@MenuCount)+',"MenuList":['

WHILE @@FETCH_STATUS = 0

BEGIN

IF @[email protected]

BEGIN

SELECT @JSON+='{"MenuID":'+CONVERT(varchar(10),@MenuID)+',"MenuName":"'[email protected]+'","MenuCode":"'[email protected]+'","MenuUrl":"'[email protected]+'","IsMenu":"'+convert(varchar(8),@IsMenu)+'"'

SELECT @JSON+='}]'

END

ELSE

BEGIN

SELECT @JSON+='},'

SELECT @j+=1;

END

FETCH NEXT FROM cur_Menu INTO @MenuID, @MenuName,@MenuCode,@MenuUrl,@IsMenu

END

SELECT @JSON+='}'

END

ELSE

BEGIN

WHILE @@FETCH_STATUS = 0

BEGIN

IF @[email protected]

BEGIN

SELECT @JSON+='}]'

END

ELSE

BEGIN

SELECT @JSON+='},'

SELECT @j+=1;

END

FETCH NEXT FROM cur_Menu INTO @MenuID, @MenuName,@MenuCode,@MenuUrl ,@IsMenu

END

SELECT @JSON+='},'

SELECT @i+=1;

END

CLOSE cur_Menu

DEALLOCATE cur_Menu

FETCH NEXT FROM cur_Root INTO @root_MenuID, @root_MenuName,@root_MenuCode

END

CLOSE cur_Root

DEALLOCATE cur_Root

SELECT @JSON+=']'[email protected]_Rules+'}]';

SELECT @s_ReturnJson= @JSON

END

结果如下：

[{"count":7,"list":[{"MenuID":2,"MenuName":"信息管理","MenuCode":"NS","MenuCount":2,"MenuList":[{"MenuID":3,"MenuName":"信息发布","MenuCode":"NS_Edit","MenuUrl":"Manage/News/NewsEdit.aspx","IsMenu":"1"},{"MenuID":17,"MenuName":"新闻列表","MenuCode":"NS_NL","MenuUrl":"Manage/News/NewsList.aspx","IsMenu":"1"}]},{"MenuID":4,"MenuName":"新车管理","MenuCode":"4C","MenuCount":2,"MenuList":[{"MenuID":5,"MenuName":"车辆报价","MenuCode":"4C_PC","MenuUrl":"Manage/Cars/CarPrice.aspx","IsMenu":"1"},{"MenuID":16,"MenuName":"所售车型","MenuCode":"4C_CL","MenuUrl":"Manage/Cars/SellCarList.aspx","IsMenu":"1"}]},{"MenuID":6,"MenuName":"留言咨询","MenuCode":"CM","MenuCount":4,"MenuList":[{"MenuID":7,"MenuName":"试乘试驾","MenuCode":"CM_App","MenuUrl":"Manage/Application/ApplicationList.aspx","IsMenu":"1"},{"MenuID":8,"MenuName":"底价咨询","MenuCode":"CM_zxdj","MenuUrl":"Manage/Comment/zxdj.aspx","IsMenu":"0"},{"MenuID":9,"MenuName":"客户留言","MenuCode":"CM_leave","MenuUrl":"Manage/LeaveMessage/LeaveMessage_List.aspx","IsMenu":"1"},{"MenuID":20,"MenuName":"站内消息","MenuCode":"CM_Mes","MenuUrl":"Manage/Message/Message_List.aspx","IsMenu":"0"}]},{"MenuID":26,"MenuName":"二手车管理","MenuCode":"2S","MenuCount":8,"MenuList":[{"MenuID":31,"MenuName":"二手车列表","MenuCode":"2S_List","MenuUrl":"Manage/2Shou/2CarList.aspx","IsMenu":"1"},{"MenuID":30,"MenuName":"二手车添加","MenuCode":"2S_ADD","MenuUrl":"Manage/2Shou/2CarAdd.aspx","IsMenu":"1"},{"MenuID":28,"MenuName":"已售车辆","MenuCode":"2S_Selled","MenuUrl":"Manage/2Shou/selled.aspx","IsMenu":"0"},{"MenuID":27,"MenuName":"竞价平台","MenuCode":"2S_P","MenuUrl":"Manage/2Shou/p.aspx","IsMenu":"1"},{"MenuID":29,"MenuName":"二手车修改","MenuCode":"2S_Edit","MenuUrl":"Manage/2Shou/2CarEdit.aspx","IsMenu":"0"},{"MenuID":38,"MenuName":"我的拍卖","MenuCode":"2S_MyP","MenuUrl":"Manage/2Shou/mysell.aspx","IsMenu":"0"},{"MenuID":39,"MenuName":"竞拍终端页","MenuCode":"2S_PZD","MenuUrl":"Manage/2Shou/2CarView.aspx","IsMenu":"0"},{"MenuID":40,"MenuName":"我的竞拍","MenuCode":"2S_MYPP","MenuUrl":"Manage/2Shou/my_p.aspx","IsMenu":"1"}]},{"MenuID":34,"MenuName":"团购管理","MenuCode":"4s_Tuan","MenuCount":4,"MenuList":[{"MenuID":35,"MenuName":"添加团购","MenuCode":"4s_AddTuan","MenuUrl":"Manage/tuan/tuanadd.aspx","IsMenu":"1"},{"MenuID":37,"MenuName":"团购上传","MenuCode":"Tuan_Up","MenuUrl":"Manage/tuan/tuanpicupload.aspx","IsMenu":"0"},{"MenuID":42,"MenuName":"团购列表","MenuCode":"4s_TuanLis","MenuUrl":"Manage/tuan/tuanList.aspx","IsMenu":"1"},{"MenuID":43,"MenuName":"参加团购人员","MenuCode":"4s_tuanPeo","MenuUrl":"Manage/tuan/tuanListPeople.aspx","IsMenu":"0"}]},{"MenuID":41,"MenuName":"推广功能","MenuCode":"TG","MenuCount":1,"MenuList":[{"MenuID":15,"MenuName":"我要推广","MenuCode":"TG_Push","MenuUrl":"Manage/Extend/MyExtend.aspx","IsMenu":"1"}]},{"MenuID":10,"MenuName":"系统功能","MenuCode":"CF","MenuCount":6,"MenuList":[{"MenuID":11,"MenuName":"我的信息","MenuCode":"CF_ME","MenuUrl":"Manage/mydefault.aspx","IsMenu":"0"},{"MenuID":12,"MenuName":"系统模板","MenuCode":"CF_MD","MenuUrl":"Manage/Model/MyModel.aspx","IsMenu":"0"},{"MenuID":13,"MenuName":"员工管理","MenuCode":"CF_USERS","MenuUrl":"Manage/Users/MyUser.aspx","IsMenu":"1"},{"MenuID":14,"MenuName":"我要充值","MenuCode":"CF_Pay","MenuUrl":"Manage/Pay/Pay.aspx","IsMenu":"0"},{"MenuID":33,"MenuName":"4s信息管理","MenuCode":"CF_XXGL","MenuUrl":"Manage/edit4sInfo.aspx","IsMenu":"1"},{"MenuID":36,"MenuName":"系统菜单","MenuCode":"CF_Menu","MenuUrl":"Manage/Menu/Default.aspx","IsMenu":"0"}]}],"Rules":"Null"}]然后前台就可以通过JS解析来构建菜单了。

原文：http://blog.csdn.net/wdw984/article/details/39933679

weixin_39862669

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle存储过程解析json,存储过程解析表菜单并输出JSON格式字符串

表结构如下：SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[T_4s_Menu]([F_MenuID] [int] IDENTITY(1,1) NOT NULL,[F_MenuName] [nvarchar](50) NULL,[F_MenuUrl] [varchar](10...
复制链接

扫一扫