存储过程查询返回JSON序列导入到表（适用6.0.100json插件）
本帖最后由 咖啡里 于 2020-9-2 03:48 编辑活字格的功能越来越强大，前两天发布了内测版6.0.100，增加了不少强大的功能，其中JSON功能感觉不错，所以就测试的时候顺便玩玩。之前使用存储过程返回的结果集一直使用XML的方式再解析到表，由于XML所生成的字符传过长，查询结果过多时还明显感觉到速度会有所减弱，相比JSON返回的序列就简洁得多。按理应该有所提升（个人理解，没有测试过），当然下面的方法也是从sqlserver查询中取得XML再拼接生成的JSON返回来的序列，这种做法在数据库处理的速度上大大优于页面的处理。
存储过程
-- =============================================
-- Author: Lee lin
-- Create date: 2020-9-2 02:31:16
-- Description: json_text
-- =============================================
CREATE PROCEDURE Pm_json_text
(@jsontext NVARCHAR(MAX) OUT )
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString)'
SET @SQL = @SQL + 'AS('
SET @SQL = @SQL + 'SELECT AS AA, AS BB, AS CC FROM .'+ 'FOR XML RAW,TYPE,ELEMENTS'
SET @SQL = @SQL + ')'
SET @SQL = @SQL + 'SELECT @XML=FROM'
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString=CAST(@XML AS VARCHAR(MAX))
DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)
DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<row>'
DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</row>'
DECLARE @StartField VARCHAR(100);SET @StartField='<'
DECLARE @EndField VARCHAR(100);SET @EndField='>'
SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
SET @JSON=''
WHILE @RowStart>0
BEGIN
SET @RowStart=@RowStart+Len(@StartRoot)
SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
SET @JSON=@JSON+'{'
-- for each row
SET @FieldStart=CharIndex(@StartField,@Row,0)
WHILE @FieldStart>0
BEGIN
-- parse node key
SET @FieldStart=@FieldStart+Len(@StartField)
SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
SET @JSON=@JSON+'"'+@KEY+'":'
-- parse node value
SET @FieldStart=@FieldEnd+1
SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
SET @Value=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
SET @JSON=@JSON+'"'+@Value+'",'
SET @FieldStart=@FieldStart+Len(@StartField)
SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
SET @FieldStart=CharIndex(@StartField,@Row,@FieldEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
SET @JSON=@JSON+'},'
--/ for each row
SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
SET @JSON='['+@JSON+']'
SELECT @jsontext=@JSON
END
GO设置存储过程
设置json导入命令
几步就完成。看效果
再给有兴趣的格友一个思路：
楼主的方法很好，不过还有一点点缺点，那就是要到数据库后台里面去写一个存储过程，
一旦这个储存过程需要修改，那也必须要连到数据库后台。如果客户的项目不允许我们远程维护，那就比较麻烦了。
那有没有方法可以在用sql语法，让活字格可以得到任意的返回值呢？答案一定是可以的。
思路：
1）建立一个表，2列：sql方案名称，sql代码
2）用c#做一个服务器api，可以接收用活字格post发过来的sql代码，作为参数，然后在api中执行sql，得到返回值后，再回传给活字格。（sql的返回值有两个格式json和xml，如果是新版sql，建议大家直接用json了，老版本的好像只支持xml）
3）得到回传值后，如果是json的，可以直接利用楼主的方式。如果是xml的方式，需要自己
写js解析。
最后再写个页面，可以直接修改这个sql方案表。这样就可以变成一个万能sql数据存取解决方案了。 现在服务端命令可以执行存储过程，加上页面表格不绑定表和Json格式自动填充表格，综合起来感觉活字格应该可以一步到位处理存储过程返回结果集到表格了，或者出官方插件处理，这样不是更友好:lol dystar001 发表于 2020-9-2 23:27
再给有兴趣的格友一个思路：
楼主的方法很好，不过还有一点点缺点，那就是要到数据库后台里面去写一个存 ...
这个就是通用的存储过程。定义一个传入的参数，这个参数就是要查询表的SELECT拼接语句，只要期初把这个通用的存储过程放到数据库里面。随时要查哪个表就把这个表的SELECT拼接语句传进去即可，后期只要有权限调用这个存储过程就可以执行，不用每一个查询都写一次。屡试屡爽！
测试了一下高版本sql server自带的JSON功能更给力（2016后面的版本含2016支持）。建议大家还是使用高版本，整体速度以倍数增加。 牛皮啊~~~
流弊 本帖最后由 sz_xd 于 2020-9-3 11:07 编辑
非常感谢无私分享！:handshake
请问楼主：你附件中 json.bak 是怎么使用？烦请指教，谢谢！
sz_xd 发表于 2020-9-3 10:50
非常感谢无私分享！
请问楼主：你附件中 json.bak 是怎么使用？烦请指教，谢谢！
这个是sql2008的数据库完整备份，最低恢复到同一版本或更高版本即可！ 咖啡里 发表于 2020-9-3 03:52
测试了一下高版本sql server自带的JSON功能更给力（2016后面的版本含2016支持）。建议大家还是使用高版本， ...
SQL2106以下版本才用这个参数FOR XML，SQL2016以上的版本的可以直接使用FOR JOSN